--- note: N+1・コネクションプール・長時間TxとSQLインジェクションを、境界で同時に防ぐ created: 2026-08-14T10:00:00+09:00 --- # 第11回｜アプリとの境界＋インジェクション > アプリと DB のあいだで起きる典型問題（N+1・プール・長時間トランザクション）をつぶし、SQL インジェクションを原理から確実に防ぐ。 ## この回のねらい これまでの回は「DB の中」の話だった。今回はアプリケーションと DB の**境界**に立つ。境界で起きる問題は SQL の書き方だけでは見えにくく、アプリ側のコード（とくに ORM の使い方）と密接に絡む。ここで扱うのは 性能を静かに殺す N+1 問題、接続を使い回すコネクションプール、長く開きっぱなしのトランザクションが 生む害、そして最も危険な脆弱性である SQL インジェクションである。最後の項目は「防ぐ」が目的であり、 文字列連結がなぜ危険かを原理から理解し、パラメータ化クエリで機械的に塞げるようになることを目指す。 第10回の最小権限とも接続し、「防いだうえで、万一破られても被害を小さくする」という多層防御の考え方を 身につける。 ## 到達目標 - N+1 問題を「発行 SQL の本数」で説明でき、実際にログから本数を数えて検出できる。 - N+1 を `JOIN`（または `WHERE ... = ANY($1)` のバッチ取得）で 1〜2 本に削減できる。 - コネクションプールがなぜ必要かを、PostgreSQL の接続コストの観点から説明できる。 - pgbouncer のセッションモードとトランザクションモードの違いと、それぞれの制約を説明できる。 - 「プールサイズを大きくすれば速い」が誤解である理由を、DB の並列度の観点から説明できる。 - 長時間トランザクションの害（ロック保持・VACUUM 阻害）と、リトライ／べき等性の必要を説明できる。 - ORM が発行する SQL をログで読み、必要なら生 SQL に落とせる。 - SQL インジェクションの原理を説明し、なぜエスケープ自作ではなくプレースホルダなのかを言える。 - パラメータ化クエリで確実に防ぎ、最小権限で被害範囲が縮むことを説明できる。 ## 前提と準備 第1回で構築した PostgreSQL 16 系のデータベースに、共通スキーマ（`customers` / `categories` / `products` / `product_prices` / `orders` / `order_items` / `events`）とデータが投入済みであることを 前提にする。今回はアプリ側のコードを擬似コードで示し、DB 側では `psql` で挙動を確認する。第10回で 作った読取専用ロール（例: `app_readonly`）が手元にあると、被害範囲の実験ができる。無ければ本編の 説明を読むだけでもよい。 発行 SQL を数えるために、セッション単位で全文をログに出す設定を使う。サーバの設定ファイルを触らず、 自分のセッションだけに効かせる。 ```sql SET log_statement = 'all'; -- このセッションの発行 SQL をサーバログに全て記録する ``` ORM を使う場合は、その ORM の SQL エコー機能（多くは「echo」「log」「debug」等のオプション）を 有効にすれば、同じことがアプリ側のログで観察できる。 ## この回で新しく出てくる用語 アプリ側の語が中心になる回なので、先に一覧で押さえる。 回をまたいで使う語は[用語集](glossary.md)にもまとめている。 | 用語 | 一行での説明 | |---|---| | ORM | オブジェクトとテーブルの行を対応づけ、SQLを書かずにDBを操作させるライブラリの総称 | | N+1 問題 | 一覧を1本引いたあと、その各行について1本ずつ追加のクエリを発行してしまうアンチパターン | | 遅延ロード / eager ロード | 関連データを、触れた時点で都度読むか、あらかじめまとめて読むか | | `= ANY(配列)` | 配列に含まれるどれかと一致するかを判定する述語。`IN (...)` を1つのパラメータで書ける形 | | コネクションプール | 確立済みの接続を保持して貸し借りし、接続確立コストを償却する仕組み | | べき等性 | 同じ操作を2回実行しても、結果が1回分にしかならない性質 | | SQLインジェクション | 入力がデータではなくSQLの構文として解釈されてしまう脆弱性 | | プレースホルダ | SQL文の骨組みと値を分けて渡すための、値の位置を表す記号（`$1`, `$2`, ...） | | 多層防御 | 単独では破られうる対策を重ね、破られた後の被害も小さくする考え方 | ## 本編 ### N+1 問題 ―― 発行 SQL の本数で捉える N+1 問題とは、「一覧を1本のクエリで取得したあと、その各行について追加のクエリを1本ずつ発行して しまう」アンチパターンである。一覧が N 件なら、一覧取得の 1 本＋各行の N 本で、合計 **N+1 本**の クエリが飛ぶ。1本1本は速くても、往復のたびにネットワーク遅延と接続の取り合いが積み上がり、件数に 比例して遅くなる。 共通スキーマで、「先頭 100 人の顧客について、それぞれの注文数を表示する」処理を考える。素朴に書くと 次の擬似コードになる。 ```text customers = query("SELECT id, email FROM customers ORDER BY id LIMIT 100") # ← 1 本 for c in customers: # ループの中で、顧客1人ごとに1本ずつクエリを発行してしまう row = query("SELECT count(*) FROM orders WHERE customer_id = " + c.id) # ← N 本 print(c.email, row.count) ``` この処理が発行する SQL は、一覧の 1 本＋ループ内の 100 本で **101 本**になる。`log_statement = 'all'` を有効にしてこの処理を流し、サーバログに現れる文の数を数えれば、はっきり 101 行が確認できる。 「1件ずつループする」という手続き的な発想（第2回で矯正した癖）が、そのまま発行 SQL の本数に化けて 現れているのが N+1 の正体である。 **解消法1: JOIN で 1 本にまとめる。** 「各顧客の注文数」は集約で一度に求められる。 ```sql SELECT c.id, c.email, count(o.id) AS order_count FROM (SELECT id, email FROM customers ORDER BY id LIMIT 100) c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.email ORDER BY c.id; ``` ```text id | email | order_count ----+---------------------+------------- 1 | user00001@example.jp | 23 2 | user00002@example.jp | 18 3 | user00003@example.jp | 0 ... (100 rows) ``` `LEFT JOIN` にしているのは、注文が 1 件もない顧客も 0 件として残すためである（`INNER JOIN` にすると 注文ゼロの顧客が消える）。`count(o.id)` は `NULL` を数えないので、注文のない顧客は自然に 0 になる （第2回の `COUNT(列名)` の挙動）。これで発行 SQL は **1 本**になった。 **解消法2: バッチ取得（`= ANY($1)`）で 2 本にまとめる。** 一覧を取ったあと、集めた ID の配列を1つの パラメータとして渡し、ループの N 本を1本にたたむ方法もある。JOIN が組みにくい ORM でも使いやすい。 ```sql -- 1本目: 顧客一覧を取る(アプリ側で id を配列 [1,2,...,100] に集める) SELECT id, email FROM customers ORDER BY id LIMIT 100; -- 2本目: 集めた id 配列を1つのパラメータ $1 として渡し、まとめて集計する SELECT customer_id, count(*) AS order_count FROM orders WHERE customer_id = ANY($1) -- $1 = bigint[] の配列パラメータ GROUP BY customer_id; ``` psql で挙動だけ確かめるなら、配列リテラルを直接置いて試せる。 ```sql SELECT customer_id, count(*) AS order_count FROM orders WHERE customer_id = ANY(ARRAY[1,2,3,4,5]::bigint[]) GROUP BY customer_id ORDER BY customer_id; ``` ```text customer_id | order_count -------------+------------- 1 | 23 2 | 18 4 | 31 5 | 7 (4 rows) ``` バッチ版では発行 SQL は **2 本**（一覧＋バッチ集計）になる。注文が 0 件の顧客はこの結果に現れない ので、アプリ側で「結果に無い顧客は 0 件」と補完する。`IN (...)` を文字列連結で組み立てるのではなく 配列パラメータ 1 個で渡すのが要点で、これは後述のインジェクション対策とも直結する。 `JOIN` とバッチ取得の使い分けは、必要な列がどれだけあるかで決めるとよい。関連行の中身（注文の 明細など）が必要なら `JOIN` か `= ANY($1)` で本体をまとめて引き、件数だけなら集約 1 本で足りる。 ### ORM の遅延ロードと eager ロード N+1 は ORM の**遅延ロード（lazy loading）**で無自覚に起きることが多い。関連（例: 顧客に紐づく注文） に初めてアクセスした瞬間、ORM が裏でクエリを 1 本発行する。これがループの中にあると、そのまま N+1 になる。 ```text customers = Customer.where(region: '関東').limit(100) # ← 1 本 for c in customers: print(c.orders.count) # c.orders に触れた瞬間、ORM が裏で1本発行 → N 本 ``` 対策は、関連をあらかじめまとめて読む **eager ロード**に切り替えることである。ORM によって名前は 違うが（`includes` / `joins` / `selectinload` / `JOIN FETCH` 等）、内部的には上の「JOIN で1本」か 「`= ANY($1)` でバッチ2本」のどちらかに落ちる。どちらに落ちているかは、ORM の SQL ログを見れば 分かる。ここで重要なのは、**ORM を信じて中身を見ないのではなく、発行された SQL を必ず読む**という 姿勢である。ORM が非効率な SQL を吐いていたら、その部分だけ生 SQL に落とすのも正しい判断になる。 実行計画の読み方は[第8回](08-explain.md)、遅いクエリの定点観測は[第12回](12-operations-monitoring.md)で扱う。 ### コネクションプール ―― なぜ必要か PostgreSQL は 1 接続につき 1 つの**バックエンドプロセス**を割り当てる（接続ごとに OS プロセスを fork するモデル）。そのため接続の確立には、TCP 接続・認証・プロセス生成・カタログ読み込みといった 無視できないコストがかかる。Web アプリのように「短いリクエストが大量に来る」用途で、リクエストの たびに接続を張っては捨てると、その確立コストを毎回払うことになる。 コネクションプールは、確立済みの接続を一定数プールしておき、リクエストが来たら貸し出し、終わったら 返却して**使い回す**仕組みである。これで接続確立コストを償却できる。プールにはアプリのプロセス内で 動くもの（ライブラリ型）と、独立プロセスとして DB の手前に立つもの（pgbouncer など）がある。 ```text client 1 ---+ client 2 ---+ client 3 ---+---> [ pgbouncer ] ---+---> PG backend 1 ... ---+ +---> PG backend 2 client N ---+ +---> PG backend 3 ``` 左がアプリ側のクライアント接続（数百）、右が DB 側に実際に張られるバックエンド（少数）である。 図の要点は、「数百のクライアント接続」を「少数のサーバ接続」に集約する点にある。アプリ側が何百 接続を張っていても、DB 側に実際に張られるバックエンドは十数〜数十に抑えられる。PostgreSQL の `max_connections`（既定 100）を超える接続要求を捌く前段としても、プールは実質的に必須になる。 ### pgbouncer のプーリングモード pgbouncer には貸し出しの粒度が異なる 3 つのモードがある。実務でよく使う 2 つを押さえる。 | モード | サーバ接続を貸す単位 | 使い回しの度合い | 主な制約 | |---|---|---|---| | セッション（session） | クライアント接続が切れるまで | 低い | クライアントと 1 対 1。セッション機能はそのまま使える | | トランザクション（transaction） | 1 トランザクションのあいだだけ | 高い | セッションに紐づく状態が次の Tx に引き継がれない | | ステートメント（statement） | 1 文ごと | 最も高い | 複数文トランザクション不可。特殊用途 | トランザクションモードは、トランザクションが終わるたびにサーバ接続をプールへ返す。同じサーバ接続を 多数のクライアントで細かく使い回せるので効率が高い。ただしサーバ接続がコロコロ替わるため、 **セッションに紐づく機能の前提が崩れる**。具体的には、セッションをまたぐサーバサイドのプリペアド ステートメント、`SET`（セッション変数）、advisory lock、`LISTEN`/`NOTIFY`、セッションローカルな一時 テーブルなどである。これらを使うアプリは、設定で無効化するか、セッションモードを選ぶ必要がある。 迷ったらセッションモードから始め、接続効率が問題になった段階でトランザクションモードを検討する、 という順序が安全である。 ### 「プールサイズを大きくすれば速い」は誤解 プールの同時接続数（アクティブに走らせる接続の上限）を上げれば上げるほど速くなる、というのは よくある誤解である。DB が同時に処理できる仕事量は、CPU コア数とディスク I/O で頭打ちになる。 コア数を超える数のクエリを同時に走らせても、実際には CPU の奪い合い（コンテキストスイッチ）や ロック競合、I/O の輻輳が増えるだけで、スループットはむしろ落ち、レイテンシは悪化する。 したがってプールの役割は「たくさん同時に流す」ことではなく、「同時に走る数を**適切な上限で頭打ちに する**」ことにある。上限を超えたリクエストはプールのキューで待たせた方が、全体としては速く捌ける。 出発点の目安としては、まず「コア数の数倍程度（例: 十数〜数十）」の小さめの値から始め、[第12回](12-operations-monitoring.md)の 定点観測でスループットとレイテンシを見ながら調整するのがよい。「大きいほど速い」ではなく「小さすぎ ても大きすぎても遅く、最適点がある」と捉える。 ### 長時間トランザクションの害とリトライ・べき等性 トランザクションを長く開きっぱなしにすると、境界で二重に害が出る。 第一に、**ロックを長く保持する**。更新した行のロックはコミット（またはロールバック）まで解放され ないので、その行を触りたい他のトランザクションを待たせ続ける。並行制御の詳細は[第9回](09-transactions.md)で 扱ったとおりである。 第二に、**VACUUM を阻害する**。PostgreSQL は MVCC のため、更新・削除された古い行（デッドタプル）を すぐには消さず、まだそれを見る可能性のあるトランザクションが 1 つでも生きているあいだは残す。 長時間走る（あるいは `idle in transaction` で放置された）トランザクションは、この「まだ見えている かもしれない下限」を過去に固定してしまい、VACUUM がデッドタプルを回収できなくなる。結果、テーブルと インデックスが肥大化（bloat）し、全体が遅くなる。`orders` のように `status` が頻繁に更新される テーブルではデッドタプルが出やすく、影響が大きい。 対策の基本は「トランザクションを短く保つ」ことに尽きる。とくに**トランザクションの中で外部 API 呼び出しなどの待ちを含めない**（HTTP の応答を待つあいだ Tx を開いたままにしない）。放置対策として `idle_in_transaction_session_timeout` を設定し、開きっぱなしを強制的に切る手もある。 短く保つと今度は、直列化失敗（`40001`）やデッドロック（`40P01`）でトランザクションが失敗する頻度が 上がる（第9回）。これらは「もう一度やり直せば通る」種類の失敗なので、アプリ側で**リトライ**する。 リトライを安全にする前提が**べき等性（idempotency）**である。同じ操作を2回実行しても結果が 1回分にしかならないよう設計しておかないと、リトライで二重登録・二重課金が起きる。 ```sql -- べき等な登録の例: 一意キー衝突を握りつぶす(2回流しても1行のまま) INSERT INTO customers (email, region) VALUES ('idem@example.jp', '関東') ON CONFLICT (email) DO NOTHING; ``` `email` の `UNIQUE` 制約と `ON CONFLICT DO NOTHING` の組み合わせで、同じ登録が2回来ても行は 増えない。「リトライ前提でトランザクションは短く、操作はべき等に」がアプリ境界の設計指針になる。 ### SQL インジェクション ―― 文字列連結がなぜ危険か SQL インジェクションは、利用者が入力した文字列を SQL 文に**文字列連結**で埋め込むことで、入力の 一部が「データ」ではなく「SQL の構文」として解釈されてしまう脆弱性である。原理を安全に再現する。 顧客をメールアドレスで1件引く処理を、文字列連結で組み立てたとする。 ```text # 危険: 入力をそのまま SQL 文の中に連結している(擬似コード) sql = "SELECT id, email FROM customers WHERE email = '" + input + "'" ``` `input` が普通のメールアドレス `alice@example.jp` なら、組み上がる SQL は無害である。 ```sql SELECT id, email FROM customers WHERE email = 'alice@example.jp'; ``` ところが `input` に `' OR '1'='1`（先頭に閉じ引用符、常に真の条件）を渡すと、組み上がる SQL は 次のように変わる。 ```sql SELECT id, email FROM customers WHERE email = '' OR '1'='1'; ``` `WHERE` が常に真になり、**全顧客が返る**。認証や絞り込みを回避して、本来見えないはずのデータが 一括で漏れる。psql で「組み上がってしまった SQL」をそのまま流すと、害が再現できる（＝アプリの バグを DB 側で見ている）。 ```sql SELECT id, email FROM customers WHERE email = '' OR '1'='1'; ``` ```text id | email ----+---------------------- 1 | user00001@example.jp 2 | user00002@example.jp ... (50000 rows) ``` もう一つの典型が、コメント記号 `--` で以降を無効化する手口である。ログイン照合が `WHERE email = '<入力>' AND password = '<入力>'` のような形だと、メール欄に `admin@example.jp'; --` を入れられると、`--` 以降（`AND password = ...`）がコメント扱いになり、パスワード照合が消える。 条件が骨抜きにされる、という点は `' OR '1'='1` と同じ構造である。要は、**入力がクエリの構文を 書き換えられる**ことがすべての害の根っこにある。 ### 対策 ―― パラメータ化クエリ（プレースホルダ）で塞ぐ 正しい対策は、**SQL 文の骨組みと、値とを、別々に DB へ渡す**ことである。プレースホルダ（PostgreSQL では `$1`, `$2`, ...）を置いた SQL 文を先に「これはこういう構文だ」と解析（parse）させ、値は後から 「この位置にこの値を入れる」と束縛（bind）する。値がどんな文字列でも、それは常に**リテラル値** として扱われ、二度と SQL の構文として解釈されない。 psql でこの分離を体験するには `PREPARE`／`EXECUTE` を使う（ドライバが内部でやっているのと同じ ことを手で行う）。 ```sql -- 骨組みを先に用意する。email = $1 の $1 は「値が入る位置」 PREPARE lookup(text) AS SELECT id, email FROM customers WHERE email = $1; -- 値を束縛して実行する。普通のメールなら該当行が返る EXECUTE lookup('alice@example.jp'); -- 攻撃文字列 ' OR '1'='1 を「値」として渡す -- (psql のリテラル記法上、文中の ' は '' と書く。これはエスケープ対策ではなく -- 単にリテラルの書き方。ドライバなら生の文字列をそのまま渡すだけでよい) EXECUTE lookup(''' OR ''1''=''1'); ``` ```text id | email ----+------- (0 rows) ``` `' OR '1'='1` は `email` 列と**リテラルとして**突き合わされ、そんなメールアドレスの顧客はいないので 0 行になる。全件が漏れることはない。骨組み（`WHERE email = $1`）は最初に確定していて、あとから 渡す値がそれを書き換える余地がないからである。アプリ側では、ドライバのパラメータ渡し（`$1` に 入力をバインド）を使うだけで、この分離が常に働く。 ```text # 安全: SQL 文と値を分けて渡す(擬似コード)。値は連結しない db.execute("SELECT id, email FROM customers WHERE email = $1", [input]) ``` **なぜ「エスケープ自作」ではなくプレースホルダなのか。** 入力中の `'` を `''` に置換する等の エスケープを自前で書いて防ごうとするのは、確実ではない。エスケープの規則は文字列リテラル・識別子・ `LIKE` パターン・数値などで異なり、文字エンコーディングによっては別の抜け道も生まれる。どれか一箇所 でも処理を忘れたり誤ったりすれば穴が開く。プレースホルダは、そもそも値を SQL 構文として**解析させ ない**ため、エスケープの正しさに依存しない。責任をドライバと DB のプロトコルに寄せるのが、確実で かつ楽な道である。「エスケープで頑張る」ではなく「値は必ずプレースホルダ」と機械的に決めておく。 ### 「ORM なら安全」という誤解と、生 SQL の穴 多くの ORM は、値を渡すと既定でパラメータ化してくれる。だが安全なのは「ORM だから」ではなく 「パラメータ化しているから」である。ORM を使っていても、次のような場面で穴が開く。 - **生 SQL に文字列連結する。** `db.execute("... WHERE email = '" + input + "'")` のように、ORM の raw クエリ機能へ連結した文字列を渡せば、素の文字列連結と同じ脆弱性になる。生 SQL でもプレース ホルダを使う。 - **値ではない部分をユーザー入力で組み立てる。** 列名・テーブル名・`ORDER BY` の対象列・`ASC/DESC` などは、プレースホルダで束縛できない（`$1` は値の位置にしか置けない）。これらをユーザー入力から 作ると連結せざるを得ず、穴になる。対策は**許可リスト（allowlist）**である。受け取った列名を、 あらかじめ決めた候補集合と突き合わせ、一致したものだけを使う。 ```text # 危険: 並び替え列をユーザー入力から連結している sql = "SELECT id, email FROM customers ORDER BY " + sort_col # 安全: 許可リストで受理してから使う。任意の文字列は通さない allowed = {"id", "email", "created_at"} if sort_col not in allowed: raise Error("invalid sort column") sql = "SELECT id, email FROM customers ORDER BY " + sort_col ``` `IN (...)` を作りたいときも、要素を文字列連結でつなぐのではなく、前節の `= ANY($1)` に配列 パラメータ 1 個を渡す形にすれば、値は常にパラメータ化される。N+1 のバッチ取得とインジェクション 対策が、ここで同じ技法に収束する。 ### 最小権限との合わせ技 ―― 万一破られても被害を小さく パラメータ化は「入れさせない」防御である。これに第10回の**最小権限**を重ねると、「万一入られても、 できることを小さく抑える」防御が加わる。多層防御（defense in depth）である。 アプリが第10回で作った読取専用ロール（例: `app_readonly`）で接続していれば、仮にインジェクションで 更新文が通ってしまっても、権限がなければその文自体が失敗する。 ```sql -- app_readonly で接続している状態で、注入された(想定の)書き換えを試す UPDATE orders SET status = 'cancelled' WHERE id = 1; ``` ```text ERROR: permission denied for table orders ``` 同様に、アプリのロールに `admin_secrets` のような機微テーブルへの `SELECT` 権限を与えていなければ、 注入された `SELECT` でもそのテーブルは読めない。RLS（行レベルセキュリティ）を併用すれば、読める 行そのものをロールやセッション変数で絞れる。詳細は[第10回](10-roles-rls-audit.md)を参照。 要点は、片方に頼らないことである。最小権限だけでは、読取専用ロールでも「読めるデータの全件漏洩」は 防げない。パラメータ化だけでは、実装のどこか1箇所の連結ミスで破られうる。**パラメータ化で入口を 塞ぎ（予防）、最小権限で被害範囲を絞る（低減）**の両輪で守る。 ## ハンズオン ### 課題1: N+1 の発行 SQL 本数を数え、JOIN／バッチ取得で削減する **やること** 1. 「先頭 100 人の顧客の注文数を表示する」処理を、N+1 になる擬似コードとして書く。 2. その処理が発行する SQL の本数を、`log_statement = 'all'`（または ORM の SQL ログ）で数える。 3. 同じ結果を `JOIN` 版（1 本）と `= ANY($1)` バッチ版（2 本）で書き、本数が減ることを確認する。 **想定解答** N+1 版の擬似コードと本数は本編のとおりで、一覧 1 本＋ループ 100 本＝**101 本**である。ログを数える と、`SELECT id, email FROM customers ...` が 1 行と、`SELECT count(*) FROM orders WHERE customer_id = ...` が 100 行、合わせて 101 行が並ぶ。 JOIN 版は 1 本で同じ表を得る。 ```sql SELECT c.id, c.email, count(o.id) AS order_count FROM (SELECT id, email FROM customers ORDER BY id LIMIT 100) c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.email ORDER BY c.id; ``` ```text id | email | order_count ----+----------------------+------------- 1 | user00001@example.jp | 23 2 | user00002@example.jp | 18 3 | user00003@example.jp | 0 ... (100 rows) ``` バッチ版は 2 本（一覧を取り、集めた ID 配列を `= ANY($1)` に渡す）。psql では配列リテラルで確認できる。 ```sql SELECT customer_id, count(*) AS order_count FROM orders WHERE customer_id = ANY(ARRAY(SELECT id FROM customers ORDER BY id LIMIT 100)) GROUP BY customer_id ORDER BY customer_id; ``` 発行 SQL は 101 本 → 1〜2 本に減った。往復回数が件数に比例して増える構造を、集約 1 回に畳んだのが 効いている。行数が同じでも「何本のクエリに分かれているか」で性能が大きく変わる、という感覚を持つ。 ### 課題2: 脆弱クエリに注入を通し、パラメータ化で塞ぎ、最小権限で被害を縮める **やること** 1. 文字列連結で組んだ想定の SQL に `' OR '1'='1` を注入し、全件が返ることを確認する。 2. 同じ検索を `PREPARE`／`EXECUTE`（プレースホルダ）に置き換え、注入文字列が値として扱われ 0 行に なることを確認する。 3. 読取専用ロールで接続し、注入で更新文が通っても権限で失敗することを確認する。 **想定解答** まず「組み上がってしまった SQL」を流して害を再現する。これはアプリのバグを DB 側で見ている状態である。 ```sql -- 脆弱: 入力 ' OR '1'='1 が連結された結果 SELECT id, email FROM customers WHERE email = '' OR '1'='1'; ``` ```text id | email ----+---------------------- 1 | user00001@example.jp ... (50000 rows) ``` 全件が返る。次に、同じ検索をプレースホルダに置き換える。 ```sql PREPARE lookup(text) AS SELECT id, email FROM customers WHERE email = $1; EXECUTE lookup(''' OR ''1''=''1'); -- 攻撃文字列を「値」として渡す ``` ```text id | email ----+------- (0 rows) ``` `' OR '1'='1` は `email` とリテラル比較され、該当なしの 0 行になる。骨組みが先に確定していて値が構文を 書き換えられない、というのがこの差の理由である。 最後に、最小権限の効果を見る。読取専用ロールで接続し、注入で通ったと仮定した更新を試す。 ```bash psql -d postshop -U app_readonly -c "UPDATE orders SET status = 'cancelled' WHERE id = 1;" ``` ```text ERROR: permission denied for table orders ``` パラメータ化で入口を塞ぎ、さらに最小権限で「万一通っても書き換えは不可」という二段構えができている。 どちらか一方ではなく、両方をかけることで守りが厚くなる。 ## よくあるつまずきと対処 | 症状・誤解 | 原因 | 対処 | |---|---|---| | 一覧表示が件数に比例して遅い | ループの中で1件ずつクエリを発行する N+1 | 発行 SQL をログで数え、`JOIN` か `= ANY($1)` で 1〜2 本に畳む | | 入力の `'` を自前でエスケープして防ごうとする | エスケープ規則は文脈・エンコーディング依存で、どこか1箇所の漏れで破られる | 値は必ずプレースホルダ（`$1`）で渡し、SQL 構文として解析させない | | 「ORM を使っているから安全」と考える | 安全は ORM ではなくパラメータ化に由来。生 SQL 連結や列名の連結で穴が開く | 生 SQL でもプレースホルダ。列名・`ORDER BY` は許可リストで受理する | | プールサイズを上げれば速くなると思う | DB の並列度は CPU・I/O で頭打ち。増やすと競合でむしろ遅くなる | 小さめの上限から始め、定点観測で最適点を探す（第12回） | | トランザクションモードでプリペアドや `SET` が効かない | サーバ接続が Tx ごとに替わり、セッション状態が引き継がれない | セッションモードにするか、当該機能を無効化して使う | | バッチ取得で注文 0 件の顧客が結果に出ない | `= ANY($1)` の集計は該当行のない顧客を返さない | アプリ側で「結果に無い顧客は 0 件」と補完する。`LEFT JOIN` 版なら DB 側で 0 が出る | | 更新が突然ブロックされ、テーブルが肥大化する | 長時間・`idle in transaction` の Tx がロック保持と VACUUM 阻害を起こす | Tx を短く保ち、外部呼び出しを含めない。`idle_in_transaction_session_timeout` で切る | ## 到達度チェックリスト - [ ] N+1 問題を「一覧 1 本＋各行 N 本＝ N+1 本」で説明でき、ログから本数を数えられる。 - [ ] 同じ集計を `JOIN`（1 本）と `= ANY($1)` バッチ（2 本）の両方で書ける。 - [ ] ORM の遅延ロードが N+1 を生む仕組みを説明し、eager ロードや生 SQL への落とし方を言える。 - [ ] コネクションプールが必要な理由を、PostgreSQL の接続コストの観点から説明できる。 - [ ] pgbouncer のセッションモードとトランザクションモードの違いと制約を説明できる。 - [ ] 「プールサイズは大きいほど速い」が誤りである理由を、DB の並列度で説明できる。 - [ ] 長時間トランザクションがロック保持と VACUUM 阻害を起こすこと、リトライにべき等性が要ることを説明できる。 - [ ] SQL インジェクションの原理を説明し、なぜエスケープ自作ではなくプレースホルダなのかを言える。 - [ ] パラメータ化と最小権限を組み合わせた多層防御を説明できる。 ## 宿題（次回までの自習） 1. `products` と `order_items` で「商品ごとの販売数量合計」を、まず N+1 の擬似コードで書き（商品を ループして数量を1本ずつ合計する）、次に `JOIN` + `GROUP BY` の 1 本に書き換える。`log_statement` で発行本数が減ることを確認する。 2. `orders` を `status` でメールアドレスから検索する処理を、文字列連結版と `PREPARE`／`EXECUTE` 版の 両方で書き、`' OR '1'='1` を渡したときの結果の差（全件 vs 0 行）を自分の目で確かめる。 3. 第10回の読取専用ロールで接続し、`INSERT`／`UPDATE`／`DELETE` がそれぞれ `permission denied` に なることを確認する。どの操作がどの権限で止まるかを表にまとめる。 ## 次回への接続 今回はアプリ境界の性能問題とセキュリティを扱った。発行 SQL の本数・接続数・トランザクション長・ 権限は、いずれも「動いてはいるが、運用で効いてくる」指標である。[第12回](12-operations-monitoring.md)では `pg_stat_statements` を使って、遅いクエリ・呼び出し回数・累積時間を定点観測し、N+1 や長時間 Tx を 本番のデータから見つけ出す方法に進む。今回の「本数で捉える」感覚が、そのまま監視の読み方につながる。 また、長時間 Tx が阻害する VACUUM とテーブル肥大化の運用対処も第12回で扱う。 ## 参考 - PostgreSQL 公式ドキュメント: "Server Programming" > "PREPARE"（プリペアドステートメントの構文） - PostgreSQL 公式ドキュメント: "The SQL Language" > "Data Manipulation" > "INSERT ... ON CONFLICT"（べき等な登録） - PostgreSQL 公式ドキュメント: "Server Administration" > "Routine Vacuuming"（VACUUM とデッドタプル回収） - PostgreSQL 公式ドキュメント: "Client Interfaces" > "libpq" > "Command Execution Functions"（パラメータ付きクエリの送り方） - pgbouncer 公式ドキュメント（プーリングモード: session / transaction / statement の解説） - OWASP: "SQL Injection" および "SQL Injection Prevention Cheat Sheet"（原理と防御の一次資料） - 各 ORM の SQL ログ設定（SQLAlchemy の `echo`、ActiveRecord のログ、Prisma の `log` 等、使用 ORM の該当項）