SQLの構造とデータを分ける
検索欄へ入った文字列を、SQL文の一部として連結すると何が起きるでしょうか。ここではメモリ内のSQLiteへ架空の文書を二件だけ作り、検索語がデータのままで扱われる場合と、式の一部になる場合を比較します。実データ、外部のDB、既存サービスは使いません。実験が終わるとDBも破棄されます。Webセキュリティの総論を先に読み、入力がどこで命令として解釈されるかという視点を持って始めてください。
目的は、悪い文字をいくつか消す規則を作ることではありません。SQLの構造をプログラム側で決め、検索語などの値を、DBドライバの束縛機能へ別に渡す境界を確かめます。Python 3.12のsqlite3を使うため、プレースホルダは?です。別のドライバが同じ記法を使うとは限りません。ここでの実行確認をPostgreSQLの実験や、既存のDB教材の接続試験として扱わないでください。
実行前に三つの検索結果を予測する
文書1は所有者A、題名alpha、文書2は所有者B、題名betaとします。Aの文書を題名で探すSQLへ、引用符を閉じてOR 1=1を続け、残りをコメントにする文字列を連結すると、検索条件の意味が変わります。コードのleakedには二件が返ると予測してください。ここでのSQLは、実験がこの条件を再現するために意図的に連結しているものです。
同じ文字列を?の値として渡せば、題名そのものがその文字列と等しいかを検索します。そのような題名の文書はないので結果は空です。通常のalphaを束縛すれば文書1だけが返ります。この三つを、連結版の二件、同じ入力の束縛版のゼロ件、正常入力の一件として先に記録します。すべてゼロ件にするだけの実装では、正しい検索まで壊してしまうため、正常な入力も必ず確認します。
コードを実行し結果と版を保存する
次のコードをquery_boundary.pyへ保存し、python3.12 query_boundary.pyで実行します。追加ライブラリは不要です。テーブルの作成と初期データの登録も含んでいます。版の出力と三つの検索結果、最後の成功表示を保存してください。実測ではPython 3.12.14、SQLite 3.53.1で全assertが成功し、連結版は[(1,), (2,)]、束縛版は[]でした。
入力値を束縛するときは、値を一つでもタプルなどの引数列として渡します。コードの(owner,)の末尾のカンマは一要素タプルを作るためのものです。値を文字列の整形で埋めたあとにexecuteを呼んでも、束縛へ変わったことにはなりません。使うAPIへSQL文字列と値を別々に渡している箇所を、実行結果と合わせて指させるようにしてください。
import sqlite3
with sqlite3.connect(':memory:') as db:
db.execute('CREATE TABLE documents (id INTEGER PRIMARY KEY, owner TEXT, title TEXT)')
db.executemany('INSERT INTO documents VALUES (?, ?, ?)', [(1, 'A', 'alpha'), (2, 'B', 'beta')])
value = "' OR 1=1 --"
unsafe = "SELECT id FROM documents WHERE owner = 'A' AND title = '" + value + "'"
leaked = db.execute(unsafe).fetchall()
assert leaked == [(1,), (2,)]
bound = db.execute('SELECT id FROM documents WHERE owner = ? AND title = ?', ('A', value)).fetchall()
assert bound == []
assert db.execute('SELECT id FROM documents WHERE owner = ? AND title = ?', ('A', 'alpha')).fetchall() == [(1,)]
assert db.execute('SELECT id FROM documents WHERE owner = ? AND title = ?', ('B', 'beta')).fetchall() == [(2,)]
# Binding a caller-supplied owner does not authenticate or authorize that owner.
order_columns = {'title': 'title COLLATE BINARY', 'id': 'id'}
def list_for_owner(owner, order):
if order not in order_columns:
raise ValueError('unknown sort key')
return db.execute('SELECT id FROM documents WHERE owner = ? ORDER BY ' + order_columns[order], (owner,)).fetchall()
assert list_for_owner('A', 'title') == [(1,)]
try:
list_for_owner('A', 'id; DROP TABLE documents')
except ValueError:
pass
else:
raise AssertionError('unknown identifier accepted')
assert db.execute('SELECT count(*) FROM documents').fetchone()[0] == 2
print('SQLite', sqlite3.sqlite_version)
print('concatenated:', leaked, 'bound:', bound)
print('value binding and identifier allowlist passed')列名には値の束縛と別の規則がいる
並び替えに使う列名を利用者が選べる場合も考えます。値用の?へtitleという文字列を渡すことは、任意の列識別子を安全に指定する一般的な仕組みではありません。コードでは外から受け取るキーをtitleまたはidへ限定し、それぞれに対応するSQLの断片をプログラム内で固定します。列名の文字列をそのまま通すのではなく、許可リストから選びます。
不明なキーを与える試験はValueErrorで止まり、その後もテーブルに二件あることを確認します。拒否を確認する前に任意のSQLを実行する設計にはしません。実際の機能で昇順・降順や集計方法も選べるなら、それらにも同様に有限の選択肢を設けます。許可リストは、入力へ含まれる文字をざっと検査する規則より、どの構造を作れるかを読みやすく制限できます。選べる列が増えたときも、変更箇所をレビューで追いやすくなります。
パラメータ化しても認可の責任は残る
コードには、所有者Bと題名betaを正常に束縛すれば文書2が返るassertもあります。これは、値の束縛が利用者の本人確認や所有者の権限を検証する仕組みではないことを示します。実際のAPIで要求のownerをそのまま信じれば、SQLの構造を壊さなくても他人の文書を選べてしまう場合があります。認証された主体から、許可した範囲をサーバ側で決める必要があります。
また、束縛は機密情報をログへ残してよい根拠にはなりません。検索語、トークン、個人情報などを扱う場合は、エラーログや監視へ出す範囲も設計します。トランザクションの境界、取得件数の制限、タイムアウトも、SQLの構造と値を分けることとは別の責任です。この実験が示した一つの境界を、データアクセス全体の安全性が完成したという結論へ広げないでください。
正常な識別子の選択も実験へ残す
未知の並び替えキーを拒否したことに加え、titleという許可したキーが正常に使えたことも確認します。許可リストを空にして全入力を拒否しただけでは、要件を満たしません。複数の文書を追加する場合は、同じ所有者に異なる題名を与え、並び順の期待値を先に決めると、キーが本当に列の選択へ対応しているかをさらに確かめられます。
課題と解答で境界を言い直す
課題として、引用符を含む通常の題名を追加し、値の束縛で検索してください。解答では、引用符を手で二重にしたり除いたりせず、元の文字列をそのまま値として渡します。SQLの構造へ埋め込まないため、通常のデータとして扱えます。追加した行の所有者も、意図した絞込みの範囲に含めたことを確認します。
次に、許可リストへcreatedというキーを追加するなら、テーブルの列、固定するSQL断片、試験の期待値をどこで変えるか考えます。単に利用者の入力を通す変更にはしません。最後に、Aの利用者がowner=Bと送っても他人の文書を見られないことを、今回の束縛試験だけで証明できるか答えます。解答はできません。別途、認証と認可を通した要求の試験が必要です。記録には成功結果だけでなく、この実験が検証していない境界も具体的に一つ残してください。