Should numbers from user input be quoted in MySQL queries to help avoid SQL injection attacks?

Question

Should numbers from user input be quoted in MySQL queries to help avoid SQL injection attacks?

Say i have a form on a page asking for someone's age. They enter their age and hit submit. The following php code deals with the form submission: (age is an int field in the db table.)

$Number = mysqli_real_escape_string($dbc, $_POST["age"]);
$Query = "INSERT INTO details (age) VALUES ($Number)";
$Result = mysqli_query($dbc, $Query);

Instead of this, is there anything to be gained to enclosing the user input in single quotes, even though it is not a string? Like this:

...
$Query = "INSERT INTO details (age) VALUES ('$Number')";  <-- quotes
...

What about performing a SELECT? Is this:

$ID = mysqli_real_escape_string($dbc, $_POST["id"]);
$Query = "SELECT * FROM users WHERE id = '$ID'";
$Result = mysqli_query($dbc, $Query);

better than:

$ID = mysqli_real_escape_string($dbc, $_POST["id"]);
$Query = "SELECT * FROM users WHERE id = $ID";      <-- no quotes
$Result = mysqli_query($dbc, $Query);

---

NOTE: I am aware of prepared statements and usually use them over string concatenation but this is legacy code i'm dealing with. I want to secure it as best as i can.

Answer 1

If you add numbers, use the intval/floatval functions, don't use mysql_real_escape_string for those.

For everything you use mysql_real_escape_string for, you must use quotes, example:

$input = "foo'bar";
$input = mysql_real_escape_string($input);
//foo\'bar
mysql_query("SELECT $input");
//SELECT foo\'bar
//which is still an SQL syntax error.

Answer 2

You really shoud use sprintf, even if in legacy code it takes 2 mins to modify and is in my opinion totally worth the time.

Shamelessly ripped from php.net:

// Formulate Query
// This is the best way to perform an SQL query
// For more examples, see mysql_real_escape_string()
$query = sprintf("SELECT firstname, lastname, address, age FROM friends 
                 WHERE  firstname='%s' AND lastname='%s'",
                 mysql_real_escape_string($firstname),
                 mysql_real_escape_string($lastname));

// Perform Query
$result = mysql_query($query);

Your query is now pretty much safe from being passed the wrong types to it's fields and unescaped caracters.

Answer 3

You SHOULD use the PHP filters, and filter for numbers - even for ranges, regular expressions; with default values, NULL on failure, etc.

http://hu.php.net/manual/en/ref.filter.php

if the values come from a request variable, e.g. $_POST, see:

http://hu.php.net/manual/en/function.filter-input.php