CODEWITHSUNDEEP
phpmysqldatabase
CyberJunkie
CyberJunkie

Reputation: 22674

MYSQL query magically grabs numeric value

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

Upvotes: 1

Views: 671

Answers (4)

Wrikken
Wrikken

Reputation: 70460

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)

Upvotes: 2

Dejan Marjanović
Dejan Marjanović

Reputation: 19380

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
?>

Upvotes: 1

genesis
genesis

Reputation: 50976

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);

Upvotes: 0

RiaD
RiaD

Reputation: 47620

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"

Upvotes: 1

Related Questions