一次SQL請求，返回分頁數(shù)據(jù)和總條數(shù)，你學(xué)會了嗎？
日常搬磚，總少不了需要獲取分頁數(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客戶端選項client_multi_statements默認(rèn)為false：會禁止多條 SQL 語句的執(zhí)行，這意味著在單個sql請求中只有第一條 SQL 語句會被執(zhí)行，后續(xù)的 SQL 語句將被忽略。
這是一種提高數(shù)據(jù)庫操作安全性的方法，可以有效防止 SQL 注入攻擊和意外執(zhí)行多條語句帶來的風(fēng)險。
MySQL客戶端支持修改這樣的設(shè)定 ：client_multi_statements=true。
圖片
劣勢：存在sql注入的風(fēng)險， 錯誤處理比較復(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)險，我們就展開聊一聊。
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)險，但是還是沒有辦法避免單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)險[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)罩救缦拢?/p>
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)罩荆簊how 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)險；
之后落地到sql注入的原理， 給出了參數(shù)化查詢（預(yù)編譯語句）能防止sql注入的核心機(jī)制。
參考資料
[1]參數(shù)化查詢可以防止sql注入風(fēng)險: https://go.dev/doc/database/sql-injection
