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)

Good ol' Web Defacements

Published: 2007-01-06
Last Updated: 2007-01-06 06:58:56 UTC
by Johannes Ullrich (Version: 1)
0 comment(s)
Shaylan just wrote us "my website has been taken over" ... "people are using them without my permission".

 
He attached a file which is probably collected from his site. It includes a flash animation and redirects to what looks like a Turkish political website. I don't speak turkish, so I am not sure what side/view the site covered. However, the entire episode certainly brought back long subpressed memories about turkish/armenian usenet fights and "hacked by chinese" messages spread by Code Red. Political "hacktivism" has kind of taken a lesser role these days with everybody focusing on making money.

But what are the lesson here?
  1. They are still out there. Usually bottom feeding on the scraps left by bots (or using bots to their advantage of course).
  2. "Best Practices": Its hard to run a hacker-proof website. But its like anything... a little effort goes a long way and keeps out most of the riff-raff.
  3. Shared Servers: Don't use them for business critical web sites. They are fine to run a hobby site from. But for anything else: Get a dedicated system. IMHO, the jury is still out if virtual machines provide enough separation.
Lets see what the weekend brings, but with a bit of luck we will not get flooded by current events and I will find a bit time to discuss web security (or well... if you REALLY want to learm more about web security, see me at RSA early february)
Keywords:
0 comment(s)

Comments


Diary Archives