预编译下的SQL注入

SQL注入是Web安全中影响比较深远的漏洞，但随着预编译框架的出现，SQL注入已经不再像之前那么泛滥，但是预编译是否适用所有的应用场景？是否使用了预编译，就能杜绝所有的SQL注入？如何在代码层面有效的防止SQL注入漏洞？

过程分析

• 开启MySQL日志功能

   # 查看是否开启日志功能以及日志文件所在目录
   mysql> show global variables like "%genera%";
   +------------------+---------------------------+
   | Variable_name    | Value                   |
   +------------------+---------------------------+
   | general_log      | OFF                        |
   | general_log_file | D:dataAMao.log  |
   +------------------+---------------------------+
   
   # 开启日志功能
   mysql> set global general_log = on;

• 执行简单预编译

  执行了一次编译和两次查询

   mysql> prepare stmt_name from 'select * from user where `name`=?';
   Query OK, 0 rows affected (0.00 sec)
   Statement prepared
   
   mysql> set @query_name='allen';
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> execute stmt_name using @query_name;
   +----+-------+--------+--------+------+
   | id  | name  | gender | age | flag |
   +----+-------+-----------+-----+------+
   |  1 | allen |             1 |  20 |     1 |
   +----+-------+-----------+-----+------+
   1 row in set (0.00 sec)
   
   mysql> set @query_name2 = 'joy';
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> execute stmt_name using @query_name2;
   +---+-------+----------+------+------+
   | id | name | gender | age | flag |
   +----+------+-----------+-----+------+
   |  6  | joy    |           1 |  21   |    1 |
   +----+-------+-----------+------+-----+
   1 row in set (0.00 sec)

• 查看日志

  这里去掉了一些非必要的信息

       Argument
   Prepare select * from user where `name`=?
   Query   set @query_name='allen'
   Queryexecute stmt_name using @query_name
   Execute select * from user where `name`='allen'
   Query   set @query_name2 = 'joy'
   Queryexecute stmt_name using @query_name2
   Execute select * from user where `name`='joy'

• 对比普通请求过程

    select * from user where name = 'joy';
   +----+------+--------+-----+------+
   | id | name | gender | age | flag |
   +----+------+--------+-----+------+
   |  6 | joy  |      1 |  21 |    1 |
   +----+------+--------+-----+------+

       Argument
   Query    select * from user where name = 'joy'

分析对比

• 普通SQL语句执行过程

  在数据库接收到SQL语句后，首先对其进行语义解析，生成语法树，随后对SQL语句进行优化并制定执行计划最终执行

• 预编译过程

  在预编译过程中，数据库首先接收到带有预编译占位符的SQL语句，解析生成语法树(Lex)，并缓存在cache中，然后接收对应的参数信息，从cache中取出语法树设置参数，然后再进行优化和执行。

  由于参数信息传入前语法树就已生成，执行的语法结构也就无法因参数而改变，自然也就杜绝了SQL注入的出现

  TIPS：预编译可以实现一次编译、多次执行，省去了解析优化等过程，能够提高效率

demo分析

 <?php
 try {
     $user = 'root';
     $pass = 'root';
     $name = $_GET['name'];
     $dbh = new PDO('mysql:host=localhost;dbname=tt', $user, $pass);
     // 设置为预编译模式，这里后面会展开讲，先mark
     $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
     $stmt = $dbh->prepare("select * from user where name = :user_name");
     $stmt->bindParam(':user_name',$name);
     if($stmt->execute()){
         while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
             print_r($row);
     }
     }
 } catch (PDOException $e) {
     die($e->getMessage());
     
 }
 ?>

• 提交 ?name=joy ，查看日志信息

   Connect      root@localhost on tt using TCP/IP
   Prepare      select * from user where name = ?
   Execute       select * from user where name = 'joy'
   Close stmt
   Quit

  整个流程分为五步：

• 连接

• 预编译

• 传入参数并执行

• 关闭预编译语句

• 退出

• 提交 ?name=joy%27and 1=1%23 ，查看日志

   Connect      root@localhost on tt using TCP/IP
   Prepare      select * from user where name = ?
   Execute       select * from user where name = 'joy'and 1=1#'
   Close stmt
   Quit

  从日志可以发现，这里的 ' 被转义，而引号内部的 # 自然不会被解析成注释符号

由单引号被转义联想到两个点：一个是整型注入，另一个是宽字节注入

整形注入是不需要单引号闭合的，而宽字节注入则可以绕过单引号的转义

整型注入

修改上面demo的代码中的预编译语句为：

$stmt = $dbh->prepare("select * from user where id = :user_id");

提交 ?id=1 and 1=1# ，查看日志

Prepare	select * from user where id = ?
Execute	select * from user where id = '1 and 1=1#'

查看日志发现，在执行的SQL语句中，变量位置还是加了引号，所以使用了预编译后，并不存在整型注入

并且由于MySQL的弱类型转换，并不影响查询结果

mysql> select * from user where id =1;
+--+-------+--------+-----+----+
| id | name  | gender | age | flag |
+--+-------+--------+-----+----+
|  1 | allen    |          1 |    20 |    1 |
+--+-------+--------+-----+----+

mysql> select * from user where id ='1';
+--+-------+--------+-----+----+
| id | name  | gender | age | flag |
+--+-------+--------+-----+----+
|  1 |   allen |           1 |    20 |    1 |
+--+-------+--------+-----+----+
1 row in set (0.00 sec)

mysql> select * from user where id ='1aaa';
+--+-------+--------+-----+----+
| id | name  | gender | age | flag |
+--+-------+--------+-----+----+
|  1 |    allen |          1 |    20 |    1 |
+--+-------+--------+-----+----+

宽字节注入

在上述demo中添加设置编码方式的代码：

$dbh -> exec("set names 'GBK'");

提交参数：?id=1%df%27union select 1,user(),version(),4,5%23

查看日志：

Connect	root@localhost on tt using TCP/IP
Query	set names 'GBK'
Query	select * from user where id = '1運'union select 1,user(),version(),4,5#'
Quit	

利用条件：PHP version < 5.3.6

应用场景

ASC/DESC

应用场景：当应用显示多条数据时，通常可以选择正向排序或者逆向排序，此时就会用到 ASC/DESC

ASC/DESC 是SQL语句中影响语义的关键字，是不能用单引号引起来的

mysql> select * from user order by age asc;
+--+-------+--------+-----+----+
| id | name  | gender | age | flag |
+--+-------+--------+-----+----+
| 18 | Mike   |           1 |      8 |    1 |
| 17 | Jack    |           1 |    18 |    1 |
|  1  | allen   |           1 |    20 |    1 |
| 16 | James |           1 |    30 |    1 |
+--+-------+--------+-----+-----+
4 rows in set (0.00 sec)

mysql> select * from user order by age 'asc';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''asc'' at line 1

mysql> prepare stmt_name from 'select * from user order by age ?';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

<?php
	if($_POST['order'] === 'DESC'){
        $order = 'DESC';
    }else{
        $order = 'ASC'
    }

表名/字段名

表名与列名是不能被预编译的，这是由于在预编译生成语法树的过程中，预处理器在检查解析后的语法树时，会确定数据表和数据列是否存在，此两者必须为具体值，不能被占位符 ? 所替代

mysql> select * from user;
+--+-------+--------+-----+----+
| id | name  | gender | age | flag |
+--+-------+--------+-----+----+
|  1  | allen   |           1 |   20 |    1 |
| 18 | Mike   |           1 |     8 |    1 |
| 17 | Jack    |           1 |   18 |    1 |
| 16 | James |           1 |   30 |    1 |
+--+-------+--------+-----+----+
4 rows in set (0.00 sec)

mysql> prepare stmt_table from 'select * from ?';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

mysql> select name from user;
+-------+
| name  |
+-------+
| allen    |
| Mike    |
| Jack     |
| James  |
+-------+
4 rows in set (0.00 sec)

mysql> prepare stmt_c from 'select ? from user';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @query_c = 'name';
Query OK, 0 rows affected (0.00 sec)

mysql>  execute stmt_c using @query_c;
+------+
| ?        |
+------+
| name |
| name |
| name |
| name |
+------+
4 rows in set (0.00 sec)

order by

order by 用来指定某个字段作为排序依据，前面也解释了字段名不能使用预编译

mysql> select * from user order by age;
+--+-------+--------+-----+----+
| id | name  | gender | age | flag |
+--+-------+--------+-----+----+
|  2 |    alice |           2 |   18 |    1 |
|  1 |    allen |           1 |  20 |     1 |
|  3 |   bob   |           1 |  21 |     1 |
+--+-------+---------+---+----+

mysql> select * from user order by 'age';
+--+-------+--------+-----+----+
| id | name  | gender | age | flag |
+--+-------+--------+-----+----+
|  1 |   allen |           1 |    20 |    1 |
|  2 |   alice |            2 |   18 |    1 |
|  3 |  bob   |            1 |   21 |    1 |
+--+-------+--------+-----+----+

$stmt = $pdo->prepare("select * from user order by ?");  
$stmt->bindValue(1,'age');  

/*
最后执行的SQL语句为：select * from user order by 'age'
也就达不到排序的效果
执行结果：
   	Array ( 
    [0] => Array ( [id] => 1 [name] => allen [gender] => 1 [age] => 20 [flag] => 1 ) 
    [1] => Array ( [id] => 2 [name] => alice [gender] => 2 [age] => 18 [flag] => 1 ) 
    [2] => Array ( [id] => 3 [name] => bob [gender] => 1 [age] => 21 [flag] => 1 ) 
	)

• 前端表单

  <form action="" method="post">
      <select name="order">
          <option value="0">id</option>
          <option value="1">name</option>
          <option value="2">age</option>
      </select>
      <input type="submit" name="submit">
  </form>

• 白名单函数

  <?php
      $i = $_POST['order'];
  	switch($i){
          case 0:
              $order = "id";
              break;
          case 1:
              $order = "name";
              break;
          default:
              $order = "age";
              break;
      }

模拟预编译

注释掉Demo代码中的：

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

查看日志，发现没有进行预编译操作

Connect	root@localhost on tt using TCP/IP
Query	select * from user where name = 'allen'
Quit	

进行注入测试，发现进行了转义

root@localhost on tt using TCP/IP
Query	select * from user where name = 'allen''
Quit	

这里总结了一些常见接口的默认预编译方式：

总结

参考

• Link1:https://xz.aliyun.com/t/7132

• Link2:https://blog.nowcoder.net/n/be73b8f592504ae8b1d00368433061be

本文始发于微信公众号（山石网科安全技术研究院）：预编译下的SQL注入