Preventing SQL Injection in PHP - Insert and Update


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

example:
// Insert new user into database
$query = "insert into users (username,password) values('".$_POST['username']."','".$_POST['password']."')";
mysql_query($query);
 
// We didn't check $_POST['password'], it could be anything the user wanted!
// For example:
$_POST['username'] = "jack";
$_POST['password'] = "'); delete from users where 1=1 or username='";
 
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
INSERT INTO users (username,password) VALUES('jack',''); DELETE FROM users WHERE 1=1 OR username='');
In this case 2 queries would be executed. How do we prevent this?

Well, we need to call a quote function to sanitize every input coming from the user.
function quote($value)
{
    if (get_magic_quotes_gpc())
        $value = stripslashes($value);
    if (is_numeric($value))
        return "'$value'";
    return "'".mysql_real_escape_string($value)."'";
}

For insert it is easiest to do programmatically with php. Example:
function get_quoted_insert($table, $assoc_arr)
{
    foreach($assoc_arr as $k=>$v)
        $assoc_arr[$k] = quote($v);
 
    $insertstr="INSERT INTO `".$table."`";
    $insertstr.=" (`". implode("`,`", array_keys($assoc_arr)) ."`) VALUES" ;
    $insertstr.=" (". implode(",", array_values($assoc_arr)) .");" ;
    return $insertstr;
}
 
$record = array();
$record['username'] = $_POST['username'];
$record['password'] = $_POST['password'];
$query = get_quoted_insert('users',$record);
echo $query;

For $_POST['username'] = 'jack'; and $_POST['password']="o'brien", this would produce
INSERT INTO `users` (`username`,`password`) VALUES('jack','o\\'brien');
In this case, the ' in o'brien will not break the SQL statement.

It is also useful to have a nice php function for update
function get_quoted_update($table, $update_arr, $id)
{
    $update_str="update  `".$table."` set ";
    foreach($update_arr as $k=>$v)
       $update_str.= "`".$k."`=".quote($v).",";
    $update_str = substr($update_str, 0, -1)." ";
    $update_str.="where id=".quote($id);
    return $update_str;
}
 
$record = array();
$record['username'] = $_POST['username'];
$record['password'] = $_POST['password'];
$query = get_quoted_update('users',$record, '5');
echo $query;

For $_POST['username'] = 'jack'; and $_POST['password']="o'brien", this would produce
UPDATE `users` SET `username`='jack',`password`='o\\'brien' WHERE id='5';
In this case, the ' in o'brien will not break the SQL statement.