实战1：如何用 PREPARE 防止 SQL 注入

优质

小牛编辑

128浏览

2023-12-01

1. 前言

在前面的小节中，我们一起学习了 SQL Prepare，本小节以实战的角度来继续深挖 Prepare，如果你还不了解 Prepare，请先阅读 Prepare 小节，然后再来学习本小节。

本质上讲，SQL 注入是一个安全性的话题。如果你的程序没有任何防止 SQL 注入的措施，那么你的程序是极端危险的，用户数据可能会被窃取、篡改，造成不可估量的损失。

既然 SQL 注入如此危险，那么如何防范了？SQL 注入的防范措施有很多，甚至都可以写上一整本书来介绍了，不过这都不是本小节的内容。本小节会介绍一种十分有效的防范 SQL 注入的措施——Prepare防止SQL注入。

2. SQL 如何注入

在讲解如何用 Prepare 防止 SQL 注入前，我们需要先了解一下 SQL 是如何被注入的。

SQL 注入的主要方式是将SQL代码插入到参数中，这些参数会被置入到 SQL 命令中执行。单纯地理解这句话还是有些抽象的，我们还是以一个小例子来加以说明。

2.1 SQL 注入案例

我们新建一个测试数据表 imooc_user：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
INSERT INTO imooc_user(id,username,age)
VALUES (1,'peter',18),(2,'pedro',24),(3,'jerry',22),(4,'mike',18),(5,'tom',20);

+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1  | peter    | 18  |
| 2  | pedro    | 24  |
| 3  | jerry    | 22  |
| 4  | mike     | 18  |
| 5  | tom      | 20  |
+----+----------+-----+

有了测试表之后，我们设想一个场景，在后端服务中有一个 API 接口，该接口接收前端传来的参数，然后查询数据库得到结果。

这个后端 API 接口实现很简单，它接收前端的 id 参数，并查询数据库返回结果，如下：

SELECT * FROM imooc_user WHERE id = [id];

[id]表示这是一个动态参数，该参数由前端传入而来。若前端传1，会得到这样的结果：

# SELECT * FROM imooc_user WHERE id = 1;
+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1  | peter    | 18  |
+----+----------+-----+

若前端传10，结果将为空。

前端的参数是可以伪造的，如果有恶意攻击者知道了该接口，他完全可以传入这样的参数：0 OR 1=1，拼接以后 SQL 语句如下：

SELECT * FROM imooc_user WHERE id = 0 OR 1=1;

很不幸，由于 SQL 的特性，1=1永远为真，因此攻击者可以轻松地拿到所有的用户数据。换言之，用户的数据被泄漏了，这就是一次简单的 SQL 注入攻击。

2.2 SQL 注入特点

从上面的案例可以发现，SQL 注入攻击其实很简单，利用到了 SQL 解析的原理。接下来我们分析一下上面的案例中 SQL 是如何被注入的？

前端参数不安全，易伪造，后端参数并未校验，而是直接使用；
后端接口在使用 SQL 时，直接使用了最原始的 SQL 拼接方式，安全性很低，易被攻击。

总结而言，后端开发者在开发过程中没有足够的安全意识，给了恶意攻击者可乘之机。

3. SQL 注入措施

我们知道了 SQL 是如何注入了以后，那么后端开发者能够采取哪些措施了？

我们总结了常见且有效的两种方式：

前端传入的参数安全性很低，需要进行类型校验才能访问接口；
SQL 执行不应该使用字符串拼接的方式，优先使用Prepare。

3.1 参数校验

参数校验是一种有效且方便的措施，一般在控制层进行校验。我们举几个比较常见的校验例子：

整数校验，如判断 id 是否为整数，非整数则报错，可以有效的抑制上面案例中的 SQL 注入；
正则校验，如判断用户名是否符合规则，不能含有.，首字符必须是英文字符等。

参数校验可以将非法参数拦截在外，保证 SQL 接触参数的合法性，而在实际应用中，参数校验几乎是一种标配。如果你在实际开发中，有用到参数校验，那么你有意识到它的重要性吗？如果你没有意识到，那么此时是否可以思考一下如何去让你的校验更加安全、有效。

3.2 SQL 预处理

SQL Prepare 是一种在数据库层面上防止 SQL 注入的方式，它简单且高效，且无需三方支持就能够有效的断绝掉 SQL 注入。

3.2.1 Prepare 如何防止 SQL 注入

那么 Prepare 是如何防止 SQL 注入的呢？在本小节的开头，我们提到 SQL注入的主要方式是将 SQL 代码注入到参数中，什么是 SQL 代码呢？像0 OR 1=1这样的 SQL 段就是 SQL 代码，SQL 引擎会将它解析后再执行，这样OR 1=1就会生效。

想要从根源上解决 SQL 注入的问题，那么必须要让OR 1=1失效，而 Prepare 正是这样的一种处理方式。Prepare 会先将 SQL 模板传递给 SQL 引擎，SQL 引擎拿到 SQL 模板后，会编译模板生成相应的SQL执行计划，此时 SQL 已经被编译了。

当EXECUTE再携带0 OR 1=1这样的参数时，OR 1=1不会再被编译，数据库只会单纯的将它视为一个普通的字符串参数，因此OR就会失效，OR 1=1也会失效，这样 SQL 注入的问题就从根本上解决了。

3.2.2 Prepare 防止 SQL 注入实例

我们还是以 imooc_user 为例来说明 Prepare 的用法。SQL 注入的语句如下：

SELECT * FROM imooc_user WHERE id = 0 OR 1=1;

不论是参数校验，还是预处理都能够解决掉这次 SQL 注入，预处理的解决方式如下。

预处理会先编译 SQL 模板语句：

PREPARE finduserbyid FROM 'SELECT * FROM imooc_user WHERE id = ?';

预编译后，数据库已经生成了该 SQL 语句的执行计划，你可以简单地理解为：

数据库： 嘿！老铁，语句我已经收到了，执行计划已经搞好了，你只需要按照?占位符传入相应的参数就行了。

应用程序： 我传入的参数如果是0 OR 1=1，你会怎么处理啊？

数据库： 老铁放心，执行计划已经生成好了，不会再解析了，参数里面的OR和=也不会再被解析，我们直接把它当成一个参数处理了。
图片描述

SQL 语句如下：

SET @id='0 OR 1=1';
EXECUTE finduserbyid USING @id;

结果如下：

+----+----------+-----+
| id | username | age |
+----+----------+-----+

从结果中可以得出，即使注入了OR 1=1，查询结果仍然为空，用户数据没有泄漏。

4. 实践

4.1 语言原生

Prepare 能够直接了当地解决掉大部分的 SQL 注入问题，所以它的使用是十分广泛的，几乎所有 ORM 框架都会默认提供 API 来方便使用它。

4.1.1 原生 PHP

当然不少语言，诸如PHP甚至在语言层面上支持了它，如：

$stmt = $mysqli->prepare("DELETE FROM planet WHERE name = ?");
$stmt->bind_param('s', "earth");
$stmt->execute();

4.1.2 原生 Java

如果你是Java开发者，如果不使用 ORM 框架，你也可以直接使用原生 API 来使用 Prepare：

public class PrepareTest {

    public static void main(String[] args) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc", "root", "123456");
        PreparedStatement preStatement = conn.prepareStatement("SELECT * FROM imooc_user WHERE id = ?");
        preStatement.setInt(1, 1);
        ResultSet result = preStatement.executeQuery();
        while (result.next()) {
            System.out.println("username: " + result.getString("username"));
        }
    }
}

当然还有一些其它语言也在标准库中直接支持了预处理的使用。

4.2 ORM 框架

4.2.1 Mybatis

如此重要的特性，自然会被 ORM 框架所青睐。在国内使用颇为广泛的 ORM 框架——Mybatis，完全可以无痛使用 Prepare，如果你在 Mybatis 的Mapper配置文件中，写入了如下语句：

<select resultType="com.pedro.mybatis.model.Article">
  select * from article where id = #{id}
</select>

Mybatis 默认的会把#{}占位符里面的参数使用相应数据库的占位符替换，如果是 MySQL 则被替换为?。

因此该语句默认会使用 Prepare 处理 SQL 语句，当然如果你不想使用预处理，可以将#{id}替换为${id}。Mybatis 会使用 SQL 拼接的方式完成 SQL 语句，然后查询，不过绝大部分人都会使用#{id}，我们也推荐你这么做。

4.2.2 Sequelize

如果你是Node.js开发者，想必一定使用过 Sequelize 这个 ORM 框架吧。当然如果你大部分时间都是通过模型API来操作数据的话，可能还不知道 Sequelize 的原生查询方式。

Sequelize 可以直接使用query方法来直接使用 SQL 语句，且它支持两种模式下的 SQL 预处理，如下：

sequelize.query('SELECT * FROM projects WHERE status = ?',
  { replacements: ['active'], type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

sequelize.query('SELECT * FROM projects WHERE status = :status ',
  { replacements: { status: 'active' }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

Sequelize 支持两种模式的占位符处理，一种是?模式，它通过数组传参，然后预处理查询；一种是:status命名模式，它通过对象传参，然后预处理查询。

如果你使用其它的框架或者其它的语言，你也可以自行尝试一下它的 Prepare 使用方式。

5. 小结

如果你的开发环境允许，请一定使用 Prepare 来查询 SQL，它的优点远大于缺点。
不同的数据库虽然有不同的 Prepare 支持，但是你都可以通过 ORM 来无痛使用。
还有很多语言和框架支持 Prepare，如go也是在标准库中支持了 Prepare，那么你使用的语言呢。