commit 7a8d3060c7b9cc65d45c82f630730bb668c4f878
from: Jeremy Jackson <jeremy.jackson089@gmail.com>
date: Sat May  6 16:31:41 2023 UTC

WIP: Convery query statements to prepare statements.

commit - c4544fb9455479b09a80d29d1e7f29e649579830
commit + 7a8d3060c7b9cc65d45c82f630730bb668c4f878
blob - 066ee4c92f0b08502c0058975ca3f51e0253f469
blob + 3ceaf5438e064a2273785c9b764bfcb8a79786cf
--- alexandria/game.php
+++ alexandria/game.php
@@ -18,8 +18,13 @@ function confirm($i)
 
   $mysqli = new mysqli("localhost", $rpgc_db_username, $rpgc_db_password);
   $mysqli->select_db("games") or die ("Could not select database.\n");
-  $result = $mysqli->query("SELECT g.name, g.system, g.linkgid, g.blurb, g.sstatus, g.developer, g.jppub, g.jpyear, g.napub, g.nayear, g.palpub, g.palyear, g.directory, g.os, g.minCPU, g.recCPU, g.hd, g.ram, s.url, s.staff1, s.staff2, s.staff3, s.maint1, s.maint2, s.maint3 FROM games AS g, shrines AS s WHERE g.gid='$gid' AND (((g.sstatus='X' OR g.sstatus='A' OR g.sstatus='N') AND s.gid=0) OR ((g.sstatus != 'X' AND g.sstatus != 'A' AND g.sstatus != 'N') AND s.gid=g.gid))");
-  $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
+  $statement = $mysqli->prepare("SELECT g.name, g.system, g.linkgid, g.blurb, g.sstatus, g.developer, g.jppub, g.jpyear, g.napub, g.nayear, g.palpub, g.palyear, g.directory, g.os, g.minCPU, g.recCPU, g.hd, g.ram, s.url, s.staff1, s.staff2, s.staff3, s.maint1, s.maint2, s.maint3 FROM games AS g, shrines AS s WHERE g.gid=? AND (((g.sstatus='X' OR g.sstatus='A' OR g.sstatus='N') AND s.gid=0) OR ((g.sstatus != 'X' AND g.sstatus != 'A' AND g.sstatus != 'N') AND s.gid=g.gid))");
+
+  $statement->bind_param('s', $gid);
+  $statement->execute();
+  $result = $statement->get_result();
+  $row = $result->fetch_assoc();
+
   $name = stripslashes($row['name']);
   $shortsys = $row['system'];
   $sys = str_replace(' ', '_', $shortsys) . '/';
@@ -62,11 +67,16 @@ function confirm($i)
 <center><h2>
 <?php
    print "<font color=\"#FFFF00\">$name ($system)</h2></font>";
-   $result3 = $mysqli->query("SELECT name FROM games WHERE linkgid=$ourgid AND status='N'");
+   $statement3 = $mysqli->prepare("SELECT name FROM games WHERE linkgid=? AND status='N'");
+
+   $statement3->bind_param('s', $ourgid);
+   $statement3->execute();
+   $result3 = $statement3->get_result();
+   
    if (mysqli_num_rows($result3) > 0)
    {   print "<SMALL>(AKA";
        $first = true;
-       while ($row3 = mysqli_fetch_array($result3, MYSQLI_ASSOC))
+       while ($row3 = $result3->fetch_assoc())
        {  if (!$first) print ",";
           print " " . stripslashes($row3['name']);
           $first = false;
@@ -103,9 +113,13 @@ function confirm($i)
 
 <?php
   //do it all over again, this time for "alternate releases" which do NOT have their own pages.
-  $result4 = $mysqli->query("SELECT name, system, napub, nayear, jppub, jpyear, palpub, palyear, developer FROM games WHERE sstatus='X' AND status='L' AND linkgid='$gid'") or die("Could not get linked releases.");
+  $statement4 = $mysqli->prepare("SELECT name, system, napub, nayear, jppub, jpyear, palpub, palyear, developer FROM games WHERE sstatus='X' AND status='L' AND linkgid=?");
+  $statement4->bind_param('s', $gid);
+  $statement4->execute();
+  $result4 = $statement4->get_result() or die("Could not get linked releases.");
+  
   if (mysqli_num_rows($result4) >= 1) print '<P><CENTER><BIG><U>Alternate Releases:</U></BIG></CENTER></P>';
-  while ($row4 = mysqli_fetch_array($result4, MYSQLI_ASSOC)) {   ?>
+  while ($row4 = $result4->fetch_assoc()) {   ?>
 <P><table border=0 cellspacing=2 cellpadding=2 width="70%">
   <tr>
 	 <th colspan="4" background="/images/bgd.gif"><?php print stripslashes($row4['name']) . " (" . $row4['system'] . ")";?></td>
@@ -142,8 +156,13 @@ function confirm($i)
 <?php
    if ($sstatus == "N") //it's a collection of games
    {   print "<P>The games contained in this title are:<UL>";
-       $result4 = $mysqli->query("SELECT c.linkgid, g.name, g.system, g.sstatus, g.linkgid AS link FROM collections as c, games as g WHERE c.gid='$gid' AND c.linkgid=g.gid ORDER BY g.name");
-       while ($row4 = mysqli_fetch_array($result4, MYSQLI_ASSOC))
+       $statement4 = $mysqli->prepare("SELECT c.linkgid, g.name, g.system, g.sstatus, g.linkgid AS link FROM collections as c, games as g WHERE c.gid=? AND c.linkgid=g.gid ORDER BY g.name");
+
+       $statement4->bind_param('s', $gid);
+       $statement4->execute();
+       $result4 = $statement4->get_result();
+
+       while ($row4 = $result4->fetch_assoc())
        {   $linkedgid = $row4['linkgid'];
            if ($row4['sstatus'] == 'X') $linkedgid = $row4['link'];
            print "<LI><A HREF=game.php?game=$linkedgid>" . stripslashes($row4['name']) . " (" . $row4['system'] . ")</A>";
@@ -151,12 +170,17 @@ function confirm($i)
        print "</UL>";
    }
 
-   $query = "SELECT gid, name, system FROM games where (linkgid=$gid AND status='L' AND sstatus != 'X' AND gid != 0) OR (gid='$linkgid' AND gid != 0)";
-   $result3 = $mysqli->query($query);
+   $query = "SELECT gid, name, system FROM games where (linkgid=? AND status='L' AND sstatus != 'X' AND gid != 0) OR (gid=? AND gid != 0)";
+   $statement3 = $mysqli->prepare($query);
+
+   $statement3->bind_param('is', $gid, $linkgid);
+   $statement3->execute();
+   $result3 = $statement3->get_result();
+   
    if (mysqli_num_rows($result3) > 0)
    {   print "<P>See also:";
        $notFirstOne = false;
-       while ($row3 = mysqli_fetch_array($result3, MYSQLI_ASSOC))
+       while ($row3 = $result3->fetch_assoc())
        {   if ($notFirstOne) print ",";
            print " <A HREF=game.php?game=" . $row3['gid'] . ">" . stripslashes($row3['name']) . " (" . $row3['system'] . ")</A>";
            $notFirstOne = true;
@@ -189,12 +213,17 @@ function confirm($i)
    <td background="/images/slbg.gif"><b>Size</b></td>
 </tr>
 <?php
-      $result2 = $mysqli->query("SELECT * from faqs where gid=$gid ORDER BY title");
+      $statement2 = $mysqli->prepare("SELECT * from faqs where gid=? ORDER BY title");
+
+      $statement2->bind_param('i', $gid);
+      $statement2->execute();
+      $result2 = $statement2->get_result();
+
       if (mysqli_num_rows($result2) < 1)
       {   print "<TR><TD COLSPAN=4>No FAQs are available for this game.</TD></TR>";
       }
 	  else {
-      while ($row4 = mysqli_fetch_array($result2, MYSQLI_ASSOC))
+      while ($row4 = $result2->fetch_assoc())
       {   print "<tr><td><a href=\"http://alexandria.rpgclassics.com/$sys" . $directory . "/" . $row4['path'] . "\">" . $row4['title'] . "</a></td>";
           if ($row4['email'] != "") 
 		  {  print "<td>";
blob - 221752b584f5637f44b4e3d1b4ea2611bc12e8cc
blob + b2c5a5ab27d309640dfc07c18ddc66602bff06e2
--- alexandria/system.php
+++ alexandria/system.php
@@ -25,10 +25,15 @@ function getstatus($status)
 
   $mysqli = new mysqli("localhost", $rpgc_db_username, $rpgc_db_password);
   $mysqli->select_db("games") or die ("Could not select database.\n");
-  $result = $mysqli->query("SELECT g.gid, g.name, g.linkgid, g.sstatus, n.sstatus AS nickstatus, n.linkgid AS link2, l.sstatus AS nick2status FROM games as g, games as n, games as l WHERE (g.system='$system') AND (((g.sstatus='X' AND g.linkgid=n.gid) OR (g.sstatus != 'X' AND n.gid=0))) AND (((n.sstatus='X' AND n.linkgid=l.gid) OR (n.sstatus != 'X' AND l.gid=0))) ORDER BY g.name");
+  $statement = $mysqli->prepare("SELECT g.gid, g.name, g.linkgid, g.sstatus, n.sstatus AS nickstatus, n.linkgid AS link2, l.sstatus AS nick2status FROM games as g, games as n, games as l WHERE (g.system=?) AND (((g.sstatus='X' AND g.linkgid=n.gid) OR (g.sstatus != 'X' AND n.gid=0))) AND (((n.sstatus='X' AND n.linkgid=l.gid) OR (n.sstatus != 'X' AND l.gid=0))) ORDER BY g.name");
+
+  $statement->bind_param('s', $system);
+  $statement->execute();
+  $result = $statement->get_result();
+
   $resultarray = array();
   $i = 0;
-  while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
+  while ($row = $result->fetch_assoc())
   {  $gid = $row['gid'];
      $name = stripslashes($row['name']);
      $sstatus = $row['sstatus'];
blob - 7755c6a60393dd347ea27e39498f0560e33929a3
blob + 105055ad3398d7ecdfdf27dbbac24dd4304678cf
--- fanart/artist.php
+++ fanart/artist.php
@@ -8,17 +8,27 @@
    include('fanart-header.php');
    $mysqli = new mysqli("localhost", $rpgc_db_username, $rpgc_db_password);
    $mysqli->select_db("games");
-   $result = $mysqli->query("SELECT * FROM fa_artist where name=\"$name\"");
+
+   $statement = $mysqli->prepare("SELECT * FROM fa_artist where name=?");
+   $statement->bind_param('s', $name);
+   $statement->execute();
+   $result = $statement->get_result();
+
    if (!$result)
    {   print "The artist $name does not exist.";
        return;
    }
-   $row = mysqli_fetch_array($result);
+   $row = $result->fetch_assoc();
    print '<p align="center"><font face="Courier New" size="5" color="#FFFF00"><strong><u><b>' . $name . '</b></u></strong></font></p>';
    if ($row['email'] && $row['website']) print '<table align="center" width="80%" border="0" background="/images/misc/bg2.gif"><tr><td align="right">>>Send comments to <A HREF="mailto:' . $row['email'] . '">' . $name . '</A> or visit the artist\'s <A HREF=' . $row['website'] . '>homepage</A>.</td></tr></table>';
    elseif ($row['email']) print '<table align="center" width="80%" border="0" background="/images/misc/bg2.gif"><tr><td align="right">>>Send comments to <A HREF="mailto:' . $row['email'] . '">' . $name . '</A>.</td></tr></table>';
    elseif ($row['website']) print '<table align="center" width="80%" border="0" background="/images/misc/bg2.gif"><tr><td align="right">>>Visit the artist\'s <A HREF="' . $row['website'] . '">homepage</A>.</td></tr></table>';
-   $result = $mysqli->query("SELECT * from fa_picture WHERE artist=\"$name\" ORDER BY section, title");
+   
+   $statement = $mysqli->prepare("SELECT * from fa_picture WHERE artist=? ORDER BY section, title");
+   $statement->bind_param('s', $name);
+   $statement->execute();
+   $result = $statement->get_result();
+
    if (!$result) 
    {   print "Error in query for finding pictures.";
        return;
@@ -37,7 +47,7 @@
    $counter = 0;
    print '<P><table align="center" width="80%" border="1" CELLSPACING="1" CELLPADDING="1" background="/images/misc/bg2.gif">';
 
-   while ($row = mysqli_fetch_array($result))
+   while ($row = $result->fetch_assoc())
    {   if ($counter % 2 == 0)
 	   {   print '</tr><tr>';
 	   }
blob - 5130ae368ce2f3619b5dd3c61c47b750ebd2bd23
blob + 7399ed22c2b5a6988a7d942ea17fbebdd31e2278
--- fanart/section.php
+++ fanart/section.php
@@ -24,7 +24,12 @@
 	   {   $section = $keys[$i];
            print '<p align="center"><font face="Courier New" size="5" color="#FFFF00"><strong><u><b>' . $section. '</b></u></strong></font></p>';
            if (trim($text[$section])) print '<p><table align="center" width="80%" border="0" background="/images/misc/bg2.gif"><tr><td align="right">' . $text[$section] . '</td></tr></table>';
-		   $result = $mysqli->query("SELECT * FROM fa_picture, fa_artist where section='" . $section . "' and fa_picture.artist=fa_artist.name ORDER BY artist, title");
+		   $statement = $mysqli->prepare("SELECT * FROM fa_picture, fa_artist where section=? and fa_picture.artist=fa_artist.name ORDER BY artist, title");
+
+		   $statement->bind_param('s', $section);
+		   $statement->execute();
+		   $result = $statement->get_result();
+
 	       if (!$result)
 	       {   print "There was an error in retrieving pictures.";
 		       return;
@@ -36,7 +41,7 @@
 		   }
 		   $counter = 0;
 		   print '<P><table align="center" width="80%" border="1" CELLSPACING="1" CELLPADDING="1" background="/images/misc/bg2.gif">';
-		   while ($row = mysqli_fetch_array($result))
+		   while ($row = $result->fetch_assoc())
 		   {   if ($counter % 2 == 0 && $counter > 0)
 			   {   print '</tr><tr>';
 			   }
@@ -68,7 +73,12 @@
        if (trim($text)) print '<table align="center" width="80%" border="0" background="/images/misc/bg2.gif"><tr><td align="right">' . $text . '</td></tr></table>';
 	   $mysqli = new mysqli("localhost", $rpgc_db_username, $rpgc_db_password);
 	   $mysqli->select_db("games");
-	   $result = $mysqli->query("SELECT * FROM fa_picture, fa_artist where section=\"$section\" and fa_picture.artist=fa_artist.name ORDER BY artist, title");
+	   $statement = $mysqli->prepare("SELECT * FROM fa_picture, fa_artist where section=? and fa_picture.artist=fa_artist.name ORDER BY artist, title");
+
+	   $statement->bind_param('s', $section);
+	   $statement->execute();
+	   $result = $statement->get_result();
+
 	   if (!$result)
 	   {   print "There was an error in retrieving pictures.";
 		   return;
@@ -81,7 +91,7 @@
 	   $counter = 0;
 	   print '<P><table align="center" width="80%" border="1" CELLSPACING="1" CELLPADDING="1" background="/images/misc/bg2.gif">';
 
-	   while ($row = mysqli_fetch_array($result))
+	   while ($row = $result->fetch_assoc())
 	   {   if ($counter % 2 == 0 && $counter > 0)
 		   {   print '</tr><tr>';
 		   }