Sanitizing Integer Database Input

Question

Hey guys,

I have an application that takes data via a POST request. I am using this data to insert a new row into the database. I know that using mysql_real_escape_string() (plus removing % and _) is the way to go for strings, but what about integer values? Right now, I am using the PHP function intval() on them.

However, I wanted to make sure that intval() is perfectly safe. I can't see a way of an attacker preforming a SQL injection attack when the variables are run through intval() first (since it always returns an integer), but I wanted to make sure this is the case from people that have more experience than I.

Thanks.

Answer 1

Yes, intval() is safe. There is absolutely no way to perform an SQL injection when the parameter is converted to integer, because (obviously) the format of an integer does not allow putting SQL keywords (or quotes, or whatever) in it.

Answer 2

The easiest way to prevent SQL injection is to always use prepared statments. Use the mysqli libraries or better yet an ORM such as doctrine etc.

Your queries then become something like:

$stmt = $db->prep_stmt("select * from .... where userid = ? and username = ?");

/* Binding 2 parameters. */
$stmt->bind_param("is", $userid, $username);

$userid = 15;
$username = "don";

/* Executing the statement */
$stmt->execute( ) or die ("Could not execute statement");

Answer 3

I always do

$var = (int)$_POST['var'];

to make sure $var is handled as an integer in all circumstances, and never look at $_POST['var'] again. Looking at the manual, intval() does exactly the same.

Whichever way you go, in subsequence $var will be an actual integer, which is quite safe to handle.

Answer 4

Prepared statment is best way to deal sql injection. or use PDO otherwise, intval is better than is_numeric