商品検索クエリを修正する

はじめに

小さなアウトドア用品店の検索では、名前が完全一致する掲載済み商品だけを返す必要があります。クエリの組み立て方の誤りにより、入力からその規則を変えられ、普通のアポストロフィでも検索が壊れます。実際のリクエスト経路を観察し、短い Python 関数を修正して、安全性と有用な結果をテストします。

Workstation、App Server、Shop Web は準備済みです。端末の基本操作とタブの切り替えができれば進められます。SQL、今回必要な Python 構文、エディター操作はここで説明します。データは合成ですが、HTTP サービスと SQLite の解析は実際に動作します。

通常の商品検索を実行する

このステップでは、店舗の検索を使い、結果とデータベースの行を結び付けます。小さなアウトドア用品店の検索を修正する仕事です。入力した名前と完全一致する掲載済み商品だけを返す必要があります。商品と価格はすべて合成の練習用データです。

このステップを始めるには、ラボ画面の Shop Web タブを選択してください。

テーブルはレコードを行として保存し、名前の付いた列で各レコードを記述します。次は準備済みの products テーブルです。listed は公開商品なら 1、内部試作品なら 0 です。価格は練習用通貨の整数単位です。

Shop Web タブに切り替え、Product name に Trail Mug と入力して Search を選びます。rows に [1, "Trail Mug", 18] が入り、error が null（エラーなし）になります。ページが Python アプリにリクエストを送り、アプリが SQLite に一致するレコードを問い合わせ、結果を返します。ページ、アプリのコード、データベースは、この経路の別々の部分です。

クエリはデータベースへの質問です。ローカルの学習用トレースに、SQLite が実行した SQL が表示されます。

SELECT id, name, price FROM products WHERE listed = 1 AND name = 'Trail Mug' ORDER BY id

SELECT は返す列、FROM はテーブルを指定します。WHERE は行を絞り、AND は掲載済みと名前の完全一致という両条件を要求します。単一引用符は SQL のテキスト値を囲みます。ORDER BY id で行の順序を安定させます。結果には listed 列がありませんが、クエリはその列を使って絞り込みます。

Workstation 端末はリクエストを送るクライアントです。App Server タブでは、後で修正するアプリのディレクトリを開きます。同じ練習用ホスト上の別セッションであり、別のマシンではありません。実際のアプリが配信するページにも、この関係が表示されます。

Workstation タブで同じリクエストを送ります。

次のコマンドを実行する前に、Workstation タブに切り替えてください。

python3 probe.py "Trail Mug"

python3 は準備済みクライアント probe.py を実行します。引数は検索テキスト 1 個です。二重引用符により、空白を含む名前を 1 つの引数に保ち、外側の引用符はシェルが取り除きます。クライアントは値を安全にエンコードして、Web アプリの形式である HTTP リクエストにします。クライアントの 127.0.0.1 はこの練習用ホストです。

JSON 出力の rows、trace、error を読みます。JSON は構造化された応答で、配列には角括弧を使い、null はエラーなしを表します。接続できない場合は Shop Web を再読み込みします。接続エラーと、正常な検索で結果が空であることは別です。完了チェックは実際のリクエスト結果を読むため、レポートは不要です。

入力によるクエリの変化を見る

このステップでは、この練習用店舗だけを対象に、範囲を限定した SQL インジェクションを観察します。SQL インジェクションとは、入力がテキスト値にとどまらずクエリ構造を変えてしまうことです。このテストは準備済みラボ内でのみ使ってください。

このステップを始めるには、ラボ画面の Workstation タブを選択してください。

App Server タブに切り替えます。ファイルを編集せず表示する cat で短い関数を読みます。

次のコマンドを実行する前に、App Server タブに切り替えてください。

cat query.py

def search(db, text): は関数を定義します。db は開かれたデータベース接続、text はリクエストから受け取った値です。字下げされた行が関数本体です。sql = は変数に Python 文字列を代入します。二重引用符が Python 文字列を、その内部の単一引用符が SQL テキストを囲みます。+ text + はユーザーの値を SQL に直接連結します。db.execute(sql) が SQLite に実行を依頼し、fetchall() が結果行を集め、return が返します。

Workstation タブで、ハイフン 2 個の後の空白も含め、次の値を正確に送ります。

次のコマンドを実行する前に、Workstation タブに切り替えてください。

python3 probe.py "' OR 1=1 -- "

外側の二重引用符はシェル用です。先頭の単一引用符が SQLite に入り、連結されたクエリの空のテキスト値を閉じます。OR はどちらかの条件を許可し、1=1 は常に真です。-- がコメントを始めるため、元の閉じ引用符と並べ替え句は無視されます。トレースには次が表示されます。

SELECT id, name, price FROM products WHERE listed = 1 AND name = '' OR 1=1 -- ' ORDER BY id

[3, "Prototype Mug", 99] を含む 4 行すべてが表示されます。その試作品は未掲載です。読み取りだけでも、本来の業務境界を越えたことになります。HTTP 応答の成功だけでは検索の安全性は証明できません。

アポストロフィを含む正当な名前を試します。

python3 probe.py "Camper's Cup"

脆弱なアプリは rows: []、空の trace、near "s": syntax error を含む error を返します。SQLite が不正な SQL を実行できず、完了したクエリがないためトレースもありません。これは想定された失敗で、クライアント自体は正常終了するため続行できます。

アポストロフィは正当な商品データです。削除すれば要求した名前が変わり、含まれる入力をすべて拒否すれば顧客を拒否します。OR などの単語を禁止すると ORANGE Bowl も壊れます。値を保ちながら SQL 構造から分離する必要があります。

検索値をバインドする

このステップでは、列、完全一致、掲載条件、順序を保って関数を修正します。App Server タブに切り替えます。

このステップを始めるには、ラボ画面の App Server タブを選択してください。

パラメーター化クエリは SQL 構造と値を分けます。Python の SQLite API では ? が 1 つの値の位置を示します。値は execute に別々に渡します。プレースホルダーを引用符で囲んだり、値を SQL に連結したりしません。例えば db.execute("SELECT ?", (text,)) は渡されたテキストをデータとして返し、SQL と解釈しません。

(text,) は要素 1 個のタプルで、順序付きのパラメーター集合です。末尾のカンマでタプルになり、(text) だけでは括弧に入った元の値です。execute(sql, (text,)) の外側の括弧は、SQL テンプレートと値のタプルという 2 引数で関数を呼び出します。ドライバーが値をプレースホルダーにバインドします。

端末エディターでファイルを開きます。

次のコマンドを実行する前に、App Server タブに切り替えてください。

nano query.py

矢印キーで移動し、Backspace/Delete で削除して、文字を入力します。sql = と return で始まる 2 つの字下げ行を置き換え、関数を次のとおりにします。

def search(db, text):
    sql = "SELECT id, name, price FROM products WHERE listed = 1 AND name = ? ORDER BY id"
    return db.execute(sql, (text,)).fetchall()

本体の各行の先頭に空白 4 個を置き、def の前は字下げしません。Python は字下げで本体をまとめます。Ctrl+O で保存し、Enter で query.py を確定して、Ctrl+X で終了します。元のコメントは残せます。

アプリはリクエストごとに query.py を読み、コンパイルします。保存だけで反映され、再起動は不要です。未保存の編集内容は反映されません。入力や字下げの誤りは次の応答の error に現れます。同じファイルを開いて修正・保存し、再送してください。

Workstation タブで 2 つの値を再試行します。

次のコマンドを実行する前に、Workstation タブに切り替えてください。

python3 probe.py "Camper's Cup"

python3 probe.py "' OR 1=1 -- "

実在する商品は [2, "Camper's Cup", 22] を返し、特殊なテストは rows: [] を返します。どちらも error: null です。このデータベースには特殊な文字列と完全一致する商品がありません。

トレースには Camper''s Cup や、引用符で囲まれた特殊な文字列が表示される場合があります。SQLite のトレースは、エスケープされた値を含むバインド後の展開 SQL であり、元のテンプレートではありません。値が見えても Python で連結したとは限りません。query.py でテンプレートと別のタプルを確認してください。バインドを手作業のエスケープに置き換えないでください。

業務用の検索機能を保つ

このステップでは、安全性と通常機能を一緒に確認します。検索を止めたり空の固定応答を返したりすれば試作品は隠れても、顧客が商品を探せなくなります。

このステップを始めるには、ラボ画面の Workstation タブを選択してください。

Workstation タブで通常の名前をもう一度送ります。

次のコマンドを実行する前に、Workstation タブに切り替えてください。

python3 probe.py "Trail Mug"

引き続き [1, "Trail Mug", 18] が返ります。OR を含む正当な単語が拒否されないことも確認します。

python3 probe.py "ORANGE Bowl"

[4, "ORANGE Bowl", 12] が期待値です。未掲載の試作品を普通の名前で検索します。

python3 probe.py "Prototype Mug"

rows: [] と error: null が期待値です。バインドが注入を防ぎ、変更していない listed = 1 が公開商品を制御します。修正時にこの条件を消すのは危険です。

Shop Web タブに戻り、Camper's Cup を入力して Search を選び、商品行を確認します。' OR 1=1 -- でも繰り返し、エラーなしの空結果を確認します。ブラウザーとクライアントは同じ関数を使います。

ステップの完了チェックを実行します。読み取り専用リクエストに加え、一時的なメモリ内データベースの追加合成行で、あなたの関数を変更せずに実行します。本番役のデータベースを編集せず、完全一致、記号、掲載規則、未知の名前を確認します。SQL に見える名前でも、その名前が存在して掲載済みなら、普通のデータとして検索できなければなりません。

失敗したら App Server タブで query.py を開き、プレースホルダー、タプルのカンマ、字下げ、業務条件を修正例と比べます。保存して再試行します。データベース、クライアント、サーバーの枠組みは変えないでください。レコードの変更や固定回答ではクエリは修復されません。

まとめ

ページから実際の SQLite クエリまで検索を追い、入力による構造の変化を観察して、プレースホルダーと値 1 個のタプルで Python 呼び出しを修正しました。通常名、アポストロフィ、限定テスト、掲載規則の維持を確認しました。注文検索のチャレンジでは、同じ分離を別の業務対象に応用します。