Make MySQL not to convert string to number

Question

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

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

This is working when given usernames like foo, bar123 or ids like 1, 123. But when I give a username like 2foo it selects both user 2foo and user with id=2. So it takes the 2 of 2foo and finds a user. Additionally I get a warning message: 1292: Truncated incorrect DOUBLE value: 2foo.

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

Could you share the relevant piece of code that builds the actual query? This is most likely not a problem in mysql, but in the generated question. — Joachim Isaksson
– Joachim Isaksson, Commented Jan 9, 2012 at 15:33
I entered this query into MySQL Query Browser: SELECT * FROM users WHERE id = '2foo' OR username = '2foo'. There is no surrounding code as I identified this query as the problematic one from my application. MySQL-Version: 5.1.41-3ubuntu12.10 — arnep
– arnep, Commented Jan 9, 2012 at 15:38
The correct thing to do would be to check if $x is a number and build your query accordingly:if(is_numeric($x)){ $sql = "SELECT * FROM users WHERE id = $x OR username = '$x'" } else { $safeX = mysql_real_escape_string($x); // Or prepared statements, or whatever else you prefer. $sql = "SELECT * FROM users WHERE username = '$x'" } — Jory Geerts
– Jory Geerts, Commented Jan 9, 2012 at 15:42
Out of curiosity, what do you do if a user enrolls with username '123' ? — pilcrow
– pilcrow, Commented Jan 9, 2012 at 16:24
@JoryGeerts that is my solution now, thank you very much for your ideas (post it as an answer to get upvotes) — arnep
– arnep, Commented Jan 9, 2012 at 16:25

Eugen Rieck · Accepted Answer · 2012-01-09 15:59:14Z

5

Your query is formed in a way, that triggers a "this-is-a-feature-not-a-bug" behaviour in MySQL: You compare the same string ('$x') to a numeric field (id) and to a varchar field (username).

While I am sure, there are ways to make this work in SQL, I suspect the only correct way is to fix the PHP that creates the query. Something like

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, just for the sake of completeness:

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

Note: Form the OQ I assume, that $x is already escaped.

edited Jan 9, 2012 at 15:59

answered Jan 9, 2012 at 15:41

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

pilcrow Over a year ago

(1) That REGEXP will match '2foo' — you need to anchor it on both ends. (2) I'd suggest IF(val REGEXP ..., val, NULL), since there might be an id that equals zero in the OP's table, but there won't be an id that equals NULL.

Eugen Rieck Over a year ago

My bad, fixed the regex. As for the 0/Null I am quite sure the users table has id int auto_increment ...

pilcrow Over a year ago

how can you be sure? Did you see the OP's DDL? What about NO_AUTO_VALUE_ON_ZERO, precisely for storing zeroes in AUTO_INCREMENT columns?

Eugen Rieck Over a year ago

I can't be sure! But from the OQ's tone and content I assume the OP will be able to adapt the SQL to whatever he needs, if (and only if) he choses not to go with my suggestion to fix the PHP

arnep Over a year ago

@EugenRieck @pilcrow I used your suggestion to distinguish by is_numeric/is_string. Using an if having two SQL queries with a index on each queried column is faster than using a regex I think, also it is cleaner code. The ID is indeed the auto-incremented primary key. "this-is-a-feature-not-a-bug-but-none-the-less-throws-a-warning" ;-)

Collectives™ on Stack Overflow

Make MySQL not to convert string to number

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related