home   articles   tags   browse code   

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.
 

Tags: php, mysql, security
 
Bookmark this...
 
Comment on this...
 


 

 



Related Articles
 

php
 

mysql
 

security
 

home  |  privacy policy  |  terms of use  |  contact  


©2012, Zedwood.com
zedwood.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to amazon.com