Dunhamzzz
Dunhamzzz

Reputation: 14808

Does is_numeric() mean a var is safe for MySQL?

Question says it all hopefully, if I check a variable returns true for is_numeric(), is it ok to put directly into the MySQL query, or do I need to apply standard escaping? I'm thinking null character, overflow exploits and stuff.

An ambiguous example would be:

if(is_numeric($_GET['user_id'])) {
    mysql_query("SELECT * FROM `users` WHERE id = ".$_GET['user_id']);
}

The datatype in MySQL is INT().

Upvotes: 7

Views: 4720

Answers (4)

Jirka Kopřiva
Jirka Kopřiva

Reputation: 3099

Because the programm must be ready for variant for no-existing id this single row should be enough:

mysql_query(sprintf("SELECT * FROM `users` WHERE `id` = %d LIMIT 1",$_GET['user_id'])); 

Whatever what will not be a decimal number we pass inside the sprintf will be turned to decimal. The zero (~ bad input) has the same state as no existing id.

Saving condition and declaring extra variable.

Upvotes: -1

deceze
deceze

Reputation: 522382

Considering that "10e3" is_numeric, no.

If you want numbers (as in, only digits), you'll have to check for ctype_digit (which would still break SQL for numbers like 0123) or cast the number to an int or float. If it's acceptable for the number to be something other than all digits, you'll need to apply the SQL safe escaping and quoting.

Upvotes: 5

Matt Asbury
Matt Asbury

Reputation: 5662

From http://php.net/manual/en/function.is-numeric.php:

Be careful when using is_numeric() to escape SQL strings. is_numeric('0123') returns true but 0123 without quotes cannot be inserted into SQL. PHP interprets 0123 without quotes as a literal octal number; but SQL just throws a syntax error.

Upvotes: 4

fire
fire

Reputation: 21531

The safest way in my opinion is to convert the user_id to an integer, if it's invalid it will return 0.

$user_id = (int) $_GET['user_id'];

if ($user_id > 0) {
    mysql_query("SELECT * FROM `users` WHERE `id` = " . $user_id);
}

Upvotes: 8

Related Questions