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.

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

zedwood.com

Preventing SQL Injection in PHP - Insert and Update