MYSQL query magically grabs numeric value

Question

I have the following query

SELECT * FROM (`user_profiles`) WHERE `user_id` = $user_id LIMIT 1

$user_id is a URI segment. For instance $user_id = 64 would produce

SELECT * FROM (`user_profiles`) WHERE `user_id` = '64' LIMIT 1

If I add alphabetical characters to the user id, e.g. http://www.mysite.com/profile/64kjdsg

I get:

SELECT * FROM (`user_profiles`) WHERE `user_id` = '64kjdsg' LIMIT 1

which still returns the correct data although there is no user id equal to 64kjdsg. The user id column in my table is int(11). The query seems to automatically grab the numeric value from 64kjdsg and match that in the db table. Is this a MYSQL function I'm not aware of?

How is this happening? I'm querying using the Codeigniter framework if that makes ant difference.

UPDATE: found a similar question MySQL integer comparison ignores trailing alpha characters

If you throw quotes around the user id in the query I believe it is treated initially as text. If you remove the quotes you should get a mysql error upon non-numeric input — MoarCodePlz
– MoarCodePlz, Commented Jul 17, 2011 at 19:29
This is not real code, is it? In MySQL, back ticks are used in table or column names, not in strings. — Álvaro González
– Álvaro González, Commented Jul 17, 2011 at 19:31
@ Álvaro G. Vicario, oops my mistake, I corrected the code. It does not have back ticks in the script. — CyberJunkie
– CyberJunkie, Commented Jul 17, 2011 at 19:34

Wrikken · Accepted Answer · 2011-07-17 19:34:28Z

As you are comparing to a numeric column, MySQL casts your string to a number (so it removes everything from the occurance of the first non-number character). It's its default behavior:

mysql> select '23andthensome' + 4;
+---------------------+
| '23andthensome' + 4 |
+---------------------+
|                  27 |
+---------------------+
1 row in set, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '23andthensome' |
+---------+------+---------------------------------------------------+
1 row in set (0.02 sec

So, make more proper queries, check beforehand wether something is a number (filter_var(FILTER_VALIDATE_INT,$id);), only use it when it is, and then: don't send it as a string to MySQL: if you want to compare numbers, send the number, which should not be quoted.

Alternatively, you can let MySQL do the work, but it seems a waste:

mysql> select 23 = '23andthensome';
+----------------------+
| 23 = '23andthensome' |
+----------------------+
|                    1 |
+----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select cast(23  as CHAR) = '23andthensome';
+-------------------------------------+
| cast(23  as CHAR) = '23andthensome' |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (0.02 sec)

RiaD · Accepted Answer · 2011-07-17 19:28:23Z

1

Check in your script is urlsegment integer. You can use ctype_digit to do it. If isn't, don't touch your db. Say "No such user"

answered Jul 17, 2011 at 19:28

RiaD

47.8k12 gold badges85 silver badges128 bronze badges

Comments

Dejan Marjanović · Accepted Answer · 2011-07-17 19:29:36Z

1

Check this out: http://ideone.com/khpEv, it is called type juggling. If you '64kjdsg' string will be converted to integer (64) because user_id is INT else it will produce syntax error.

PHP example:

<?php
echo (int) '64kjdsg'; // 64
?>

answered Jul 17, 2011 at 19:29

Dejan Marjanović

19.4k7 gold badges54 silver badges67 bronze badges

Comments

genesis · Accepted Answer · 2011-07-17 19:28:44Z

0

this is security hole however, if

$user_id = 5;

result is

SELECT * FROM (`user_profiles`) WHERE `user_id` = 5 LIMIT 1

not

SELECT * FROM (`user_profiles`) WHERE `user_id` = `5` LIMIT 1

try to use intval()

$user_id = intval($user_id);

answered Jul 17, 2011 at 19:28

genesis

51.1k20 gold badges99 silver badges127 bronze badges

Collectives™ on Stack Overflow

MYSQL query magically grabs numeric value

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related