Make MySQL not convert a string to a number

Question

I have a query in my application that selects users from a table by id or username:

SELECT * FROM users WHERE id = '$x' OR username = '$x' 

This works if user names are specified, such as foo , bar123 or identifiers of type 1 , 123 . But when I give a username, for example 2foo , it selects 2foo user and user with id=2 . Therefore, it takes 2 of 2foo and finds the user. In addition, I get a warning message: 1292: Truncated invalid value DOUBLE: 2foo.

Is there a way to tell MySQL not to do this conversion (for this query, but not for the whole db)? Or do I need to do filtering after the query to discard false results?

Answer 1

Your query is formed in a way that causes the behavior of "this-is-a-feature-not-a-bug" in MySQL: you compare the same string ('$ x') with the numeric field (id) and in the varchar field (Username).

Although I'm sure there are ways to make this work in SQL, I suspect that the only right way is to fix the PHP that creates the query. Sort of

 if (is_numeric($x)) $sql="SELECT * FROM users WHERE id = '$x' OR username = '$x'"; else $sql="SELECT * FROM users WHERE username = '$x'"; 

should help.

Here is the SQL version, for completeness only:

 SELECT * FROM users WHERE id = IF('$x' REGEXP '^[0-9]+$','$x',0) OR username = '$x' 

Note: form OQ, I assume $ x is already escaped.