Is this query safe from sql injection?

Question

The script is in PHP and as DB I use MySQL. Here is the script itself.

$unsafe_variable = $_GET["user-input"];
$sql=sprintf("INSERT INTO table (column) VALUES('%s')",$unsafe_variable);
mysql_query($sql);

Some people say that if user assigns ;DROP TABLE blah; string to the variable $unsafe_variable it deletes the table.

But I tried this example,

http://localhost/test.php?user-input=DROP%20TABLE%20my_table 

But it didn't delete the table but instead inserted a new row (;DROP TABLE blah;) in the table.

Could anybody explain me how it is possible to attack this script with sql injections?

Answer 1

That particular injection wouldn't work since PHP's mysql_query function only allows one query per call. However, the following may work if column has a primary or unique key:

$unsafe_variable = "admin') ON DUPLICATE KEY UPDATE password=MD5(CONCAT('knownsalt', 'newpassword'))#";

Better to use the long-winded mysql_real_escape_string function:

$sql=sprintf("INSERT INTO table (column) VALUES(%s)",
             mysql_real_escape_string($unsafe_variable));
mysql_query($sql);

Answer 2

mysql_query() doesn't allow the execution of multiple queries in one function. So you can't INSERT and then DROP the table. But you shouldn't rely on this as 'security'. Use parametrized queries instead. Check out PHP's PDO library.

However, they could change just about anything else, like possibly SELECTing a password field from another table as a subquery to place into that table so they can view the hash.