Convert SQL to PDO to prevent SQL Injection

Question

I am still new to php and MySQL as far as development is concerned. I would love some advice to make sure I am doing everything correctly. I have one page that I believe is converted the correct way and one page that I need to convert.

Here is the one that I have setup is this correct?

<?php $title = 'SEARCH'; $page = '';include 'includes/header.php';?>

<body>

<?php include 'includes/nav.php'; ?>

<?php
$q = $_GET['q'];

// CONNECT TO THE DATABASE
$DB_NAME = 'code_storage';
$DB_HOST = 'localhost';
$DB_USER = 'user';
$DB_PASS = 'pass';

try {
    $dbcon = new PDO("mysql:host=$DB_HOST;dbname=$DB_NAME", $DB_USER, $DB_PASS);
    //echo 'Connected to database';

    $sql = <<<SQL
    SELECT * 
    FROM snippets
    WHERE CODE_NAME LIKE '%$q%' OR
    CODE_DESC LIKE '%$q%' OR
    CODE_TAGS LIKE '%$q%' OR
    CODE_USAGE LIKE '%$q%'
 SQL;
    echo '<div class="row">';
    echo    '<div class="panel">';
        printf("Your search for <b>$q</b> returned %d records.\n", $dbcon->query($sql)->rowCount());
    echo    '</div>';
    echo '</div>';

    foreach ($dbcon->query($sql) as $row) {
        //print $row['CODE_NAME'] . '<br/>' . "\n";
        echo '   <div class="row">' . "\n";
        echo '       <div class="large-12 columns">' . "\n";
        echo '              <b><a href="results.php?id=' . $row['_ID'] . '">' . $row['CODE_NAME'] . '</a></b><br/><br/>' . "\n";
        echo '       </div>' . "\n";
        echo '   </div>' . '<br/><br/>' . "\n";
    }


    $dbcon = null;
}
catch(PDOException $e)
{
    echo $e->getMessage();
}
 ?>
<?php include 'includes/footer.php';?>

And here is the one I still need to convert this is the once I could really use some help with

<?php

// CONNECT TO THE DATABASE
$DB_NAME = 'code_storage';
$DB_HOST = 'localhost';
$DB_USER = 'user';
$DB_PASS = 'pass';


$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

// Fix for the ' and " 

$_POST['name'] = $mysqli->real_escape_string($_POST['name']);
$_POST['desc'] = $mysqli->real_escape_string($_POST['desc']);
$_POST['usage'] = $mysqli->real_escape_string($_POST['usage']);
$_POST['code'] = $mysqli->real_escape_string($_POST['code']);
$_POST['tags'] = $mysqli->real_escape_string($_POST['tags']);

$sql = <<<SQL
    INSERT 
    INTO snippets
    (CODE_NAME,CODE_DESC,CODE_USAGE,CODE_SYNTAX,CODE_TAGS)
    VALUES
    ('$_POST[name]', '$_POST[desc]', '$_POST[usage]', '$_POST[code]', '$_POST[tags]');
SQL;

if(!$result = $mysqli->query($sql)){
    echo '<br/><br/><br/><br/>' . "\n";
    echo '<div class="row">' . "\n";
    echo '  <div class="large-12 columns">' . "\n"; 
    echo '      <div data-alert class="alert-box alert">' . "\n";
    echo '          There was an error' . "\n";
    echo '          <a href="../site/upload.php" class="close">&times;</a>' . "\n";
    echo '      </div>' . "\n";
    echo '  </div>' . "\n";
    echo '</div>' . "\n";
    echo '<br/><br/><br/><br/>' . "\n";
    echo '<div class="row">' . "\n";
    echo '  <div class="large-12 columns">' . "\n";
    die('There was an error with the code [' . $mysqli->error . ']');
    echo '  </div>' . "\n";
    echo '</div>' . "\n";

}
    echo '<br/><br/><br/><br/>' . "\n";
    echo '<div class="row">' . "\n";
    echo '  <div class="large-12 columns">' . "\n"; 
    echo '      <div data-alert class="alert-box success">' . "\n";
    echo '          Code Successfully Added' . "\n";
    echo '          <a href="../site/" class="close">&times;</a>' . "\n";
    echo '      </div>' . "\n";
    echo '  </div>' . "\n";
    echo '</div>' . "\n";
    echo '<br/><br/><br/><br/>' . "\n";
*/

?>

*EDIT* Here is what I have so far but it doesn't seem to be returning any records any ideas why?

 $q = $_GET['q'];

$DB_NAME = 'code_storage';
$DB_HOST = 'localhost';
$DB_USER = 'user';
$DB_PASS = 'pass';


$dsn = "mysql:host=$DB_HOST;dbname=$DB_NAME";
$db = new PDO($dsn, $DB_USER, $DB_PASS);

$query = "SELECT * FROM `SNIPPETS` WHERE `CODE_NAME` LIKE :name OR `CODE_DESC` LIKE :name OR `CODE_TAGS` LIKE :name OR `CODE_USAGE` LIKE :name";
$prep = $db->prepare($query);
$prep->execute(array(":name" => "%" . $q . "%"));

echo '<div class="row">';
    echo    '<div class="panel">';
       printf("Your search for <b>$q</b> returned %d records.\n", $prep->rowCount());
    echo    '</div>';
echo '</div>';

while ($row = $prep->fetch()) {
    echo '   <div class="row">' . "\n";
    echo '       <div class="large-12 columns">' . "\n";

    echo             $row['CODE_NAME'] . '<br/><br/>' . "\n";
    echo             $row['CODE_DESC'] . '<br/><br/>' . "\n";
    echo             $row['CODE_USAGE']. '<br/><br/>' . "\n";
    echo '       </div>' . "\n";
    echo '   </div>' . '<br/><br/>' . "\n"; 


}

$db = null;

I have found out that I am getting this error

Connection failed: SQLSTATE[HY093]: Invalid parameter number: number of bound    variables does not match number of tokens

More information on preventing injection: http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php?lq=1 — BLaZuRE, Jun 04 '13 at 22:55
`mysqli` also provides for bound parameters; no need to switch to PDO for that (albeit it's a nicer interface). The first snippet, btw, is anything but correct. It still interpolates input directly, sans escaping or any sign of prepared statements. — mario, Jun 04 '13 at 22:58
You might find something useful in our [PDO Tutorial for MySQL Developers](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers) — TML, Jun 04 '13 at 23:05
[PDO query fails but I can't see any errors. How to get an error message from PDO?](http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15990858) — Your Common Sense, Jun 05 '13 at 14:25

Bill Karwin · Accepted Answer · 2013-06-05T18:15:12.777

Unfortunately, some versions of PDO don't know how to use a named parameter multiple times in the same query. If I recall, this bug is fixed in newer versions, but you may be stuck using an older version of PHP with PDO.

One workaround is this:

$query = "SELECT * FROM `SNIPPETS` WHERE `CODE_NAME` LIKE :name1 
  OR `CODE_DESC` LIKE :name2 
  OR `CODE_TAGS` LIKE :name3 
  OR `CODE_USAGE` LIKE :name4";
$prep = $db->prepare($query);
$qpattern = "%" . $q . "%";
$prep->execute(array(":name1" => $qpattern, ":name2" => $qpattern, ":name3" => $qpattern, ":name4" => $qpattern));

Or else use positional parameters instead of named parameters:

$query = "SELECT * FROM `SNIPPETS` WHERE `CODE_NAME` LIKE ? 
  OR `CODE_DESC` LIKE ? 
  OR `CODE_TAGS` LIKE ? 
  OR `CODE_USAGE` LIKE ?";
$prep = $db->prepare($query);
$qpattern = "%" . $q . "%";
$prep->execute(array_fill(0, 4, $qpattern));

PS: Not related to your question about using prepared statements, but your use of '%pattern%' for full-text search is thousands of times slower than using any fulltext index solution.

Another good habit is to check for errors after every call to PDO::prepare() or PDOStatement::execute(). Either enable exception mode, or else check the return values for false:

if (($prep = $db->prepare(...)) === false) {
  // check $db->errorInfo() for details
}
if ($prep->execute(...) === false) {
  // check $prep->errorInfo() for details
}

Checking for errors is also a best practice for the old mysql API, and the mysqli API.

Thank you so much that worked, not sure why I can't wrap my head around prepared statements but thanks again. — ondrovic, Jun 05 '13 at 18:11
Bill thanks again for the great example, I have now converted all my code over to use prepared statements and PDO. I have yet to take a look at the fulltext index solution but plan on it this weekend. — ondrovic, Jun 07 '13 at 21:51

Convert SQL to PDO to prevent SQL Injection

1 Answers1