PDO Baby Steps: Prepared Statements

by on February 22, 2012 in
PDO Baby Steps: Prepared Statements Difficulty Level: Intermediate
 

As part of my PDO un-newbification I’m being told to use Prepared Statements a lot. After a little research I discovered that these are basically a very easy way to be secure. They set a variable rather than text in your SQL requests. So you could convert hackable, old, this:

$oldcrap =  mysql_query("SELECT username, password FROM my_table");

…and turn it into variable driven, new, pretty this:

$sth = $dbh->prepare("SELECT :user, :pass FROM :mytable");

Notice the colons tacked to the front of the variables? That tells PHP “HEY! THESE ARE VARIABLES!”.

PHP will then look for “parameters” you “bind” to that SQL statement (AKA: variables you tacked in there). To get PHP to do this rather than thinking you’re just some idiot who likes colons, simply add your params like this:

$sth = $dbh->prepare("SELECT :user, :pass FROM :mytable");
$sth->bindParam(':user', $username);
$sth->bindParam(':pass', $password);
$sth->bindParam(':mytable', $table);

Ok, so now PHP can read and include variables set earlier.

// Set dummy variables
$username = "John";
$password = "john123";
$table = "tblLogin";

Once all your parameters (variables) are bound (tacked on there), you can execute the code with… uh.. execute().

$sth->execute();

All together now!

// DB Settings
$host = 'localhost';
$db = 'my_database';
$user = 'root';
$pass = '';
 
// Set dummy variables
$username = "John";
$password = "john123";
$table = "tblLogin";
 
// Prepare your statement handle
$sth = $dbh->prepare("SELECT :user, :pass FROM :mytable");
 
// Bind variables to your statement
$sth->bindParam(':user', $username);
$sth->bindParam(':pass', $password);
$sth->bindParam(':mytable', $table);
 
// Flip the switch
$sth->execute();
 
// Test it out
foreach ($sth as $row) {
    echo $row['username'], " - ", $row['password'];
}

Hope that helps. If anything is unclear or wrong, leave a comment and I’ll be glad to try and help.

Further Reading

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers – An old-to-new series of examples tutorial
http://php.net/manual/en/pdo.prepared-statements.php
http://net.tutsplus.com/tutorials/php/the-problem-with-phps-prepared-statements/
http://www.phpeveryday.com/articles/PDO-Prepared-Statement-P550.html

 
If you liked this article, you might also like:
  • http://www.tresorparissale.com/ Tresor Paris

    Although I do not know what you say.But it looks like a great look

  • Ivan Ferrer

    are you sure this query is ok? I think that :user and :pass must be on the WHERE side like this:
    SELECT * FROM :mytable WHERE user= :user AND pass=:pass

    what you specify after SELECT is the field name, not the value you are querying.

    • fulldesign

      Yes good point! Other than that, great tutorial, well written and easy to follow.

      Also even though the method used here for bindParam() is probably the easiest to understand starting with PDO. You can instead put the bindParam() and execute() into one statement using an array like this:

      So this:
      $sth->bindParam(‘:user’, $username);
      $sth->bindParam(‘:pass’, $password);
      $sth->bindParam(‘:mytable’, $table);
      $sth->execute();

      changes to this:
      $sth->execute(array(‘:user’ => $username, ‘:pass’ => $password, ‘:mytable’ => $table));

      • Ivan Ferrer

        wow, good to know. Thanks.

    • sam

      Your example is ONLY correct if you want to return EVERYTHING from :mytable.

      In the example, and in most real world uses you only want to grab the :user and :pass ONLY such as in a login situation, so for such a use, the example shown is correct.

      • Tony Allen

        He’s actually right. You want to only grab the user and password (or verify it exists) where user is John and password is john123 unless those are literally the names of the columns. If you don’t want everything from that row, you could just say “SELECT username, password FROM :mytable WHERE username=:user AND password=:pass” and that will look in the table for a user named John with a password of john123.

  • CommanderWaffles

    You forgot to add this line in:

    $dbh = new PDO(“mysql:host=$host;db=$db”, $user, $pass);

  • James Wilson

    Thanks for the guides.

    I don’t understand why $sth->query() then requires:

    $sth->setFetchMode(PDO::FETCH_ASSOC);

    before the line:

    foreach ($sth as $row)

    Where as a prepared statement with $sth->execute(); can then go straight to the loop?