Sql预编译与模拟预编译研究

Sql预编译与模拟预编译研究
2020-02-01 16:51:50 Author: xz.aliyun.com(查看原文) 阅读量:377 收藏

写在前面

众所周知，预编译是解决sql注入的一个很好的方案，但是预编译在现实使用中却有着很多有趣的细节需要研究下。在没有经过实验之前，针对如下问题我也比较模糊，例如：

1、Mysql预编译和模拟预编译有什么不同？哪种方式理论上更加安全呢？

2、PHP中链接数据库Mysqli接口与PDO接口默认采用哪种方式进行预编译？

3、Python中MySQLdb又是默认采用哪种方式进行预编译？

4、程序采用Mysql数据库预编译方式，转义环节是有客户端(PHP、Python、Java)完成的，还是由服务器端(Mysql数据库)完成?

本文将对上述这些问题进行分析

Mysql预编译和模拟预编译

首先介绍下sql预编译和模拟预编译的区别之

sql预编译

以mysql数据库举例：通常情况下，在数据库接收到一条普通的
SQL语句后，首先对其进行语义解析，随后对此条SQL
语句进行优化并制定执行计划并执行；当采用预编译操作时，首先将待执行的SQL
语句中的参数值用占位符替代。当带着占位符的SQL
语句模板被数据库编译、解析后，再通过向占位符绑定参数进行查询操作。

反观Sql注入的根源，是在本应该传递参数数据的地方，
传入了精心构造的sql语句。而经过预编译操作之后，无论后续向模板传入什么参数，这些参数仅仅被当成字符串进行查询处理，因此杜绝了sql注入的产生

接下来看一下预编译在mysql数据库中如何操作

首先，我们可以通过 PREPARE stmt_name FROM preparable_stm
语法来预编译一条sql语句模板，如下图：

接着通过set来绑定参数，如下图：

最后通过EXECUTE stmt_name [USING @var_name [, @var_name]...]的语法来选择编译好的stmt_test模板以接收name参数并执行查询，如下图：

通过查看mysql日志可以发现，与执行普通sql语句使用的query命令不同，这里使用了prepare命令与execute命令，见下图

当后续使用同一模板不同参数值(不同的name值)进行查询进行查询时，例如下图：

这里查询name值为”othername”的列，由于这里使用的仍是经过prepaer的stmt_test模板，程序将使用先前存储于缓冲区预编译后的模板进行解析，而不需要再次通过prepare，见下图

上图中可见，预编译可以实现一次编译、多次执行，省去了解析优化等过程。

在实际操作中，当客户端在与mysql数据库通信时，为了表明当前请求消息的类型，会发送命令请求报文，报文格式如下图所示：

通常情况下，如果简单的执行sql语句，数据包中会使用类型值为0x03的COM_QUERY消息报文，见下图

而在使用预编译功能时，则会使用类型值为0x16的COM_STMT_PREPARE进行预编译并使用0x17进行执行，见下图

上图中22对应十六进制的0x16 COM_STMT_PREPARE阶段

上图报文中23对应十六进制的0x17 COM_STMT_EXECUTE阶段

模拟预编译

模拟预编译是防止某些数据库不支持预编译而设置的(如sqllite与低版本mysql)。如果模拟预处理开启，那么客户端程序内部会模拟mysql数据库中的参数绑定这一过程。也就是说，程序会在内部模拟prepare的过程，当执行execute时，再将拼接后的完整SQL语句发送给mysql数据库执行

有如下案例，这里使用PDO接口进行数据库操作

从上图代码中可见，使用prepare预编译sql模板，并通过bindParam进行参数绑定，最终通过execute进行执行，但这是否是真正的sql预编译呢？

我们可以看下mysql日志事实记录，如下

可以看到数据库日志中并没有prepare阶段与execute阶段。反而和执行普通的sql查询一样，简简单单的Query了PDO传递过来的sql语句

这是为什么呢？

正如上文所说：为了防止某些数据库不支持预编译而设置的(如sqllite与低版本mysql)，PDO默认使用的是模拟预编译而非mysql数据库预处理(本地预处理)。如果模拟预处理开启，那么客户端程序内部会模拟mysql数据库中的参数绑定这一过程。也就是说，程序会在内部模拟prepare这一过程，当execute方法执行时，再将拼接后的完整SQL语句发送给mysql数据库进行查询

PDO中通过PDO::ATTR_EMULATE_PREPARES参数控制所使用的的预编译模式，默认使用模拟预处理进行操作。详细的可见下图官网给出的说明：