インデックス・トランザクション・SQLインジェクション対策
この章の目次開く
基本的なSQLを書けるようになったら、次に必要なのは「速く、安全に、壊さずに使う」ための知識です。 遅いクエリ、不完全な更新、SQLインジェクションは、実務で大きな障害や情報漏洩につながります。
この章では、インデックス、トランザクション、SQLインジェクション対策をまとめて扱います。
学習者SQLが動くことは確認したけど、遅いかどうか、安全かどうかってどう判断するの？
インデックスとは
インデックスは、テーブルから目的の行を探しやすくするための仕組みです。 本の索引のように、全ページを先頭から読む代わりに、目的の項目へ素早くたどり着けます。
CREATE INDEX idx_users_email
ON users (email);このインデックスがあると、次のような検索が速くなりやすいです。
SELECT id, name
FROM users
WHERE email = 'alice@example.com';インデックスが効きやすい場面
インデックスは、次のような列に向いています。
WHEREでよく使う列JOINの条件に使う列ORDER BYでよく使う列UNIQUE制約を付けたい列
CREATE INDEX idx_orders_user_id
ON orders (user_id);SELECT id, ordered_at
FROM orders
WHERE user_id = 1
ORDER BY ordered_at DESC;複数列を組み合わせた複合インデックスもあります。
CREATE INDEX idx_orders_user_id_ordered_at
ON orders (user_id, ordered_at);複合インデックスでは、列の順番が重要です。
(user_id, ordered_at) のインデックスは、user_id で絞って ordered_at で並べるようなクエリに向いています。
インデックスを貼りすぎない
インデックスは検索を速くしますが、無料ではありません。
インデックスには次のコストがあります。
- ディスク容量を使う
INSERT、UPDATE、DELETEが遅くなる- 不要なインデックスが増えると管理が難しくなる
データを更新するたびに、インデックス側も更新する必要があるためです。
EXPLAINで実行計画を見る
EXPLAIN を使うと、DBMSがどのようにクエリを実行しようとしているかを確認できます。
EXPLAIN
SELECT id, name
FROM users
WHERE email = 'alice@example.com';DBMSによって表示形式は違いますが、主に次の点を見ます。
| 観点 | 見ること |
|---|---|
| テーブルの読み方 | 全件走査か、インデックスを使っているか |
| 想定行数 | どのくらいの行を読む見込みか |
| JOIN順序 | どのテーブルから処理するか |
| ソート | 追加の並び替えが発生しているか |
EXPLAIN で実行計画を確認します。
学習者インデックスを貼ればとにかく速くなるの？全部の列に貼ったほうがいい？
先生インデックスは検索を速くするけど、INSERT・UPDATE・DELETEが遅くなる。まずEXPLAINで遅いクエリを特定してから、必要な列にだけ貼るのが正解だよ。
トランザクションとは
トランザクションは、複数のSQLをひとまとまりの処理として扱う仕組みです。 すべて成功したら確定し、途中で失敗したら元に戻せます。
代表例は、ポイント送金です。
BEGIN;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
COMMIT;途中で失敗した場合は ROLLBACK します。
BEGIN;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
-- ここで何か問題が起きた
ROLLBACK;この場合、最初の更新も取り消されます。
COMMITとROLLBACK
| 命令 | 意味 |
|---|---|
BEGIN | トランザクションを開始する |
COMMIT | 変更を確定する |
ROLLBACK | 変更を取り消す |
トランザクションが必要な場面は、複数の変更がセットで意味を持つときです。
- 注文作成と在庫減算
- 送金元の減額と送金先の増額
- ユーザー作成とプロフィール作成
- 複数テーブルにまたがるステータス更新
ACID
トランザクションの性質はACIDとして説明されます。
| 性質 | 意味 |
|---|---|
| Atomicity | 原子性。全部成功か全部失敗 |
| Consistency | 一貫性。制約を満たす状態を保つ |
| Isolation | 独立性。同時実行の影響を制御する |
| Durability | 永続性。確定した変更は失われない |
最初からすべてを厳密に覚える必要はありません。 まずは「途中で失敗したときに中途半端な状態を残さないための仕組み」と理解すると使いやすいです。
ロックと同時更新
複数の処理が同じ行を同時に更新しようとすると、競合が起きます。 DBMSはロックを使って、同時更新による不整合を防ぎます。
たとえば在庫を減らす処理では、同時に注文が入ると在庫数がずれる可能性があります。
BEGIN;
SELECT stock
FROM products
WHERE id = 1
FOR UPDATE;
UPDATE products
SET stock = stock - 1
WHERE id = 1;
COMMIT;FOR UPDATE は、対象行を更新用にロックするために使われます。
対応や書き方はDBMSによって違うため、実務では利用DBMSの仕様を確認します。
学習者ORMを使っていればSQLインジェクションは気にしなくていい？
先生ORMでも生SQLを文字列連結で書いたら危険。ORMのクエリビルダやプレースホルダ機能をちゃんと使うことが大事だよ。
SQLインジェクションとは
SQLインジェクションは、ユーザー入力をSQL文字列に直接埋め込むことで、意図しないSQLを実行される脆弱性です。
// 危険な例
const sql = `SELECT * FROM users WHERE email = '${email}'`;ユーザーが次のような値を入力すると、SQLの構造が壊されます。
' OR '1' = '1結果として、条件が常に真になり、全ユーザーが取得される可能性があります。
プレースホルダを使う
SQLインジェクション対策の基本は、プレースホルダやパラメータバインドを使うことです。
// 例: パラメータ化クエリ
const sql = 'SELECT id, name FROM users WHERE email = ?';
const params = [email];SQL本文と値を分けて渡すことで、入力値はSQL構文ではなく「値」として扱われます。
ユーザー入力をSQL文字列に直接連結せず、必ずプレースホルダで渡します。ORMやクエリビルダを使っていても、文字列連結でSQLを組み立てれば危険です。
// 危険: ORMを使っていても生SQLを連結している
const users = await db.query(
`SELECT * FROM users WHERE name LIKE '%${keyword}%'`
);プレースホルダを使います。
const users = await db.query(
'SELECT * FROM users WHERE name LIKE ?',
[`%${keyword}%`]
);よくあるハマりどころ
インデックスを貼ったのに速くならない
インデックスを貼っても、クエリ条件やデータ分布によっては使われないことがあります。
- 条件に関数をかけている
- 前方一致ではない
LIKE '%keyword%'を使っている - 複合インデックスの左側の列を使っていない
- そもそも対象行が多すぎる
EXPLAIN で確認し、実際に使われているかを見る必要があります。
トランザクション内で外部APIを呼ぶ
トランザクション中に外部APIを呼ぶと、API応答待ちの間ロックを持ち続けることがあります。 DB更新に必要な処理だけをトランザクション内に入れ、外部通信は前後に分ける設計を検討します。
エスケープすれば十分だと思う
SQLインジェクション対策は、手作業のエスケープに頼るべきではありません。 DBドライバやORMが提供するパラメータ化の仕組みを使うのが基本です。
ちゃんと使うためのポイント
- インデックスは検索やJOINを速くするが、更新コストもある
- 遅いSQLは
EXPLAINで実行計画を見る - 複数更新を一体として扱う処理にはトランザクションを使う
COMMITは確定、ROLLBACKは取り消し- ロックは必要だが、長く持たない
- SQLインジェクション対策では、文字列連結ではなくプレースホルダを使う
ここまでで、SQLの基本構文、JOIN、集計、サブクエリ、設計、実務上の安全性までを一通り見ました。
クイズに戻るときは、まず SELECT、WHERE、JOIN、GROUP BY、NULL、インデックス、トランザクションの違いを意識して解いてみましょう。
