Why does MySQL return rows that seemingly do not match the WHERE clause?

Question

suppose the user input

mysite.com/profile?identity=1 mysite.com/profile?identity=dinodsja mysite.com/profile?identity=1a 

getting the value

$identity = $_GET['identity']; // identity can be user_id or user_name 

and i have a simple select query:

SELECT * FROM lb_users WHERE (user_id = 'dinodsja' OR user_name = 'dinodsja') AND user_status = 1 

and it works fine. but the problem is:

SELECT * FROM lb_users WHERE (user_id = '1a' OR user_name = '1a') AND user_status = 1 

when I execute this query it also returns the result without satisfying the condition.

Table structure:

user_id     bigint(25) user_name   varchar(50)     utf8_general_ci 

**

-> Is this a MySQL Bug ?  -> How can we avoid this ?  -> What will be the query ? 

**

Answer 1

The reason for that is because the data type of the column user_ID is integer.

MySQL silently drops any trailing NON-Number (and anything that follows within) in the value and that is why 1a is equal to 1 since a will be remove in the value.

• SQLFiddle Demo

Answer 2

I do remember having a similar problem long ago.

First some background: This is not a bug. It is actually a feature. Ok, it's one that might lead to such unexpected behaviour, but MySQL is thereby very tolerant w.r.t. user inputs, respective select queries:

mysql> SELECT 'a' = 'a ';         -> 1 mysql> SELECT 'A' = 'a';         -> 1 

Therefore, with implicit type conversion, the result of, e.g, '1a' in INTEGER is 1, but also:

mysql> SELECT 0 = 'x6';         -> 1 mysql> SELECT 1 = ' 1';         -> 1 mysql> SELECT 1 = ' 1a';         -> 1 

This feature is also implemented in other not statically typed languages. PHP, for instance, calls this type juggling. See the PHP String conversion rules and this example from the documentation:

<?php   $foo = "0";                     // $foo is string (ASCII 48)   $foo += 2;                      // $foo is now an integer (2)   $foo = $foo + 1.3;              // $foo is now a float (3.3)   $foo = 5 + "10 Little Piggies"; // $foo is integer (15)   $foo = 5 + "10 Small Pigs";     // $foo is integer (15) ?> 

See JavaScript:

<script>   document.write(parseInt("40 years") + "<br>"); </script>  => 40 

Nevertheless, the solution to your problem is pretty easy: Just cast the integer to a char and do the comparison then:

mysql> SELECT * FROM lb_users WHERE (CAST(user_id AS CHAR) = '1' OR user_name = '1')         -> 1 mysql> SELECT * FROM lb_users WHERE (CAST(user_id AS CHAR) = '1a' OR user_name = '1a')         -> 0 mysql> SELECT * FROM lb_users WHERE (CAST(user_id AS CHAR) = 'dinodsja' OR user_name = 'dinodsja')         -> 1 

I made a fiddle for everyone to try it out: http://sqlfiddle.com/#!2/c2835/14/0

Hope that helps,

-Hannes