動的SQLの組み立て方
動的なSQLでも、構造を選ぶ部分と値を渡す部分を分けます。
説明
プレースホルダーは通常、値を束縛するためのものです。列名やASC・DESCなどのSQL構造を、そのままパラメーターで置き換えることはできません。構造が変わる箇所は許可した候補から組み立てます。
商品の一覧を並べ替え、複数IDを検索します。列名のような値を束縛した場合、許可リストによる組立て、IN句の要素数、空の一覧、LIKEのワイルドカードを比較します。
動的なSQLでも、構造を選ぶ部分と値を渡す部分を分けます。
1商品と価格を確認する
ID順ではbook、pen、bag、価格は300、100、500です。並び替えの違いが分かる資料です。
from sd02 import *
db = database()
print(db.execute("SELECT * FROM products ORDER BY id比較前のID順を確認する").fetchall())価格の昇順ならpen、book、bagになるはずです。実際の結果と比較します。
2列名を値として束縛した場合
ORDER BY ?にpriceという文字列を束縛します。price列の指定にはなりません。
print(db.execute("SELECT id,name,price FROM products ORDER BY ?,id同じ定数で並べるSQLを実行する",("price",)列名ではなく文字列値として束縛する).fetchall())全行に同じ文字列priceを並び順の値として与えるため、二番目のidで並びます。列を選ぶ機能としては誤った実装です。
3許可した列と方向を選ぶ
外部のsortキーを、内部で固定した列名へ対応させます。方向もasc/descの二つだけです。
sql = ordered("price","asc")許可した列と方向でSQLを作る
print(sql)
print(db.execute(sql).fetchall())
print(db.execute(ordered("price","desc")許可した別方向を選ぶ).fetchall())昇順はpen・book・bag、降順は逆です。受領した文字列をそのままSQL構造へ入れていません。
4許可していない構造を拒否する
列名へSQLの一部のような値を渡します。DBへ渡す前に許可リストで拒否します。
try: ordered("price DESC, name","asc")未知の列指定を渡す
except ValueError as error: print(str(error))
print(db.execute("SELECT COUNT(*) FROM products").fetchone())sort_not_allowedが返り、3件の商品はそのままです。SQLの構造に使う値は、許可した候補から選びます。
5IN句の要素数だけを組み立てる
IDが二つなら?を二つ生成し、それぞれを値として束縛します。
print(selected(db,[1,3])要素数に合わせた束縛を作る)SQLはIN (?,?)、引数は(1,3)、結果はbookとbagです。値そのものはSQLへ連結していません。
6一覧文字列と空一覧を比較する
1,3という一つの文字列は、二つのIDとは別の値です。空一覧はこのAPIでは結果なしにします。
print(db.execute("SELECT id,name FROM products WHERE id IN (?)",("1,3",)一覧のように見える一つの値を渡す).fetchall())
print(selected(db,[])空一覧の仕様を確認する)どちらも0行です。配列の展開規則を決めず、一つの文字列をINへ束縛するだけでは期待する検索になりません。
7LIKEでは束縛後もワイルドカードが働く
SQL構造から値を分離しても、LIKEのパターンとしての%は有効です。文字どおりの%を探すときは別にエスケープします。
print(db.execute("SELECT name FROM products WHERE name LIKE ? ORDER BY id",("%",)ワイルドカードを検索値として渡す).fetchall())
print(db.execute("SELECT name FROM products WHERE name LIKE ? ESCAPE '!'パターンのエスケープ文字を指定する ORDER BY id",("!%",)文字どおりの%を指定する).fetchall())%は全件、!%は文字どおりの%という名前を探すので0件です。SQL注入対策と検索パターンの意味を分けて扱います。
まとめ
列名や並び順は許可した構造から選びます。
IN句は要素数を組み立て、各値を別に束縛します。
束縛してもLIKEなどの検索言語の意味は残ります。
操作履歴と解説を振り返れます。
Python 3.15 / SQLite / 固定の利用者・商品表
from sd02 import *
db = database()
print(db.execute("SELECT * FROM products ORDER BY id").fetchall())[(1, 'book', 300), (2, 'pen', 100), (3, 'bag', 500)]
print(db.execute("SELECT id,name,price FROM products ORDER BY ?,id",("price",)).fetchall())[(1, 'book', 300), (2, 'pen', 100), (3, 'bag', 500)]
sql = ordered("price","asc")
print(sql)
print(db.execute(sql).fetchall())
print(db.execute(ordered("price","desc")).fetchall())SELECT id,name,price FROM products ORDER BY price ASC, id [(2, 'pen', 100), (1, 'book', 300), (3, 'bag', 500)] [(3, 'bag', 500), (1, 'book', 300), (2, 'pen', 100)]
try: ordered("price DESC, name","asc")
except ValueError as error: print(str(error))
print(db.execute("SELECT COUNT(*) FROM products").fetchone())sort_not_allowed (3,)
print(selected(db,[1,3]))('SELECT id,name FROM products WHERE id IN (?,?) ORDER BY id', (1, 3), [(1, 'book'), (3, 'bag')])print(db.execute("SELECT id,name FROM products WHERE id IN (?)",("1,3",)).fetchall())
print(selected(db,[]))[] []
print(db.execute("SELECT name FROM products WHERE name LIKE ? ORDER BY id",("%",)).fetchall())
print(db.execute("SELECT name FROM products WHERE name LIKE ? ESCAPE '!' ORDER BY id",("!%",)).fetchall())[('book',), ('pen',), ('bag',)]
[]演習完了