技術文章 » 預編譯SQL為什麼能夠防止SQL隱碼攻擊

預編譯SQL為什麼能夠防止SQL隱碼攻擊

2022-12-07 21:02:41

前言

之前我一個搞網路安全的朋友問了我一個的問題，為啥用 PreparedStatement 預編譯的 SQL 就不會有被 SQL 注入的風險？

第一時間我聯想到的是八股文中關於 Mybatis 的指令碼 ${} 和 #{} 的問題，不過再想想，為啥 ${} 會有 SQL 注入的風險，而 #{} 就沒有？是因為到 PreparedStatement 做了什麼處理嗎？不知道。

然後我又想了想，預編譯到底是個什麼概念？預編譯或者不預編譯的 SQL 對資料庫來說有什麼區別嗎？PreparedStatement 又在這個過程中扮演了怎樣的角色？不知道。

好吧，我發現我確實對這個問題一無所知，看來需要親自研究一下了。

一、資料庫預編譯

當我們說到關於持久層框架的功能，必然需要先想想這個功能的源頭到底是不是直接通過資料庫提供的。實際上和事務一樣，SQL 預編譯的功能也是需要資料庫提供底層支援的。

1、預編譯SQL的用法

以 MySQL 為例，在 MySQL 中，所謂預編譯其實是指先提交帶預留位置的 SQL 模板，然後為其指定一個 key，MySQL 先將其編譯好，然後使用者再拿著 key 和預留位置對應的引數讓 MySQL 去執行，用法有點像 python 中的 format 函數。

一個標準的預編譯 SQL 的用法如下：

prepare prepare_query from 'select * from s_user where username = ?' # 提交帶有預留位置的引數化 SQL，也可以理解為 SQL 模板
set @name = '%王五'; # 指定一個引數
execute prepare_query using @name; # 指定引數化 SQL 的 key 和引數，讓 MySQL 自己去拼接執行

先通過 prepare 設定一個 SQL 模板，然後通過 execute 提交引數，MySQL 會自行根據引數替換預留位置，到最後執行的 SQL 就是：

select * from s_user where username = '%王五'

2、預編譯的原理

這裡有個有意思問題，按網上的說法，prepare 執行的時候實際上 SQL 已經編譯完了，所以可以防止注入，因為後續不管塞什麼引數都不可能在調整語法樹了，換個角度想，這是不是說明，如果我們一開始就讓 prepare 執行的 SQL 模板的關鍵字變成預留位置，是不是應該在這個時候就編譯不通過？

比如，可以把查詢的表名改成預留位置：

prepare prepare_query from 'select * from ? where username = ?'

# > 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to 
# use near '? where username = ?' at line 1

實際上也確實不行，因為編譯時必須確定主表，因此在 from 後面加預留位置會導致預編譯不通過。

那麼只在查詢欄位裡面套一個巢狀查詢呢？

prepare prepare_query from 'select ? from s_user';
SET @c = '(select * from s_user) as q';
EXECUTE prepare_query using @c;

# 查詢結果
# (select * from s_user) as q
# (select * from s_user) as q
# (select * from s_user) as q
# ......

查詢成功了，不過得到的結果的固定的 (select * from s_user) 這個字串，我們檢查一下 MySQL 的執行紀錄檔，看看最終執行的 SQL 變成什麼樣了：

Prepare	select ? from s_user
Query	SET @c = '(select * from s_user) as q'
Query	EXECUTE prepare_query using @c
Execute	select '(select * from s_user) as q' from s_user # 最終執行的SQL

顯然，(select * from s_user) 引數本身被直接跳脫為了一串普通的字串，我們試圖「注入」的 SQL 片段完全不會生效。

換而言之，對於預編譯 SQL 來說，我們作為模板的引數化 SQL 已經完成的編譯過程，這段 SQL 包含幾條有效語句？查哪張表？查哪些欄位？作為條件的欄位有哪些？......這些在 prepare 語句執行完後都是固定的，此後我們再通過 execute 語句塞進去的任何引數，都會進行跳脫，不會再作為 SQL 的一部分。這就是為什麼說預編譯 SQL 可以防止注入的原因。

二、JDBC的預編譯

現在我們知道了預編譯在資料庫中是個怎樣的功能，那麼 JDBC 又是如何把這個功能提供給開發者使用的呢？

1、PreparedStatement

從最開始學 JDBC 時，我們就知道通過 JDBC 連線資料庫一般是這樣寫的：

Class.forName(JDBC_DRIVER); // 載入驅動
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 獲取連線
PreparedStatement preparedStatement = connection.prepareStatement(sql); // 獲取sqlStatement
preparedStatement.setString(1, foo); // 設定引數
ResultSet resultSet = preparedStatement.executeQuery(); // 執行SQL

這裡有一個關鍵角色 PreparedStatement，相比起它的父介面 Statement，它最大的變化是多了各種格式為 setXXX 的、用於設定與預留位置對應的引數的方法，顯然它正對應著上文我們提到的預編譯 SQL。

2、虛假的「預編譯」

不過事情顯然沒有這麼簡單，我們依然以 MySQL 為例，預設情況下 MySQL 驅動包提供的 PreparedStatement 實現類 ClientPreparedStatement 也能起到防止 SQL 注入的功能，但是方式跟我們想的不太一樣。

假設現有如下程式碼，我們嘗試模擬進行一次 SQL 注入：

String sql = "select * from s_user where username = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "王五' union select * from s_user");
ResultSet resultSet = preparedStatement.executeQuery();

執行上述程式碼並正常的請求資料庫，然後我們去資料庫執行紀錄檔中檢視對應的執行的 SQL 如下，會發現只有這麼一行：

Query select * from s_user where username = '王五'' union select * from s_user'

顯然跟我們上文說到的先 prepare 再 execute 流程不同，帶有預留位置的原始 SQL 模板並沒有在紀錄檔中出現，但是程式碼中的 王五' 確實也被跳脫為了 '王五''。

資料庫到底收到了哪些資料？

那麼資料庫到底拿到的就是這條 SQL，還是原始的 SQL 模板 + 引數呢？

為了瞭解這一點，我們打斷點跟蹤 ClientPreparedStatement.executeQuery 方法，一路找到它組裝請求資料庫的引數的那一行程式碼：

Message sendPacket = ((PreparedQuery<?>) this.query).fillSendPacket();

最後我們會進入 AbstractPreparedQuery.fillSendPacket 這個方法，這裡主要乾的事是把我們帶預留位置的原始 SQL 模板和引數合併為最終要執行的 SQL ，並封裝到 NativePacketPayload 物件，用於在後續發起 TCP 請求時把 SQL 引數轉為二進位制封包。

為了驗證這一點，我們先拿到 sendPacket 物件，再獲取裡面的位元組陣列，最後轉為字串：

可以看到內容就是已經格式化完的 SQL：

select * from s_user where username = '王五'' union select * from s_user'

現在答案就很明顯了，跳脫在 preparedStatement.setString 方法呼叫的時候完成，而 PreparedStatement 在發起請求前就把跳脫後的引數和 SQL 模板進行了格式化，最後傳送到 MySQL 的時候就是一條普通的 SQL。

鑑於此，我們可以說 MySQL 提供的 PreparedStatement 在預設情況下是假的「預編譯」，它只不過在設定引數的時候幫我們對引數做了一下跳脫，但是最後傳送到資料庫的依然是普通的 SQL，而不是按預編譯 SQL 的方式去執行。

3、真正的預編譯

好吧，那既然 MySQL 提供了這個預編譯的功能，那通過 JDBC 肯定也還是有辦法用上真正的預編譯功能的，實際上要做到這點也很簡單，就是直接在驅動的 url 上配上 useServerPrepStmts=true ，這樣就會真正的啟用 MySQL 的預編譯功能。

依然以上文的程式碼為例：

String sql = "select * from s_user where username = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "王五' union select * from s_user");
ResultSet resultSet = preparedStatement.executeQuery();

設定了 useServerPrepStmts=true 後再執行程式碼，去資料庫檢視執行紀錄檔有：

Execute select * from s_user where username = '王五\' union select * from s_user'
Prepare select * from s_user where username = ?

此時 MySQL 的預編譯功能就真正的生效了。

我們回到 ClientPreparedStatement.executeQuery 建立 sendPacket 地方看，此時通過 ((PreparedQuery<?>) this.query).fillSendPacket(); 拿到的 Message 物件是 null，然後進一步追蹤到最後向 MySQL 傳送請求的地方 NativeSession.execSQL：

public <T extends Resultset> T execSQL(Query callingQuery, String query, int maxRows, NativePacketPayload packet, boolean streamResults,
                                       ProtocolEntityFactory<T, NativePacketPayload> resultSetFactory, ColumnDefinition cachedMetadata, boolean isBatch) {

    // ... ...

    try {
        // 如果 sendPacket 為 null，則呼叫 sendQueryString 方法，把原始 sql 和引數序列化為二進位制封包
        return packet == null
            ? ((NativeProtocol) this.protocol).sendQueryString(callingQuery, query, this.characterEncoding.getValue(), maxRows, streamResults, cachedMetadata, resultSetFactory)
            // 否則呼叫 sendQueryPacket 方法，直接傳送封包
            : ((NativeProtocol) this.protocol).sendQueryPacket(callingQuery, packet, maxRows, streamResults, cachedMetadata, resultSetFactory);

    }

    // ... ...

}

更具體的實現就不看了，基本都是關於序列化請求引數的邏輯。

三、Myabtis預留位置與預編譯

至此問題真相大白了，不過還是順帶扯一下八股文常提到的 Mybatis 預留位置 #{} 與 ${} 是如何影響 SQL 注入問題的。

當然，看完上面的內容其實就已經很好猜到原因了：

  • #{} 對應的內容會作為 SQL 引數的一部分通過 PreparedStatement.setXXX 裝入請求；
  • ${} 對應的內容會直接作為 SQL 模板的一部分，而不會視為獨立的請求引數；

在 Mybatis 中，用於解析預留位置的類為 GenericTokenParser ，根據它我們很容易在原始碼中找到預留位置的處理方法，從而驗證我們的猜想：

其中，#{} 預留位置在 SqlSourceBuilder.ParameterMappingTokenHandler.handleToken 方法中處理：

public String handleToken(String content) {
    parameterMappings.add(buildParameterMapping(content));
    return "?";
}

可見 #{} 預留位置會被解析為 ? 預留位置，而對於的資料會被新增到 parameterMappings 用於後續塞到 PreparedStatement。

而 ${} 預留位置在 PropertyParser.VariableTokenHandler.handleToken 方法中被處理：

public String handleToken(String content) {
    if (variables != null) {
        String key = content;
        if (enableDefaultValue) {
            final int separatorIndex = content.indexOf(defaultValueSeparator);
            String defaultValue = null;
            if (separatorIndex >= 0) {
                key = content.substring(0, separatorIndex);
                defaultValue = content.substring(separatorIndex + defaultValueSeparator.length());
            }
            if (defaultValue != null) {
                return variables.getProperty(key, defaultValue);
            }
        }
        if (variables.containsKey(key)) {
            return variables.getProperty(key);
        }
    }
    return "${" + content + "}";
}

若預留位置符合規範，則佔會根據預留位置中的內容去使用者給定的引數中取值，並且讓值直接替換掉原本 SQL 指令碼中的 ${} 預留位置。

這就是「 Mybatis 用 #{} 而不是 ${} 可以防止 SQL 注入的真相。

總結

回顧一下全文，當我們說「預編譯」的時候，其實這個功能來自於資料庫的支援，它的原理是先編譯帶有預留位置的 SQL 模板，然後在傳入引數讓資料庫自動替換 SQL 中預留位置並執行，在這個過程中，由於預編譯好的 SQL 模板本身語法已經定死，因此後續所有引數都會被視為不可執行的非 SQL 片段被跳脫，因此能夠防止 SQL 注入。

當我們通過 JDBC 使用 PreparedStatement 執行預編譯 SQL 的時候，此處的預編譯實際上是假的預編譯（至少 MySQL 是如此，不過其他資料庫仍待確認），PreparedStatement 只是在設定引數的時候自動做了一層跳脫，最終提交給資料庫執行的 SQL 仍然是單條的非預編譯 SQL。

而當我們通過在驅動 url 上開啟 useServerPrepStmts 設定後，預編譯就會真正的生效，驅動包發往資料庫的請求就會分成帶預留位置的 SQL 模板和引數，到了資料庫再由資料庫完成格式化並執行。

此外，八股文常提到的「Mybatis 的 #{} 相比 ${} 可以防止 SQL 注入」這一點，本質上是因為 #{} 預留位置會被解析為 SQL 模板中的 ? 預留位置，而 ${} 預留位置會被直接解析為 SQL 模板的一部分導致的。

最後腦補一下，由於 useServerPrepStmts 不開啟時 PreparedStatement 的預編譯實際上是假的預編譯，所以理論上使用 #{} 也並非絕對安全，如果有辦法繞過 PreparedStatement 的檢查，那麼資料庫拿到被注入過的 SQL 直接執行，依然有暴斃的風險。