Moved BP.pm to new db framework

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

2 files changed, 105 insertions, 72 deletions

diff --git a/Changelog b/Changelog
index 46a3014d..8f372673 100644
--- a/Changelog
+++ b/Changelog
@@ -10,6 +10,7 @@ Security:
 * Audited OE.pm and AM.pm for SQL injection problems. (Chris T)
 * 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)
 
 Localization:
 * Moved localization files to standard codes (Seneca)
diff --git a/LedgerSMB/BP.pm b/LedgerSMB/BP.pm
index c69a3a92..0a084798 100755
--- a/LedgerSMB/BP.pm
+++ b/LedgerSMB/BP.pm
@@ -40,7 +40,7 @@ sub get_vc {
 	my ($self, $myconfig, $form) = @_;
 
 	# connect to database
-	my $dbh = $form->dbconnect($myconfig);
+	my $dbh = $form->{dbh};
 
 	my %arap = ( invoice => ['ar'],
 				 packing_list => ['oe', 'ar'],
@@ -60,39 +60,48 @@ sub get_vc {
 	my $n;
 	my $count;
 	my $item;
+	my $sth;
 
+	$item = $form->{dbh}->quote($item);
 	foreach $item (@{ $arap{$form->{type}} }) {
-		$query = qq|SELECT count(*)
-					  FROM (SELECT DISTINCT vc.id
-									   FROM $form->{vc} vc, $item a, status s
-									  WHERE a.$form->{vc}_id = vc.id
-										AND s.trans_id = a.id
-										AND s.formname = '$form->{type}'
-										AND s.spoolfile IS NOT NULL) AS total|;
-
-		($n) = $dbh->selectrow_array($query);
+		$query = qq|
+			SELECT count(*)
+			  FROM (SELECT DISTINCT vc.id
+				  FROM $form->{vc} vc, $item a, status s
+				 WHERE a.$form->{vc}_id = vc.id
+			               AND s.trans_id = a.id
+			               AND s.formname = ?
+			               AND s.spoolfile IS NOT NULL) AS total|;
+
+		$sth = $dbh->prepare($query);
+		$sth->execute($form->{type});
+		($n) = $sth->fetchrow_array($query);
 		$count += $n;
 	}
 
 	# build selection list
 	my $union = "";
 	$query = "";
+	my @queryargs = ();
 
 	if ($count < $myconfig->{vclimit}) {
 
 		foreach $item (@{ $arap{$form->{type}} }) {
-			$query .= qq| $union
-						 SELECT DISTINCT vc.id, vc.name
-									FROM $item a
-									JOIN $form->{vc} vc ON (a.$form->{vc}_id = vc.id)
-									JOIN status s ON (s.trans_id = a.id)
-								   WHERE s.formname = '$form->{type}'
-									 AND s.spoolfile IS NOT NULL|;
+			$query .= qq| 
+				$union
+				SELECT DISTINCT vc.id, vc.name
+				  FROM $item a
+				  JOIN $form->{vc} vc 
+				       ON (a.$form->{vc}_id = vc.id)
+				  JOIN status s ON (s.trans_id = a.id)
+				 WHERE s.formname = ?
+				       AND s.spoolfile IS NOT NULL|;
 			$union = "UNION";
+			push @queryags, $form->{type};
 		}
 
 		$sth = $dbh->prepare($query);
-		$sth->execute || $form->dberror($query);
+		$sth->execute(@queryargs) || $form->dberror($query);
 
 		while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 			push @{ $form->{"all_$form->{vc}"} }, $ref;
@@ -100,9 +109,9 @@ sub get_vc {
 
 		$sth->finish;
 	}
+	$dbh->{commit};
 
 	$form->all_years($myconfig, $dbh);
-	$dbh->disconnect;
 
 }
 
@@ -112,7 +121,7 @@ sub get_spoolfiles {
 	my ($self, $myconfig, $form) = @_;
 
 	# connect to database
-	my $dbh = $form->dbconnect($myconfig);
+	my $dbh = $form->{dbh};
 
 	my $query;
 	my $invnumber = "invnumber";
@@ -133,6 +142,7 @@ sub get_spoolfiles {
 
 	($form->{transdatefrom}, $form->{transdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
 
+	my @queryargs;
 	if ($form->{type} eq 'timecard') {
 		my $dateformat = $myconfig->{dateformat};
 		$dateformat =~ s/yy/yyyy/;
@@ -140,30 +150,40 @@ sub get_spoolfiles {
 
 		$invnumber = 'id';
 
-		$query = qq|SELECT j.id, e.name, j.id AS invnumber,
-						   to_char(j.checkedin, '$dateformat') AS transdate,
-						   '' AS ordnumber, '' AS quonumber, '0' AS invoice,
-						   '$arap{$form->{type}}[0]' AS module, s.spoolfile
-					  FROM jcitems j
-					  JOIN employee e ON (e.id = j.employee_id)
-					  JOIN status s ON (s.trans_id = j.id)
-					 WHERE s.formname = '$form->{type}'
-					   AND s.spoolfile IS NOT NULL|;
+		$query = qq|
+			SELECT j.id, e.name, j.id AS invnumber,
+			       to_char(j.checkedin, ?) AS transdate,
+			       '' AS ordnumber, '' AS quonumber, '0' AS invoice,
+			       '$arap{$form->{type}}[0]' AS module, s.spoolfile
+			  FROM jcitems j
+			  JOIN employee e ON (e.id = j.employee_id)
+			  JOIN status s ON (s.trans_id = j.id)
+			 WHERE s.formname = ?
+			       AND s.spoolfile IS NOT NULL|;
+		@queryargs = ($dateformat, $form->{type});
 
 		if ($form->{"$form->{vc}_id"}) {
-			$query .= qq| AND j.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
+			$query .= qq| AND j.$form->{vc}_id = ?|;
+			push(@queryargs, $form->{"$form->{vc}_id"});
 		} else {
 
 			if ($form->{$form->{vc}}) {
 				$item = $form->like(lc $form->{$form->{vc}});
-				$query .= " AND lower(e.name) LIKE '$item'";
+				$query .= " AND lower(e.name) LIKE ?";
+				push(@queryargs, $item);
 			}
 		}
 
-		$query .= " AND j.checkedin >= '$form->{transdatefrom}'" if $form->{transdatefrom};
-		$query .= " AND j.checkedin <= '$form->{transdateto}'" if $form->{transdateto};
-
+		if ($form->{transdatefrom}){
+			$query .= " AND j.checkedin >= ?";
+			push (@queryargs, $form->{transdatefrom});
+		}
+		if ($form->{transdateto}){
+			$query .= " AND j.checkedin <= ?";
+			 push (@queryargs, $form->{transdateto});
+		}
 	} else {
+		@queryargs = ();
 
 		foreach $item (@{ $arap{$form->{type}} }) {
 
@@ -175,44 +195,56 @@ sub get_spoolfiles {
 				$invoice = "'0'"; 
 			}
 
-			$query .= qq| $union
-						  SELECT a.id, vc.name, a.$invnumber AS invnumber, a.transdate,
-								 a.ordnumber, a.quonumber, $invoice AS invoice,
-								 '$item' AS module, s.spoolfile
-							FROM $item a, $form->{vc} vc, status s
-						   WHERE s.trans_id = a.id
-							 AND s.spoolfile IS NOT NULL
-							 AND s.formname = '$form->{type}'
-							 AND a.$form->{vc}_id = vc.id|;
-
+			$query .= qq| 
+				$union
+				SELECT a.id, vc.name, a.$invnumber AS invnumber, a.transdate,
+				       a.ordnumber, a.quonumber, $invoice AS invoice,
+				       ? AS module, s.spoolfile
+				  FROM $item a, $form->{vc} vc, status s
+				 WHERE s.trans_id = a.id
+				       AND s.spoolfile IS NOT NULL
+				       AND s.formname = ?
+				       AND a.$form->{vc}_id = vc.id|;
+
+			push (@queryargs, $item, $form->{type});
 			if ($form->{"$form->{vc}_id"}) {
 				$query .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
 			} else {
 
 				if ($form->{$form->{vc}} ne "") {
-					$item = $form->like(lc $form->{$form->{vc}});
-					$query .= " AND lower(vc.name) LIKE '$item'";
+					$item = $form->like(
+						lc $form->{$form->{vc}});
+					$query .= " AND lower(vc.name) LIKE ?";
+					push @queryargs, $item;
 				}
 			}
 
 			if ($form->{invnumber} ne "") {
 				$item = $form->like(lc $form->{invnumber});
-				$query .= " AND lower(a.invnumber) LIKE '$item'";
+				$query .= " AND lower(a.invnumber) LIKE ?";
+				push @queryargs, $item;
 			}
 
 			if ($form->{ordnumber} ne "") {
 				$item = $form->like(lc $form->{ordnumber});
-				$query .= " AND lower(a.ordnumber) LIKE '$item'";
+				$query .= " AND lower(a.ordnumber) LIKE ?";
+				push @queryargs, $item;
 			}
 
 			if ($form->{quonumber} ne "") {
 				$item = $form->like(lc $form->{quonumber});
-				$query .= " AND lower(a.quonumber) LIKE '$item'";
+				$query .= " AND lower(a.quonumber) LIKE ?";
+				push @queryargs, $item;
 			}
 
-			$query .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
-			$query .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
-
+			if ($form->{transdatefrom}){
+				$query .= " AND a.transdate >= ?";
+				push @queryargs, $form->{transdatefrom};
+			}
+			if ($form->{transdateto}){
+				$query .= " AND a.transdate <= ?";
+				push @queryargs, $form->{transdateto};
+			}
 			$union = "UNION";
 
 		}
@@ -230,14 +262,14 @@ sub get_spoolfiles {
 	$query .= " ORDER by $sortorder";
 
 	my $sth = $dbh->prepare($query);
-	$sth->execute || $form->dberror($query);
+	$sth->execute(@queryargs) || $form->dberror($query);
 
 	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 		push @{ $form->{SPOOL} }, $ref;
 	}
 
 	$sth->finish;
-	$dbh->disconnect;
+	$dbh->commit;
 
 }
 
@@ -247,14 +279,15 @@ sub delete_spool {
 	my ($self, $myconfig, $form) = @_;
 
 	# connect to database, turn AutoCommit off
-	my $dbh = $form->dbconnect_noauto($myconfig);
+	my $dbh = $form->{dbh};
 
 	my $query;
 	my %audittrail;
 
-	$query = qq|UPDATE status 
-				   SET spoolfile = NULL
-				 WHERE spoolfile = ?|;
+	$query = qq|
+		UPDATE status 
+		   SET spoolfile = NULL
+		 WHERE spoolfile = ?|;
 
 	my $sth = $dbh->prepare($query) || $form->dberror($query);
 
@@ -264,11 +297,12 @@ sub delete_spool {
 			$sth->execute($form->{"spoolfile_$i"}) || $form->dberror($query);
 			$sth->finish;
 
-			%audittrail = ( tablename  => $form->{module},
-							reference  => $form->{"reference_$i"},
-							formname   => $form->{type},
-							action     => 'dequeued',
-							id         => $form->{"id_$i"} );
+			%audittrail = ( 
+				tablename  => $form->{module},
+				reference  => $form->{"reference_$i"},
+				formname   => $form->{type},
+				action     => 'dequeued',
+				id         => $form->{"id_$i"} );
 
 			$form->audittrail($dbh, "", \%audittrail);
 		}
@@ -276,7 +310,6 @@ sub delete_spool {
 
 	# commit
 	my $rc = $dbh->commit;
-	$dbh->disconnect;
 
 	if ($rc) {
 		foreach my $i (1 .. $form->{rowcount}) {
@@ -296,7 +329,7 @@ sub print_spool {
 	my ($self, $myconfig, $form) = @_;
 
 	# connect to database
-	my $dbh = $form->dbconnect_noauto($myconfig);
+	my $dbh = $form->{dbh};
 
 	my %audittrail;
 
@@ -328,11 +361,12 @@ sub print_spool {
 			$sth->execute($form->{"spoolfile_$i"}) || $form->dberror($query);
 			$sth->finish;
 
-			%audittrail = ( tablename  => $form->{module},
-							reference  => $form->{"reference_$i"},
-							formname   => $form->{type},
-							action     => 'printed',
-							id         => $form->{"id_$i"} );
+			%audittrail = ( 
+				tablename  => $form->{module},
+				reference  => $form->{"reference_$i"},
+				formname   => $form->{type},
+				action     => 'printed',
+				id         => $form->{"id_$i"} );
 
 			$form->audittrail($dbh, "", \%audittrail);
 
@@ -340,10 +374,8 @@ sub print_spool {
 		}
 	}
 
-	$dbh->disconnect;
 
 }
 
-
 1;