diff options
| -rw-r--r-- | Changelog | 1 | ||||
| -rwxr-xr-x | LedgerSMB/CA.pm | 349 |
2 files changed, 237 insertions, 113 deletions
@@ -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; } |