Prepared statements in MySQL and PHP

Published: 2007-01-06
Last Updated: 2007-01-07 01:15:57 UTC
by Johannes Ullrich (Version: 1)
0 comment(s)
Starting with version 4.1, MySQL offers prepared statements. A prepared statement is a great way to avoid SQL insertion issues. However, frequently prepared statements are not used as they require a bit more typing. So I would like to take this opportunity to show off a few tricks to make it easier to use prepared statements.

First of all, what is a prepared statement:
Without prepared statements to help you, a SQL query is assembled as a string and then passed to the database. You will typically find code like this:

$sQuery="select id from users where email='$sEmail'";
$hResult=mysql_query($sQuery);

The big problem here is SQL injection. What if we don't validate $sEmail well and end up with Mr. '; drop table users; registering?

So how do prepared statements help? Prepared statements execute in stages. At first, you will send the statement to the database, but replace all variables with "?":

$hStmt=mysqli_prepare($dblink,"select id from suers where email=?")

Note that there are no quotes around the ?. Next, we have to associate values to the parameter. This is done using the "bind" statement.

mysqli_stmt_bind_param($hStmt,"s",$sEmail);

The trick here is that MySQL will always consider "$sEmail" as a single value, no matter how many quotes and semi-colons you insert. Did you see that I am now using "mysqli", not "mysql"? mysqli is phps improved MySQL module which enables you to take full advantage of new features like prepared statements. I used the procedural style above, to point out the differences with respect to prepared statements. But in addition, mysqli provides a nice object oriented interface.

Ok. We got our variables bound, and now we need to talk about executing the statement and retrieving the results. The result is bound to a variable pretty much in the same way as parameters:
mysqli_stmt_execute($hStmt);
mysqli_stmt_bind_result($hStmt,$nID);

The real beauty of prepared statements comes to play if you try to execute the same statement multiple times with different parameters. All you have to do is change the content of your bound variable, execute the statement again, and retrieve the results from your already bound result variable. So you don't have to redo the "prepare". MySQL only has to parse your statement once.

But on the other hand, for a quick value retrieval like in the example above, thinks look overly complex.

Enter the object zone. As mentioned above, mysqli is available as a class. And you are free to extend it. You can find the extended class I use on DShield and ISC in our Sourceforge.Net CVS repository.

Couple highlights:
The "simple_query" function can be used to retrieve a single value. The example shown about would look like:
$nID=$oDB->simple_query("select id from users where email=?","s",$sEmail);

All the prepare and bind mess is hidden inside the class.

Or a more complex example. Lets say you would like to dump the output of a query into an HTML table. Something I do a lot for this site ;-):



$sTemplate="  %%port%%  %%count%%  ";
$sQuery="select port, count from port_summary where date=?";
print $oDB->template_query($sQuery,'s',$dDate,$sTemplate,'oddrow','evenrow',600);

"template_query" will fill the results retrieved from the database into a template, one row at a time. "oddrow" and "evenrow" are styles that will be used for odd and even rows respectively. The last parameter specifies a "cache time". The resulting HTML snippet will be cached for just that many seconds in order to save a lot of work if people ask for he same query over and over.

Oh. And before I forget ;-) The extended class above is released under GPL V2. If you are interested in helping to work on the ISC or DShield sites, drop me an e-mail.
Keywords:
0 comment(s)

Comments


Diary Archives