From bb374138781bbf3a368d104445aee887494aa381 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Wed, 21 Feb 2007 16:02:56 +0000 Subject: [PATCH] Vermeidung von SQL injection durch Verwendung parametrisierter Abfragen. --- SL/BP.pm | 184 ++++++++++++++++++++++++++++++------------------------- 1 file changed, 99 insertions(+), 85 deletions(-) diff --git a/SL/BP.pm b/SL/BP.pm index 266679575..559ea22bb 100644 --- a/SL/BP.pm +++ b/SL/BP.pm @@ -34,6 +34,8 @@ package BP; +use SL::DBUtils; + sub get_vc { $main::lxdebug->enter_sub(); @@ -51,36 +53,39 @@ sub get_vc { check => 'ap', receipt => 'ar'); - $query = qq|SELECT count(*) - FROM (SELECT DISTINCT ON (vc.id) vc.id - FROM $form->{vc} vc, $arap{$form->{type}} 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|; + my $vc = $form->{vc} eq "customer" ? "customer" : "vendor"; + my $arap_type = defined($arap{$form->{type}}) ? $arap{$form->{type}} : 'ar'; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - my ($count) = $sth->fetchrow_array; - $sth->finish; + $query = + qq|SELECT count(*) | . + qq|FROM (SELECT DISTINCT ON (vc.id) vc.id FROM $vc vc, $arap_type a, status s | . + qq| WHERE a.${vc}_id = vc.id AND s.trans_id = a.id AND s.formname = ? | . + qq| AND s.spoolfile IS NOT NULL) AS total|; + + $main::lxdebug->message(0, "kuh1 $query"); + + my ($count) = selectrow_query($form, $dbh, $query, $form->{type}); # build selection list if ($count < $myconfig->{vclimit}) { - $query = qq|SELECT DISTINCT ON (vc.id) vc.id, vc.name - FROM $form->{vc} vc, $arap{$form->{type}} 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|; - } - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $query = + qq|SELECT DISTINCT ON (vc.id) vc.id, vc.name | . + qq|FROM $vc vc, $arap_type a, status s | . + qq|WHERE a.${vc}_id = vc.id AND s.trans_id = a.id AND s.formname = ? | . + qq| AND s.spoolfile IS NOT NULL|; + + $sth = $dbh->prepare($query); + $sth->execute($form->{type}) || $form->dberror($query . " ($form->{type})"); + + $form->{"all_${vc}"} = []; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{"all_${vc}"} }, $ref; + } + $sth->finish; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{"all_$form->{vc}"} }, $ref; + $main::lxdebug->message(0, "kuh2 $query"); } - $sth->finish; $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -94,13 +99,14 @@ sub payment_accounts { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT DISTINCT ON (s.chart_id) c.accno, c.description - FROM status s, chart c - WHERE s.chart_id = c.id - AND s.formname = '$form->{type}'|; + my $query = + qq|SELECT DISTINCT ON (s.chart_id) c.accno, c.description | . + qq|FROM status s, chart c | . + qq|WHERE s.chart_id = c.id AND s.formname = ?|; my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth->execute($form->{type}) || $form->dberror($query . " ($form->{type})"); + $form->{accounts} = []; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{accounts} }, $ref; } @@ -119,27 +125,31 @@ sub get_spoolfiles { # connect to database my $dbh = $form->dbconnect($myconfig); - my ($query, $arap); + my ($query, $arap, @values); my $invnumber = "invnumber"; + my $vc = $form->{vc} eq "customer" ? "customer" : "vendor"; + if ($form->{type} eq 'check' || $form->{type} eq 'receipt') { $arap = ($form->{type} eq 'check') ? "ap" : "ar"; my ($accno) = split /--/, $form->{account}; - $query = qq|SELECT a.id, s.spoolfile, vc.name, ac.transdate, a.invnumber, - a.invoice, '$arap' AS module - FROM status s, chart c, $form->{vc} vc, $arap a, acc_trans ac - WHERE s.formname = '$form->{type}' - AND s.chart_id = c.id - AND c.accno = '$accno' - AND s.trans_id = a.id - AND a.$form->{vc}_id = vc.id - AND ac.trans_id = s.trans_id - AND ac.chart_id = c.id - AND NOT ac.fx_transaction|; - } else { + $query = + qq|SELECT a.id, s.spoolfile, vc.name, ac.transdate, a.invnumber, | . + qq| a.invoice, '$arap' AS module | . + qq|FROM status s, chart c, $vc vc, $arap a, acc_trans ac | . + qq|WHERE s.formname = ? | . + qq| AND s.chart_id = c.id | . + qq| AND c.accno = ? | . + qq| AND s.trans_id = a.id | . + qq| AND a.${vc}_id = vc.id | . + qq| AND ac.trans_id = s.trans_id | . + qq| AND ac.chart_id = c.id | . + qq| AND NOT ac.fx_transaction|; + @values = ($form->{type}, $accno); + } else { $arap = "ar"; my $invoice = "a.invoice"; @@ -149,51 +159,58 @@ sub get_spoolfiles { $invoice = '0'; } - $query = qq|SELECT a.id, a.$invnumber AS invnumber, a.ordnumber, - a.quonumber, a.transdate, $invoice AS invoice, - '$arap' AS module, vc.name, s.spoolfile - FROM $arap 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|SELECT a.id, a.$invnumber AS invnumber, a.ordnumber, a.quonumber, | . + qq| a.transdate, $invoice AS invoice, '$arap' AS module, vc.name, | . + qq| s.spoolfile | . + qq|FROM $arap a, ${vc} vc, status s | . + qq|WHERE s.trans_id = a.id | . + qq| AND s.spoolfile IS NOT NULL | . + qq| AND s.formname = ? | . + qq| AND a.${vc}_id = vc.id|; + @values = ($form->{type}); } - if ($form->{"$form->{vc}_id"}) { - $query .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|; - } else { - if ($form->{ $form->{vc} }) { - my $name = $form->like(lc $form->{ $form->{vc} }); - $query .= " AND lower(vc.name) LIKE '$name'"; - } + if ($form->{"${vc}_id"}) { + $query .= qq| AND a.${vc}_id = ?|; + push(@values, conv_i($form->{"${vc}_id"})); + } elsif ($form->{ $vc }) { + $query .= " AND vc.name ILIKE ?"; + push(@values, $form->like($form->{ $vc })); } - if ($form->{invnumber}) { - my $number = $form->like(lc $form->{invnumber}); - $query .= " AND lower(a.invnumber) LIKE '$number'"; - } - if ($form->{ordnumber}) { - my $ordnumber = $form->like(lc $form->{ordnumber}); - $query .= " AND lower(a.ordnumber) LIKE '$ordnumber'"; - } - if ($form->{quonumber}) { - my $quonumber = $form->like(lc $form->{quonumber}); - $query .= " AND lower(a.quonumber) LIKE '$quonumber'"; + foreach my $column (qw(invnumber ordnumber quonumber)) { + if ($form->{$column}) { + $query .= " AND a.$column ILIKE ?"; + push(@values, $form->like($form->{$column})); + } } if ($form->{type} =~ /(invoice|sales_order|sales_quotation|packing_list|puchase_order|request_quotation)$/) { - $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(@values, $form->{transdatefrom}); + } + if ($form->{transdateto}) { + $query .= " AND a.transdate <= ?"; + push(@values, $form->{transdateto}); + } } my @a = (transdate, $invnumber, name); my $sortorder = join ', ', $form->sort_columns(@a); - $sortorder = $form->{sort} if $form->{sort}; - $query .= " ORDER by $sortorder"; + if (grep({ $_ eq $form->{sort} } + qw(transdate invnumber ordnumber quonumber name))) { + $sortorder = $form->{sort}; + } + + $query .= " ORDER BY $sortorder"; my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth->execute(@values) || + $form->dberror($query . " (" . join(", ", @values) . ")"); + $form->{SPOOL} = []; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{SPOOL} }, $ref; } @@ -215,13 +232,11 @@ sub delete_spool { my $query; if ($form->{type} =~ /(check|receipt)/) { - $query = qq|DELETE FROM status - WHERE spoolfile = ?|; + $query = qq|DELETE FROM status WHERE spoolfile = ?|; } else { - $query = qq|UPDATE status SET - spoolfile = NULL, - printed = '1' - WHERE spoolfile = ?|; + $query = + qq|UPDATE status SET spoolfile = NULL, printed = '1' | . + qq|WHERE spoolfile = ?|; } my $sth = $dbh->prepare($query) || $form->dberror($query); @@ -238,9 +253,8 @@ sub delete_spool { if ($rc) { foreach my $i (1 .. $form->{rowcount}) { - $_ = qq|$spool/$form->{"spoolfile_$i"}|; if ($form->{"checked_$i"}) { - unlink; + unlink(qq|$spool/$form->{"spoolfile_$i"}|); } } } @@ -258,10 +272,9 @@ sub print_spool { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|UPDATE status SET - printed = '1' - WHERE formname = '$form->{type}' - AND spoolfile = ?|; + my $query = + qq|UPDATE status SET printed = '1' | . + qq|WHERE formname = ? AND spoolfile = ?|; my $sth = $dbh->prepare($query) || $form->dberror($query); foreach my $i (1 .. $form->{rowcount}) { @@ -279,7 +292,8 @@ sub print_spool { close(IN); close(OUT); - $sth->execute($form->{"spoolfile_$i"}) || $form->dberror($query); + $sth->execute($form->{type}, $form->{"spoolfile_$i"}) || + $form->dberror($query . " ($form->{type}, " . $form->{"spoolfile_$i"} . ")"); $sth->finish; } -- 2.20.1