データベース操作と権限の制限
呼出しAPIの性質、生成SQL、DB側の権限がそれぞれどこまで保護するか説明します。
説明
安全なDB利用には、値の束縛に加えて、実行できる操作や読める項目の制限も必要です。ORMや共通関数を使っていても、生SQLを文字列連結すれば入力の解釈はDBへ届きます。
SQLiteの実行APIで単一文と複数文の扱いを比較し、読取り用ビューを使います。最後にauthorizerを設定して、給与の参照や更新をDBの実行段階で拒否し、元データを照合します。
呼出しAPIの性質、生成SQL、DB側の権限がそれぞれどこまで保護するか説明します。
1一覧向けのビューを取得する
directoryは給与列を含まないビューです。元表には給与が保存されています。
from sd02 import *
db = database()
print(db.execute("SELECT * FROM directory一覧用の項目だけを取得する ORDER BY id").fetchall())
print(db.execute("SELECT id,salary FROM users元表へのアクセスも確認する ORDER BY id").fetchall())返却項目を限定したビューでも、同じ接続が元表へ自由にアクセスできれば給与も読めます。
2共通関数が作るSQLと値を確認する
ORMや問合せビルダーを使う場合も、最終的に値が束縛されているか確認します。ここではその実行境界をDB-APIで示します。
generated =SQLと束縛値を別に保持する ("SELECT id,name FROM users WHERE department=? ORDER BY id",("red",))
print(generated)
print(db.execute(*generated)構造と値を分けてDBへ渡す.fetchall())SQLと引数が分かれ、redの2行だけが返ります。APIの名前ではなく、実行するSQLと値の渡し方を確認します。
3executeの単一文制限を確認する
SQLiteのPython executeは一度に一文を実行するAPIです。二文を渡した場合の実際のエラーを確認します。
print(run(db,"SELECT 1; SELECT 2")一度に二文を渡す)一文だけ実行できるというエラーです。この制限があっても、一つのSELECT内のORなどで範囲が変わる問題は防げません。
4一文の中で変わる条件を確認する
複数文ではなく、一つのSELECTへ条件を混ぜた場合です。
print(db.execute(unsafe(連結で条件が変わる一文を実行する"' OR 1=1 -- ")).fetchall())
print(safe(db,束縛版と比較する"' OR 1=1 -- "))連結版は全件、束縛版は0件です。単一文のAPIを使うだけでSQL注入を防げるわけではありません。
5接続へauthorizerを設定する
この接続では更新・追加・削除・表削除と給与列の参照を拒否します。SQLiteのコールバックが文の準備段階で操作を確認します。
db.set_authorizer(protect)DBの実行段階に制限を設定する
print(db.execute("SELECT * FROM directory ORDER BY id").fetchall())許可した一覧の参照は続けられます。authorizerはこの接続に設定するため、別接続や既存設定を含む運用全体で適用箇所を管理します。
6給与参照と更新を試す
権限のない列と書込みをそれぞれ試します。
print(run(db,"SELECT salary FROM users制限した列を参照する"))
print(run(db,"UPDATE users SET禁止した更新を要求する name='changed' WHERE id=1"))
print(run(db,"DELETE FROM users禁止した削除を要求する WHERE id=2"))参照・更新・削除が拒否されます。入力の束縛が正しくても、不要な権限を持たせないことには別の効果があります。
7拒否後の保存内容を確認する
一覧の行が保持されていることを読み、検証用に制限を外して給与も照合します。
print(db.execute("SELECT * FROM directory拒否後の一覧を確認する ORDER BY id").fetchall())
db.set_authorizer(None)検証用に元表を照合できる状態へ戻す
print(db.execute("SELECT id,name,salary元データが保持されたことを確認する FROM users ORDER BY id").fetchall())名前・行数・給与は元の3人分のままです。制限を外すのは検証の照合用で、読取り用の通常処理へ権限解除を組み込む意図ではありません。
まとめ
生成SQLとパラメーターを実行境界で確認します。
単一文制限だけでは、一文内のSQL注入を防げません。
必要な読取り・書込みの範囲へDB権限も絞ります。
操作履歴と解説を振り返れます。
Python 3.15 / SQLite / 固定の利用者・商品表
from sd02 import *
db = database()
print(db.execute("SELECT * FROM directory ORDER BY id").fetchall())
print(db.execute("SELECT id,salary FROM users ORDER BY id").fetchall())[(1, 'alice', 'red'), (2, 'bob', 'blue'), (3, "O'Reilly", 'red')] [(1, 100), (2, 200), (3, 300)]
generated = ("SELECT id,name FROM users WHERE department=? ORDER BY id",("red",))
print(generated)
print(db.execute(*generated).fetchall())('SELECT id,name FROM users WHERE department=? ORDER BY id', ('red',))
[(1, 'alice'), (3, "O'Reilly")]print(run(db,"SELECT 1; SELECT 2"))You can only execute one statement at a time.
print(db.execute(unsafe("' OR 1=1 -- ")).fetchall())
print(safe(db,"' OR 1=1 -- "))[(1, 'alice'), (2, 'bob'), (3, "O'Reilly")] []
db.set_authorizer(protect)
print(db.execute("SELECT * FROM directory ORDER BY id").fetchall())[(1, 'alice', 'red'), (2, 'bob', 'blue'), (3, "O'Reilly", 'red')]
print(run(db,"SELECT salary FROM users"))
print(run(db,"UPDATE users SET name='changed' WHERE id=1"))
print(run(db,"DELETE FROM users WHERE id=2"))access to users.salary is prohibited not authorized not authorized
print(db.execute("SELECT * FROM directory ORDER BY id").fetchall())
db.set_authorizer(None)
print(db.execute("SELECT id,name,salary FROM users ORDER BY id").fetchall())[(1, 'alice', 'red'), (2, 'bob', 'blue'), (3, "O'Reilly", 'red')] [(1, 'alice', 100), (2, 'bob', 200), (3, "O'Reilly", 300)]
演習完了