mysql select issues

olechka

OK, this is really weird, and I am not sure whether it's a MySQL bug or a feature, but I don't like it.

Let's say I have a table that has a CHAR(60) field ('Name') and INT(11) field ('id').

The query I run is
SELECT * FROM test WHERE id = "dfgfg"

I think that it should either return an empty set (because obviously "dfgfg" is not an INT and can't possibly be a valid value), or, as a worst case scenario, break. Instead, it returns all rows where ID is 0.

The same happens if I look for rows where id="" (empty string).

Is there a way to make MySQL return only the rows that are REALLY equal to WHERE values?

I know that application should check all values that are passed to MySQL from the user... But if returning everything that equals to 0 whenever the type of a search value doesn't match the field type is expected behavior in MySQL, it's just scary.

tomhath

Yea, that's the defined behavior.

If you don't want to use php to ensure that you have a number, and if you insist on having IDs in your database that are zero (you're not using autoincrement?), then you can CAST the ID column to a varchar and use it as a string (since that's what you appear to be doing anyway). Something like this (untested):

select * from test where CAST(id as varchar) = "dfdf"

http://dev.mysql.com/doc/mysql/en/Cast_Functions.html

Sxooter

I know MySQL has a tendency to go against spec, but you should know that in standard SQL syntax, " is used to quote identifiers, and ' is used to quote values. Thus:

select * from table where id="abc";

tells the query parser to select all rows from table where id is equal to the value stored in column abc. Since there is no column abc, mysql sets that value to zero.

select * from table where id='abc'

tells the parser to select all rows from table where id is equal to the sting abc.

olechka

Thank you for your answers!

You know, I thought of that too, and I was hoping that changing quotes would help... It didn't. It produces exactly the same result whether I use "dfgdfg" or 'dfgdfg'.

As far as using autoincrement for ids so that they are never a 0... I just used ID as an example. It's true that ID won't normally be a zero, but I have a lot of columns where I store 0 or 1 as true / false, for instanse, or columns with numeric status codes that could be 0 or any other number.

I guess the only way is to check ALL values with PHP and hope that I don't miss anything, since the results could be pretty drastic...

Sxooter

The other option is to use a database that isn't quite so quick and easy with your data integrity, like firebird or postgres. Here's what postgres does:

select * from t where a='abc';
ERROR: invalid input syntax for integer: "abc"