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);