實戰(zhàn)1：如何用 PREPARE 防止 SQL 注入

1. 前言

在前面的小節(jié)中，我們一起學習了 SQL Prepare，本小節(jié)以實戰(zhàn)的角度來繼續(xù)深挖 Prepare，如果你還不了解 Prepare，請先閱讀 Prepare 小節(jié)，然后再來學習本小節(jié)。

本質(zhì)上講，SQL 注入是一個安全性的話題。如果你的程序沒有任何防止 SQL 注入的措施，那么你的程序是極端危險的，用戶數(shù)據(jù)可能會被竊取、篡改，造成不可估量的損失。

既然 SQL 注入如此危險，那么如何防范了？SQL 注入的防范措施有很多，甚至都可以寫上一整本書來介紹了，不過這都不是本小節(jié)的內(nèi)容。本小節(jié)會介紹一種十分有效的防范 SQL 注入的措施——Prepare防止SQL注入。

2. SQL 如何注入

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

SQL 注入的主要方式是將SQL代碼插入到參數(shù)中，這些參數(shù)會被置入到 SQL 命令中執(zhí)行。單純地理解這句話還是有些抽象的，我們還是以一個小例子來加以說明。

2.1 SQL 注入案例

我們新建一個測試數(shù)據(jù)表 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  |
+----+----------+-----+

有了測試表之后，我們設(shè)想一個場景，在后端服務(wù)中有一個 API 接口，該接口接收前端傳來的參數(shù)，然后查詢數(shù)據(jù)庫得到結(jié)果。

這個后端 API 接口實現(xiàn)很簡單，它接收前端的 id 參數(shù)，并查詢數(shù)據(jù)庫返回結(jié)果，如下：

SELECT * FROM imooc_user WHERE id = [id]; 

[id]表示這是一個動態(tài)參數(shù)，該參數(shù)由前端傳入而來。若前端傳1，會得到這樣的結(jié)果：

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

若前端傳10，結(jié)果將為空。

前端的參數(shù)是可以偽造的，如果有惡意攻擊者知道了該接口，他完全可以傳入這樣的參數(shù)：0 OR 1=1，拼接以后 SQL 語句如下：

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

很不幸，由于 SQL 的特性，1=1永遠為真，因此攻擊者可以輕松地拿到所有的用戶數(shù)據(jù)。換言之，用戶的數(shù)據(jù)被泄漏了，這就是一次簡單的 SQL 注入攻擊。

2.2 SQL 注入特點

從上面的案例可以發(fā)現(xiàn)，SQL 注入攻擊其實很簡單，利用到了 SQL 解析的原理。接下來我們分析一下上面的案例中 SQL 是如何被注入的？

• 前端參數(shù)不安全，易偽造，后端參數(shù)并未校驗，而是直接使用；

• 后端接口在使用 SQL 時，直接使用了最原始的 SQL 拼接方式，安全性很低，易被攻擊。

總結(jié)而言，后端開發(fā)者在開發(fā)過程中沒有足夠的安全意識，給了惡意攻擊者可乘之機。

3. SQL 注入措施

我們知道了 SQL 是如何注入了以后，那么后端開發(fā)者能夠采取哪些措施了？

我們總結(jié)了常見且有效的兩種方式：

1. 前端傳入的參數(shù)安全性很低，需要進行類型校驗才能訪問接口；
2. SQL 執(zhí)行不應(yīng)該使用字符串拼接的方式，優(yōu)先使用Prepare。

3.1 參數(shù)校驗

參數(shù)校驗是一種有效且方便的措施，一般在控制層進行校驗。我們舉幾個比較常見的校驗例子：

• 整數(shù)校驗，如判斷 id 是否為整數(shù)，非整數(shù)則報錯，可以有效的抑制上面案例中的 SQL 注入；
• 正則校驗，如判斷用戶名是否符合規(guī)則，不能含有.，首字符必須是英文字符等。

參數(shù)校驗可以將非法參數(shù)攔截在外，保證 SQL 接觸參數(shù)的合法性，而在實際應(yīng)用中，參數(shù)校驗幾乎是一種標配。如果你在實際開發(fā)中，有用到參數(shù)校驗，那么你有意識到它的重要性嗎？如果你沒有意識到，那么此時是否可以思考一下如何去讓你的校驗更加安全、有效。

3.2 SQL 預(yù)處理

SQL Prepare 是一種在數(shù)據(jù)庫層面上防止 SQL 注入的方式，它簡單且高效，且無需三方支持就能夠有效的斷絕掉 SQL 注入。

3.2.1 Prepare 如何防止 SQL 注入

那么 Prepare 是如何防止 SQL 注入的呢？在本小節(jié)的開頭，我們提到 SQL注入的主要方式是將 SQL 代碼注入到參數(shù)中，什么是 SQL 代碼呢？像0 OR 1=1這樣的 SQL 段就是 SQL 代碼，SQL 引擎會將它解析后再執(zhí)行，這樣OR 1=1就會生效。

想要從根源上解決 SQL 注入的問題，那么必須要讓OR 1=1失效，而 Prepare 正是這樣的一種處理方式。Prepare 會先將 SQL 模板傳遞給 SQL 引擎，SQL 引擎拿到 SQL 模板后，會編譯模板生成相應(yīng)的SQL執(zhí)行計劃，此時 SQL 已經(jīng)被編譯了。

當EXECUTE再攜帶0 OR 1=1這樣的參數(shù)時，OR 1=1不會再被編譯，數(shù)據(jù)庫只會單純的將它視為一個普通的字符串參數(shù)，因此OR就會失效，OR 1=1也會失效，這樣 SQL 注入的問題就從根本上解決了。

3.2.2 Prepare 防止 SQL 注入實例

我們還是以 imooc_user 為例來說明 Prepare 的用法。SQL 注入的語句如下：

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

不論是參數(shù)校驗，還是預(yù)處理都能夠解決掉這次 SQL 注入，預(yù)處理的解決方式如下。

預(yù)處理會先編譯 SQL 模板語句：

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

預(yù)編譯后，數(shù)據(jù)庫已經(jīng)生成了該 SQL 語句的執(zhí)行計劃，你可以簡單地理解為：

數(shù)據(jù)庫： 嘿！老鐵，語句我已經(jīng)收到了，執(zhí)行計劃已經(jīng)搞好了，你只需要按照?占位符傳入相應(yīng)的參數(shù)就行了。

應(yīng)用程序： 我傳入的參數(shù)如果是0 OR 1=1，你會怎么處理?。?/p>

數(shù)據(jù)庫： 老鐵放心，執(zhí)行計劃已經(jīng)生成好了，不會再解析了，參數(shù)里面的OR和=也不會再被解析，我們直接把它當成一個參數(shù)處理了。

SQL 語句如下：

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

結(jié)果如下：

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

從結(jié)果中可以得出，即使注入了OR 1=1，查詢結(jié)果仍然為空，用戶數(shù)據(jù)沒有泄漏。

4. 實踐

4.1 語言原生

Prepare 能夠直接了當?shù)亟鉀Q掉大部分的 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開發(fā)者，如果不使用 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"));
        }
    }
}

當然還有一些其它語言也在標準庫中直接支持了預(yù)處理的使用。

4.2 ORM 框架

4.2.1 Mybatis

如此重要的特性，自然會被 ORM 框架所青睞。在國內(nèi)使用頗為廣泛的 ORM 框架——Mybatis，完全可以無痛使用 Prepare，如果你在 Mybatis 的Mapper配置文件中，寫入了如下語句：

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

Mybatis 默認的會把#{}占位符里面的參數(shù)使用相應(yīng)數(shù)據(jù)庫的占位符替換，如果是 MySQL 則被替換為?。

因此該語句默認會使用 Prepare 處理 SQL 語句，當然如果你不想使用預(yù)處理，可以將#{id}替換為${id}。Mybatis 會使用 SQL 拼接的方式完成 SQL 語句，然后查詢，不過絕大部分人都會使用#{id}，我們也推薦你這么做。

4.2.2 Sequelize

如果你是Node.js開發(fā)者，想必一定使用過 Sequelize 這個 ORM 框架吧。當然如果你大部分時間都是通過模型API來操作數(shù)據(jù)的話，可能還不知道 Sequelize 的原生查詢方式。

Sequelize 可以直接使用query方法來直接使用 SQL 語句，且它支持兩種模式下的 SQL 預(yù)處理，如下：

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 支持兩種模式的占位符處理，一種是?模式，它通過數(shù)組傳參，然后預(yù)處理查詢；一種是:status命名模式，它通過對象傳參，然后預(yù)處理查詢。

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

5. 小結(jié)

• 如果你的開發(fā)環(huán)境允許，請一定使用 Prepare 來查詢 SQL，它的優(yōu)點遠大于缺點。
• 不同的數(shù)據(jù)庫雖然有不同的 Prepare 支持，但是你都可以通過 ORM 來無痛使用。
• 還有很多語言和框架支持 Prepare，如go也是在標準庫中支持了 Prepare，那么你使用的語言呢。