Mysql预编译的二三事

Mysql Web

字数统计: 2.1k阅读时长: 8 min

 2020/02/02   Share

Abstract

使用的环境是phpstudy自带的mysql-5.5.53 + Wireshark

集成环境默认是不会打开log功能，需要在my.ini中自行添加以下配置项，重启即可

#错误日志
log-error="E:/phpstudy/PHPTutorial/MySQL/logs/error.log"  
#sql语句
log="E:/phpstudy/PHPTutorial/MySQL/logs/mysql.log"  
long_query_time=2  
#慢查询日志
log-slow-queries= "E:/phpstudy/PHPTutorial/MySQL/logs/slowquery.log"

预编译

在使用PreparedStatement执行SQL命令时，会带着占位符被数据库进行编译和解析，然后放到命令缓冲区。接受客户端发来的参数，利用缓存，拼接Sql语句解析执行，这里虽然会再被解析一次，但不会再次被编译，可以把这个过程看作sql语句的模板化或参数化。

Sql语句

编译

1
2
3

mysql> prepare student from 'select * from stu where `name`=?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

绑定参数

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

执行

假如有多个@参数，用,分隔

mysql> execute student using @name;
+----+-------+-------+
| id | name  | class |
+----+-------+-------+
|  0 | alice | 4     |
+----+-------+-------+
1 row in set (0.00 sec)

作为对比，我们再正常查询一条数据和再次调用预定义的语句

最后来查看日志中执行过程

预编译语句与普通语句不同的是，首先会Prepare模板，然后向占位符绑定的自定义参数赋值后再调用Execute执行语句。而且再次使用该模板时，并不需要重新Prepare，直接调用即可，正所谓一次编译多次执行

释放

1 2	mysql> deallocate prepare student; Query OK, 0 rows affected (0.00 sec)

转义

使用预编译语句能够防御绝大部分的Sqli，主要归功于能自动对特殊字符进行转义处理。这里就有个疑问了——到底是mysql客户端进行的转义？还是服务器？

使用刚刚定义的Prepare语句，键入set @name="bo'b";，然后执行语句，抓包查看

很明显，转义时Mysql服务端实现的。

协议分析

先来说下Mysql连接方式：

TCP/IP：这是用的最多的一种方式，经过三次握手之后建立连接
TLS/SSL：基于SSL加密协议，连接之前进行SSL握手并提供证书才能建立连接
Unix Sockets：在*nix下使用，因为该套接字不是网络协议，所以只能在Client和Server为同一台机器的情况下使用
共享内存和命名管道：在Vista以后的系统，Windows开始新增了命名管道机制，用于两个不同的进程之间通信

Client -> Server

客户端向服务端发送的数据包格式为：

Type	Name	Description
int<1>	执行命令	执行的操作，比如切换数据库，查询表等操作
string	参数	命令相应的参数

很简单就是Command+Statement，常见的命令类型有这些

Type	Name	Description
0x01	COM_QUIT	关闭连接
0x16	COM_STMT_PREPARE	预处理SQL语句
0x17	COM_STMT_EXECUTE	执行预处理语句
0x19	COM_STMT_CLOSE	销毁预处理语句

Server->Client

稍微比前者要复杂一点，每个数据包前都会添加数据包信息(length+id)

Type	Name	Description
int<3>	payload_length(包数据长度)	具体数据包的内容长度，从出去头部四个字节后开始的内容
int<1>	sequence_id(包序列id)	每个包的序列id，总数据内容大于16MB时需要用，从0开始，依次增加，新的命令执行会重载为0
string	payload(具体数据)	包中除去头部后的具体数据内容

语言支持

In PHP

php下有两种操作数据库的接口Mysqli和PDO，我们依次来看

Mysqli

<?php
$db = new mysqli('localhost','root','yoga','test');
$conn = $db->prepare("select * from stu where name = ?");
$conn->bind_param('s',$name);
$name = 'alice';
$conn->bind_result($id,$n,$class);
$conn->execute();
while ($conn->fetch()){
    printf("%d %s %s",$id,$n,$class);
}
$conn->close(); //关闭预处理
$db->close(); //关闭数据库连接

通过执行文件然后查看日志文件的方式来判断

显然是使用了预编译语句

PDO

<?php
$db = new PDO('mysql:host=localhost;dbname=test','root','yoga');
$link = $db->prepare("Select * from stu where name=:name");
$link->bindParam('name',$name);
$name = 'alice';
if ($link->execute()){
    while ($row=$link->fetch(PDO::FETCH_ASSOC))
        print_r($row);
    $name = 'alic"e';
    $link->execute();
}
$link->closeCursor();

运行然后查看日志

日志中可以看出没有进行预编译处理，但是却对双引号进行转义操作。这就涉及到了PDO存在的模拟预编译机制

因为不是所有数据库驱动都支持SQL预编译(如sqlite)，所以PDO存在“模拟预处理机制”。如果说开启了模拟预处理，那么PDO内部会模拟参数绑定的过程，SQL语句是在最后execute()的时候才发送给数据库执行。

也就是说在程序内部对用户输入做了一个类似过滤转义的操作，然后发往服务端执行。文档规定了一个参数PDO::ATTR_EMULATE_PREPARES来选择所使用的预编译模式

PDO::ATTR_EMULATE_PREPARES 启用或禁用预处理语句的模拟。有些驱动不支持或有限度地支持本地预处理。使用此设置强制PDO总是模拟预处理语句（如果为 TRUE ），或试着使用本地预处理语句（如果为 FALSE）。如果驱动不能成功预处理当前查询，它将总是回到模拟预处理语句上。需要 bool 类型。

添加一行代码，然后执行查看日志

1 2	//设置为本地预处理 $db->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

可以看到使用了预编译语句

In Python

Mysqldb

import MySQLdb

conn = MySQLdb.connect(host="localhost",user="root",passwd="yoga",db="test")
curos = conn.cursor()
sql = "select * from stu where `name` = %s"
curos.execute(sql,"alice")
print curos.fetchone()
curos.execute(sql,"ali'ce")
print curos.fetchone()

运行查看日志

与PDO一样，使用的是模拟预编译操作来转义特殊字符

Pymysql

#!/usr/bin/python
# -*- coding: utf-8 -*-
import pymysql
conn = pymysql.connect(host="localhost",user="root",passwd="yoga",db="test")
curos = conn.cursor()
sql = "select * from stu where `name` = %s"
curos.execute(sql,"alice")
print curos.fetchone()
curos.execute(sql,"ali'ce")
print curos.fetchone()

也是模拟预编译操作

In Java

JDBC的预编译查询有两种形式：客户端预编译与服务端预编译

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class jdbc {
    public static void main(String[] args) throws ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        try (Connection connection = DriverManager.getConnection(url,"root","yoga")){
            String sql = "select * from stu where name = ?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, "alice");
            statement.execute();
            statement.setString(1, "ali'ce");
            statement.execute();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

运行查看日志

可以看出，默认条件下JDBC的Mysql驱动使用的是客户端预编译，类似于之前所说的模拟预编译

mysql-connector-java-5.1.42-bin.jar!\com\mysql\jdbc\PreparedStatement.class:setString()

与PDO一样，JDBC也有一个用来控制是否进行服务端预编译操作的URL选项useServerPrepStmts。该值默认为False，也就是进行客户端预编译；我们可以指定为True，让其进行服务端预编译

1	jdbc:mysql://localhost:3306/test?useServerPrepStmts=true

然后运行查看日志

显然启用了预编译语句。

此外与之对应的还有一个参数——cachePrepStmts，表示是否开启缓存prepare预编译对象。尝试关闭statement，来对比下

在没有开启缓存的时候，服务端进行了两次的预编译操作。接下来开启cachePrepStmts

可以看到服务端只进行了一个预编译操作，之后每次都从本地缓存中读取。当开启缓存的时候，JDBC驱动会以sql语句作为Key，预编译后的对象preparestatement作为Value，保存在Map中，用于下次重复利用。

All In All

对于关键词order by来说，如果使用预编译处理，参数绑定为String类型，order by 的参数会被单引号包裹，导致无法排序；对于拼接列名、表名的sql语句来说，参数绑定后也会用单引号包裹，故也无法使用预编译处理；而且在prepare绑定参数阶段也能够报错注入

但是预编译还是能在大多数情况下防御SQL注入，还是需要结合特定业务来防御

Referer

https://scala.cool/2017/11/mysql-protocol/

https://blog.csdn.net/HeatDeath/article/details/79702663

https://blog.csdn.net/yanghuan313/article/details/70477360

Next Post

XXE in Java
Previous Post

Exchange SSRF漏洞利用及分析

CATALOG

1. Abstract
2. 预编译
3. 语言支持
4. All In All
5. Referer

