Help converting over to PDO/Prepared Statements

Question

theundeadelvis 0 Newbie Poster

12 Years Ago

Hello! We have an older site that was recenlty scanned and shown to be vulnerable to cross-site scripting and SQl injection. It was suggested that we modify our code to use prepared statements/PDO. They even offered this as a sample snippit of code:

$user = 'xxxx';
$pass = 'xxxxx';

$dbh = new PDO('mysql:host=mysqlxxxxx.com;port=xxxx;dbname=Classroom', $user, $pass) or die("Could not connect.");
$stmt = $dbh->prepare("SELECT * FROM MainDB where ID = ?") or die("Could not prepare query");



if ($stmt->execute(array($_GET['room']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}
?>

However, I'm not sure how to even begin modifying our queries to take advantage of this? Could anyone suggest an example, or point me in the right direction? Here's is an example of one file I will need to modify:

#!/usr/local/bin/php


$Room = $_REQUEST['room'];

$Style = "<style>\n";
$Style .= ".MarginSet {margin: 0px;";
$Style .= "            padding: 0px;";
$Style .= "            list-style-position: inside;}\n";
$Style .= ".CatHead {font-size: 115%;\n";
$Style .= "            font-weight: bolder;}\n";
$Style .= "</style>\n\n";
?>
<?php require("../topper.php"); ?>

  <div id="centerwrap">
    <div id="content">
    <!-- Begin Main Text of Page -->
    <?php

    $dbQuery = "Select MainDB.SeatingCap, MainDB.ID, MainDB.Building, MainDB.Room, MainDB.ID, ";
    $dbQuery .= "BuildingDB.BuildingName, BuildingDB.BuildingAbv, MainDB.Wheelchair, ";
    $dbQuery .= "MainDB.Lighting, MainDB.AdditionalInfo, MainDB.Contact, ";
    $dbQuery .= "LightingControlsDB.LightingControlsID, LightingControlsDB.LightingType, MainDB.Windows, WindowDB.WindowID, ";
    $dbQuery .= "WindowDB.WindowTreatment, MainDB.FP";
    $dbQuery .= " from MainDB ";
    $dbQuery .= "Left JOIN BuildingDB on MainDB.Building = BuildingDB.BuildingID ";
    $dbQuery .= "Left JOIN LightingControlsDB on MainDB.Lighting = LightingControlsDB.LightingControlsID ";
    $dbQuery .= "Left JOIN WindowDB on MainDB.Windows = WindowDB.WindowID ";
    $dbQuery .= "where MainDB.ID = '" . stripslashes($Room) . "' ";

    $table = "Classroom";
    require("../db.php");

    $HumanRoom = mysql_result($results,$x,"BuildingDB.BuildingAbv"). mysql_result($results,$x,"Room");

    $InstAbr = mysql_result($results,$x,"BuildingDB.BuildingAbv");
    $InstRoom = mysql_result($results,$x,"Room");
    $Info = mysql_result($results,$x,"MainDB.AdditionalInfo");
    $Contact = mysql_result($results,$x,"MainDB.Contact");
    $FP = mysql_result($results,$x,"MainDB.FP");
    $dbQuery2 = "Select Room, Building, Abr, PDF from installed_rooms where Abr='" . $InstAbr ."' and Room='" . $InstRoom ."'";

    $table2 = "cts_general";
    require("../db2.php");

    if(mysql_Numrows($results2)>0)
    {
    $InstUrl = mysql_result($results2,0,"PDF");
    }

    echo "<a href='javascript:history.back();'>Return to Search Results</a>&nbsp;&nbsp;&nbsp;<a href='/~cts/roomdb/index.php'>New Search</a>";
    echo "<h2>" . mysql_result($results,$x,"BuildingDB.BuildingName") . " (" . mysql_result($results,$x,"BuildingDB.BuildingAbv");
    echo ") " . mysql_result($results,$x,"Room"). "</h2>\n";
    echo "<table>\n<tr style='vertical-align: top;'>\n";
    echo "<td width=505>";
    echo "<img src='rooms/" . $HumanRoom . "a.jpg' style='width: 500px; border: 5px solid #7d110c;' name='BigImg' ID='BigImg'>\n";
    echo "<br />\n";
//  echo "<a href=\"javascript:document.BigImg.src = 'rooms/" . $HumanRoom . "a.jpg '\">";
    echo "<img src='rooms/" . $HumanRoom . "at.jpg' style='border: 2px solid #7d110c; cursor: pointer;' onclick='document.BigImg.src = \"rooms/" . $HumanRoom . "a.jpg \"'>\n";
    echo "<img src='rooms/" . $HumanRoom . "bt.jpg' style='border: 2px solid #7d110c; cursor: pointer;' onclick='document.BigImg.src = \"rooms/" . $HumanRoom . "b.jpg \"'>\n";

    if($FP)
    {
        echo "<img src='rooms/" . $HumanRoom . "fpt.gif' style='border: 2px solid #7d110c; cursor: pointer;' onclick='document.BigImg.src = \"rooms/" . $HumanRoom . "fp.gif \"'>\n";
    }
    if($InstUrl != '')
    {
    echo "<a href='" . $InstUrl . "'>Classroom Usage Instructions</a>\n";
    }

    echo "<br /><br />\n";

    echo "<span class='CatHead'>Contact Information</span><br />\n";
    echo "<span margin-right: 200px;>For more information about this room please contact: " . $Contact;
    echo "</span>\n";
    echo "</td>\n";
//------------------End Left Margin------------>

//------------------Begin Right Margin ---------------->
    echo "<td style='padding-left: 10px;'>";
    echo "<span class='CatHead'>Physical Specification</span><br />\n";
    echo "Seating Capacity: " . mysql_result($results,$x,"MainDB.SeatingCap") . "<br />\n";
    echo "Lights: " . mysql_result($results,$x,"LightingControlsDB.LightingType") . "<br />\n";
    echo mysql_result($results,$x,"WindowDB.WindowTreatment") . "<br />\n";

//<-------------------------------  Chalkboard Info

        $dbQuery2 = "Select Width, Height, Position from ChalkboardDB where MainDBID = " . $Room;   

//      print $dbQuery2;

        $table2 = "Classroom";
        require("../db2.php");

        while (mysql_Numrows($results2)>$y)                            //if there are records in the fields
        {
        echo "Chalkboard:  ";

        echo mysql_result($results2,$y,"Position") . ": " . mysql_result($results2,$y,"Width") . " foot wide<br />\n";   
        $y++;
        }

// <---------------------------------  End of chalkboard Info

    echo "</div>";
    echo "<br /><ul class='MarginSet'><span class='CatHead'>Technical Specifications</span><br />\n";

//<-------------------------------  Projection Screen Info

        $dbQuery2 = "Select DDP, Location, Width from ProjectionScreenDB where MainDBID = " . $Room;    

//      print $dbQuery2;

        $table2 = "Classroom";
        require("../db2.php");

        while (mysql_Numrows($results2)>$y)                            //if there are records in the fields
        {
        if(mysql_result($results2,$y,"DDP") == 1)
        {
        echo "Data Projector<br />\n";
        echo "Screen: ";
        }
        else
        {
        echo "Screen: "; 
        }
        echo mysql_result($results2,$y,"Location") . " " . mysql_result($results2,$y,"Width") . " foot wide<br />\n";    

//      echo "Projections Screens : " . mysql_result($results2,$y,"Location"). "<br />\n";
        $y++;
        }

// <---------------------------------  End of screen Info

    $dbQuery = "Select AmenityMainLink.MainDBID, AmenityMainLink.AmenityID, AmenityDB.Amenity, AmenityDB.Description, AmenityDB.MOrder";
    $dbQuery .= " from AmenityDB ";
    $dbQuery .= "Left JOIN AmenityMainLink on AmenityMainLink.AmenityID = AmenityDB.AmenityID "; 
    $dbQuery .= "where AmenityMainLink.MainDBID = '" . $Room . "' ";
    $dbQuery .= " order by AmenityDB.MOrder";
    $table = "Classroom";
    require("../db.php");

    while (mysql_Numrows($results)>$x) 
    { 

    echo "<li>" . mysql_result($results,$x,"AmenityDB.Amenity") . "</li>\n";

    $x++;
    }

    ?>
    <li>Overhead Transparency Projector</li>
    <li>Wireless Network Access in room</li>
    <li>Active Network Jack</li>
    </ul>

    <?
    if ($Info)
    {
    echo "<br /><span class='CatHead' style='margin: 10px;'>Room Notes:</span><br/>\n";
    echo $Info;
    }
    ?>
    </td>
    </tr>
    </table>
    <!-- End Main Text of Page -->   
    </div>
    <!-- end content -->
    <!--stopindex-->

<?php require("../footer.php"); ?>

</body>
</html>

Thanks in advanace!

php

2 Contributors
8 Replies
194 Views
22 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by Squidge

Squidge 101 Newbie Poster

12 Years Ago

As a strating point

$dbQuery = "Select MainDB.SeatingCap, MainDB.ID, MainDB.Building, MainDB.Room, MainDB.ID, ";
$dbQuery .= "BuildingDB.BuildingName, BuildingDB.BuildingAbv, MainDB.Wheelchair, ";
$dbQuery .= "MainDB.Lighting, MainDB.AdditionalInfo, MainDB.Contact, ";
$dbQuery .= "LightingControlsDB.LightingControlsID, LightingControlsDB.LightingType, MainDB.Windows, WindowDB.WindowID, ";
$dbQuery .= "WindowDB.WindowTreatment, MainDB.FP";
$dbQuery .= " from MainDB ";
$dbQuery .= "Left JOIN BuildingDB on MainDB.Building = BuildingDB.BuildingID ";
$dbQuery .= "Left JOIN LightingControlsDB on MainDB.Lighting = LightingControlsDB.LightingControlsID ";
$dbQuery .= "Left JOIN WindowDB on MainDB.Windows = WindowDB.WindowID ";
$dbQuery .= "where MainDB.ID = '" . stripslashes($Room) . "' ";

Can still be used:

$stmt = $dbh->prepare($dbQuery);
$stmt->execute();

Squidge 101 Newbie Poster

12 Years Ago

I am still learning as well, bought a book to help me a long the way. It did look to be a very steep learning curve but i am slowly getting my head around it. If you need more assistance please let me know.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

theundeadelvis 0 Newbie Poster · Answer 1 · 2012-09-05T19:29:52+00:00

Thanks so much for the quick reply! I thought that I would still be using dbQuery lines, but this is all new to me, so I wasn't sure. I'll play around with this and see what I can make happen.

theundeadelvis 0 Newbie Poster · Answer 2 · 2012-09-05T20:05:43+00:00

Thank you, and I apologize if I'm missing the obivous, I'm really having trouble wrapping my brain around this. Am I at all close to implementing this correctly:

#!/usr/local/bin/php
<?php
$PageTitle = "Test";
$SubHeader = "Test";

$user = 'xxxx';
$pass = 'xxxxxx';

    $dbQuery = "Select MainDB.SeatingCap, MainDB.ID, MainDB.Building, MainDB.Room, MainDB.ID, ";
    $dbQuery .= "BuildingDB.BuildingName, BuildingDB.BuildingAbv, MainDB.Wheelchair, ";
    $dbQuery .= "MainDB.Lighting, MainDB.AdditionalInfo, MainDB.Contact, ";
    $dbQuery .= "LightingControlsDB.LightingControlsID, LightingControlsDB.LightingType, MainDB.Windows, WindowDB.WindowID, ";
    $dbQuery .= "WindowDB.WindowTreatment, MainDB.FP";
    $dbQuery .= " from MainDB ";
    $dbQuery .= "Left JOIN BuildingDB on MainDB.Building = BuildingDB.BuildingID ";
    $dbQuery .= "Left JOIN LightingControlsDB on MainDB.Lighting = LightingControlsDB.LightingControlsID ";
    $dbQuery .= "Left JOIN WindowDB on MainDB.Windows = WindowDB.WindowID ";
    $dbQuery .= "where MainDB.ID = '" . stripslashes($Room) . "' ";

$dbh = new PDO('mysql:host=mysqlxxxxxxxedu;port=xxxx;dbname=Classroom', $user, $pass) or die("Could not connect.");
$stmt = $dbh->prepare("SELECT * FROM MainDB where ID = ?") or die("Could not prepare query");
if ($stmt->execute(array($_GET['room']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}
?>

Thanks Again!

Squidge 101 Newbie Poster · Answer 3 · 2012-09-05T20:25:06+00:00

This is a changed line 20 onwards. If you are not using $dbQuery, i would suggest removing it

$dbh = new PDO('mysql:host=localhost;dbname=Classroom',$user,$pass);

$stmt = $dbh->prepare("SELECT * FROM MainDB where ID=:id");
$stmt->bindParam(':id', $_GET['room']);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);

while($row = $stmt->fetch())
{
    print_r($row);
}

Squidge 101 Newbie Poster · Answer 4 · 2012-09-05T20:25:45+00:00

This is assuming that you are using ? as placement for $_GET['room']

theundeadelvis 0 Newbie Poster · Answer 5 · 2012-09-06T12:43:17+00:00

Thanks again Squidge! I'll give this a try today.

Squidge 101 Newbie Poster · Answer 6 · 2012-09-06T17:30:37+00:00

Squidge 101 Newbie Poster

12 Years Ago

Let me knnow how you get on :)