From 11eaefaf930356bb4e77f52296179d5cee2fe394 Mon Sep 17 00:00:00 2001 From: Julian Maurice 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 Rebased-by: Martin Renvoize --- circ/overdue.pl | 61 ++++++++++++++++++++++++++----------------------- 1 file changed, 33 insertions(+), 28 deletions(-) diff --git a/circ/overdue.pl b/circ/overdue.pl index 38740fba22d..dacc8a9301c 100755 --- a/circ/overdue.pl +++ b/circ/overdue.pl @@ -231,14 +231,6 @@ if ($noreport) { $bornamefilter =~ s/\*/\%/g; $bornamefilter =~ s/\?/\_/g; - # Escape 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, borrowers.surname, @@ -284,22 +276,23 @@ 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"; @@ -308,11 +301,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 ) : ''; @@ -322,10 +330,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.45.2