select - mySQL returns all rows when field=0

Question

I was making some tests, and it was a surprise when i was querying a table, and the query SELECT * FROM table WHERE email=0 returned all rows from the table.

This table has no '0' values and it's populated with regular e-mails.

Why this happens? This can lead to serious security problems.

Is there a way to avoid this without modifying the query?

Am i missing something here?

Thanks.

Answer 1

This is because it is converting the email field (which I assume is a varchar field) to an integer. Any field without a valid integer will equate to 0. You should make sure that you only compare string fields to string values (same goes for dates, comparing to dates). The query should be as follows.

SELECT * FROM table WHERE email='0';