mySQL returns all rows when field=0

mysql select where-clause
AFRC picture AFRC · Oct 24, 2011 · Viewed 10k times · Source

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

Kibbee picture Kibbee · Oct 24, 2011

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';

Related questions

CodeIgniter: How to use WHERE clause and OR clause

I am using the following code to select from a MySQL database with a Code Igniter webapp: $query = $this->db->get_where('mytable',array('id'=>10)); This works great! But I want to write the following MySQL statement …

Read More
MySQL - UPDATE query based on SELECT Query

I need to check (from the same table) if there is an association between two events based on date-time. One set of data will contain the ending date-time of certain events and the other set of data will contain the …

Read More
SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More