MySQL `WHERE` gives unselected results for matching 0

Question

I have a description of the user_info table 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 the table

 0 admin admin admin d033e22ae348aeb5660fc2140aec35850c4da997 [e-mail] a 1 staff Staffer staff d033e22ae348aeb5660fc2140aec35850c4da997 [e-mail] s 2 staff2 stafer staff2 d033e22ae348aeb5660fc2140aec35850c4da997 [e-mail] s 10 Shanoop Pattanath shan123456 5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8 [e-mail] 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 [e-mail] a 1 staff Staffer staff d033e22ae348aeb5660fc2140aec35850c4da997 [e-mail] s 2 staff2 stafer staff2 d033e22ae348aeb5660fc2140aec35850c4da997 [e-mail] s 

How does this query match all the data? this query should not yield any result, right? what have i done here? detailed answers are well rated by 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 quotation marks. I solved this problem by doing so. But why does MySQL give this wired output.?

Answer 1

It looks like a comparison is being made from int to int .

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

If you look at this SQL script , you will see that using CONVERT in the user_name and password field to make them int will output 0, which makes your comparison true.

If you want to compare two varchar values, make sure you select criteria using single quotes:

 user_name = '0' AND password = '0' 

Great question btw!

Answer 2

Check the fiddle .

The thing user_name is varchar , and 0 is INT .
You cannot compare a INT and a String .

In practice, you should put it in qoutes, for example:

 `user_name` = '0' -- wrong input AND `password` = '0' -- wrong input 

But if you want to make sure that the compared data will always be a string. You can try something like:

  WHERE `user_name` = CAST(0 AS CHAR); 

---

If you did

 SELECT * FROM supportContacts WHERE type = 1; 

He will not return anything.

Why? This is because it converts your column to an integer. Any field without a valid integer will be 0. You need to make sure that you are only comparing string fields with string values.