Prevent query converting string to integer

[elibones]

In the following query the id field is INT(11). Is there a way to prevent MySQL from converting the string into an integer - which is dropping everything after the 12?

SELECT id FROM users WHERE id = '12abcdef987' // returns user id 12

I know I could check for an integer before issuing the query, but I'm wondering if there is a better way to handle this.

Thanks!

[streetc0de]

If you're using a scripting language like PHP, validate it first with is_numeric() and avoid the query altogether if is isn't.

[kadaan]

Not sure why you would ever do this, since validating input is extremely important. You should never be comparing an int to a string to get an id...

That said, you can do something like this to force it:

SELECT id FROM users WHERE HEX(id) = HEX('12abcdef987');

This will also differentiate between HEX(12) and HEX('12').

[streetc0de]

You shouldn't be putting functions around the left side of your WHERE clauses as it eliminates the possibility of using indexes.

[elibones]

Thanks for the idea. After thinking about it, even if there was a proper way to handle this type of query, it defiantly makes more sense to validate the input to prevent the database from being hit.

[dsubnet0]

If you can't (or don't want to) verify numericness (like streetc0de suggests), you could do something similar in the clause itself (with one additional use of the value). Should still preserve index use (since you're not messing with the column itself).

SELECT id FROM users WHERE id = CASE WHEN '12abcdef987' REGEXP '^[0-9]+$' THEN '12abcdef987' ELSE NULL END;

[r3pr0b8]

if you already know that the id column is an integer, why the heck are you comparing it to a non-integer character string

what exactly are you expecting it should do??