Finished moving IS.pm to new system

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

2 files changed, 270 insertions, 238 deletions

diff --git a/Changelog b/Changelog
index d6f4a969..aec572c4 100644
--- a/Changelog
+++ b/Changelog
@@ -10,9 +10,9 @@ Security:
 * Audited OE.pm, AA.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)
-* Audited CA.pm for SQL injection and moved to new API. (Chris T)
-* Audited CT.pm for SQL inection and moved to new API. (Chris T)
+* Audited BP.pm, CA.pm, CT.pm for SQL injection and moved to new API. (Chris T)
+* Audited IS.pm for SQL injection and moved to new API. (Chris T)
+
 
 Localization:
 * Moved localization files to standard codes (Seneca)
diff --git a/LedgerSMB/IS.pm b/LedgerSMB/IS.pm
index 7d495376..a7d69bf3 100755
--- a/LedgerSMB/IS.pm
+++ b/LedgerSMB/IS.pm
@@ -1698,294 +1698,326 @@ sub delete_invoice {
 		}
 	}
   
-  $dbh->commit;
+	$dbh->commit;
   
-  $rc;
+	$rc;
   
 }
 
 
 
 sub retrieve_invoice {
-  my ($self, $myconfig, $form) = @_;
+	my ($self, $myconfig, $form) = @_;
   
-  # connect to database
-  my $dbh = $form->dbconnect_noauto($myconfig);
-
-  my $query;
-
-  if ($form->{id}) {
-    # get default accounts and last invoice number
-    $query = qq|SELECT d.curr AS currencies
-		FROM defaults d|;
-  } else {
-    $query = qq|SELECT d.curr AS currencies, current_date AS transdate
-                FROM defaults d|;
-  }
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  my $ref = $sth->fetchrow_hashref(NAME_lc);
-  for (keys %$ref) { $form->{$_} = $ref->{$_} }
-  $sth->finish;
+	my $dbh = $form->{dbh};
+
+	my $query;
+
+	if ($form->{id}) {
+		# get default accounts and last invoice number
+		$query = qq|SELECT d.curr AS currencies FROM defaults d|;
+	} else {
+		$query = qq|
+			SELECT d.curr AS currencies, current_date AS transdate
+			  FROM defaults d|;
+	}
+	my $sth = $dbh->prepare($query);
+	$sth->execute || $form->dberror($query);
+
+	my $ref = $sth->fetchrow_hashref(NAME_lc);
+	for (keys %$ref) { $form->{$_} = $ref->{$_} }
+	$sth->finish;
 
   
-  if ($form->{id}) {
+	if ($form->{id}) {
     
-    # retrieve invoice
-    $query = qq|SELECT a.invnumber, a.ordnumber, a.quonumber,
-                a.transdate, a.paid,
-                a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes,
-		a.duedate, a.taxincluded, a.curr AS currency,
-		a.employee_id, e.name AS employee, a.till, a.customer_id,
-		a.language_code, a.ponumber
-		FROM ar a
-	        LEFT JOIN employee e ON (e.id = a.employee_id)
-		WHERE a.id = $form->{id}|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    $ref = $sth->fetchrow_hashref(NAME_lc);
-    for (keys %$ref) { $form->{$_} = $ref->{$_} }
-    $sth->finish;
-
-    # get shipto
-    $query = qq|SELECT * FROM shipto
-                WHERE trans_id = $form->{id}|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    $ref = $sth->fetchrow_hashref(NAME_lc);
-    for (keys %$ref) { $form->{$_} = $ref->{$_} }
-    $sth->finish;
-
-    # retrieve individual items
-    $query = qq|SELECT i.description, i.qty, i.fxsellprice, i.sellprice,
-		i.discount, i.parts_id AS id, i.unit, i.deliverydate,
-		i.project_id, pr.projectnumber, i.serialnumber, i.notes,
-		p.partnumber, p.assembly, p.bin,
-		pg.partsgroup, p.partsgroup_id, p.partnumber AS sku,
-		p.listprice, p.lastcost, p.weight, p.onhand,
-		p.inventory_accno_id, p.income_accno_id, p.expense_accno_id,
-		t.description AS partsgrouptranslation
-		FROM invoice i
-	        JOIN parts p ON (i.parts_id = p.id)
-	        LEFT JOIN project pr ON (i.project_id = pr.id)
-	        LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
-		LEFT JOIN translation t ON (t.trans_id = p.partsgroup_id AND t.language_code = '$form->{language_code}')
-		WHERE i.trans_id = $form->{id}
-		AND NOT i.assemblyitem = '1'
-		ORDER BY i.id|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    # foreign currency
-    &exchangerate_defaults($dbh, $form);
-
-    # query for price matrix
-    my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
+		# retrieve invoice
+		$query = qq|
+			   SELECT a.invnumber, a.ordnumber, a.quonumber,
+			          a.transdate, a.paid,
+			          a.shippingpoint, a.shipvia, a.terms, a.notes, 
+			          a.intnotes,
+			          a.duedate, a.taxincluded, a.curr AS currency,
+			          a.employee_id, e.name AS employee, a.till, 
+			          a.customer_id,
+			          a.language_code, a.ponumber
+			     FROM ar a
+			LEFT JOIN employee e ON (e.id = a.employee_id)
+			    WHERE a.id = ?|;
+
+		$sth = $dbh->prepare($query);
+		$sth->execute($form->{id}) || $form->dberror($query);
+
+		$ref = $sth->fetchrow_hashref(NAME_lc);
+		for (keys %$ref) { $form->{$_} = $ref->{$_} }
+		$sth->finish;
+
+		# get shipto
+		$query = qq|SELECT * FROM shipto WHERE trans_id = ?|;
+		$sth = $dbh->prepare($query);
+		$sth->execute($form->{id}) || $form->dberror($query);
+
+		$ref = $sth->fetchrow_hashref(NAME_lc);
+		for (keys %$ref) { $form->{$_} = $ref->{$_} }
+		$sth->finish;
+
+		# retrieve individual items
+		$query = qq|
+			   SELECT i.description, i.qty, i.fxsellprice, 
+			          i.sellprice, i.discount, i.parts_id AS id, 
+			          i.unit, i.deliverydate, i.project_id, 
+			          pr.projectnumber, i.serialnumber, i.notes,
+			          p.partnumber, p.assembly, p.bin,
+			          pg.partsgroup, p.partsgroup_id, 
+			          p.partnumber AS sku, p.listprice, p.lastcost,
+			          p.weight, p.onhand, p.inventory_accno_id, 
+			          p.income_accno_id, p.expense_accno_id,
+			          t.description AS partsgrouptranslation
+			     FROM invoice i
+		             JOIN parts p ON (i.parts_id = p.id)
+			LEFT JOIN project pr ON (i.project_id = pr.id)
+			LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+			LEFT JOIN translation t 
+			          ON (t.trans_id = p.partsgroup_id 
+			          AND t.language_code 
+			          = ?)
+			    WHERE i.trans_id = ?
+			          AND NOT i.assemblyitem = '1'
+			 ORDER BY i.id|;
+		$sth = $dbh->prepare($query);
+		$sth->execute($form->{language_code}, $form->{id}) 
+			|| $form->dberror($query);
+
+		# foreign currency
+		&exchangerate_defaults($dbh, $form);
+
+		# query for price matrix
+		my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
     
-    # taxes
-    $query = qq|SELECT c.accno
-		FROM chart c
-		JOIN partstax pt ON (pt.chart_id = c.id)
-		WHERE pt.parts_id = ?|;
-    my $tth = $dbh->prepare($query) || $form->dberror($query);
+		# taxes
+		$query = qq|
+			SELECT c.accno
+			  FROM chart c
+			  JOIN partstax pt ON (pt.chart_id = c.id)
+			 WHERE pt.parts_id = ?|;
+		my $tth = $dbh->prepare($query) || $form->dberror($query);
    
-    my $taxrate;
-    my $ptref;
+		my $taxrate;
+		my $ptref;
     
-    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
 
-      my ($dec) = ($ref->{fxsellprice} =~ /\.(\d+)/);
-      $dec = length $dec;
-      my $decimalplaces = ($dec > 2) ? $dec : 2;
+			my ($dec) = ($ref->{fxsellprice} =~ /\.(\d+)/);
+			$dec = length $dec;
+			my $decimalplaces = ($dec > 2) ? $dec : 2;
 
-      $tth->execute($ref->{id});
+			$tth->execute($ref->{id});
 
-      $ref->{taxaccounts} = "";
-      $taxrate = 0;
+ 			$ref->{taxaccounts} = "";
+			$taxrate = 0;
       
-      while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
-	$ref->{taxaccounts} .= "$ptref->{accno} ";
-	$taxrate += $form->{"$ptref->{accno}_rate"};
-      }
-      $tth->finish;
-      chop $ref->{taxaccounts};
-
-      # price matrix
-      $ref->{sellprice} = ($ref->{fxsellprice} * $form->{$form->{currency}});
-      PriceMatrix::price_matrix($pmh, $ref, $form->{transdate}, $decimalplaces, $form, $myconfig);
-      $ref->{sellprice} = $ref->{fxsellprice};
-
-      $ref->{partsgroup} = $ref->{partsgrouptranslation} if $ref->{partsgrouptranslation};
+			while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
+				$ref->{taxaccounts} .= "$ptref->{accno} ";
+				$taxrate += $form->{"$ptref->{accno}_rate"};
+			}
+			$tth->finish;
+			chop $ref->{taxaccounts};
+
+			# price matrix
+			$ref->{sellprice} = 
+				($ref->{fxsellprice} 
+					* $form->{$form->{currency}});
+			PriceMatrix::price_matrix(
+				$pmh, $ref, $form->{transdate}, $decimalplaces, 
+				$form, $myconfig);
+			$ref->{sellprice} = $ref->{fxsellprice};
+
+			$ref->{partsgroup} = $ref->{partsgrouptranslation} 
+				if $ref->{partsgrouptranslation};
       
-      push @{ $form->{invoice_details} }, $ref;
-    }
-    $sth->finish;
+			push @{ $form->{invoice_details} }, $ref;
+		}
+		$sth->finish;
 
-  }
+	}
 
-  my $rc = $dbh->commit;
-  $dbh->disconnect;
   
-  @queries = $form->run_custom_queries('ar', 'SELECT');
-  $form->{dbh}->commit;
-  $rc;
+	@queries = $form->run_custom_queries('ar', 'SELECT');
+	my $rc = $dbh->commit;
+	$rc;
 
 }
 
 
 sub retrieve_item {
-  my ($self, $myconfig, $form) = @_;
+	my ($self, $myconfig, $form) = @_;
   
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-
-  my $i = $form->{rowcount};
-  my $null;
-  my $var;
-
-  my $where = "WHERE p.obsolete = '0' AND NOT p.income_accno_id IS NULL";
-
-  if ($form->{"partnumber_$i"} ne "") {
-    $var = $form->like(lc $form->{"partnumber_$i"});
-    $where .= " AND lower(p.partnumber) LIKE '$var'";
-  }
-  if ($form->{"description_$i"} ne "") {
-    $var = $form->like(lc $form->{"description_$i"});
-    if ($form->{language_code} ne "") {
-      $where .= " AND lower(t1.description) LIKE '$var'";
-    } else {
-      $where .= " AND lower(p.description) LIKE '$var'";
-    }
-  }
-
-  if ($form->{"partsgroup_$i"} ne "") {
-    ($null, $var) = split /--/, $form->{"partsgroup_$i"};
-    $var *= 1;
-    if ($var == 0) {
-      # search by partsgroup, this is for the POS
-      $where .= qq| AND pg.partsgroup = '$form->{"partsgroup_$i"}'|;
-    } else {
-      $where .= qq| AND p.partsgroup_id = $var|;
-    }
-  }
-
-  if ($form->{"description_$i"} ne "") {
-    $where .= " ORDER BY 3";
-  } else {
-    $where .= " ORDER BY 2";
-  }
+	# connect to database
+	my $dbh = $form->dbconnect($myconfig);
+
+	my $i = $form->{rowcount};
+	my $null;
+	my $var;
+
+	my $where = "WHERE p.obsolete = '0' AND NOT p.income_accno_id IS NULL";
+
+	if ($form->{"partnumber_$i"} ne "") {
+		$var = $dbh->quote($form->like(lc $form->{"partnumber_$i"}));
+		$where .= " AND lower(p.partnumber) LIKE $var";
+	}
+	if ($form->{"description_$i"} ne "") {
+		$var = $dbh->quote($form->like(lc $form->{"description_$i"}));
+
+		if ($form->{language_code} ne "") {
+			$where .= " AND lower(t1.description) LIKE $var";
+		} else {
+			$where .= " AND lower(p.description) LIKE $var";
+		}
+	}
+
+	if ($form->{"partsgroup_$i"} ne "") {
+		($null, $var) = split /--/, $form->{"partsgroup_$i"};
+		$var = $dbh->quote($var);
+		if ($var == 0) {
+			# search by partsgroup, this is for the POS
+			$where .= qq| AND pg.partsgroup = |.
+				$dbh->quote($form->{"partsgroup_$i"});
+		} else {
+			$where .= qq| AND p.partsgroup_id = $var|;
+		}
+	}
+
+	if ($form->{"description_$i"} ne "") {
+		$where .= " ORDER BY 3";
+	} else {
+		$where .= " ORDER BY 2";
+	}
   
-  my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
-                 p.listprice, p.lastcost,
-		 p.unit, p.assembly, p.bin, p.onhand, p.notes,
-		 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id,
-		 pg.partsgroup, p.partsgroup_id, p.partnumber AS sku,
-		 p.weight,
-		 t1.description AS translation,
-		 t2.description AS grouptranslation
-                 FROM parts p
-		 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
-		 LEFT JOIN translation t1 ON (t1.trans_id = p.id AND t1.language_code = '$form->{language_code}')
-		 LEFT JOIN translation t2 ON (t2.trans_id = p.partsgroup_id AND t2.language_code = '$form->{language_code}')
+	my $query = qq|
+		   SELECT p.id, p.partnumber, p.description, p.sellprice,
+			  p.listprice, p.lastcost, p.unit, p.assembly, p.bin, 
+		          p.onhand, p.notes, p.inventory_accno_id, 
+		          p.income_accno_id, p.expense_accno_id, pg.partsgroup, 
+		          p.partsgroup_id, p.partnumber AS sku, p.weight,
+		          t1.description AS translation, 
+		          t2.description AS grouptranslation
+                     FROM parts p
+		LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
+		LEFT JOIN translation t1 
+		          ON (t1.trans_id = p.id AND t1.language_code = ?)
+		LEFT JOIN translation t2 
+		          ON (t2.trans_id = p.partsgroup_id 
+		          AND t2.language_code = ?)
 	         $where|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+	my $sth = $dbh->prepare($query);
+	$sth->execute($form->{language_code}, $form->{language_code}) 
+		|| $form->dberror($query);
 
-  my $ref;
-  my $ptref;
+	my $ref;
+	my $ptref;
 
-  # setup exchange rates
-  &exchangerate_defaults($dbh, $form);
+	# setup exchange rates
+	&exchangerate_defaults($dbh, $form);
   
-  # taxes
-  $query = qq|SELECT c.accno
-	      FROM chart c
-	      JOIN partstax pt ON (c.id = pt.chart_id)
-	      WHERE pt.parts_id = ?|;
-  my $tth = $dbh->prepare($query) || $form->dberror($query);
+	# taxes
+	$query = qq|
+		SELECT c.accno
+		  FROM chart c
+		  JOIN partstax pt ON (c.id = pt.chart_id)
+		 WHERE pt.parts_id = ?|;
+	my $tth = $dbh->prepare($query) || $form->dberror($query);
 
 
-  # price matrix
-  my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
+	# price matrix
+	my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
 
-  my $transdate = $form->datetonum($myconfig, $form->{transdate});
+	my $transdate = $form->datetonum($myconfig, $form->{transdate});
   
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
 
-    my ($dec) = ($ref->{sellprice} =~ /\.(\d+)/);
-    $dec = length $dec;
-    my $decimalplaces = ($dec > 2) ? $dec : 2;
+		my ($dec) = ($ref->{sellprice} =~ /\.(\d+)/);
+		$dec = length $dec;
+		my $decimalplaces = ($dec > 2) ? $dec : 2;
 
-    # get taxes for part
-    $tth->execute($ref->{id});
+		# get taxes for part
+		$tth->execute($ref->{id});
 
-    $ref->{taxaccounts} = "";
-    while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
-      $ref->{taxaccounts} .= "$ptref->{accno} ";
-    }
-    $tth->finish;
-    chop $ref->{taxaccounts};
+		$ref->{taxaccounts} = "";
 
-    # get matrix
-    PriceMatrix::price_matrix($pmh, $ref, $transdate, $decimalplaces, $form, $myconfig);
+		while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
+			$ref->{taxaccounts} .= "$ptref->{accno} ";
+		}
+		$tth->finish;
+		chop $ref->{taxaccounts};
 
-    $ref->{description} = $ref->{translation} if $ref->{translation};
-    $ref->{partsgroup} = $ref->{grouptranslation} if $ref->{grouptranslation};
+		# get matrix
+		PriceMatrix::price_matrix(
+			$pmh, $ref, $transdate, $decimalplaces, $form, 
+			$myconfig);
+
+		$ref->{description} = $ref->{translation} 
+			if $ref->{translation};
+
+		$ref->{partsgroup} = $ref->{grouptranslation} 
+			if $ref->{grouptranslation};
     
-    push @{ $form->{item_list} }, $ref;
+		push @{ $form->{item_list} }, $ref;
 
-  }
+	}
   
-  $sth->finish;
-  $dbh->disconnect;
+	$sth->finish;
+	$dbh->commit;
   
 }
 
 
 sub exchangerate_defaults {
-  my ($dbh, $form) = @_;
+	my ($dbh2, $form) = @_;
+	$dbh = $form->{dbh};
 
-  my $var;
+
+	my $var;
   
-  # get default currencies
-  my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
-  my $eth = $dbh->prepare($query) || $form->dberror($query);
-  $eth->execute;
-  ($form->{defaultcurrency}, $form->{currencies}) = $eth->fetchrow_array;
-  $eth->finish;
-
-  $query = qq|SELECT buy
-              FROM exchangerate
-	      WHERE curr = ?
-	      AND transdate = ?|;
-  my $eth1 = $dbh->prepare($query) || $form->dberror($query);
-
-  $query = qq~SELECT max(transdate || ' ' || buy || ' ' || curr)
-              FROM exchangerate
-	      WHERE curr = ?~;
-  my $eth2 = $dbh->prepare($query) || $form->dberror($query);
-
-  # get exchange rates for transdate or max
-  foreach $var (split /:/, substr($form->{currencies},4)) {
-    $eth1->execute($var, $form->{transdate});
-    ($form->{$var}) = $eth1->fetchrow_array;
-    if (! $form->{$var} ) {
-      $eth2->execute($var);
+	# get default currencies
+	my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
+	my $eth = $dbh->prepare($query) || $form->dberror($query);
+	$eth->execute;
+	($form->{defaultcurrency}, $form->{currencies}) = $eth->fetchrow_array;
+	$eth->finish;
+
+	$query = qq|
+		SELECT buy
+		  FROM exchangerate
+		 WHERE curr = ?
+		       AND transdate = ?|;
+	my $eth1 = $dbh->prepare($query) || $form->dberror($query);
+
+	$query = qq/
+		SELECT max(transdate || ' ' || buy || ' ' || curr)
+		  FROM exchangerate
+		 WHERE curr = ?/;
+	my $eth2 = $dbh->prepare($query) || $form->dberror($query);
+
+	# get exchange rates for transdate or max
+	foreach $var (split /:/, substr($form->{currencies},4)) {
+		$eth1->execute($var, $form->{transdate});
+		($form->{$var}) = $eth1->fetchrow_array;
+
+		if (! $form->{$var} ) {
+			$eth2->execute($var);
       
-      ($form->{$var}) = $eth2->fetchrow_array;
-      ($null, $form->{$var}) = split / /, $form->{$var};
-      $form->{$var} = 1 unless $form->{$var};
-      $eth2->finish;
-    }
-    $eth1->finish;
-  }
-
-  $form->{$form->{currency}} = $form->{exchangerate} if $form->{exchangerate};
-  $form->{$form->{currency}} ||= 1;
-  $form->{$form->{defaultcurrency}} = 1;
+			($form->{$var}) = $eth2->fetchrow_array;
+			($null, $form->{$var}) = split / /, $form->{$var};
+			$form->{$var} = 1 unless $form->{$var};
+			$eth2->finish;
+		}
+		$eth1->finish;
+	}
+
+	$form->{$form->{currency}} = $form->{exchangerate} 
+		if $form->{exchangerate};
+	$form->{$form->{currency}} ||= 1;
+	$form->{$form->{defaultcurrency}} = 1;
 
 }