Why mysql_real_escape_string() isn't enough to stop SQL injection attacks!

We have all been there writing PHP code and trusting mysql_real_escape_string(). Alas it seems it is not enough, let us look at the example below ;

 $id = “0; DELETE FROM users”; 
 $id = mysql_real_escape_string($id); 
// 0; DELETE FROM users 
mysql_query_executer(“SELECT * FROM users WHERE id={$id}”); //a home brewed query executer

As you can see above simply using mysql_real_escape_string is not enough because the new output is in the end 0; Delete from users.

However don’t fret dear reader because there is a solution! Make sure the $id is indeed only a number! This can be done by simply using the code below;

 $id = “123; DELETE FROM users”; $id = (int) $id; // 123

This way any string elements will simply won’t be added the system. Now a question may arise on how to make sure you don’t get injected with attack if the parameter is in fact a string. My answer is simply : USE THE FRICKEN SQL QUOTES!

Like below;

$username = "DELETE FROM users";
$username = mysql_real_escape_string($username);
mysql_query_executer("SELECT * FROM users WHERE username='{$username}'");

The quotes will allow some protection.
I hope this helps you all! And please don’t tell newbies that mysql_real_escape_string() is enough!