Convert mysql_query to PDO statements

Question

While there are a large number of PDO usage examples I have not been able to successfully convert from mysql_query to PDO statements.

Here's what works but is insecure:

<?php
$db = mysql_connect("localhost","username","passphrase");
mysql_select_db("database",$db);
$cat= $_GET["cat"];
/* grab a row and print what we need */
$result = mysql_query("SELECT * FROM cat WHERE cat = '$cat' ",$db);
$myrow3 = mysql_fetch_row($result);
echo "$myrow3[2]";
/* here's an array */
echo '<div class="container">';
$q = mysql_query("SELECT * FROM name WHERE Field4 = '$cat'",$db);
while ($res = mysql_fetch_array($q)){
    echo '<div class="item"><p><a href="bits.php?page=' . $res['Field2'] . '&' . $res['Field6'] . '">' . $res['Field1'] . '</a></p></div>';
}
echo '</div>';
?>

Here is my attempt thus far at attempting to convert the mysql_query* to PDO statements based on How to prevent SQL injection in PHP?. Currently the page does not display, well anything. Any insight would be appreciated!

<?php

$pdo = new PDO('mysql:host=localhost;dbname=database','username','password');
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$cat= $_GET["cat"];
/* let try grabbing one of those rows, do not think an array should be here? */
$stmt = $pdo->prepare('SELECT * FROM cat WHERE cat = :cat');
$stmt->bindParam(':cat', $cat);
$result = $stmt->fetch(PDO::FETCH_ASSOC);
echo $result[2];
/* Now we need an array similar to what we had before */
echo '<div class="container">';
$stmt = $pdo->prepare('SELECT * FROM name WHERE Field4 = :Field4');
while ($res = $stmt->execute(array(':cat' => $cat))) {
    echo '<div class="item"><p><a href="bits.php?page=' . $res['Field2'] . '&' . $res['Field6'] . '">' . $res['Field1'] . '</a></p></div>';
}
echo '</div>';
?>

What is open to SQL injection is the building of SQL statements with variables from the outside. `'SELECT * FROM cat WHERE cat = $cat'` is executable code that is built with `$cat`. `$cat` is tainted, and thus taints the entire SQL statement. — Andy Lester, May 17 '13 at 20:04
`echo $result[2];` won't work because you're fetching the data into an ASSOC array, so you'll have `$result['fieldname']`, etc (whatever your fieldnames happen to be). — Spudley, May 17 '13 at 20:43

score 3 · Accepted Answer · answered May 17 '13 at 19:39

The way you are doing it, first off, isn't protecting you.

A PDO statement should look like (from the manual):

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

So, for your example:

$stmt = $pdo->prepare('SELECT * FROM cat WHERE cat = :cat');
$stmt->bindParam(':cat', $cat);
$result = $stmt->fetch(PDO::FETCH_ASSOC);

Or you could do:

$stmt = $pdo->prepare("SELECT * FROM cat where cat = ?");
if ($stmt->execute(array($cat)) {
  while ($row = $stmt->fetch()) {
    //print stuff or whatever
  }
}

Finally, in your last part:

while $stmt->execute(array(':cat' => $cat));
echo '<div class="item"><p><a href="bits.php?page=' . $res['Field2'] . '&' . $res['Field6'] . '">' . $res['Field1'] . '</a></p></div>';

It doesn't look like $res ever gets set. It should look like:

while ($res = $stmt->execute(array(':cat' => $cat)) {
    echo '<div class="item"><p><a href="bits.php?page=' . $res['Field2'] . 
         '&' . $res['Field6'] . '">' . $res['Field1'] . '</a></p></div>';
}

Convert mysql_query to PDO statements

1 Answers1