前言

之前我一個搞網(wǎng)絡(luò)安全的朋友問了我一個的問題，為啥用 PreparedStatement 預(yù)編譯的 SQL 就不會有被 SQL 注入的風(fēng)險？

第一時間我聯(lián)想到的是八股文中關(guān)于 Mybatis 的腳本 ${} 和 #{} 的問題，不過再想想，為啥 ${} 會有 SQL 注入的風(fēng)險，而 #{} 就沒有？是因為到 PreparedStatement 做了什么處理嗎？不知道。

然后我又想了想，預(yù)編譯到底是個什么概念？預(yù)編譯或者不預(yù)編譯的 SQL 對數(shù)據(jù)庫來說有什么區(qū)別嗎？PreparedStatement 又在這個過程中扮演了怎樣的角色？不知道。

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

一、數(shù)據(jù)庫預(yù)編譯

當(dāng)我們說到關(guān)于持久層框架的功能，必然需要先想想這個功能的源頭到底是不是直接通過數(shù)據(jù)庫提供的。實際上和事務(wù)一樣，SQL 預(yù)編譯的功能也是需要數(shù)據(jù)庫提供底層支持的。

1、預(yù)編譯SQL的用法

以 MySQL 為例，在 MySQL 中，所謂預(yù)編譯其實是指先提交帶占位符的 SQL 模板，然后為其指定一個 key，MySQL 先將其編譯好，然后用戶再拿著 key 和占位符對應(yīng)的參數(shù)讓 MySQL 去執(zhí)行，用法有點像 python 中的 format 函數(shù)。

一個標(biāo)準(zhǔn)的預(yù)編譯 SQL 的用法如下：

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

先通過 prepare 設(shè)置一個 SQL 模板，然后通過 execute 提交參數(shù)，MySQL 會自行根據(jù)參數(shù)替換占位符，到最后執(zhí)行的 SQL 就是：

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

2、預(yù)編譯的原理

這里有個有意思問題，按網(wǎng)上的說法，prepare 執(zhí)行的時候?qū)嶋H上 SQL 已經(jīng)編譯完了，所以可以防止注入，因為后續(xù)不管塞什么參數(shù)都不可能在調(diào)整語法樹了，換個角度想，這是不是說明，如果我們一開始就讓 prepare 執(zhí)行的 SQL 模板的關(guān)鍵字變成占位符，是不是應(yīng)該在這個時候就編譯不通過？

比如，可以把查詢的表名改成占位符：

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 后面加占位符會導(dǎo)致預(yù)編譯不通過。

那么只在查詢字段里面套一個嵌套查詢呢？

prepare prepare_query from'select ? from s_user';
SET@c='(select * from s_user) as q';
EXECUTE prepare_query using@c;# 查詢結(jié)果
# (select*from s_user) as q
# (select*from s_user) as q
# (select*from s_user) as q
# ......

查詢成功了，不過得到的結(jié)果的固定的 (select * from s_user) 這個字符串，我們檢查一下 MySQL 的執(zhí)行日志，看看最終執(zhí)行的 SQL 變成什么樣了：

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

顯然，(select * from s_user) 參數(shù)本身被直接轉(zhuǎn)義為了一串普通的字符串，我們試圖“注入”的 SQL 片段完全不會生效。

換而言之，對于預(yù)編譯 SQL 來說，我們作為模板的參數(shù)化 SQL 已經(jīng)完成的編譯過程，這段 SQL 包含幾條有效語句？查哪張表？查哪些字段？作為條件的字段有哪些？......這些在 prepare 語句執(zhí)行完后都是固定的，此后我們再通過 execute 語句塞進(jìn)去的任何參數(shù)，都會進(jìn)行轉(zhuǎn)義，不會再作為 SQL 的一部分。這就是為什么說預(yù)編譯 SQL 可以防止注入的原因。

二、JDBC的預(yù)編譯

現(xiàn)在我們知道了預(yù)編譯在數(shù)據(jù)庫中是個怎樣的功能，那么 JDBC 又是如何把這個功能提供給開發(fā)者使用的呢？

1、PreparedStatement

從最開始學(xué) JDBC 時，我們就知道通過 JDBC 連接數(shù)據(jù)庫一般是這樣寫的：

Class.forName(JDBC_DRIVER); // 加載驅(qū)動Connectionconnection= DriverManager.getConnection(URL, USERNAME, PASSWORD); // 獲取連接PreparedStatementpreparedStatement= connection.prepareStatement(sql); // 獲取sqlStatement
preparedStatement.setString(1, foo); // 設(shè)置參數(shù)ResultSetresultSet= preparedStatement.executeQuery(); // 執(zhí)行SQL

這里有一個關(guān)鍵角色 PreparedStatement，相比起它的父接口 Statement，它最大的變化是多了各種格式為 setXXX 的、用于設(shè)置與占位符對應(yīng)的參數(shù)的方法，顯然它正對應(yīng)著上文我們提到的預(yù)編譯 SQL。

2、虛假的“預(yù)編譯”

不過事情顯然沒有這么簡單，我們依然以 MySQL 為例，默認(rèn)情況下 MySQL 驅(qū)動包提供的 PreparedStatement 實現(xiàn)類 ClientPreparedStatement 也能起到防止 SQL 注入的功能，但是方式跟我們想的不太一樣。

假設(shè)現(xiàn)有如下代碼，我們嘗試模擬進(jìn)行一次 SQL 注入：

Stringsql="select * from s_user where username = ?";
PreparedStatementpreparedStatement= connection.prepareStatement(sql);
preparedStatement.setString(1, "王五' union select * from s_user");
ResultSetresultSet= preparedStatement.executeQuery();

運(yùn)行上述代碼并正常的請求數(shù)據(jù)庫，然后我們?nèi)?shù)據(jù)庫執(zhí)行日志中查看對應(yīng)的執(zhí)行的 SQL 如下，會發(fā)現(xiàn)只有這么一行：

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

顯然跟我們上文說到的先 prepare 再 execute 流程不同，帶有占位符的原始 SQL 模板并沒有在日志中出現(xiàn)，但是代碼中的 王五' 確實也被轉(zhuǎn)義為了 '王五''。

數(shù)據(jù)庫到底收到了哪些數(shù)據(jù)？

那么數(shù)據(jù)庫到底拿到的就是這條 SQL，還是原始的 SQL 模板 + 參數(shù)呢？

為了了解這一點，我們打斷點跟蹤 ClientPreparedStatement.executeQuery 方法，一路找到它組裝請求數(shù)據(jù)庫的參數(shù)的那一行代碼：

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

最后我們會進(jìn)入 AbstractPreparedQuery.fillSendPacket 這個方法，這里主要干的事是把我們帶占位符的原始 SQL 模板和參數(shù)合并為最終要執(zhí)行的 SQL ，并封裝到 NativePacketPayload 對象，用于在后續(xù)發(fā)起 TCP 請求時把 SQL 參數(shù)轉(zhuǎn)為二進(jìn)制數(shù)據(jù)包。

為了驗證這一點，我們先拿到 sendPacket 對象，再獲取里面的字節(jié)數(shù)組，最后轉(zhuǎn)為字符串：

可以看到內(nèi)容就是已經(jīng)格式化完的 SQL：

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

現(xiàn)在答案就很明顯了，轉(zhuǎn)義在 preparedStatement.setString 方法調(diào)用的時候完成，而 PreparedStatement 在發(fā)起請求前就把轉(zhuǎn)義后的參數(shù)和 SQL 模板進(jìn)行了格式化，最后發(fā)送到 MySQL 的時候就是一條普通的 SQL。

鑒于此，我們可以說 MySQL 提供的 PreparedStatement 在默認(rèn)情況下是假的“預(yù)編譯”，它只不過在設(shè)置參數(shù)的時候幫我們對參數(shù)做了一下轉(zhuǎn)義，但是最后發(fā)送到數(shù)據(jù)庫的依然是普通的 SQL，而不是按預(yù)編譯 SQL 的方式去執(zhí)行。

3、真正的預(yù)編譯

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

依然以上文的代碼為例：

Stringsql="select * from s_user where username = ?";
PreparedStatementpreparedStatement= connection.prepareStatement(sql);
preparedStatement.setString(1, "王五' union select * from s_user");
ResultSetresultSet= preparedStatement.executeQuery();

設(shè)置了 useServerPrepStmts=true 后再執(zhí)行代碼，去數(shù)據(jù)庫查看執(zhí)行日志有：

Executeselect*from s_user where username ='王五\'unionselect*from s_user'
Prepare select * from s_user where username = ?

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

我們回到 ClientPreparedStatement.executeQuery 創(chuàng)建 sendPacket 地方看，此時通過 ((PreparedQuery<?>) this.query).fillSendPacket(); 拿到的 Message 對象是 null，然后進(jìn)一步追蹤到最后向 MySQL 發(fā)送請求的地方 NativeSession.execSQL：

public <T extendsResultset> T execSQL(Query callingQuery, String query, int maxRows, NativePacketPayload packet, boolean streamResults,ProtocolEntityFactory<T, NativePacketPayload> resultSetFactory, ColumnDefinition cachedMetadata, boolean isBatch) {// ... ...try {// 如果 sendPacket 為 null，則調(diào)用 sendQueryString 方法，把原始 sql 和參數(shù)序列化為二進(jìn)制數(shù)據(jù)包returnpacket== null? ((NativeProtocol) this.protocol).sendQueryString(callingQuery, query, this.characterEncoding.getValue(), maxRows, streamResults, cachedMetadata, resultSetFactory)// 否則調(diào)用 sendQueryPacket 方法，直接發(fā)送數(shù)據(jù)包: ((NativeProtocol) this.protocol).sendQueryPacket(callingQuery, packet, maxRows, streamResults, cachedMetadata, resultSetFactory);}// ... ...}

更具體的實現(xiàn)就不看了，基本都是關(guān)于序列化請求參數(shù)的邏輯。

三、Myabtis占位符與預(yù)編譯

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

當(dāng)然，看完上面的內(nèi)容其實就已經(jīng)很好猜到原因了：

• #{} 對應(yīng)的內(nèi)容會作為 SQL 參數(shù)的一部分通過 PreparedStatement.setXXX 裝入請求；

• ${} 對應(yīng)的內(nèi)容會直接作為 SQL 模板的一部分，而不會視為獨(dú)立的請求參數(shù)；

在 Mybatis 中，用于解析占位符的類為 GenericTokenParser ，根據(jù)它我們很容易在源碼中找到占位符的處理方法，從而驗證我們的猜想：

其中，#{} 占位符在 SqlSourceBuilder.ParameterMappingTokenHandler.handleToken 方法中處理：

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

可見 #{} 占位符會被解析為 ? 占位符，而對于的數(shù)據(jù)會被添加到 parameterMappings 用于后續(xù)塞到 PreparedStatement。

而 ${} 占位符在 PropertyParser.VariableTokenHandler.handleToken 方法中被處理：

public String handleToken(String content) {if (variables != null) {Stringkey= content;if (enableDefaultValue) {finalintseparatorIndex= content.indexOf(defaultValueSeparator);StringdefaultValue=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 + "}";
}

若占位符符合規(guī)范，則占會根據(jù)占位符中的內(nèi)容去用戶給定的參數(shù)中取值，并且讓值直接替換掉原本 SQL 腳本中的 ${} 占位符。

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

總結(jié)

回顧一下全文，當(dāng)我們說“預(yù)編譯”的時候，其實這個功能來自于數(shù)據(jù)庫的支持，它的原理是先編譯帶有占位符的 SQL 模板，然后在傳入?yún)?shù)讓數(shù)據(jù)庫自動替換 SQL 中占位符并執(zhí)行，在這個過程中，由于預(yù)編譯好的 SQL 模板本身語法已經(jīng)定死，因此后續(xù)所有參數(shù)都會被視為不可執(zhí)行的非 SQL 片段被轉(zhuǎn)義，因此能夠防止 SQL 注入。

當(dāng)我們通過 JDBC 使用 PreparedStatement 執(zhí)行預(yù)編譯 SQL 的時候，此處的預(yù)編譯實際上是假的預(yù)編譯（至少 MySQL 是如此，不過其他數(shù)據(jù)庫仍待確認(rèn)），PreparedStatement 只是在設(shè)置參數(shù)的時候自動做了一層轉(zhuǎn)義，最終提交給數(shù)據(jù)庫執(zhí)行的 SQL 仍然是單條的非預(yù)編譯 SQL。

而當(dāng)我們通過在驅(qū)動 url 上開啟 useServerPrepStmts 配置后，預(yù)編譯就會真正的生效，驅(qū)動包發(fā)往數(shù)據(jù)庫的請求就會分成帶占位符的 SQL 模板和參數(shù)，到了數(shù)據(jù)庫再由數(shù)據(jù)庫完成格式化并執(zhí)行。

此外，八股文常提到的“Mybatis 的 #{} 相比 ${} 可以防止 SQL 注入”這一點，本質(zhì)上是因為 #{} 占位符會被解析為 SQL 模板中的 ? 占位符，而 ${} 占位符會被直接解析為 SQL 模板的一部分導(dǎo)致的。

最后腦補(bǔ)一下，由于 useServerPrepStmts 不開啟時 PreparedStatement 的預(yù)編譯實際上是假的預(yù)編譯，所以理論上使用 #{} 也并非絕對安全，如果有辦法繞過 PreparedStatement 的檢查，那么數(shù)據(jù)庫拿到被注入過的 SQL 直接執(zhí)行，依然有暴斃的風(fēng)險。