簡單聊聊 SQL 中的預備語句

好久沒寫文章了，新年還是得寫點技術水文來保證下狀態，正好最近遇到一個比較有意思的問題，就來簡單聊聊一下關於 MySQL 中 Prepared Statements 吧

開始#

gorm 是大家在使用 Go 開發時的比較常用的 ORM 了，最近在使用 gORM 的時候遇到一個很有意思的問題。首先我大概描述一下這個問題

在使用 gORM 的 Raw 方法進行 SQL 查詢時，構造了如下類似的 SQL

select * from demo where match(name) AGAINST('+?' IN BOOLEAN MODE)

在隨後傳入參數的時候，返回 Error : sql: expected 0 arguments, got 1。而其餘的諸如如下的查詢就正常執行

select * from demo where name = ?

最開始我以為這是 gORM 中拼接 SQL 模塊的問題，但是看了下代碼後發現一個很有趣的邏輯。gORM 中並沒有拼接 Raw SQL 的相關邏輯，它會直接調用 Golang 中的標準庫 database/sql 來進行 SQL 的處理，而 database/sql 將會直接調用對應資料庫驅動的實現，我們先來看看在 databse/sql 中關於 Query 的邏輯。

func (db *DB) queryDC(ctx, txctx context.Context, dc *driverConn, releaseConn func(error), query string, args []interface{}) (*Rows, error) {
	queryerCtx, ok := dc.ci.(driver.QueryerContext)
	var queryer driver.Queryer
	if !ok {
		queryer, ok = dc.ci.(driver.Queryer)
	}
	if ok {
		var nvdargs []driver.NamedValue
		var rowsi driver.Rows
		var err error
		withLock(dc, func() {
			nvdargs, err = driverArgsConnLocked(dc.ci, nil, args)
			if err != nil {
				return
			}
			rowsi, err = ctxDriverQuery(ctx, queryerCtx, queryer, query, nvdargs)
		})
		if err != driver.ErrSkip {
			if err != nil {
				releaseConn(err)
				return nil, err
			}
			// Note: ownership of dc passes to the *Rows, to be freed
			// with releaseConn.
			rows := &Rows{
				dc:          dc,
				releaseConn: releaseConn,
				rowsi:       rowsi,
			}
			rows.initContextClose(ctx, txctx)
			return rows, nil
		}
	}

	var si driver.Stmt
	var err error
	withLock(dc, func() {
        // 比較有意思的地方
		si, err = ctxDriverPrepare(ctx, dc.ci, query)
	})
	if err != nil {
		releaseConn(err)
		return nil, err
	}

	ds := &driverStmt{Locker: dc, si: si}
	rowsi, err := rowsiFromStatement(ctx, dc.ci, ds, args...)
	if err != nil {
		ds.Close()
		releaseConn(err)
		return nil, err
	}

	// Note: ownership of ci passes to the *Rows, to be freed
	// with releaseConn.
	rows := &Rows{
		dc:          dc,
		releaseConn: releaseConn,
		rowsi:       rowsi,
		closeStmt:   ds,
	}
	rows.initContextClose(ctx, txctx)
	return rows, nil
}
}

在 database/sql 執行 QueryDC 邏輯時，會調用 ctxDriverPrepare 方法來進行 SQL Query 的預處理，我們來看看這段邏輯

func ctxDriverPrepare(ctx context.Context, ci driver.Conn, query string) (driver.Stmt, error) {
	if ciCtx, is := ci.(driver.ConnPrepareContext); is {
		return ciCtx.PrepareContext(ctx, query)
	}
	si, err := ci.Prepare(query)
	if err == nil {
		select {
		default:
		case <-ctx.Done():
			si.Close()
			return nil, ctx.Err()
		}
	}
	return si, err
}

在其中，ctxDriverPrepare 會調用 ci.Prepare(query) 來執行對應 SQL Driver 實現的 Prepare 或者 PrepareContext 方法來對 SQL 預處理，在 go-mysql-driver 中，對應的實現是這樣

func (mc *mysqlConn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error) {
	if err := mc.watchCancel(ctx); err != nil {
		return nil, err
	}

	stmt, err := mc.Prepare(query)
	mc.finish()
	if err != nil {
		return nil, err
	}

	select {
	default:
	case <-ctx.Done():
		stmt.Close()
		return nil, ctx.Err()
	}
	return stmt, nil
}

這一段的邏輯是 go-mysql-driver 會向 MySQL 發起 prepared statement 請求，獲取到對應的 Stmt 後將其返回

在 stmt 中包含了對應的參數數量，stmt name 等信息。在這裡，SQL 會將？等參數佔位符進行解析，並告知客戶端需要傳入的參數數量

問題也出在這裡，我們重新看一下之前的 SQL

select * from demo where match(name) AGAINST('+?' IN BOOLEAN MODE)

在這裡，我使用了 MySQL 5.7 後支持的 Full Text Match ，在這裡，我們待匹配的字符串 +? 會被 MySQL 解析成為一個待查詢的字符串，而不会作為佔位符進行解析，那麼返回 stmt 中，需要傳入的參數數量為 0，而 database/sql 會在後續的邏輯中對我們傳入的參數和需要傳入的參數數量進行匹配，如果不一致則會拋出 Error 。

好了，問題找到了，那麼 Prepared Statement 究竟是什麼東西，而我們為什麼又需要這個？

Prepared Statement#

什麼是 Prepared Statement？#

其實大致的內容前面已經聊的比較清楚了，我們來重新複習下：Prepared Statement 是一種 MySQL（其餘的諸如 PGSQL 也有類似的東西）的機制，用於預處理 SQL，將 SQL 和查詢數據分離，以期保證程序的健壯性。

在 MySQL 官方的介紹中，Prepared Statement 有如下的好處

Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of almost-identical statements, with only changes to literal or variable values in clauses such as WHERE for queries and deletes, SET for updates, and VALUES for inserts.

Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

簡而言之是：

提升性能，避免重複解析 SQL 帶來的開銷
避免 SQL 注入

MySQL 的 Prepared Statement 有兩種使用方式，一種是使用二進制的 Prepared Protocol（這個不在今天的文章的範圍內，改天再寫篇文章來聊聊 MySQL 中的一些二進制協議），一種是使用 SQL 進行處理

在 Prepared Statement 中有著三種命令

PREPARE 用於創建一個 Prepared Statement
EXECUTE 用於執行一個 Prepared Statement
DEALLOCATE PREPARE 用於銷毀一個 Prepared Statement

這裡需要注意一點的是，Prepared Statement 存在 Session 限制，一般情況下一個 Prepared Statement 僅存活於它被創建的 Session 。當連接斷開，者在其餘情況下 Session 失效的時候，Prepared Statement 會自動被銷毀。

接下來，我們來動手實驗下

怎麼使用 Prepared Statement#

首先我們先創建一個測試表

create table if not exists `user`
(
    `id`   bigint(20)   not null auto_increment,
    `name` varchar(255) not null,
    primary key (`id`)
) engine = InnoDB
  charset = 'utf8mb4';

然後插入數據

insert into user (`name`) values ('abc');

好了，我們先按照傳統的方式進行查詢下

select *
from user
where name = 'abc';

好了，我們現在來使用 Prepared Statement

首先使用 Prepared 關鍵字創建一個 statement

set @s = 'select * from user where name=?';

PREPARE demo1 from @s;

然後使用 Execute 關鍵字來執行 Statement

set @a = 'abc';

EXECUTE demo1 using @a;

嗯，還是很簡單的對吧

為什麼要使用 Prepared Statement？#

其中一個很重要的理由是可以避免 SQL Injection Attack （SQL 注入）的情況出現，而問題在於，為什麼 Prepared Statement 能夠避免 SQL 注入？

其實很簡單，我們將 Query 和 Data 進行了分離

還是以之前的表作為例子

在沒有手動處理 SQL 和參數的情況下，我們往往使用字符串拼接，那麼這樣會利用 SQL 語法來構造一些非法 SQL，以 Python 為例

b = "'abc';drop table user"
a = f"select * from user where name={b}"

那麼這樣一段代碼將會生成這樣的 SQL

select * from user where name='abc';drop table user

嗯，，，，資料庫從入門到刪表跑路.pdf

那麼，我們來使用 Prepared Statement 來看看

set @a = '\'abc\';drop table user';

EXECUTE demo1 using @a;

然後我們最後執行的語句是

select * from user where name='\'abc\';drop table user'

因為我們將 Query 與 Query Params 在結構上進行了區分，這個時候我們無論輸入什麼，都会將其作為 Query Params 的一部分進行處理，從而避免了注入的風險

Prepared Statement 的優劣#

好處顯而易見

因為資料庫會對 Prepared Statement 進行緩存，從而免去了客戶端重複處理 SQL 帶來的開銷
避免 SQL Injection Attack
語義清楚

缺點也有不少

Prepared Statement 的二進制協議存在客戶端兼容的問題，有些語言的客戶端不一定會對 Prepared Statement 提供二進制的協議支持
因為存在兩次與資料庫的通信，在密集進行 SQL 查詢的情況下，可能會出現 I/O 瓶頸

所以具體還是要根據場景來做 Trade-off 了

碎碎念#

飛機上寫下這篇文章算是作為新年的一個新開始吧，爭取多寫文章，規範作息，好好照顧女朋友。對了，通過這段時間的一些折騰（比如解析 Binlog 之類的），突然發現 MySQL 是個寶庫，後面會寫幾篇文章來聊聊踩坑 MySQL 中的 Binlog 和 Protocol 中的一些坑和好玩的地方（嗯 Flag ++，千萬別催稿（逃

好了，今晚就先這樣，飛機要落地了，我先關電腦了（逃