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.

// Query database to check if there are any matching users
$query = "SELECT * FROM users WHERE user='".$_POST['username']."' AND password='".$_POST['password']."'";
// 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 the following is recommended:
$query = sprintf("SELECT * FROM users WHERE user=%s AND password=%s",


thats a lot of code to have to enter for every time. Instead use this function, qsprintf:
//way less code
$query = qsprintf("SELECT * FROM users WHERE user=%s AND password=%s",
//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.
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)