how to cast bigint to prevent sql injection in php?

Question

i am using php and running sql queries on a mysql server. in order to prevent sql injections, i am using mysql_real_escape_string.

i am also using (int) for numbers casting, in the following manner:

$desired_age = 12;
$query = "select id from users where (age > ".(int)$desired_age.")";
$result = mysql_query($query);

that work.

But, when the variable contains larger numbers, casting them fails since they are larger than int.

$user_id = 5633847511239487;
$query = "select age from users where (id = ".(int)$user_id.")";
$result = mysql_query($query);
// this will not produce the desired result, 
// since the user_id is actually being cast to int

Is there another way to cast large number (like BIGINT), except for the use of mysql_real_escape_string, when is comes to sql injection prevention?

Answer 1

Validate input. Don't just simply escape it, validate it, if it's a number. There're couple of PHP functions which do the trick, like is_numeric() - Finds whether a variable is a number or a numeric string

http://www.php.net/is_numeric

Answer 2

If you are generating the user ID yourself there is no need to cast it for MySQL since there is no chance of SQL injection or other string issues.

If it is a user submitted value then use filter_var() (or is_numeric()) to verify it is a number and not a string.

Answer 3

You could use something like:

preg_replace('/[^0-9]/','',$user_id);

to replace all non numeric symbols in your string. But there actually is no need to do so, simply use mysql_real_escape_string() as your integer value will be converted to a string anyway once $query is built.

Answer 4

Use server-side prepared, parametrized statements (and thus remove the need for xyz_real_escape_string()) and/or treat the id as a string. The MySQL server has built-in rules for string<->number conversions and if you should decide to change to type/structure of the id field you don't have to change the php code as well. Unless you have concrete needs for (micro-)optimization there's usually no need to let the code make this kind of assumptions about the structure and value range of an id field in the database.

$pdo = new PDO('mysql:...');
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$stmt = $pdo->prepare('SELECT age FROM users WHERE id=?');
$stmt->execute(array('5633847511239487'));