Preventing SQL Injection in PHP - Select

PHP code is vulnerable to an SQL injection attack when it takes user input and directly adds it to an SQL query string.

example:

<?php
// Query database to check if there are any matching users
$query = "SELECT * FROM users WHERE user='".$_POST['username']."' AND password='".$_POST['password']."'";
mysql_query($query);
 
// We didn't check $_POST['password'], it could be anything the user wanted!  
// For example:
$_POST['username'] = 'zedwood';
$_POST['password'] = "' OR 1=1";
 
echo $query;
?>

In the above example, the user is able to inject some sql into the password field... and the program blindly passes it into sql. The generated query string is

SELECT * FROM users WHERE user='zedwood' AND password='' OR 1=1

This query allows them to login without a password. So... this is bad. Now, how to protect against it...

From php.net the following is recommended:

<?php
$query = sprintf("SELECT * FROM users WHERE user=%s AND password=%s",
     mysql_real_escape_string($_POST['username']),
     mysql_real_escape_string($_POST['password']),
    );
?>

but....

thats a lot of code to have to enter for every time. Instead use this function, qsprintf:

<?php
//way less code
$query = qsprintf("SELECT * FROM users WHERE user=%s AND password=%s",
     $_POST['username'],
     $_POST['password'],
    );
 
//here is the code
function qsprintf()
{
    $numargs  = func_num_args();
    $arg_list = func_get_args();
    $format  = $arg_list[0];
    $next_arg_list = array();
    for ($i = 1; $i < $numargs; $i++)
        $next_arg_list[] = mysql_real_escape_string($arg_list[$i]);
    return vsprintf($format, $next_arg_list);
}
?>

Note 1:
due to % symbol, qsprintf doesn't work very well with this SQL query: select * from users where first_name like '%scott' because it will interpret %s of scott as the %s format specifier used in printf.

Note 2:
it is a good idea to replace the call to mysql_real_escape_string above, with a custom quote function in order to take care of magic_quotes.

<?php
function quote($value)
{
    if (get_magic_quotes_gpc())
        $value = stripslashes($value);
    if (is_numeric($value))
        return "'$value'";
    return "'".mysql_real_escape_string($value)."'";
}
?>

code snippets are licensed under Creative Commons CC-By-SA 3.0 (unless otherwise specified)

zedwood.com

Preventing SQL Injection in PHP - Select