Using MySQLi Prepared Statements to Avoid SQL Injection

I’m going to demonstrate a very short and simple method of avoiding SQL Injection at the SQL query level. You’ll need MySQLi support, on Debian you can apt-get install php5-mysql will contain everything that you need, and would be installed by default with your LAMP Installation.

Calling: http://www.mynonexistentdomain.com/test.php?user_id=10&record_number=1

Ignoring any PHP escaping functions or validation for example, you may wish to take the ‘user_id’ from user input, and prepare a query with it, in it’s simplest and most insecure form: $sql = “SELECT username FROM users WHERE user_id=’” . $_GET['user_id'] . “‘ AND rnum=’” . $_GET['record_number'] . “‘;”; If you’re not yet sure yet why this is insecure, read my SQL Injection article. You do have the option of cleaning up your input with mysql_real_escape_string() but this article briefly demonstrates another approach – using prepared statements. It is important to note that you’ll need to establish a MySQLi connection in order to use this feature.

In OO (Object Oriented) PHP

$db = new mysqli($host,$user,$passwd,$dbname); //Connect to our database
$statement = $mysqli->prepare(”SELECT username FROM users WHERE user_id=? AND rnum=?”); //Prepare a statement, using ‘?s’ for our placeholders
$statement->bind_param(’ii’, $_GET['user_id'],$_GET['record_number']); //Replace our ?s above with our input, specifying ‘ii’ meaning that we are passing two integers.
$statement->execute(); //Run the query

We could even now reassign variables to $_GET and $_GET and re-execute $statement->execute(); as our statement has already been prepared.

In the case above, we are using bound parameters. We can also use bound results which is however not going to be covered in this article as it is not directly involved in SQL Injection prevention. Here’s a good tutorial for further reading: http://forum.codecall.net/php-tutorials/12442-php-5-mysqli-prepared-statements.html

The $statement->bind_param is the instruction to only accept ‘i’ or integer type. You could also use ‘d’, ’s’ and ‘b’ – double, string and blob respectively. This is what filters out the potential exploit. You would still want to be cleaning input though, and checking for errors..