Is this query injection proof?

Question

I am using PDO to talk to my database, and I wonder if casting a type like this

$dbh->query("SELECT * FROM recipes WHERE id=".(int)$id);

is sufficient to prevent sql injection? In this case $id is always an integer.

I also wonder what would be a good way to prevent an injection in this kind of statement if the variable was a string.

Answer 1

Yes. Casting to int prevents all the nasty SQL injection possibilities.

If the variable were a string, you should use prepared statements to pass it.

$sql = 'SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql);
$sth->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $sth->fetchAll();

Answer 2

You must escape table and field names in query:

$dbh->query("SELECT * FROM `recipes` WHERE `id=`'".(int)$id."'");

Answer 3

Since you are already using PDO, a better approach will be to use:

• Prepared Statements

This is much better:

$dbh->prepare("SELECT * FROM recipes WHERE id = ?");
$dbh->bindParam(1, (int) $id);
// more code.....

Answer 4

Since you specifically cast $id to an integer, it is safe. For a string (or any other data type) you need to escape it before executing the query; have a look at PDO::quote.

Answer 5

Yes, bind to a integer is enough to prevent SQL Injection if the parameter is expected as a integer.

You can also use an Automatic SQL Injection Tool to detect it.