How to validate integer values to avoid SQL injection?

Question

The best way to avoid SQL injection for defined value type such as numbers, is to validate the value; since it is easier to do so comparing with mysqli preparation. In PHP, we can do this by.

1. if(!is_numeric($value)) {$value=0;}
2. $value=floatval($value);
3. $value=intval($value);
4. $value=$value * 1;

What is the most reliable one? or a better idea?

UPDATE: Although I have stated in the original question, most of folks emphasized the usefulness of parameterized queries. Definitely, it is the most efficient way to avoid SQL injection. But when we can simply validate an integer numeric value; IMHO, there is no need to parametrization.

Answer 1

For integers and floats you can use this if you don't want to do a parameterised query.

$clean_number = (int)$value;

$clean_number = (float)$value;

These are actually casting the value as int and float, this is faster than intval() and floatval() for example because it does not suffer the function overhead.

Answer 2

I prefer to use the filter extension:

$id = filter_var($id, FILTER_SANITIZE_NUMBER_INT);

You should definitively go for parameterized queries.

Answer 3

You can use intval() to coerce a value to integer. The most reliable way to avoid SQL injection is to use parameterised queries.

Answer 4

$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sth = $dbh->prepare('SELECT * FROM table WHERE id = :id');
$sth->bindParam(':id', $id, PDO::PARAM_INT); 
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC);

Answer 5

If you want a seasoned advise, you have to change your mind. Completely.

The thing that being your main concern, in fact, is the most negligible thing in the world. You may use one way or another with not a slightest difference. There is no "most reliable" way. That's just several ways of doing the same.

On the other hand, "there is no need to parametrization" is a grave delusion.
Parameterized queries can do any good only if used explicitly, throughout whole site, with no exceptions. One exception can spoil all the defense.

Not to mention that parameterized query can make your life much, much easier.
Prepared statements are not such an ugly code that propagated on this site by some enthusiasts. It's actually quick and neat way of writing safe code.
Say, the code that took cetver a dozen lines can be done in just one:

$data = $db->getAll("SELECT * FROM table WHERE id = :placeholder:",$id);

and be perfectly safe
without ugly manual binding.
without error-prone manual casting.

Another example to show you the power of placeholders

$sql = "SELECT * FROM table WHERE tstamp BETWEEN ?i AND ?i AND flag=?s AND IN in (?a)";
$data = $db->getAll($sql,$min,$max,$flag,$array_of_ids);

Two lines.

I am not too good with PDO but it would be like a dozen lines of code even without connect

$in  = implode(',', array_fill(0, count($array_of_ids), '?'));
$sql = "SELECT * FROM table WHERE tstamp BETWEEN ? AND ? AND flag=? AND id IN ($in)"
$sth = $dbh->prepare($sql);
$stmt->bindValue(1, $min);
$stmt->bindValue(2, $max);
$stmt->bindValue(3, $flag);
foreach ($array_of_ids as $i => $id) {
  $stmt->bindValue(($i+4), $id);
}
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC);

And comparable amount with your current manual casting.

This is actually the power of programming.
One can write a program to do all the dirty job for them.
An approach almost never seen on this site.

Sapienti sat

Answer 6

You can use this function, mysql_real_escape_string() to espace special characters, but what you are doing its fine to protect an sqli attack.