一次 sql 請求，返回分頁數據和總條數

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

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

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

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/Hhw7hYLx3Magk0L1Uv_hEA

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

2. sql 注入

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

3.1 預編譯語句

總結

猜你喜歡