From 6aaed579a01d95b634a06d6a0b4bf11ac61363b6 Mon Sep 17 00:00:00 2001
From: "Martin Helmling martin.helmling@octosoft.eu"
 <martin.helmling@octosoft.eu>
Date: Wed, 18 Jan 2017 17:24:58 +0100
Subject: [PATCH] Paginierung schneller machen:

SQL-Injection raus
---
 SL/WH.pm          | 27 ++++++++++++++++++++-------
 bin/mozilla/wh.pl | 37 ++++++++++++++++---------------------
 2 files changed, 36 insertions(+), 28 deletions(-)

diff --git a/SL/WH.pm b/SL/WH.pm
index 78a1442e5..dac6911e2 100644
--- a/SL/WH.pm
+++ b/SL/WH.pm
@@ -552,10 +552,18 @@ sub get_warehouse_journal {
     GROUP BY $group_clause
     ORDER BY r_${sort_spec}) AS lines WHERE r_qty>0|;
 
-  $query .= " LIMIT $filter{limit}"    if $filter{limit} ;
-  $query .= " OFFSET $filter{offset}"  if $filter{offset} ;
+  my @all_vars = (@filter_vars,@filter_vars,@filter_vars);
 
-  my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars, @filter_vars, @filter_vars);
+  if ($filter{limit}) {
+    $query .= " LIMIT ?";
+    push @all_vars,$filter{limit};
+  }
+  if ($filter{offset}) {
+    $query .= " OFFSET ?";
+    push @all_vars, $filter{offset};
+  }
+
+  my $sth = prepare_execute_query($form, $dbh, $query, @all_vars);
 
   my ($h_oe_id, $q_oe_id);
   if ($form->{l_oe_id}) {
@@ -824,10 +832,15 @@ sub get_warehouse_report {
       GROUP BY $group_clause
       ORDER BY $sort_spec ) AS lines WHERE qty<>0|;
 
-  $query .= " LIMIT $filter{limit}"    if $filter{limit} ;
-  $query .= " OFFSET $filter{offset}"  if $filter{offset} ;
-
-  my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars);
+  if ($filter{limit}) {
+    $query .= " LIMIT ?";
+    push @filter_vars,$filter{limit};
+  }
+  if ($filter{offset}) {
+    $query .= " OFFSET ?";
+    push @filter_vars, $filter{offset};
+  }
+  my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars );
 
   my (%non_empty_bins, @all_fields, @contents);
 
diff --git a/bin/mozilla/wh.pl b/bin/mozilla/wh.pl
index 5df5ac052..50bed75ce 100644
--- a/bin/mozilla/wh.pl
+++ b/bin/mozilla/wh.pl
@@ -593,22 +593,21 @@ sub generate_journal {
   }
   # /filter stuff
 
-  my $allrows = 0;
-  $allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
+  my $allrows        = !!($form->{report_generator_output_format} ne 'HTML') ;
 
   # manual paginating
-  my $pages = {};
-  my $page = $::form->{page} || 1;
-  $pages->{per_page}        = $::form->{per_page} || 15;
-  my $first_nr = ($page - 1) * $pages->{per_page};
-  my $last_nr  = $first_nr + $pages->{per_page};
+  my $pages          = {};
+  my $page           = $::form->{page} || 1;
+  $pages->{per_page} = $::form->{per_page} || 15;
+  my $first_nr       = ($page - 1) * $pages->{per_page};
+  my $last_nr        = $first_nr + $pages->{per_page};
 
   # no optimisation if qty op
   if ( !$allrows && $form->{maxrows} && !$filter{qty_op}) {
     $filter{limit}  = $pages->{per_page};
     $filter{offset} = ($page - 1) * $pages->{per_page};
-    $first_nr = 0;
-    $last_nr = $pages->{per_page};
+    $first_nr       = 0;
+    $last_nr        = $pages->{per_page};
   }
 
   my @contents  = WH->get_warehouse_journal(%filter);
@@ -696,9 +695,6 @@ sub generate_journal {
     $entry->{type_and_classific} = $::request->presenter->type_abbreviation($entry->{part_type}).
                                    $::request->presenter->classification_abbreviation($entry->{classification_id});
     $entry->{qty}        = $form->format_amount(\%myconfig, $entry->{qty});
-#    $entry->{qty}        = $form->format_amount_units('amount'     => $entry->{qty},
-#                                                      'part_unit'  => $entry->{partunit},
-#                                                      'conv_units' => 'convertible');
     $entry->{trans_type} = $locale->text($entry->{trans_type});
 
     my $row = { };
@@ -828,20 +824,19 @@ sub generate_report {
   $form->{report_generator_output_format} = 'HTML' if !$form->{report_generator_output_format};
 
   # manual paginating
-  my $allrows = 0;
-  $allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
-  my $page = $::form->{page} || 1;
-  my $pages = {};
-  $pages->{per_page}        = $::form->{per_page} || 20;
-  my $first_nr = ($page - 1) * $pages->{per_page};
-  my $last_nr  = $first_nr + $pages->{per_page};
+  my $allrows        = !!($form->{report_generator_output_format} ne 'HTML') ;
+  my $page           = $::form->{page} || 1;
+  my $pages          = {};
+  $pages->{per_page} = $::form->{per_page} || 20;
+  my $first_nr       = ($page - 1) * $pages->{per_page};
+  my $last_nr        = $first_nr + $pages->{per_page};
 
   # no optimisation if qty op
   if ( !$allrows && $form->{maxrows} && !$filter{qty_op}) {
     $filter{limit}  = $pages->{per_page};
     $filter{offset} = ($page - 1) * $pages->{per_page};
-    $first_nr = 0;
-    $last_nr = $pages->{per_page};
+    $first_nr       = 0;
+    $last_nr        = $pages->{per_page};
   }
 
   my @contents  = WH->get_warehouse_report(%filter);
-- 
2.20.1