Preventing SQL Injection with Parametrized Queries

Still using magic quotes, or using addslashes to add slashes all $_POST and $_GET variables to prevent SQL Injection? Using magic quotes is considered not a good idea, and using addslashes will also certainly drive you insane.

Parameterized statements

To protect against SQL injection, user input must not directly be embedded in SQL statements. Instead, parameterized statements must be used (preferred), or user input must be carefully escaped or filtered. With most development platforms, parameterized statements can be used that work with parameters (sometimes called placeholders or bind variables) instead of embedding user input in the statement. In many cases, the SQL statement is fixed. The user input is then assigned (bound) to a parameter.
From: Wikipedia

MySQLi / PDO-only!?

PHP has built-in support for parameterized statements using the MySQL Improved Extension (MySQLi) or PHP Data Objects. So no build-in support if you're stuck with the old MySQL extension. As I was using the old MySQL extension in most of my projects and I really wanted to use parameterized statements, I found a way to implement them myself.

As placeholders I used the question mark (?), as MySQLi also uses the same syntax. Say I want to fetch a user from the database by its field `id`, one could use the following code:

$result = mysql_query("SELECT * FROM `user` WHERE `id` = {$_GET['id']}");

As obvious to most readers, this makes you vulnerable to SQL Injection as $_GET['id'] is used without any escaping or checking. Using a parametrized query, the same query would read:

SELECT * FROM `user` WHERE `id` = ?

I implemented the function 'parametrized_query' which takes a query and multiple substitution values. The query above is executed in the code like this:

$result = parametrized_query("SELECT * FROM `user` WHERE `id` = ?",
   $_GET['id']);

sprintf and mysql_real_escape_string

How does parametrized_query work? First, I escape all the string values send to the function using mysql_real_escape_string and put them between a single apostrophe: 'string'. This makes strings safe from SQL injection.

Then, using sprintf, the parameters are injected in the query. As sprintf uses %s instead of ? for placeholders, I use str_replace to substitute ? for %s.

The constructed query then looks like this (remember, $_GET only returns strings):

SELECT * FROM `user` WHERE `id` = '1'

I could force the parameter into an integer by first casting $_GET['id'] into an int:

$result = parametrized_query("SELECT * FROM `user` WHERE `id` = ?", (int) $_GET['id']);

The query then looks like this:

SELECT * FROM `user` WHERE `id` = 1

The code

Combining everything together, you end up with the following:

function parametrized_query()
{
    $query = func_get_args();
   $query[0] = str_replace("?", "%s", $query[0]);

    for($i = 1; $i < count($query); $i++) {
        if(is_string($query[$i])) {
            $query[$i] =
               "'".mysql_real_escape_string($query[$i])."'";
        } else if(is_null($query[$i])) {
            $query[$i] = 'NULL';
        }
    }

    $sql = call_user_func_array("sprintf", $query);

    return mysql_query($sql);
}