以避免 SQL Injection
但仍有方法繞過 mysql_real_escape_string 的過濾功能
參考 http://www.blackhatlibrary.net/SQL_injection
例如 client 下的 URI
/article.php?id=1 UNION ALL SELECT 1,group_concat(table_name,0x2e,column_name) from information_schema.columns where table_schema=database()
article.php 中的寫法類似：
<?php $id = _GET['id']; //這時，$id = '1 UNION ALL SELECT 1,group_concat(table_name,0x2e,column_name) from information_schema.columns where table_schema=database() $sqlcmd = "SELECT id,account FROM AA WHERE id=" . mysql_real_escape_string($id);
組合出來的新的 $sqlcmd 變成
SELECT id,account FROM AA WHERE id=1 UNION ALL SELECT 1,group_concat(table_name,0x2e,column_name) from i
nformation_schema.columns where table_schema=database();
這命令撈出來的資料類似：
mysql> SELECT id,account FROM AA WHERE id=1 UNION ALL SELECT 1,group_concat(table_name,0x2e,column_name) from information_schema.columns where table_schema=database()\G
*************************** 1. row ***************************
id: 1
account: item_recommend.Item_recommend_no,item_recommend.Item_no,item_recommend.status,AA.id,AA.account,advert.ad_id,advert.ad_title,advert.ad_ph
oto,tupu
1 row in set (0.01 sec)
mysql>
client 再多加工 可以變出更多花樣
PS: php中若是這樣就避開問題
$sqlcmd = "SELECT id,account FROM AA WHERE id='" . mysql_real_escape_string($id) . "'"; //前後多了 ' 符號
對於整數資料的 id ，直接 intval 才是王道
$id = intval( $_GET['id'] );
特別注意 intval 分 32 / 64bits
參考 PHP 手冊：
http://php.net/manual/en/function.intval.php
The integer value ofvaron success, or 0 on failure. Empty arrays return 0, non-empty arrays return 1.The maximum value depends on the system. 32 bit systems have a maximum signed integer range of -2147483648 to 2147483647.
So for example on such a system, intval('1000000000000') will return 2147483647.
The maximum signed integer value for 64 bit systems is 9223372036854775807.Strings will most likely return 0 although this depends on the leftmost characters of the string. The common rules of integer casting apply.
一些 32bits 的範例
<?php
echo intval(42); // 42
echo intval(4.2); // 4
echo intval('42'); // 42
echo intval('+42'); // 42
echo intval('-42'); // -42
echo intval(042); // 34
echo intval('042'); // 42
echo intval(1e10); // 1410065408
echo intval('1e10'); // 1
echo intval(0x1A); // 26
echo intval(42000000); // 42000000
echo intval(420000000000000000000); // 0
echo intval('420000000000000000000'); // 2147483647
echo intval(42, 8); // 42
echo intval('42', 8); // 34
echo intval(array()); // 0
echo intval(array('foo', 'bar')); // 1
?>
參考資料：
http://www.blackhatlibrary.net/SQL_injection
http://www.bollymovies.ucoz.com/index/sql_injection_tutorial_mysql/0-73
http://hi.baidu.com/all3g/item/3f3d73aa9547d39b1510738d
http://lists.mysql.com/announce/364
