本篇主要分享sql注入防御之预编译的原理，并解释模糊查询语句如何适配预编译。预编译是利用占位符替代参数值，预先建立语法树的过程。注入的恶意SQL语句只会被视为参数，参与不了SQL语句的语法树构建，也就无法改变其语法结构，也就无法达到编译恶意语句的目的。然而，预编译有一些局限性，比如，模糊查询由于存在% _使得参数值是不确定的，需要修改预编译语句。

1 SQL注入原理

sql注入是指攻击者拼接恶意SQL语句到接受外部参数的动态SQL查询中，程序本身未对插入的SQL语句进行过滤，导致SQL语句直接被服务端执行。
拼接的SQL查询例如，通过在id变量后插入or 1=1这样的条件，来绕过身份验证，获得未授权数据的访问权。

1
SELECT * FROM user WHERE id = -1 or 1=1

由于or 1=1 满足永真结果，sql语句会执行输出user中的全部内容。

拼接后的执行结果示例如上

2 SQL注入的防御方式

  1. 定制严格的白名单校验，加强对用户输入的验证，限制用户输入。限制用户输入内容的大小和数据类型，强制执行适当的限制与转换，并在用户提交请求的时候进行检查，凡不符合该类型的提交就认为是非法请求。限制查询长度：SQL注入需要构造较长的SQL语句。

  2. 设置数据库权限，遵循最小化原则。根据程序要求为特定的表设置特定的权限，如：某段程序对某表只需具备select权限即可，这样即使程序存在问题，恶意用户也无法对表进行update或insert等写入操作。严格区分普通用户与管理员用户的权限。如果页面查询用户使用的是root，注入时被带入了drop table,drop database等语句，后果将不堪设想 。

  3. 限制目录权限：WEB目录应至少遵循“可写目录不可执行，可执行目录不可写”的原则，在此基础上，对各目录进行必要的权限细化。建议是不要给执行权限。

  4. 预编译。使用参数而不是将用户输入变量嵌入到SQL语句中，可以杜绝大部分的SQL注入式攻击。

那么，终于引出文章的主角——预编译。

以MySQL为例，数据库在执行SQL语句时，需要经历7个步骤：

  • 词法分析：将SQL语句分解成一个个token（关键字、标识符、运算符），然后对token进行分类和解析，生成相应的数据结构。

  • 语法分析：根据SQL语法检测规则检查语法是否正确，并生成语法树。

  • 语义分析：遍历语法树，确定表和列等信息，同时检查语义的正确性。优化处理：使用优化器对SQL语句进行处理和优化，比如执行计划、索引等。

  • 执行计划：使用执行计划生成器生成SQL语句的执行计划，比如数据的访问方式，索引的使用方式等。

  • 引擎执行：将执行计划发送给相应的数据库引擎进行处理，执行计划被翻译成底层的操作指令，执行数据扫描、索引查找、排序、分组等操作。

  • 返回数据：将执行结果返回给客户端，比如查询结果集或操作结果。在这里，我们粗暴的把执行过程理解成两步，即：先编译SQL语法结构（13步），再执行SQL语句（47步）。

SQL编译的过程包括：

  • 词法分析 Scanning 分词

  • 语法分析 Syntax 组句

  • 语义分析 Semantic 识意 收集标识符的属性信息 和 对语义的检查（检查合法性）

3 预编译的原理

预编译：用占位符替代参数值，预先建立语法树。恶意语句不参与语法书的建立，所以不影响SQL语法，也就无法造成恶意注入。最初的目的是提高代码的复用性，因为有很多只有参数值不同的SQL（完全相同的SQL会从缓存里查），比如：

1
2
select * from user where id='1'
select * from user where id='2'

这些SQL的语法树相同，但每次都要进行重复的编译，很浪费时间。SQL预编译将SQL语句模板化，用占位符替代值（参数化绑定）并存储在数据库中，以便在需要时再传入值执行，省掉了重复建立语法树的时间，实现快速执行。

1
select * from user where id={占位符}

以MySQL为例，利用mysqli的预编译功能编写的核心PHP语句为：

//定义需要预编译的SQL语句，从外界传递的参数（输入）用占位符?表示

1
2
3
4
5
6
7
8
9
$sql= "SELECT FROM security.users WHERE id= ? LIMIT 0,1";
//创建预处理对象
$mysqli_stmt = $mysqli->prepare($sql);
//绑定参数
$mysqli_stmt->bind_param('i', $id);
//绑定结果集
$mysqli_stmt->bind_result($id, $username, $password);
//执行
$mysqli_stmt->execute();

预编译语句的优势在于：一次编译、多次运行，省去了解析优化等过程。

预编译防止SQL注入的原理

正常情况下，用户输入的参数会直接参与SQL语法的编译，而预编译则是先构建语法树，确定SQL语法结构以后，再拼接用户的参数。注入的恶意SQL语句只会被视为参数，参与不了SQL语句的语法树构建，也就无法改变其语法结构，也就无法达到编译恶意语句的目的。

比如：不使用预编译，用单引号提前闭合的方式注入恶意SQL语句

1
select count(1) from students where name='张三' or '1=1'；

使用了预编译：

1
select count(1) from students where name='张三” or “1=1'；

这里payload是张三’ or ‘1=1，会被当做参数，单引号会被转义成’’，从数据库查的时候，查的就是name=’张三’ or ‘1=1’,而数据库不会存在名字为’张三’ or ‘1=1’的人，所有查不到。

4 预编译的局限性

预编译的机制是先编译，再传值，用户传递的参数无法改变SQL语法结构，从根本上解决了SQL注入的问题。但并不是所有参数都可以使用预编译。

  • 比如动态表名和列名的场景：在生成语法树的过程中，预处理器在进一步检查解析后的语法树时，会检查数据表和数据列是否存在，因此数据表和数据列不能被占位符?所替代。但在很多业务场景中，表名需要作为一个变量存在，因此这部分仍需由加号进行SQL语句的拼接，若表名是由外部传入且可控的，仍会造成SQL注入。

  • 动态传参的场景，如动态表名，列名，Order by $param，不能使用预编译，应该使用严格的白名单校验。order by后一般是接字段名，而字段名是不能带引号的，比如 order by username；如果带上引号成了order by ‘username’，那username就是一个字符串不是字段名了，这就产生了语法错误。一方面预编译又只有自动加引号的setString()方法，没有不加引号的方法；而另一方面order by后接的字段名不能有引号。

  • 凡是字符串但又不能加引号的位置都不能参数化；包括sql关键字、库名表名字段名函数名等等。不能参数化的位置。不管怎么拼接，最终都是和使用“+”号拼接字符串的功效一样：拼成了sql语句但没有防sql注入的效果。

5 模糊查询预编译

模糊查询本身并不支持预编译，占位符 ? 不适用于模糊查询中的通配符 %。占位符只能用于替换具体的值，而不能用于替换SQL语句中的其他结构，如通配符或标识符，因为预编译需要明确的参数值来进行参数绑定，所以无法进行预编译。例如：

1
SELECT * FROM users WHERE name LIKE ‘%abc%’

这个查询会返回名字中包含“abc”的所有用户。但是，由于通配符的存在，参数值是不确定的，因此无法进行预编译。当执行如下语句时，会报错：

1
SELECT * FROM users WHERE name LIKE ‘%?%’

报错：Parameter index out of range (1 > number of parameters, which is 0).

%?%是字符串，所以不会被当做一个参数解析。

解决方案是修改语句如下：

1
2
3
4
5
6
7
select id,name,age from people where address LIKE

concat('%',?,'%') order by id desc；

select id,name,age from people where address LIKE

concat('%',#{key,jdbcType=VARCHAR},'%') order by id desc；

Mybatis场景下$和#的区别

${}:表示拼接sql串，将接收到参数的内容不加任何修饰拼接在sql中，可能引发sql注入。

#{ }是预编译处理，MyBatis在处理#{ }时，它会将sql中的#{ }替换为？，然后调用PreparedStatement的set方法来赋值，传入字符串后，会在值两边加上单引号，使用占位符的方式提高效率，可以防止sql注入。因此最好使用#{ }方式。

6 总结

预编译是利用占位符替代参数值，预先建立语法树的过程。注入的恶意SQL语句只会被视为参数，参与不了SQL语句的语法树构建，也就无法改变其语法结构，也就无法达到编译恶意语句的目的。然而，预编译有一些局限性，比如，模糊查询由于存在% _使得参数值是不确定的，需要修改预编译语句。

我的上一篇博客出现了两个错误，我这里做出勘误。

我在查找相关资料的时候，在知乎上遇到了错误的回答（而且还是一个大佬的回答），大致是强调Java预编译会做强制类型转换来防止SQL注入。注意预编译防止SQL注入并不是从强制类型转换这一角度出发的，setstring()这类操作只是Java预编译对用户输入增加的过滤操作而已。务必记住关键词，那就是语法树的建立。模糊查询也是可以通过修改语句来适配预编译的。再次强调，希望大家在学习的时候务必多搜索多求证，避免出现知识误区。

7 参考链接