Attachment 168487 Details for Bug 37210 – Bug 37210: Properly escape SQL query parameters by using bind values

[patch] Bug 37210: Properly escape SQL query parameters by using bind values

Bug-37210-Properly-escape-SQL-query-parameters-by-.patch (text/plain), 4.33 KB, created by Julian Maurice on 2024-07-04 07:02:16 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Julian Maurice

Created: 2024-07-04 07:02:16 UTC

Size: 4.33 KB

patch

obsolete

>From cdcc645be28e33df4b82caffc2ef4c2c7d11ccf6 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
>
>---
> circ/overdue.pl | 53 ++++++++++++++++++++++++++++++-------------------
> 1 file changed, 33 insertions(+), 20 deletions(-)
>
>diff --git a/circ/overdue.pl b/circ/overdue.pl
>index e50bc15766..e8c1f80127 100755
>--- a/circ/overdue.pl
>+++ b/circ/overdue.pl
>@@ -223,13 +223,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,
>@@ -275,14 +268,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";
>@@ -293,11 +294,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;
>@@ -305,10 +321,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.30.2

Actions: View | Diff | Splinter Review

Attachments on bug 37210: 168197 | 168300 | 168372 | 168487 | 169182 | 169183 | 169929 | 169930