SQLiteプリペアドステートメント入門：prepare/bind/step/finalize

プリペアドステートメントの正体

SQLite に SQL の文字列を渡すと、実際にレコードが動く前に、けっこうな量の下準備が走ります。まずトークナイズして構文解析、続いてテーブル名やカラム名が本当に存在するかをチェック、実行計画を立てて、最後に SQLite の仮想マシン用のバイトコードへコンパイル——ここまで終えて、ようやくクエリが走り出します。

ここで言う プリペアドステートメント (prepared statement) とは、「バイトコードへコンパイルし終わった状態」で処理を止めて、その結果をそのまま保持しておいたもののこと。コンパイル済みプログラムには、あとから値を流し込むためのスロット——いわゆるプレースホルダ——が空けてあります。同じプログラムを値だけ差し替えて何度も実行できますし、外部から入ってきた信用ならない値を渡すときも安全に使えます。

イメージとしては、料理のたびにレシピを毎回読み上げてもらうのか、それとも一度レシピを覚えてもらって当日は材料名だけ伝えるのか、その違いに近いです。

ライフサイクル: prepare → bind → step → finalize

どの言語の SQLite ドライバも、結局は同じ 4 つの C API をラップしているだけです。C を書く予定がなくても、この呼び方は覚えておいて損はありません。エラーメッセージや公式ドキュメントがこの語彙で書かれているからです。

1. sqlite3_prepare_v2 — SQL 文字列をコンパイルしてステートメントハンドルにする。
2. sqlite3_bind_* — プレースホルダに値を流し込む(型ごとに関数が分かれている)。
3. sqlite3_step — プログラムを実行する。SELECT なら行を 1 件ずつ進めるために繰り返し呼ぶ。INSERT/UPDATE/DELETE なら一発で終わる。
4. sqlite3_finalize — 使い終わったコンパイル済みプログラムを解放する。

実行のあいだに sqlite3_reset を挟むと、走り終えたステートメントを巻き戻して、prepare をやり直さずに再バインド・再実行できます。ステートメント再利用の基本テクニックです。

SQL 中のプレースホルダ

SQL 文字列の中では、値をそのまま埋め込むのではなく、値が入る場所ごとにプレースホルダを書いておきます。SQLite では次のような書き方が使えます。

-- 匿名、位置指定:
INSERT INTO users (name, email) VALUES (?, ?);

-- 番号指定:
INSERT INTO users (name, email) VALUES (?1, ?2);

-- 名前指定:
INSERT INTO users (name, email) VALUES (:name, :email);
INSERT INTO users (name, email) VALUES (@name, @email);
INSERT INTO users (name, email) VALUES ($name, $email);

ドライバレベルのコードでは ? が一番よく使われます。パラメータが複数あったり、同じ値が何度も登場するようなケースでは、:name のような名前付きプレースホルダの方が読みやすくなります。プロジェクト単位でスタイルを統一して、最後までそれを貫くのがおすすめです。

逆に、絶対にやってはいけないのが文字列連結でクエリを組み立てることです:

-- これはやってはいけません:
"INSERT INTO users (name) VALUES ('" + user_input + "')"

そのやり方ではSQLインジェクションを招きますし、これから説明するバイトコード再利用のメリットも台無しになってしまいます。

SQLだけで動きを追ってみる

ホスト言語を使わずに prepare / bind / step の流れを確かめるために、SQLite が提供する SQL 機能だけで同じことをやってみましょう。まずはテーブルを用意し、プレースホルダを使った形式でリテラルを埋め込みながら 1 行 INSERT してみます。

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com');
INSERT INTO users (name, email) VALUES ('Boris', 'boris@example.com');

SELECT id, name, email FROM users ORDER BY id;

実際のアプリでは、値を直接埋め込んで書くことはしません。代わりに、?, ? のプレースホルダ付きで INSERT を一度だけ prepare しておき、ユーザーごとに名前とメールアドレスのペアを bind して step する、という流れになります。コンパイル済みのバイトコードは毎回同じで、変わるのはバインドする値だけです。

ステートメントを再利用する(パフォーマンス向上のカギ)

ドライバ越しに書くときの定番パターンを紹介します。以下は擬似コードで、言語ごとに書き方は微妙に違いますが、骨格はどの言語でも共通です。

-- 一度だけ準備する:
INSERT INTO users (name, email) VALUES (?, ?);

-- その後、ループ内で:
--   bind(1, name)
--   bind(2, email)
--   step()
--   reset()

プリペアドステートメントを使うと、SQLのパースとコンパイルは一度だけで済みます。あとはループのたびにバイトコードを実行して、値をスロットにコピーするだけです。大量のINSERT（たとえば10万行のインポートなど）では、毎回パースし直す方式に比べて圧倒的に速くなります。1つのトランザクションでまとめれば、桁違いの差になることも珍しくありません。

ありがちな落とし穴は、ループの 中 で prepare を呼んでしまうパターンです。これだとせっかくのメリットが帳消しになります。prepare はループの外で1回だけ、バインドと step をループの中で回すのが正解です。

sqlite パラメータ化クエリが安全な理由

バインド変数は、SQL文字列に文字列として埋め込まれているわけではありません。整数スロット・テキストスロット・BLOBスロットといった型付きのスロットを通して、バイトコードプログラムに値として渡される仕組みです。SQLiteが値の側を再びSQLとして解釈することはないので、どんな値が入ってもクエリの構造が書き換わることはありません。これが sqlite SQLインジェクション対策の本質です。

比較してみましょう。

-- 脆弱性あり。user_input が次の場合: '); DROP TABLE users;--
-- クエリは破壊的になります。
"SELECT * FROM users WHERE name = '" + user_input + "'"

-- 安全。user_input は TEXT 値としてバインドされ、
-- 内容に関係なく文字列としてのみ比較されます。
SELECT * FROM users WHERE name = ?;

2 番目の書き方なら、たとえ user_input が '); DROP TABLE users;-- のような文字列でも安全です。SQLite はその奇妙な文字列そのものを名前として持つユーザーを律儀に探しに行き、見つからずに 0 行を返すだけ。値によってクエリの構造が変わることは絶対にありません。

SQL インジェクション対策については別のドキュメントで詳しく取り上げますが、要点はシンプルです。プリペアドステートメントは SQL インジェクション対策の ひとつ ではなく、本命の 対策です。

行を返すステートメントの扱い

SELECT の場合、step は 1 行ずつ結果を返してきます。ドライバ側では通常、「完了」が返るまでループを回します。

CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('ノート',     4.50),
    ('ペン',       1.20),
    ('消しゴム',   0.80);

SELECT name, price FROM products WHERE price < 2.00 ORDER BY price;

アプリのコードでは、ドライバが 2.00 のところを ? にして SELECT を prepare し、しきい値の値をバインドしてから、ループの中で step を呼び出して 1 行ずつ読み取っていきます。最後の行を読み終えると step が完了を返すので、ドライバはステートメントを reset して別のしきい値で再実行するか、finalize して破棄します。

finalize を忘れずに

プリペアドステートメントは SQLite 内部で小さなメモリ確保を伴います。これをリークするとメモリを食いつぶすだけでなく、もっと厄介なことに、データベースに対する内部ロックを握り続けるため、他のライターをブロックしてしまうこともあります。どのドライバにも自動で後始末する仕組み(Python のコンテキストマネージャ、C# の using ブロック、C++ の RAII など)が用意されているので、きちんと活用しましょう:

• Python の sqlite3 はカーソルが GC されるタイミングで finalize されますが、明示的に cursor.close() を呼ぶ方がきれいです。
• better-sqlite3 (Node) は Statement が GC されるときに finalize されます。長生きするプリペアドステートメントを保持しておくのは問題ありません。
• 素の C では sqlite3_finalize を自分で呼ぶ必要があります。忘れると本当にバグになります。

経験則としては、prepare したら必ず誰かが finalize する、と覚えておきましょう。

自分で書かなくていい場面もある

実際のところ、sqlite3_prepare_v2 を直接呼ぶ機会はほとんどありません。高レベルなドライバは connection.execute("SELECT ... WHERE id = ?", (42,)) のような呼び出しを、内部で prepare/bind/step/finalize に展開してくれます。それでもライフサイクルを理解しておく価値があるのは、次のような理由からです:

• 「statement is busy」や「cannot operate on a finalized statement」といったエラーを見たときに、何が起きているのかピンとくるようになります。
• タイトなループで INSERT するときには、長生きするプリペアドステートメントをキャッシュすればいい、という判断ができるようになります。
• 文字列連結が一見ラクに見える場面でも、自然とパラメータ化クエリ(SQL インジェクション対策の基本)を書けるようになります。

ORM やクエリビルダはこれをさらに一歩進めて、SQL の組み立てからプリペアドステートメントの管理、型付きの結果返却までやってくれます。とはいえ中身を覗けば、やっていることは同じ 4 つの呼び出しの繰り返しです。

次回:バインド変数を使いこなす

ここまではプレースホルダについて抽象的に話してきました。次回はバインドする側を詳しく見ていきます。位置指定と名前付きパラメータの違い、型の扱い、NULL、そして実際のアプリデータをクエリに渡すときに引っかかりがちな細かい落とし穴まで取り上げます。