MySQL `WHERE ` is giving unexpeted results for matching 0

Question

I have user_info table description as follows

Field          Type         null Type        Extra
usr_id     int(11)           NO  PRI           auto_increment
f_name     varchar(50)       NO         
l_name     varchar(50)       YES            
user_name  Varchar(45)       NO         
password   varchar(128)      NO         
email      varchar(50)       NO         
type       enum('a','s','c') NO 

Data inside table

0   admin   admin   admin   d033e22ae348aeb5660fc2140aec35850c4da997    [email protected]   a
1   staff   Staffer staff   d033e22ae348aeb5660fc2140aec35850c4da997    [email protected]   s
2   staff2  stafer  staff2  d033e22ae348aeb5660fc2140aec35850c4da997    [email protected]  s
10  Shanoop Pattanath   shan123456  5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8    [email protected]   s

SQL query

SELECT 
    *
FROM
    (`user_info`)
WHERE
    `user_name` = 0  -- wrong input 
        AND `password` = 0 -- wrong input
ORDER BY `usr_id`;

Result for this query

0   admin   admin   admin   d033e22ae348aeb5660fc2140aec35850c4da997    [email protected]   a
1   staff   Staffer staff   d033e22ae348aeb5660fc2140aec35850c4da997    [email protected]   s
2   staff2  stafer  staff2  d033e22ae348aeb5660fc2140aec35850c4da997    [email protected]  s

how come this query matching with all data ? this query should not give any result, should it ? what wrong I have done here ? detailed answers well appreciated MySQL ver : 5.5.35-0ubuntu0.13.10.2 (Ubuntu). SQL FIddle

All username,password and emails are just imaginary

Update I know that 0 must be inside quotes. I solved this problem by making like that. But how come MySQL give this wired output. ?

Answer 1

It appears that the comparison being performed is an int to an int.

MySQL is converting the text in user_name and password to an int for comparison purposes. The MySQL documentation here indicates that varchar will be converted to int in this kind of operation.

If you take a look at this SQL Fiddle you will see that using CONVERT on the user_name and password field to make them int will output 0, hence making your comparison true.

If you mean to do a comparison of two varchar values, make sure that you surroung your criteria with single-quotes:

user_name = '0'
AND password = '0'

Great question btw!