日常搬磚，總少不了需要獲取分頁數(shù)據(jù)和總行數(shù)。

一直以來的實(shí)踐是編碼兩次sql請求，分別拉分頁數(shù)據(jù)和totalCount。

最近我在思考：

常規(guī)實(shí)踐為什么不是 在一次sql請求中中執(zhí)行多次sql查詢或多次更新，顯而易見的優(yōu)勢：

① 能顯著減低“客戶端和服務(wù)器之間的網(wǎng)絡(luò)往返次數(shù)”，提高吞吐量
② 簡化客戶端代碼邏輯

---

1. mysql 默認(rèn)單sql請求單語句

mysql客戶端選項(xiàng)client_multi_statements默認(rèn)為false：會禁止多條 SQL 語句的執(zhí)行，這意味著在單個sql請求中只有第一條 SQL 語句會被執(zhí)行，后續(xù)的 SQL 語句將被忽略。

這是一種提高數(shù)據(jù)庫操作安全性的方法，可以有效防止 SQL 注入攻擊和意外執(zhí)行多條語句帶來的風(fēng)險(xiǎn)。

MySQL客戶端支持修改這樣的設(shè)定 ：client_multi_statements=true。

劣勢：存在sql注入的風(fēng)險(xiǎn)， 錯誤處理比較復(fù)雜。

(1) go-sql-driver開啟多語句支持: multiStatements=true

(2)

SELECT?*??FROM?`dict_plugin`??limit??20?,10;
SELECT?count(*)?as??totalCount??from?`dict_plugin`;

將會形成2個數(shù)據(jù)集，golang的實(shí)踐如下：

results,?err?=?p.Query(querystring)for?results.Next()?{err?=?results.Scan(&...)}if?!results.NextResultSet()?{log.ErrorF(ctx,?"expected?more?result?sets:?%v",?results.Err())}for?results.Next()?{err?=?results.Scan(&totalCount)}

既然提到了開啟client_multi_statements 有sql注入的風(fēng)險(xiǎn)，我們就展開聊一聊。

2. sql注入

我們先看下sql注入的原理：

有這樣的業(yè)務(wù)sql：

var?input_name?string
query:?=?"select??*?from?user?where?user_name='"?+?input_name+"'"
sql.Query(query)

如果從界面輸入的input_name="janus';delete from user; ?--",
會形成惡意sql：select * from user where user_name='janus';delete from user; ?--' 。

這個時候，客戶端的client_multi_statements默認(rèn)值為false就能于水火之間挽救數(shù)據(jù)庫：執(zhí)行第一個sql之后，后面的惡意sql都不會執(zhí)行。

由此可知，client_multi_statements=false，確實(shí)可以顯著降低sql注入的風(fēng)險(xiǎn)，但是還是沒有辦法避免單sql注入， 比如從界面密碼框注入' OR '1'='1 會繞過登錄認(rèn)證。

query:=?"select?*?from?user?where?user='"?+?input_name?+"'?and??pwd='"?+input_pwd?+"'"?select?*?from?user?where?user='xxx'?and?pwd=''?OR?'1'='1'??--?會繞過認(rèn)證邏輯。

3. 參數(shù)化查詢防止sql注入

參數(shù)化查詢可以防止sql注入風(fēng)險(xiǎn)^[1]

//?Correct?format?for?executing?an?SQL?statement?with?parameters.var?queryStr?=?"SELECT?*?FROM?`dict_plugin_Test`?WHERE?`plugin_name`?=??"
var?args?string?=?"55?union?select?*?from?`dict_plugin_Test`"rows,?err?:=?db.Query(queryStr,?args)

sql查詢內(nèi)部會利用提供的參數(shù)1創(chuàng)建預(yù)編譯語句， 在運(yùn)行時，實(shí)際是執(zhí)行帶參的預(yù)編譯后的語句。

在服務(wù)器收到的查詢?nèi)罩救缦?#xff1a;

2024-08-13T08:07:18.922818Z???26?Connect?root@localhost?on?tcinfra_janus_sharing?using?TCP/IP
2024-08-13T08:07:18.924525Z???26?Prepare?SELECT?*?FROM?`dict_plugin_Test`?WHERE?`plugin_name`?=??
2024-08-13T08:07:18.924671Z???26?Execute?SELECT?*?FROM?`dict_plugin_Test`?WHERE?`plugin_name`?=?'55?union?select?*?from?`dict_plugin_Test`'
2024-08-13T08:07:18.925273Z???26?Close?stmt

判斷mysql數(shù)據(jù)庫開啟了查詢?nèi)罩?#xff1a;show variables like '%general_log%';
打開sql查詢?nèi)罩镜拈_關(guān)：set global general_log = on; 。

注意：參數(shù)占位符根據(jù)DBSM和驅(qū)動而有所不同，例如，Postgres 的pq驅(qū)動程序接受占位符形式是 $1而不是?。

3.1 預(yù)編譯語句

數(shù)據(jù)庫預(yù)編譯后， SQL語義結(jié)構(gòu)和數(shù)據(jù)分離，這樣即使輸入包含惡意代碼，它也只會被當(dāng)作數(shù)據(jù)處理，不會影響已經(jīng)被解析固定的SQL語義結(jié)構(gòu)。

預(yù)編譯語句包含兩次 sql交互：

① ?預(yù)編譯階段（Prepare Phase）：

• 客戶端向服務(wù)器發(fā)送一個包含 SQL 語句（帶有參數(shù)占位符）的請求。

• sql服務(wù)器對SQL 語句進(jìn)行語法和語義檢查，然后對其進(jìn)行預(yù)編譯，并為其分配一個標(biāo)識符（Statement ID）。

• 服務(wù)器返回一個確認(rèn)響應(yīng)，表示預(yù)編譯語句已經(jīng)成功準(zhǔn)備好。

② ?執(zhí)行階段（Execute Phase）：

• 客戶端發(fā)送執(zhí)行請求，包含預(yù)編譯語句的標(biāo)識符和實(shí)際參數(shù)值。

• 服務(wù)器將參數(shù)值綁定到預(yù)編譯語句的占位符上，然后執(zhí)行該語句。

• 服務(wù)器返回執(zhí)行結(jié)果（如結(jié)果集或影響的行數(shù)）。

圖示如下：

客戶端??????????????????????????服務(wù)器|???????????????????????????????||----預(yù)編譯語句（Prepare）------>||???????????????????????????????||<-------確認(rèn)響應(yīng)（OK）----------||???????????????????????????????||---執(zhí)行語句（Execute）?+?參數(shù)---->||???????????????????????????????||<----------查詢結(jié)果-------------|

我們了解到預(yù)編譯語句，將SQL語義和數(shù)據(jù)分離，通過兩次sql交互（在預(yù)編譯階段固定了sql語義結(jié)構(gòu)）， 有效防止了SQL注入攻擊, 另一方面，預(yù)編譯語句在重復(fù)執(zhí)行某一sql語句時確實(shí)有加快查詢結(jié)果的效果。

golang的預(yù)編譯的寫法與常規(guī)的sql查詢類似：

stmt,?err?:=?p.Prepare("SELECT?*?FROM?`dict_plugin_Test`?WHERE?`plugin_name`?=??")
var?args?string?=?"55?union?select?*?from?`dict_plugin_Test`"
results,?err?:=?stmt.Query(args)
if?err?!=?nil?{fmt.Printf("query?fail:?%v",?err)return?err
}
defer?stmt.Close()for?results.Next()?{err?=?results.Scan(.....)......
}

btw, C# ?其實(shí)也支持預(yù)編譯語句版本的sqlCommand：SqlCommand.Prepare()

總結(jié)

本文通過我們最初開始數(shù)據(jù)庫編程時的一個實(shí)踐， 提出在【一次sql請求中執(zhí)行多次sql查詢】的猜想；

了解到client_multi_statements= false 確實(shí)能避免一部分sql注入風(fēng)險(xiǎn)；

之后落地到sql注入的原理， 給出了參數(shù)化查詢（預(yù)編譯語句）能防止sql注入的核心機(jī)制。

參考資料

[1]

參數(shù)化查詢可以防止sql注入風(fēng)險(xiǎn): https://go.dev/doc/database/sql-injection

自古以來，同步/異步都是八股文第一章

async/await 貼臉輸出，這次你總該明白了

流量調(diào)度、微服務(wù)可尋址性和注冊中心

Go語言正/反向代理的姿勢

兩將軍問題和TCP三次握手

"家長進(jìn)校園"之《計(jì)算機(jī)和人工智能》

點(diǎn)“贊”戳“在看”