From 2c265734667136036775e36b0f91de8679af19ce Mon Sep 17 00:00:00 2001
From: slef <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,"<br>";
-	$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