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 クエリの前処理を行います。このロジックを見てみましょう。

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 ドライバの実装の 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 以降でサポートされているフルテキストマッチを使用しています。この場合、マッチさせる文字列 +? は MySQL によってクエリ対象の文字列として解析され、プレースホルダーとしては解析されません。そのため、返される stmt には必要なパラメータの数が 0 として通知され、database/sql はその後のロジックで渡されたパラメータと必要なパラメータの数を照合し、一致しない場合は Error をスローします。

さて、問題は見つかりました。では、Prepared Statement とは一体何で、なぜそれが必要なのでしょうか？

Prepared Statement#

Prepared Statement とは？#

実際のところ、前述の内容でかなり明確に説明されていますが、再度確認してみましょう。Prepared Statement は MySQL（他の PGSQL などにも似たようなものがあります）のメカニズムで、SQL を前処理し、SQL とクエリデータを分離することでプログラムの堅牢性を保証することを目的としています。

MySQL の公式な紹介によると、Prepared Statement には以下のような利点があります。

ステートメントを実行するたびに解析のオーバーヘッドが少なくなる。通常、データベースアプリケーションは、WHERE 句や削除のためのクエリ、更新のための SET、挿入のための VALUES などのリテラルや変数の値の変更のみで、ほぼ同一のステートメントを大量に処理します。

SQL インジェクション攻撃からの保護。パラメータ値にはエスケープされていない SQL クォートや区切り文字が含まれる可能性があります。

簡単に言えば：

パフォーマンスの向上、SQL の再解析によるオーバーヘッドを回避
SQL インジェクションの回避

MySQL の Prepared Statement には二つの使用方法があります。一つはバイナリの Prepared Protocol を使用する方法（これは今日の記事の範囲外で、別の日に MySQL のバイナリプロトコルについて書く予定です）、もう一つは SQL を使用して処理する方法です。

Prepared Statement には三つのコマンドがあります。

PREPARE は Prepared Statement を作成するために使用されます。
EXECUTE は Prepared Statement を実行するために使用されます。
DEALLOCATE PREPARE は Prepared Statement を破棄するために使用されます。

ここで注意が必要なのは、Prepared Statement はセッション制限があり、通常、Prepared Statement は作成されたセッション内でのみ生存します。接続が切断されるか、他の理由でセッションが無効になると、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 インジェクションを回避できるのでしょうか？

実際、非常に簡単です。クエリとデータを分離したからです。

前述のテーブルを例にとってみましょう。

手動で 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'

クエリとクエリパラメータを構造的に区別したため、どんな入力をしてもそれがクエリパラメータの一部として処理され、インジェクションのリスクを回避できます。

Prepared Statement の利点と欠点#

利点は明らかです。

データベースが Prepared Statement をキャッシュするため、クライアントが SQL を繰り返し処理するオーバーヘッドを省けます。
SQL Injection Attack を回避できます。
意味が明確です。

欠点もいくつかあります。

Prepared Statement のバイナリプロトコルにはクライアントの互換性の問題があり、一部の言語のクライアントは Prepared Statement に対するバイナリプロトコルのサポートがない場合があります。
データベースとの通信が二回発生するため、SQL クエリが密集して行われる場合、I/O ボトルネックが発生する可能性があります。

したがって、具体的にはシーンに応じてトレードオフを行う必要があります。

余談#

飛行機の中でこの文章を書いたのは、新年の新たな始まりとしての意味があります。もっと記事を書き、生活リズムを整え、彼女を大切にしたいと思います。そういえば、この期間のいくつかの試行錯誤（例えば Binlog の解析など）を通じて、MySQL が宝の山であることに気づきました。今後、MySQL の Binlog や Protocol に関するいくつかの問題や面白い点についての記事を書こうと思います（フラグ ++、決して原稿を急かさないでください（逃

さて、今夜はこれで終わりにします。飛行機が着陸するので、先にパソコンを閉じます（逃