diff options
| -rw-r--r-- | Changelog | 1 | ||||
| -rwxr-xr-x | LedgerSMB/BP.pm | 176 |
2 files changed, 105 insertions, 72 deletions
@@ -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; |