Converting your PHP app to MySQLi prepared statements

Okay, you’ve got like a zillion SQL queries in your PHP app, and probably 95% of them have a WHERE clause, and you need to make them safe so people will still download and use your app. Because if you don’t fix your injection issues, I will rain fire on your ass.

These are the steps you need to take to convert to prepared statements.

Step 0. PHP 4 is dead. Upgrade to PHP 5

All of the code in these samples is PHP 5 only. I will be using SPL and MySQLi, which are installed primarily on PHP 5.2.x installations. PHP 4 cannot be made safe, so it’s time to upgrade. This is non-negotiable in my view.

If you’re using register_globals, you have to stop. Do not use a function to register them for you in PHP 5, it’s time to do proper input validation. This will actually take you longer than converting all your queries to prepared statements.

To get a handle on this issue, what you need to do is:

1. Turn on error_reporting(E_ALL | E_STRICT);

2. Find all isset() and empty() and unless they are actually testing for a variable you’ve set, get rid of them. isset() and empty() are not validation mechanisms, they just hide tainted globals from view

3. Go to php.ini, and turn register_globals off. It should already be off

4. If your code has a construct like extract($GLOBALS) or some other mechanism to register globals, get rid of it

5. php -l file.php. This will give you a first pass which you will need to clean up

6. Use PHP Eclipse or PDT in Eclipse or the Zend IDE in Eclipse. This will give you warnings if you have uninitialized variables. Go to the properties, and make this into an error. Clean up all uninitialized variables

7. Start each script like this:

// Canonocalize

$dirty_variable = canonicalize($_POST['variable']);

// Validate

$variable = validate($dirty_variable);

// Use the variable

$stmt = $db->prepare("SELECT * FROM table WHERE id = ?");

$stmt->prepare("i", $variable);

$stmt->execute();

// and finally, if you need to output that sucker:

echo htmlescape($variable, $locale, $encoding); // $locale is probably 'en', and $encoding is probably UTF-8 or ISO 8859-1

Obviously, you need to canonicalize – that is make it the simplest possible form. If you have no idea about this extremely important topic, please consult the OWASP Developer Guide. Validation is essential. This replaces isset() and empty() and other mechanisms, with actual validation requirements. If you’re expecting an array of integers, make sure it’s an array of integers! If they have to be in a certain range, make it so. If the validation fails, put up an error message and do not proceed! This stuff is CS101, so please make sure you do this reliably for all variables without exception.

Step 1. Make sure your hoster has MySQLi

If your hoster is still running PHP 4, you need to see if they have the ability to run PHP 5. Most likely, your PHP 4 installation will not have ANY prepared statement compatible interface, like MySQLi or PDO. Of course, PDO is PHP 5 only… and it has a cool interesting feature – it emulates prepared statements for those databases that do not have support for it. But that’s for another post.

How do you check? Use phpinfo(). Create this small file somewhere with a random file name and upload it to your host:

<?php phpinfo(); ?>

Run the file, and note if you have the MySQLi interface. If you don’t, you can’t upgrade to prepared statements. It’s time to wage holy war on your hoster to make sure they install PHP 5.2.7 with MySQLi and PDO with MySQL 5 client libs for you … and all your other shared hosting friends.

Changing over to MySQLi

The simplest part of this process is to move to MySQLi from MySQL:

Instead of

$db = mysql_connect($db…

You have two choices: stay with functional MySQLi, or move to OO MySQLi. I think the latter is better, but that will be another post.

$db = mysqli_connect($db..

Now, this is where it’s important! You MUST check the value of $db for errors before continuing. You probably have this code today, but it’s important to realize that if $db == false, you didn’t get a connection.

if ( $db == false ) {

// Print up an error and stop

}

Simple Conversion

You may be tempted to just use the MySQLi extension, and move all your queries to place holder versions. That’s okay, but it can be a lot of work. Trust me, I’ve tried.

Although it seems like the easiest possible choice, converting MySQL queries to MySQLi’s prepared statements has a couple of issues.

Gotchya: There’s no easy way to bind lots of results when you use select *

With MySQL query, fetch_array will simply bind the current result set row to an associative array, and you can access it trivially. Most PHP apps use this data access pattern extensively, like this:

while ( $row = mysql_fetch_array($query) ) {

// do some work with $row

$blah = $row[‘column’];

}

Which brings us to the next gotchya:

Gotchya: There’s no fetch_array()

I don’t know why MySQLi does not have this most common of all the data access patterns, but it’s a right pain to fix. So let’s get a function to emulate fetch_array, including using anonymous field names as per above.

Okay, so we’ve decided that MySQLi sucks the proverbials… so in the next article, let’s talk about migrating non-trivial PHP apps to PDO.