How to prevent MySQL sleep injections?

Question

Recently one hacker tried to slow my website using sleep injection. Although we are using precautions like mysql_real_escape_string() to cover most of vulnerable inputs. We are passing id of the product through query string and it makes the command as:

$id = mysql_real_escape_string($_REQUEST['id']);
$qry = "Select * from products where id = ".$id;

but hacker tried to provide input as

?id=3 and sleep(4)

and query becomes

Select * from products where id = 3 and sleep(4);

Although there are some possible solutions like

1. Check if the product id is numeric or not
2. Remove word sleep from input using some customized function

Is there any other method to stop this? What is the best method to prevent sleep injections?

Answer 1

You are not escaping correctly. mysql_real_escape_string is for escaping SQL string syntax correctly, but you are simply embedding the value as bare value, not as SQL string. You need:

$qry = "SELECT * FROM products WHERE id = '$id'";

Note the quotes around the id in the query.

If the id is numeric though, casting to a number would be more sensible:

$id = (int)$_GET['id'];

Answer 2

The best method to prevent SQL injections is to use current technology. The MySQL mysql_ family of functions is deprecated and will be removed from PHP in a future revision.

You should use prepared statements with either MySQLi or PDO instead.

These technologies use prepared statements and parameterized queries. SQL statements are parsed by the database server separately from any parameters. It is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

1. MySQLi:

   $stmt = $dbConnection->prepare('SELECT * FROM table WHERE name = ?');
   $stmt->bind_param('s', $name);
   $stmt->execute();
   $result = $stmt->get_result();
   while ($row = $result->fetch_assoc()) {
       // do something with $row
   }
   

2. PDO:

   $stmt = $pdo->prepare('SELECT * FROM table WHERE name = :name');
   $stmt->execute(array(':name' => $name));
   foreach ($stmt as $row) {
       // do something with $row
   }
   

What happens is that the SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name) you tell the database engine what you want to filter on. Then when you call execute the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not a SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters you limit the risk of ending up with something you didn't intend. Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course).

Answer 3

This is wrong question to ask.

"How to prevent mysql injections?" it has to be. Sleep or not sleep - it doesn't matter.

And there are plenty of answers on this question already

• if you want a canonical one, then How can I prevent SQL injection in PHP?
• if you want to understand, how it works, here I explained it in details