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));