問題描述
MYSQL查詢神奇地抓取數值 (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
‑‑‑‑‑
參考解法
方法 1:
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)
方法 2:
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"
方法 3:
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
?>
方法 4:
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);
(by CyberJunkie、Wrikken、RiaD、Dejan Marjanović、genesis)