Why this mysql statement returns whole table?

Question

I am wondering that how this query returns all data from the table:

SELECT * FROM users WHERE email = ''=' 'or'';

It gives the same output of this:

SELECT * FROM users;

It is probably works as this:

SELECT * FROM users WHERE true;

but, WHY?

Answer 1

Break the WHERE condition down, and this starts to make sense:

email = ''

All rows with an email attribute set to a blank string '' will return TRUE. For a minute, let's assume that all rows in your users table do not meet this condition, and will return FALSE when this query is run. That leads us to the next comparison:

FALSE=' '

This comparison returns TRUE; this functionality is explained well in this SO thread - basically, the single whitespace character is treated as a trailing space and is thus truncated by the engine for the purposes of comparison. The resulting blank string is "falsy" in MySQL, which would make FALSE=FALSE evaluate to TRUE.

Evaluated, this would look in all something like

WHERE TRUE or FALSE

... which would return all your rows.

---

While outside the scope of the question, it's not particularly clear what your original query is trying to achieve. If you're looking for empty e-mail address fields in your table, don't over-complicate it.