/ - Diff - projekt kivitendo - wissen

« Zurück | Weiter »

Revision 41592331

Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt

ID 4159233152d5e20dfb0e7ea67c9b5b2a45e45f68
Vorgänger 3dd73b25
Nachfolger 419b6f00

Umformatieren der Abfragen und Vermeidung von SQL injection durch Verwendung von parametrisierten Abfragen.

       for $i (1 .. $form->{rowcount}) {
         ($form->{"tax_id_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"};
         $query = qq|SELECT c.accno, t.taxkey, t.rate
                 FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
                 WHERE t.id=$form->{"tax_id_$i"}
                 ORDER BY c.accno|;
         $query =
           qq|SELECT c.accno, t.taxkey, t.rate | .
           qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
           qq|WHERE t.id = ? | .
           qq|ORDER BY c.accno|;
         $sth = $dbh->prepare($query);
         $sth->execute || $form->dberror($query);
         ($form->{AR_amounts}{"tax_$i"}, $form->{"taxkey_$i"}, $form->{"taxrate_$i"}) =
           $sth->fetchrow_array;
           selectrow_query($form, $dbh, $query, $form->{"tax_id_$i"});
         $form->{AR_amounts}{"tax_$i"}{taxkey}    = $form->{"taxkey_$i"};
         $form->{AR_amounts}{"amount_$i"}{taxkey} = $form->{"taxkey_$i"};
         $sth->finish;
         if ($form->{taxincluded} *= 1) {
           if (!$form->{"korrektur_$i"}) {
           $tax =
-...
       # if we have an id delete old records
       if ($form->{id}) {
         # delete detail records
         $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
         $dbh->do($query) || $form->dberror($query);
         $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
         do_query($form, $dbh, $query, $form->{id});
       } else {
         my $uid = rand() . time;
         $uid .= $form->{login};
         $uid = substr($uid, 2, 75);
         $query = qq|INSERT INTO ar (invnumber, employee_id)
                     VALUES ('$uid', $form->{employee_id})|;
         $dbh->do($query) || $form->dberror($query);
         $query = qq|SELECT a.id FROM ar a
                     WHERE a.invnumber = '$uid'|;
         $sth = $dbh->prepare($query);
         $sth->execute || $form->dberror($query);
         ($form->{id}) = $sth->fetchrow_array;
         $sth->finish;
         $query = qq|SELECT nextval('glid')|;
         ($form->{id}) = selectrow_query($form, $dbh, $query);
         $query = qq|INSERT INTO ar (id, invnumber, employee_id) VALUES (?, 'dummy', ?)|;
         do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
+      }
       # update department
       ($null, $form->{department_id}) = split(/--/, $form->{department});
       $form->{department_id} *= 1;
       # escape '
       map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber notes);
       # record last payment date in ar table
       $form->{datepaid} = $form->{transdate} unless $form->{datepaid};
       my $datepaid = ($form->{paid} != 0) ? qq|'$form->{datepaid}'| : 'NULL';
       $query = qq|UPDATE ar set
     	      invnumber = '$form->{invnumber}',
     	      ordnumber = '$form->{ordnumber}',
     	      transdate = '$form->{transdate}',
     	      customer_id = $form->{customer_id},
     	      taxincluded = '$form->{taxincluded}',
     	      amount = $form->{amount},
     	      duedate = '$form->{duedate}',
     	      paid = $form->{paid},
     	      datepaid = $datepaid,
     	      netamount = $form->{netamount},
     	      curr = '$form->{currency}',
     	      notes = '$form->{notes}',
     	      department_id = $form->{department_id},
     	      employee_id = $form->{employee_id}
     	      WHERE id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       my $datepaid = ($form->{paid} != 0) ? $form->{datepaid} : undef;
       $query =
         qq|UPDATE ar set | .
         qq|  invnumber = ?, ordnumber = ?, transdate = ?, customer_id = ?, | .
         qq|  taxincluded = ?, amount = ?, duedate = ?, paid = ?, datepaid = ?, | .
         qq|  netamount = ?, curr = ?, notes = ?, department_id = ?, | .
         qq|  employee_id = ? | .
         qq|WHERE id = ?|;
       my @values = ($form->{invnumber}, $form->{ordnumber},
                     conv_date($form->{transdate}), conv_i($form->{customer_id}),
                     $form->{taxincluded} ? 't' : 'f', $form->{amount},
                     conv_date($form->{duedate}), $form->{paid},
                     conv_date($datepaid), $form->{netamount},
                     $form->{currency}, $form->{notes},
                     conv_i($form->{department_id}),
                     conv_i($form->{employee_id}),
                     conv_i($form->{id}));
       do_query($form, $dbh, $query, @values);
       # amount for AR account
       $form->{receivables} = $form->round_amount($form->{amount}, 2) * -1;
-...
       # add individual transactions for AR, amount and taxes
       for $i (1 .. $form->{rowcount}) {
         if ($form->{"amount_$i"} != 0) {
           my $project_id = undef;
           $project_id = conv_i($form->{"project_id_$i"});
           my $project_id = conv_i($form->{"project_id_$i"});
           $taxkey = $form->{AR_amounts}{"amount_$i"}{taxkey};
           @values = ($project_id);
           # insert detail records in acc_trans
           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
                                              project_id, taxkey)
     		  VALUES ($form->{id}, (SELECT c.id FROM chart c
     		                        WHERE c.accno = '$form->{AR_amounts}{"amount_$i"}'),
     		  $form->{"amount_$i"}, '$form->{transdate}', ?, '$taxkey')|;
           $query =
             qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, | .
             qq|  project_id, taxkey) | .
             qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?)|;
           @values = (conv_i($form->{id}), conv_i($form->{AR_amounts}{"amount_$i"}),
                      conv_i($form->{"amount_$i"}), conv_date($form->{transdate}),
                      $project_id, conv_i($taxkey));
           do_query($form, $dbh, $query, @values);
           if ($form->{"tax_$i"} != 0) {
             @values = ($project_id);
           if ($form->{"tax_$i"} != 0) {
             # insert detail records in acc_trans
             $query =
               qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
                                               project_id, taxkey)
                         VALUES ($form->{id}, (SELECT c.id FROM chart c
                                               WHERE c.accno = '$form->{AR_amounts}{"tax_$i"}'),
                         $form->{"tax_$i"}, '$form->{transdate}', ?, '$taxkey')|;
               qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, | .
               qq|  project_id, taxkey) | .
               qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | .
               qq|  ?, ?, ?, ?)|;
             @values = (conv_i($form->{id}), conv_i($form->{AR_amounts}{"tax_$i"}),
                        conv_i($form->{"tax_$i"}), conv_date($form->{transdate}),
                        $project_id, conv_i($taxkey));
             do_query($form, $dbh, $query, @values);
+          }
+        }
+      }
       # add recievables
       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate)
                   VALUES ($form->{id}, (SELECT c.id FROM chart c
                                         WHERE c.accno = '$form->{AR_amounts}{receivables}'),
                   $form->{receivables}, '$form->{transdate}')|;
       $dbh->do($query) || $form->dberror($query);
       $query =
         qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) | .
         qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?)|;
       @values = (conv_i($form->{id}), conv_i($form->{AR_amounts}{receivables}),
                  conv_i($form->{receivables}), conv_date($form->{transdate}));
       do_query($form, $dbh, $query, @values);
       # add paid transactions
       for my $i (1 .. $form->{paidaccounts}) {
         if ($form->{"paid_$i"} != 0) {
           my $project_id = conv_i($form->{"paid_project_id_$i"});
           $form->{"AR_paid_$i"} =~ s/\"//g;
           ($form->{AR}{"paid_$i"}) = split(/--/, $form->{"AR_paid_$i"});
-...
             $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
           if ($form->{receivables} != 0) {
             # add receivable
             $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
     		    transdate, project_id)
     		    VALUES ($form->{id},
     		           (SELECT c.id FROM chart c
     			    WHERE c.accno = '$form->{AR}{receivables}'),
     		    $amount, '$form->{"datepaid_$i"}', ?)|;
             do_query($form, $dbh, $query, $project_id);
             $query =
               qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id) | .
               qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?)|;
             @values = (conv_i($form->{id}), conv_i($form->{AR}{receivables}),
                        $amount, conv_date($form->{"datepaid_$i"}), $project_id);
             do_query($form, $dbh, $query, @values);
+          }
           $form->{receivables} = $amount;
           $form->{"memo_$i"} =~ s/\'/\'\'/g;
           if ($form->{"paid_$i"} != 0) {
             my $project_id = conv_i($form->{"paid_project_id_$i"});
             # add payment
             $amount = $form->{"paid_$i"} * -1;
             $query  = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
     		    transdate, source, memo, project_id)
     		    VALUES ($form->{id},
     			   (SELECT c.id FROM chart c
     			    WHERE c.accno = '$form->{AR}{"paid_$i"}'),
     		    $amount, '$form->{"datepaid_$i"}',
     		    '$form->{"source_$i"}', '$form->{"memo_$i"}', ?)|;
             do_query($form, $dbh, $query, $project_id);
             $query  =
               qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id) | .
               qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?)|;
             @values = (conv_i($form->{id}), conv_i($form->{AR}{"paid_$i"}), $amount,
                        conv_date($form->{"datepaid_$i"}), $form->{"source_$i"},
                        $form->{"memo_$i"}, $project_id);
             do_query($form, $dbh, $query, @values);
             # exchangerate difference for payment
             $amount =
-...
 );
             if ($amount != 0) {
               $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
     		      transdate, fx_transaction, cleared, project_id)
     		      VALUES ($form->{id},
     			     (SELECT c.id FROM chart c
     			      WHERE c.accno = '$form->{AR}{"paid_$i"}'),
     		      $amount, '$form->{"datepaid_$i"}', '1', '0', ?)|;
               do_query($form, $dbh, $query, $project_id);
               $query =
                 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id) | .
                 qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, 't', 'f', ?)|;
               @values = (conv_i($form->{id}), conv_i($form->{AR}{"paid_$i"}),
                          $amount, conv_date($form->{"datepaid_$i"}), $project_id);
               do_query($form, $dbh, $query, @values);
+            }
             # exchangerate gain/loss
-...
             if ($amount != 0) {
               $accno =
                 ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
               $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
     		      transdate, fx_transaction, cleared, project_id)
     		      VALUES ($form->{id}, (SELECT c.id FROM chart c
     					    WHERE c.accno = '$accno'),
     		      $amount, '$form->{"datepaid_$i"}', '1', '0', ?)|;
               do_query($form, $dbh, $query, $project_id);
               $query =
                 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id) | .
                 qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, 't', 'f', ?)|;
               @values = (conv_i($form->{id}), $accno, $amount,
                          conv_date($form->{"datepaid_$i"}), $project_id);
               do_query($form, $dbh, $query, @values);
+            }
+          }
-...
 );
           $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
                                           WHERE c.accno = '$accno_ar') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
           $dbh->do($query) || $form->dberror($query);
           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
                       transdate, project_id)
                       VALUES ($form->{id}, (SELECT c.id FROM chart c
                                           WHERE c.accno = '$accno_ar'),
                       $amount, '$form->{"datepaid_$i"}', ?)|;
           do_query($form, $dbh, $query, $project_id);
           $query =
             qq|DELETE FROM acc_trans | .
             qq|WHERE trans_id = ? AND amount = ? AND transdate = ? AND | .
             qq|  chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?)|;
           @values = (conv_i($form->{id}), $amount,
                      conv_date($form->{"datepaid_$i"}), $accno_ar);
           do_query($form, $dbh, $query, @values);
           $query =
             qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id) | .
             qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?)|;
           @values = (conv_i($form->{id}), $accno_ar, conv_i($amount),
                      conv_date($form->{"datepaid_$i"}), $project_id);
           do_query($form, $dbh, $query, @values);
           # record payment
           $form->{"paid_$i"} *= -1;
           $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
                                           WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
           $dbh->do($query) || $form->dberror($query);
           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
                       source, memo, project_id)
                       VALUES ($form->{id}, (SELECT c.id FROM chart c
     		                      WHERE c.accno = '$accno'),
     		  $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
     		  '$form->{"source_$i"}', '$form->{"memo_$i"}', ?)|;
           do_query($form, $dbh, $query, $project_id);
           $query =
             qq|DELETE FROM acc_trans | .
             qq|WHERE trans_id = ? AND | .
             qq|  chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) AND | .
             qq|  amount = ? AND transdate = ? AND source = ? AND memo = ?|;
           @values = (conv_i($form->{id}), $accno, conv_i($form->{"paid_$i"}),
                      conv_date($form->{"datepaid_$i"}),
                      $form->{"source_$i"}, $form->{"memo_$i"});
           do_query($form, $dbh, $query, @values);
           $query =
             qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id) | .
             qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?)|;
           @values = (conv_i($form->{id}), $accno, conv_i($form->{"paid_$i"}),
                      conv_date($form->{"datepaid_$i"}),
                      $form->{"source_$i"}, $form->{"memo_$i"}, $project_id);
           do_query($form, $dbh, $query, @values);
           # gain/loss
           $amount =
-...
                $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
               ) != 0
             ) {
             $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
                                             WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
             $dbh->do($query) || $form->dberror($query);
             $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
     	            transdate, cleared, fx_transaction, project_id)
     		    VALUES ($form->{id},
     		           (SELECT c.id FROM chart c
     		            WHERE c.accno = '$accno'),
     		    $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', ?)|;
             do_query($form, $dbh, $query, $project_id);
             $query =
               qq|DELETE FROM acc_trans | .
               qq|WHERE trans_id = ? AND | .
               qq|  chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) AND | .
               qq|  amount = ? AND transdate = ? AND cleared = 'f' AND fx_transaction = 't'|;
             @values = (conv_i($form->{id}), $accno,
                        conv_i($form->{fx}{$accno}{$transdate}),
                        conv_date($transdate));
             do_query($form, $dbh, $query, @values);
             $query =
               qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, project_id) | .
               qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, 'f', 't', ?)|;
             @values = (conv_i($form->{id}), $accno,
                        conv_i($form->{fx}{$accno}{$transdate}),
                        conv_date($transdate), $project_id);
             do_query($form, $dbh, $query, @values);
+          }
+        }
+      }
       my $datepaid = ($form->{paid})    ? qq|'$form->{datepaid}'| : "NULL";
       my $datepaid = ($form->{paid}) ? $form->{datepaid} : "NULL";
       # save AR record
       my $query = qq|UPDATE ar set
                   paid = $form->{paid},
     	      datepaid = $datepaid
                   WHERE id=$form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       my $query =
         qq|UPDATE ar set paid = ?, datepaid = ? WHERE id = ?|;
       @values = (conv_i($form->{paid}), conv_date($datepaid), conv_i($form->{id}));
       do_query($form, $dbh, $query, @values);
       my $rc = $dbh->commit;
       $dbh->disconnect;
-...
       # connect to database, turn AutoCommit off
       my $dbh = $form->dbconnect_noauto($myconfig);
       my $query = qq|DELETE FROM ar WHERE id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       my $query = qq|DELETE FROM ar WHERE id = ?|;
       do_query($form, $dbh, $query, $form->{id});
       $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
       do_query($form, $dbh, $query, $form->{id});
       # commit
       my $rc = $dbh->commit;
-...
       my @values;
       my $query = qq|SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
                      a.duedate, a.netamount, a.amount, a.paid, c.name,
     		 a.invoice, a.datepaid, a.terms, a.notes, a.shipvia,
     		 a.shippingpoint, a.storno, a.globalproject_id,
                      pr.projectnumber AS globalprojectnumber,
     		 e.name AS employee
     	         FROM ar a
     	      JOIN customer c ON (a.customer_id = c.id)
     	      LEFT JOIN employee e ON (a.employee_id = e.id)
                      LEFT JOIN project pr ON a.globalproject_id = pr.id|;
       my $query =
         qq|SELECT a.id, a.invnumber, a.ordnumber, a.transdate, | .
         qq|  a.duedate, a.netamount, a.amount, a.paid, | .
         qq|  a.invoice, a.datepaid, a.terms, a.notes, a.shipvia, | .
         qq|  a.shippingpoint, a.storno, a.globalproject_id, | .
         qq|  pr.projectnumber AS globalprojectnumber, | .
         qq|  c.name, | .
         qq|  e.name AS employee | .
         qq|FROM ar a | .
         qq|JOIN customer c ON (a.customer_id = c.id) | .
         qq|LEFT JOIN employee e ON (a.employee_id = e.id) | .
         qq|LEFT JOIN project pr ON (a.globalproject_id = pr.id)|;
       my $where = "1 = 1";
       if ($form->{customer_id}) {
         $where .= " AND a.customer_id = $form->{customer_id}";
       } else {
         if ($form->{customer}) {
           my $customer = $form->like(lc $form->{customer});
           $where .= " AND lower(c.name) LIKE '$customer'";
+        }
         $where .= " AND a.customer_id = ?";
         push(@values, $form->{customer_id});
       } elsif ($form->{customer}) {
         $where .= " AND c.name ILIKE ?";
         push(@values, $form->like($form->{customer}));
+      }
       if ($form->{department}) {
         my ($null, $department_id) = split /--/, $form->{department};
         $where .= " AND a.department_id = $department_id";
+      }
       if ($form->{invnumber}) {
         my $invnumber = $form->like(lc $form->{invnumber});
         $where .= " AND lower(a.invnumber) LIKE '$invnumber'";
         $where .= " AND a.department_id = ?";
         push(@values, $department_id);
+      }
       if ($form->{ordnumber}) {
         my $ordnumber = $form->like(lc $form->{ordnumber});
         $where .= " AND lower(a.ordnumber) LIKE '$ordnumber'";
+      }
       if ($form->{notes}) {
         my $notes = $form->like(lc $form->{notes});
         $where .= " AND lower(a.notes) LIKE '$notes'";
       foreach my $column (qw(invnumber ordnumber notes)) {
         if ($form->{$column}) {
           $where .= " AND a.$column ILIKE ?";
           push(@values, $form->like($form->{$column}));
+        }
+      }
       if ($form->{"project_id"}) {
         $where .=
-...
         push(@values, $form->{"project_id"}, $form->{"project_id"});
+      }
       $where .= " AND a.transdate >= '$form->{transdatefrom}'"
         if $form->{transdatefrom};
       $where .= " AND a.transdate <= '$form->{transdateto}'"
         if $form->{transdateto};
       if ($form->{transdatefrom}) {
         $where .= " AND a.transdate >= ?";
         push(@values, $form->{transdatefrom});
+      }
       if ($form->{transdateto}) {
         $where .= " AND a.transdate <= ?";
         push(@values, $form->{transdateto});
+      }
       if ($form->{open} || $form->{closed}) {
         unless ($form->{open} && $form->{closed}) {
           $where .= " AND a.amount <> a.paid" if ($form->{open});
-...
       my @a = (transdate, invnumber, name);
       push @a, "employee" if $form->{l_employee};
       my $sortorder = join ', ', $form->sort_columns(@a);
       $sortorder = $form->{sort} if $form->{sort};
       my $sortorder = join(', ', @a);
       $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } @a));
       $query .= " WHERE $where
                  ORDER by $sortorder";
       $query .= " WHERE $where ORDER by $sortorder";
       my $sth = $dbh->prepare($query);
       $sth->execute(@values) ||
         $form->dberror($query . " (" . join(", ", @values) . ")");
       $form->{AR} = [];
       while (my $ar = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{AR} }, $ar;
+      }

Auch abrufbar als: Unified diff

Projekt

Allgemein

Profil

projekt kivitendo

Revision 41592331

Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt