From 2c265734667136036775e36b0f91de8679af19ce Mon Sep 17 00:00:00 2001 From: slef Date: Mon, 15 Dec 2003 10:53:47 +0000 Subject: [PATCH] DBI call fix for bug 662 --- C4/BookShelves.pm | 34 +++++++++++++++++----------------- C4/Koha.pm | 3 +-- C4/Stats.pm | 31 +++++++++++++++---------------- C4/Stock.pm | 6 ++---- 4 files changed, 35 insertions(+), 39 deletions(-) diff --git a/C4/BookShelves.pm b/C4/BookShelves.pm index b5144bdd54..d545ee200b 100755 --- a/C4/BookShelves.pm +++ b/C4/BookShelves.pm @@ -96,9 +96,9 @@ sub GetShelfList { $sth->execute; my %shelflist; while (my ($shelfnumber, $shelfname) = $sth->fetchrow) { - my $sti=$dbh->prepare("select count(*) from shelfcontents where shelfnumber=$shelfnumber"); + my $sti=$dbh->prepare("select count(*) from shelfcontents where shelfnumber=?"); # FIXME - Should there be an "order by" in here somewhere? - $sti->execute; + $sti->execute($shelfnumber); my ($count) = $sti->fetchrow; $shelflist{$shelfnumber}->{'shelfname'}=$shelfname; $shelflist{$shelfnumber}->{'count'}=$count; @@ -123,15 +123,13 @@ I don't know what C<$env> is. sub GetShelfContents { my ($env, $shelfnumber) = @_; my @itemlist; - my $sth=$dbh->prepare("select itemnumber from shelfcontents where shelfnumber=$shelfnumber order by itemnumber"); - $sth->execute; + my $sth=$dbh->prepare("select itemnumber from shelfcontents where shelfnumber=? order by itemnumber"); + $sth->execute($shelfnumber); while (my ($itemnumber) = $sth->fetchrow) { my ($item) = getiteminformation($env, $itemnumber, 0); push (@itemlist, $item); } return (\@itemlist); - # FIXME - Wouldn't it be more intuitive to return a list, - # rather than a reference-to-list? } =item AddToShelf @@ -177,8 +175,8 @@ C<$env> is ignored. #' sub RemoveFromShelf { my ($env, $itemnumber, $shelfnumber) = @_; - my $sth=$dbh->prepare("delete from shelfcontents where shelfnumber=$shelfnumber and itemnumber=$itemnumber"); - $sth->execute; + my $sth=$dbh->prepare("delete from shelfcontents where shelfnumber=? and itemnumber=?"); + $sth->execute($shelfnumber,$itemnumber); } =item AddShelf @@ -199,14 +197,13 @@ C<$env> is ignored. # as well? sub AddShelf { my ($env, $shelfname) = @_; - my $q_shelfname=$dbh->quote($shelfname); - my $sth=$dbh->prepare("select * from bookshelf where shelfname=$q_shelfname"); - $sth->execute; + my $sth=$dbh->prepare("select * from bookshelf where shelfname=?"); + $sth->execute($shelfname); if ($sth->rows) { return(1, "Shelf \"$shelfname\" already exists"); } else { - $sth=$dbh->prepare("insert into bookshelf (shelfname) values ($q_shelfname)"); - $sth->execute; + $sth=$dbh->prepare("insert into bookshelf (shelfname) values (?)"); + $sth->execute($shelfname); return (0, "Done"); } } @@ -228,14 +225,14 @@ C<$env> is ignored. #' sub RemoveShelf { my ($env, $shelfnumber) = @_; - my $sth=$dbh->prepare("select count(*) from shelfcontents where shelfnumber=$shelfnumber"); - $sth->execute; + my $sth=$dbh->prepare("select count(*) from shelfcontents where shelfnumber=?"); + $sth->execute($shelfnumber); my ($count)=$sth->fetchrow; if ($count) { return (1, "Shelf has $count items on it. Please remove all items before deleting this shelf."); } else { - $sth=$dbh->prepare("delete from bookshelf where shelfnumber=$shelfnumber"); - $sth->execute; + $sth=$dbh->prepare("delete from bookshelf where shelfnumber=?"); + $sth->execute($shelfnumber); return (0, "Done"); } } @@ -246,6 +243,9 @@ END { } # module clean-up code here (global destructor) # # $Log$ +# Revision 1.11 2003/12/15 10:57:08 slef +# DBI call fix for bug 662 +# # Revision 1.10 2003/02/05 10:05:02 acli # Converted a few SQL statements to use ? to fix a few strange SQL errors # Noted correct tab size diff --git a/C4/Koha.pm b/C4/Koha.pm index 94cb2f26b1..96d7d0cea6 100644 --- a/C4/Koha.pm +++ b/C4/Koha.pm @@ -191,8 +191,7 @@ sub getbranches { my $sth=$dbh->prepare("select * from branches"); $sth->execute; while (my $branch=$sth->fetchrow_hashref) { - my $query = "select categorycode from branchrelations where branchcode = ?"; - my $nsth = $dbh->prepare($query); + my $nsth = $dbh->prepare("select categorycode from branchrelations where branchcode = ?"); $nsth->execute($branch->{'branchcode'}); while (my ($cat) = $nsth->fetchrow_array) { # FIXME - This seems wrong. It ought to be diff --git a/C4/Stats.pm b/C4/Stats.pm index 780b47d345..8c03061358 100644 --- a/C4/Stats.pm +++ b/C4/Stats.pm @@ -93,10 +93,12 @@ sub TotalPaid { my $dbh = C4::Context->dbh; my $query="Select * from accountlines,borrowers where (accounttype = 'Pay' or accounttype ='W') and accountlines.borrowernumber = borrowers.borrowernumber"; + my @bind = (); if ($time eq 'today'){ $query .= " and date = now()"; } else { - $query.=" and date>='$time' and date<='$time2'"; + $query.=" and date>=? and date<=?"; + @bind = ($time,$time2); } # my $query="Select * from statistics,borrowers # where statistics.borrowernumber= borrowers.borrowernumber @@ -109,7 +111,7 @@ sub TotalPaid { $query.=" order by timestamp"; # print $query; my $sth=$dbh->prepare($query); - $sth->execute; + $sth->execute(@bind); my @results; my $i=0; while (my $data=$sth->fetchrow_hashref){ @@ -126,12 +128,12 @@ sub getcharges{ my($borrowerno,$timestamp)=@_; my $dbh = C4::Context->dbh; my $timestamp2=$timestamp-1; - my $query="Select * from accountlines where borrowernumber=$borrowerno - and timestamp = '$timestamp' and accounttype <> 'Pay' and - accounttype <> 'W'"; - my $sth=$dbh->prepare($query); + my $query=""; + my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=? + and timestamp = ? and accounttype <> 'Pay' and + accounttype <> 'W'"); # print $query,"
"; - $sth->execute; + $sth->execute($borrowerno,$timestamp); my $i=0; my @results; while (my $data=$sth->fetchrow_hashref){ @@ -147,9 +149,8 @@ sub getcharges{ sub Getpaidbranch{ my($date,$borrno)=@_; my $dbh = C4::Context->dbh; - my $query="select * from statistics where type='payment' and datetime >'$date' and borrowernumber='$borrno'"; - my $sth=$dbh->prepare($query); - $sth->execute; + my $sth=$dbh->prepare("select * from statistics where type='payment' and datetime >? and borrowernumber=?"); + $sth->execute($date,$borrno); # print $query; my $data=$sth->fetchrow_hashref; $sth->finish; @@ -161,7 +162,7 @@ sub Getpaidbranch{ # Otherwise, it needs a POD. sub unfilledreserves { my $dbh = C4::Context->dbh; - my $query="select *,biblio.title from reserves,reserveconstraints,biblio,borrowers,biblioitems where found <> 'F' and cancellationdate + my $sth=$dbh->prepare("select *,biblio.title from reserves,reserveconstraints,biblio,borrowers,biblioitems where found <> 'F' and cancellationdate is NULL and biblio.biblionumber=reserves.biblionumber and reserves.constrainttype='o' and (reserves.biblionumber=reserveconstraints.biblionumber @@ -169,8 +170,7 @@ sub unfilledreserves { and reserves.borrowernumber=borrowers.borrowernumber and biblioitems.biblioitemnumber=reserveconstraints.biblioitemnumber order by - biblio.title,reserves.reservedate"; - my $sth=$dbh->prepare($query); + biblio.title,reserves.reservedate"); $sth->execute; my $i=0; my @results; @@ -179,12 +179,11 @@ sub unfilledreserves { $i++; } $sth->finish; - $query="select *,biblio.title from reserves,biblio,borrowers where found <> 'F' and cancellationdate + $sth=$dbh->prepare("select *,biblio.title from reserves,biblio,borrowers where found <> 'F' and cancellationdate is NULL and biblio.biblionumber=reserves.biblionumber and reserves.constrainttype='a' and reserves.borrowernumber=borrowers.borrowernumber order by - biblio.title,reserves.reservedate"; - $sth=$dbh->prepare($query); + biblio.title,reserves.reservedate"); $sth->execute; while (my $data=$sth->fetchrow_hashref){ $results[$i]=$data; diff --git a/C4/Stock.pm b/C4/Stock.pm index 98f6f2ec2f..76e0a5531a 100644 --- a/C4/Stock.pm +++ b/C4/Stock.pm @@ -36,14 +36,12 @@ $VERSION = 0.01; sub stockreport { my $dbh = C4::Context->dbh; my @results; - my $query="Select count(*) from items where homebranch='C'"; - my $sth=$dbh->prepare($query); + my $sth=$dbh->prepare("Select count(*) from items where homebranch='C'"); $sth->execute; my $count=$sth->fetchrow_hashref; $results[0]->{'value'}="$count->{'count'}\t Levin"; $sth->finish; - $query="Select count(*) from items where homebranch='F'"; - $sth=$dbh->prepare($query); + $sth=$dbh->prepare("Select count(*) from items where homebranch='F'"); $sth->execute; $count=$sth->fetchrow_hashref; $results[1]->{'value'}="$count->{'count'}\t Foxton"; -- 2.39.5