久しぶりに記事を書きました。新年なので、技術的な内容を書いて状態を保つ必要があります。最近、面白い問題に遭遇したので、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 インジェクションを回避できるのでしょうか？
実はとても簡単です。私たちは 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'
私たちはクエリとクエリパラメータを構造的に区別したため、この時点で何を入力しても、それはクエリパラメータの一部として処理され、注入のリスクを回避することができます。
Prepared Statement の利点と欠点#
利点は明らかです。
- データベースは
Prepared Statementをキャッシュするため、クライアントが SQL を再処理する際のオーバーヘッドを回避できます。 SQL Injection Attackを回避します。- 意味が明確です。
欠点もいくつかあります。
Prepared Statementのバイナリプロトコルにはクライアントの互換性の問題があり、一部の言語のクライアントはPrepared Statementに対するバイナリプロトコルのサポートを提供しない場合があります。- データベースとの通信が二回発生するため、SQL クエリが密集している場合、I/O ボトルネックが発生する可能性があります。
したがって、具体的にはシーンに応じてトレードオフを行う必要があります。
つぶやき#
飛行機の中でこの文章を書いたのは、新年の新たな始まりとしての意味があります。もっと記事を書いて、生活リズムを整え、彼女を大切にしようと思います。そういえば、この期間のいくつかの試行錯誤（例えば Binlog の解析など）を通じて、MySQL は宝の山だと気づきました。今後、MySQL の Binlog や Protocol に関するいくつかの落とし穴や面白い点について記事を書く予定です（うん、フラグ ++、決して原稿を急かさないでください（逃げ）。
さて、今夜はこの辺で。飛行機が着陸するので、先にパソコンを閉じます（逃げ）。