Audited CA.pm and moved to new API

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@329 4979c152-3d1c-0410-bac9-87ea11338e46

2 files changed, 237 insertions, 113 deletions

diff --git a/Changelog b/Changelog
index 8f372673..dad48efb 100644
--- a/Changelog
+++ b/Changelog
@@ -11,6 +11,7 @@ Security:
 * Forced edited files to have whitelisted extensions and no .. strings (Chris T)
 * Audited Form.pm for SQL-injection problems and move to new API (Chris T)
 * Audited BP.pm for SQL injection and moved to new API. (Chris T)
+* Audited CA.pm for SQL injection and moved to new API. (Chris T)
 
 Localization:
 * Moved localization files to standard codes (Seneca)
diff --git a/LedgerSMB/CA.pm b/LedgerSMB/CA.pm
index ca6eedb9..b92ac49d 100755
--- a/LedgerSMB/CA.pm
+++ b/LedgerSMB/CA.pm
@@ -42,12 +42,13 @@ sub all_accounts {
 
 	my $amount = ();
 	# connect to database
-	my $dbh = $form->dbconnect($myconfig);
+	my $dbh = $form->{dbh};
 
-	my $query = qq|SELECT accno, SUM(acc_trans.amount) AS amount
-					 FROM chart, acc_trans
-					WHERE chart.id = acc_trans.chart_id
-				 GROUP BY accno|;
+	my $query = qq|
+		   SELECT accno, SUM(acc_trans.amount) AS amount
+		     FROM chart, acc_trans
+		    WHERE chart.id = acc_trans.chart_id
+		 GROUP BY accno|;
 
 	my $sth = $dbh->prepare($query);
 	$sth->execute || $form->dberror($query);
@@ -58,8 +59,9 @@ sub all_accounts {
 	
 	$sth->finish;
 
-	$query = qq|SELECT accno, description
-				  FROM gifi|;
+	$query = qq|
+		SELECT accno, description
+		  FROM gifi|;
 
 	$sth = $dbh->prepare($query);
 	$sth->execute || $form->dberror($query);
@@ -72,10 +74,11 @@ sub all_accounts {
 
 	$sth->finish;
 
-	$query = qq|SELECT c.id, c.accno, c.description, c.charttype, 
-					   c.gifi_accno, c.category, c.link
-				  FROM chart c
-			  ORDER BY accno|;
+	$query = qq|
+		    SELECT c.id, c.accno, c.description, c.charttype, 
+		           c.gifi_accno, c.category, c.link
+		      FROM chart c
+		  ORDER BY accno|;
 
 	$sth = $dbh->prepare($query);
 	$sth->execute || $form->dberror($query);
@@ -94,7 +97,7 @@ sub all_accounts {
 	}
 
 	$sth->finish;
-	$dbh->disconnect;
+	$dbh->commit;
 
 }
 
@@ -104,21 +107,26 @@ sub all_transactions {
 	my ($self, $myconfig, $form) = @_;
 
 	# connect to database
-	my $dbh = $form->dbconnect($myconfig);
+	my $dbh = $form->{dbh};
 
 	# get chart_id
-	my $query = qq|SELECT id 
-					 FROM chart
-					WHERE accno = '$form->{accno}'|;
+	my $query = qq|
+		SELECT id 
+		  FROM chart
+		 WHERE accno = ?|;
+
+	my $accno = $form->{accno};
 
 	if ($form->{accounttype} eq 'gifi') {
-		$query = qq|SELECT id 
-					  FROM chart
-					 WHERE gifi_accno = '$form->{gifi_accno}'|;
+		$query = qq|
+			SELECT id 
+			  FROM chart
+			 WHERE gifi_accno = ?|;
+		$accno = $form->{gifi_accno};
 	}
 
 	my $sth = $dbh->prepare($query);
-	$sth->execute || $form->dberror($query);
+	$sth->execute($accno) || $form->dberror($query);
 
 	my @id = ();
 
@@ -133,16 +141,19 @@ sub all_transactions {
 
 	($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
 
+	my $fdate;
 	if ($form->{fromdate}) {
-		$fromdate_where = qq| AND ac.transdate >= '$form->{fromdate}' |;
+		$fromdate_where = qq| AND ac.transdate >= ? |;
+		$fdate = $form->{fromdate};
 	}
-
+	my $tdate;
 	if ($form->{todate}) {
-		$todate_where .= qq| AND ac.transdate <= '$form->{todate}' |;
+		$todate_where .= qq| AND ac.transdate <= ? |;
+		$tdate = $form->{todate};
 	}
 
 
-	my $false = ($myconfig->{dbdriver} =~ /Pg/) ? FALSE : q|'0'|;
+	my $false = 'FALSE';
 
 	# Oracle workaround, use ordinal positions
 	my %ordinal = ( transdate => 4,
@@ -160,34 +171,47 @@ sub all_transactions {
 
 	($null, $department_id) = split /--/, $form->{department};
 
+	my $d_id;
 	if ($department_id) {
 		$dpt_join = qq| JOIN department t ON (t.id = a.department_id) |;
-		$dpt_where = qq| AND t.id = $department_id |;
+		$dpt_where = qq| AND t.id = ? |;
+		$d_id = $department_id;
 	}
 
 
 	my $project;
 	my $project_id;
-
+	my $p_id;
 	if ($form->{projectnumber}) {
 		($null, $project_id) = split /--/, $form->{projectnumber};
-		$project = qq| AND ac.project_id = $project_id |;
+		$project = qq| AND ac.project_id = ? |;
+		$p_id = $project_id;
 	}
 
+	@queryargs = ();
+
 	if ($form->{accno} || $form->{gifi_accno}) {
 		# get category for account
-		$query = qq|SELECT description, category, link, contra
-					  FROM chart
-					 WHERE accno = '$form->{accno}'|;
+		$query = qq|
+			SELECT description, category, link, contra
+			  FROM chart
+			 WHERE accno = ?|;
 
+		$accno = $form->{accno};
 		if ($form->{accounttype} eq 'gifi') {
-			$query = qq|SELECT description, category, link, contra
-						  FROM chart
-						 WHERE gifi_accno = '$form->{gifi_accno}'
-						   AND charttype = 'A'|;
+			$query = qq|
+				SELECT description, category, link, contra
+				  FROM chart
+				 WHERE gifi_accno = ?
+				       AND charttype = 'A'|;
+			$accno = $form->{gifi_accno};
 		}
 
-		($form->{description}, $form->{category}, $form->{link}, $form->{contra}) = $dbh->selectrow_array($query);
+		$sth = $dbh->prepare($query);
+		$sth->execute($accno);
+		($form->{description}, $form->{category}, $form->{link}, 
+			$form->{contra})
+				 = $sth->fetchrow_array($query);
 
 		if ($form->{fromdate}) {
 
@@ -200,27 +224,58 @@ sub all_transactions {
 				for (qw(ar ap gl)) {
 
 					if ($form->{accounttype} eq 'gifi') {
-						$query = qq| $union
-									SELECT SUM(ac.amount)
-									  FROM acc_trans ac
-									  JOIN $_ a ON (a.id = ac.trans_id)
-									  JOIN chart c ON (ac.chart_id = c.id)
-									 WHERE c.gifi_accno = '$form->{gifi_accno}'
-									   AND ac.transdate < '$form->{fromdate}'
-									   AND a.department_id = $department_id
-									 $project |;
-
+						$query = qq| 
+							$union
+							SELECT SUM(ac.amount)
+							  FROM acc_trans ac
+							  JOIN $_ a 
+							       ON 
+							       (a.id = 
+							       ac.trans_id)
+							  JOIN chart c 
+							       ON 
+							       (ac.chart_id = 
+							       c.id)
+							 WHERE c.gifi_accno = ?
+							       AND ac.transdate 
+							       < ?
+							       AND 
+							       a.department_id 
+							       = ?
+								 $project |;
+
+						push @queryargs, 
+							$form->{gifi_accno},
+							$form->{fromdate},
+							$form->{department_id};
+						if ($p_id){
+							push @queryargs, $p_id;
+						}
 					} else {
 
-						$query .= qq| $union
-									 SELECT SUM(ac.amount)
-									   FROM acc_trans ac
-									   JOIN $_ a ON (a.id = ac.trans_id)
-									   JOIN chart c ON (ac.chart_id = c.id)
-									  WHERE c.accno = '$form->{accno}'
-										AND ac.transdate < '$form->{fromdate}'
-										AND a.department_id = $department_id
-									  $project |;
+						$query .= qq| 
+							$union
+							SELECT SUM(ac.amount)
+							  FROM acc_trans ac
+							  JOIN $_ a ON 
+							       (a.id = 
+							       ac.trans_id)
+							  JOIN chart c ON 
+							       (ac.chart_id = 
+							       c.id)
+							 WHERE c.accno = ?
+							       AND ac.transdate 
+							       < ?
+							       AND 
+							       a.department_id 
+							       = ?
+							       $project |;
+						push @queryargs, $form->{accno},
+							$form->{fromdate},
+							$department_id;
+						if ($p_id){
+							push @queryargs, $p_id;
+						}
 					}
 
 					$union = qq| UNION ALL |;
@@ -229,24 +284,41 @@ sub all_transactions {
 			} else {
 
 				if ($form->{accounttype} eq 'gifi') {
-					$query = qq|SELECT SUM(ac.amount)
-								  FROM acc_trans ac
-								  JOIN chart c ON (ac.chart_id = c.id)
-								 WHERE c.gifi_accno = '$form->{gifi_accno}'
-								   AND ac.transdate < '$form->{fromdate}'
-								$project |;
+					$query = qq|
+						SELECT SUM(ac.amount)
+						  FROM acc_trans ac
+						  JOIN chart c ON 
+						       (ac.chart_id = c.id)
+						 WHERE c.gifi_accno = ?
+						       AND ac.transdate < ?
+						$project |;
+					@queryargs = ($form->{gifi_accno}, 
+						$form->{fromdate});
+					if ($p_id){
+						push @query_ags, $p_id;
+					}
 				} else {
-					$query = qq|SELECT SUM(ac.amount)
-								  FROM acc_trans ac
-								  JOIN chart c ON (ac.chart_id = c.id)
-								 WHERE c.accno = '$form->{accno}'
-								   AND ac.transdate < '$form->{fromdate}'
-								$project |;
+					$query = qq|
+						SELECT SUM(ac.amount)
+						  FROM acc_trans ac
+						  JOIN chart c 
+						       ON (ac.chart_id = c.id)
+						 WHERE c.accno = ?
+						       AND ac.transdate < ?
+						$project |;
+					@queryargs = ($form->{accno}, 	
+						$form->{fromdate});
+					if ($p_id){
+						push @queryargs, $p_id;
+					}
 				}
 			}
 
-			($form->{balance}) = $dbh->selectrow_array($query);
-
+			$sth = $dbh->prepare($query);
+			$sth->execute(@queryargs);
+			($form->{balance}) = $sth->fetchrow_array($query);
+			$sth->finish;
+			@queryargs = ();
 		}
 	}
 
@@ -256,57 +328,108 @@ sub all_transactions {
 	foreach my $id (@id) {
 
 		# get all transactions
-		$query .= qq|$union
-					 SELECT a.id, a.reference, a.description, ac.transdate,
-							$false AS invoice, ac.amount, 'gl' as module, ac.cleared,
-							ac.source, '' AS till, ac.chart_id
-					   FROM gl a
-					   JOIN acc_trans ac ON (ac.trans_id = a.id)
-							$dpt_join
-					  WHERE ac.chart_id = $id
-							$fromdate_where
-							$todate_where
-							$dpt_where
-							$project
-
-				  UNION ALL
-
-					 SELECT a.id, a.invnumber, c.name, ac.transdate,
-							a.invoice, ac.amount, 'ar' as module, ac.cleared,
-							ac.source,
-							a.till, ac.chart_id
-					   FROM ar a
-					   JOIN acc_trans ac ON (ac.trans_id = a.id)
-					   JOIN customer c ON (a.customer_id = c.id)
-							$dpt_join
-					  WHERE ac.chart_id = $id
-							$fromdate_where
-							$todate_where
-							$dpt_where
-							$project
-
-				  UNION ALL
-
-					 SELECT a.id, a.invnumber, v.name, ac.transdate,
-							a.invoice, ac.amount, 'ap' as module, ac.cleared,
-							ac.source, a.till, ac.chart_id
-					   FROM ap a
-					   JOIN acc_trans ac ON (ac.trans_id = a.id)
-					   JOIN vendor v ON (a.vendor_id = v.id)
-							$dpt_join
-					  WHERE ac.chart_id = $id
-							$fromdate_where
-							$todate_where
-							$dpt_where
-							$project |;
+		$query .= qq|
+			$union
+			SELECT a.id, a.reference, a.description, ac.transdate,
+			       $false AS invoice, ac.amount, 'gl' as module, 
+			       ac.cleared, ac.source, '' AS till, ac.chart_id
+			  FROM gl a
+			  JOIN acc_trans ac ON (ac.trans_id = a.id)
+			$dpt_join
+			 WHERE ac.chart_id = ?
+			$fromdate_where
+			$todate_where
+			$dpt_where
+			$project|;
+		if ($d_id){
+			push @queryargs, $d_id;
+		}
+		push @queryargs, $id;
+		if ($fdate){
+			push @queryargs, $fdate;
+		}
+		if ($tdate){
+			push @queryargs, $tdate;
+		}
+		if ($d_id){
+			push @queryargs, $d_id;
+		}
+		if ($p_id){
+			push @queryargs, $p_id;
+		}
+		$query .= qq|
+
+			UNION ALL
+
+			SELECT a.id, a.invnumber, c.name, ac.transdate,
+			       a.invoice, ac.amount, 'ar' as module, ac.cleared,
+			       ac.source, a.till, ac.chart_id
+			  FROM ar a
+			  JOIN acc_trans ac ON (ac.trans_id = a.id)
+			  JOIN customer c ON (a.customer_id = c.id)
+			$dpt_join
+			 WHERE ac.chart_id = ?
+			$fromdate_where
+			$todate_where
+			$dpt_where
+			$project|;
+
+		if ($d_id){
+			push @queryargs, $d_id;
+		}
+		push @queryargs, $id;
+		if ($fdate){
+			push @queryargs, $fdate;
+		}
+		if ($tdate){
+			push @queryargs, $tdate;
+		}
+		if ($d_id){
+			push @queryargs, $d_id;
+		}
+		if ($p_id){
+			push @queryargs, $p_id;
+		}
 
+		$query .= qq|
+			 UNION ALL
+
+			SELECT a.id, a.invnumber, v.name, ac.transdate,
+			       a.invoice, ac.amount, 'ap' as module, ac.cleared,
+			       ac.source, a.till, ac.chart_id
+			  FROM ap a
+			  JOIN acc_trans ac ON (ac.trans_id = a.id)
+			  JOIN vendor v ON (a.vendor_id = v.id)
+			$dpt_join
+			 WHERE ac.chart_id = ?
+			$fromdate_where
+			$todate_where
+			$dpt_where
+			$project |;
+
+		if ($d_id){
+			push @queryargs, $d_id;
+		}
+		push @queryargs, $id;
+		if ($fdate){
+			push @queryargs, $fdate;
+		}
+		if ($tdate){
+			push @queryargs, $tdate;
+		}
+		if ($d_id){
+			push @queryargs, $d_id;
+		}
+		if ($p_id){
+			push @queryargs, $p_id;
+		}
 		$union = qq| UNION ALL |;
 	}
 
 	$query .= qq| ORDER BY $sortorder |;
 
 	$sth = $dbh->prepare($query);
-	$sth->execute || $form->dberror($query);
+	$sth->execute(@queryargs) || $form->dberror($query);
 
 	$query = qq|SELECT c.id, c.accno 
 				  FROM chart c
@@ -389,7 +512,7 @@ sub all_transactions {
 	}
 
 	$sth->finish;
-	$dbh->disconnect;
+	$dbh->commit;
 
 }