mysql string conversion return 0

Question

why select 'aaa' =0 return 1 (TRUE) if i have a table like

userid | pass

user1  | pas1

if I query :

select from table where userid = 0 and pass =0 

it gives me all the rows?

Answer 1

MySQL sees 'aaa' = 0 and thinks to itself:

"I can either convert aaa to an integer, or 0 to a string."

Guess which one it goes with?

Basically what's happening is that 'aaa' is being converting to an integer, and as it's not a valid integer, it casts to 0.

0 = 0 is of course true (or true == 1).

I suspect the same is happening with your userid column, though without knowing its values/datatype, it's hard to say.

http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html