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

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

最近我在思考：

常規實踐為什么不是 在一次sql請求中中執行多次sql查詢或多次更新，顯而易見的優勢：

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

1. mysql 默認單sql請求單語句

mysql客戶端選項client_multi_statements默認為false：會禁止多條 SQL 語句的執行，這意味著在單個sql請求中只有第一條 SQL 語句會被執行，后續的 SQL 語句將被忽略。

這是一種提高數據庫操作安全性的方法，可以有效防止 SQL 注入攻擊和意外執行多條語句帶來的風險。

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

圖片

劣勢：存在sql注入的風險， 錯誤處理比較復雜。

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

(2)

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

將會形成2個數據集，golang的實踐如下：

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注入的風險，我們就展開聊一聊。

2. sql注入

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

有這樣的業務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默認值為false就能于水火之間挽救數據庫：執行第一個sql之后，后面的惡意sql都不會執行。

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

query:= "select * from user where user='" + input_name +"' and  pwd='" +input_pwd +"'" 
 

select * from user where user='xxx' and pwd='' OR '1'='1'  -- 會繞過認證邏輯。

3. 參數化查詢防止sql注入

參數化查詢可以防止sql注入風險[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查詢內部會利用提供的參數1創建預編譯語句， 在運行時，實際是執行帶參的預編譯后的語句。

在服務器收到的查詢日志如下：

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數據庫開啟了查詢日志：show variables like '%general_log%';打開sql查詢日志的開關：set global general_log = on; 。

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

3.1 預編譯語句

數據庫預編譯后， SQL語義結構和數據分離，這樣即使輸入包含惡意代碼，它也只會被當作數據處理，不會影響已經被解析固定的SQL語義結構。

預編譯語句包含兩次 sql交互：

①  預編譯階段（Prepare Phase）：

• 客戶端向服務器發送一個包含 SQL 語句（帶有參數占位符）的請求。
• sql服務器對SQL 語句進行語法和語義檢查，然后對其進行預編譯，并為其分配一個標識符（Statement ID）。
• 服務器返回一個確認響應，表示預編譯語句已經成功準備好。

②  執行階段（Execute Phase）：

• 客戶端發送執行請求，包含預編譯語句的標識符和實際參數值。
• 服務器將參數值綁定到預編譯語句的占位符上，然后執行該語句。
• 服務器返回執行結果（如結果集或影響的行數）。

圖示如下：

客戶端                          服務器
   |                               |
   |----預編譯語句（Prepare）------>|
   |                               |
   |<-------確認響應（OK）----------|
   |                               |
   |---執行語句（Execute） + 參數---->|
   |                               |
   |<----------查詢結果-------------|

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

golang的預編譯的寫法與常規的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#  其實也支持預編譯語句版本的sqlCommand：SqlCommand.Prepare()

總結

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

了解到client_multi_statements= false 確實能避免一部分sql注入風險；

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

參考資料

[1]參數化查詢可以防止sql注入風險: https://go.dev/doc/database/sql-injection