From 221b90600418c2c750580ecd47f650fe070adef3 Mon Sep 17 00:00:00 2001
From: Julian Maurice <julian.maurice@biblibre.com>
Date: Tue, 2 Jul 2024 16:32:32 +0200
Subject: [PATCH] Bug 37210: Properly escape SQL query parameters by using bind
 values

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: wainuiwitikapark <wainuiwitikapark@catalyst.net.nz>
---
 circ/overdue.pl | 53 ++++++++++++++++++++++++++++++-------------------
 1 file changed, 33 insertions(+), 20 deletions(-)

diff --git a/circ/overdue.pl b/circ/overdue.pl
index 13dff28669..306d998f08 100755
--- a/circ/overdue.pl
+++ b/circ/overdue.pl
@@ -222,13 +222,6 @@ if ($noreport) {
 
     $bornamefilter =~s/\*/\%/g;
     $bornamefilter =~s/\?/\_/g;
-    #scape single quote
-    $bornamefilter =~s/'/\\'/g;
-    $itemtypefilter =~s/'/\\'/g;
-    $borcatfilter =~s/'/\\'/g;
-    $holdingbranchfilter =~s/'/\\'/g;
-    $homebranchfilter =~s/'/\\'/g;
-    $branchfilter =~s/'/\\'/g;
 
     my $strsth="SELECT date_due,
         borrowers.title as borrowertitle,
@@ -274,14 +267,22 @@ if ($noreport) {
     WHERE 1=1 "; # placeholder, since it is possible that none of the additional
                  # conditions will be selected by user
     $strsth.=" AND date_due               < '" . $todaysdate     . "' " unless ($showall or $datedueto);
-    $strsth.=" AND (borrowers.firstname like '".$bornamefilter."%' or borrowers.surname like '".$bornamefilter."%' or borrowers.cardnumber like '".$bornamefilter."%')" if($bornamefilter) ;
-    $strsth.=" AND borrowers.categorycode = '" . $borcatfilter   . "' " if $borcatfilter;
+    my @bind_values;
+    if ($bornamefilter) {
+        $strsth .= " AND (borrowers.firstname like ? or borrowers.surname like ? or borrowers.cardnumber like ?)";
+        push @bind_values, "$bornamefilter%", "$bornamefilter%", "$bornamefilter%";
+    }
+    if ($borcatfilter) {
+        $strsth .= " AND borrowers.categorycode = ? ";
+        push @bind_values, $borcatfilter;
+    }
     if( $itemtypefilter ){
         if( C4::Context->preference('item-level_itypes') ){
-            $strsth.=" AND items.itype   = '" . $itemtypefilter . "' ";
+            $strsth .= " AND items.itype   = ? ";
         } else {
-            $strsth.=" AND biblioitems.itemtype   = '" . $itemtypefilter . "' ";
+            $strsth .= " AND biblioitems.itemtype   = ? ";
         }
+        push @bind_values, $itemtypefilter;
     }
     if ( $borflagsfilter eq 'gonenoaddress' ) {
         $strsth .= " AND borrowers.gonenoaddress <> 0";
@@ -292,11 +293,26 @@ if ($noreport) {
     elsif ( $borflagsfilter eq 'lost') {
         $strsth .= " AND borrowers.lost <> 0";
     }
-    $strsth.=" AND borrowers.branchcode   = '" . $branchfilter   . "' " if $branchfilter;
-    $strsth.=" AND items.homebranch       = '" . $homebranchfilter . "' " if $homebranchfilter;
-    $strsth.=" AND items.holdingbranch    = '" . $holdingbranchfilter . "' " if $holdingbranchfilter;
-    $strsth.=" AND date_due >= ?" if $dateduefrom;
-    $strsth.=" AND date_due <= ?" if $datedueto;
+    if ($branchfilter) {
+        $strsth .= " AND borrowers.branchcode   = ? ";
+        push @bind_values, $branchfilter;
+    }
+    if ($homebranchfilter) {
+        $strsth .= " AND items.homebranch       = ? ";
+        push @bind_values, $homebranchfilter;
+    }
+    if ($holdingbranchfilter) {
+        $strsth .= " AND items.holdingbranch    = ? ";
+        push @bind_values, $holdingbranchfilter;
+    }
+    if ($dateduefrom) {
+        $strsth .= " AND date_due >= ?";
+        push @bind_values, DateTime::Format::MySQL->format_datetime($dateduefrom);
+    }
+    if ($datedueto) {
+        $strsth .= " AND date_due <= ?";
+        push @bind_values, DateTime::Format::MySQL->format_datetime($datedueto);
+    }
     # restrict patrons (borrowers) to those matching the patron attribute filter(s), if any
     my $bnlist = $have_pattr_filter_data ? join(',',keys %borrowernumber_to_attributes) : '';
     $strsth =~ s/WHERE 1=1/WHERE 1=1 AND borrowers.borrowernumber IN ($bnlist)/ if $bnlist;
@@ -304,10 +320,7 @@ if ($noreport) {
     $strsth.=" ORDER BY date_due, surname, firstname";
     $template->param(sql=>$strsth);
     my $sth=$dbh->prepare($strsth);
-    $sth->execute(
-        ($dateduefrom ? DateTime::Format::MySQL->format_datetime($dateduefrom) : ()),
-        ($datedueto ? DateTime::Format::MySQL->format_datetime($datedueto) : ()),
-    );
+    $sth->execute(@bind_values);
 
     my @overduedata;
     while (my $data = $sth->fetchrow_hashref) {
-- 
2.39.5