検索の入力で、他人のデータまで表示されないようにする

SQLは表へ検索などを頼む言葉です。今回のreportsという表には、持ち主ownerと本文bodyがあります。SELECT body FROM reports WHERE owner = ?は、?へ別に渡す値とownerが合う行の本文を返す枠です。

ANDは両方、ORは少なくとも一方の条件が必要なことを表します。ANDがORより先に結び付きます。会社・公開状態などの条件も実サービスでは別に必要ですが、今回の表の列と混同しないようにします。

SQLでは'で囲んだ部分を文字列として扱います。外側のORは条件を結ぶ命令、--はその行の残りを命令として読ませないための記号です。文字列の内側なら、同じ記号も値の一部です。

利用者が入れた文字を、SQLの引用符の間へそのままつなげるとどうなるでしょうか。入力中の'が文字列の終わりと読まれ、その後ろが命令になることがあります。検索したい名前と、処理の指示を分ける必要があります。

今回の最初のコードは、入力した値をSQLの文字列へつなげます。引用符とORを含む値を渡すと、名前の一致を調べるはずの条件が変わり、別の名前の本文まで返ります。これがSQLインジェクションです。

削除や複数命令を実行できなくても、読むだけで情報は漏れます。会社や本人を確認する規則があるサービスでは、その条件も入力によって崩れないかを調べます。今回のreportsは架空の名前と本文だけの小さな表です。

今回のPythonではconnection.executeの最初の引数にSQL、二つ目に値を渡します。?は値を置く位置で、引用符で囲みません。(value,)は値が一つ入った組を表し、末尾のカンマが必要です。

別に渡した名前は命令として読み直されません。引用符を含む名前もそのまま扱います。他のAPIでは:labelのような名前付きの印を使う場合がありますが、この課題の印は?です。

値を別に渡しても、その利用者が誰の書類を読んでよいかという条件がなければ、他人の書類を探せる場合があります。命令と値の分離は、認可の代わりにはなりません。

この実習は固定の架空入力を使って検索値の扱いを直します。本物のログインや会社の境界の確認は行いません。別の独力確認を追加するのでなく、同じ課題の正常な名前と記号入りの値を比べます。

初期コードへ引用符入りの入力を渡したときと、普通の名前を渡したときの結果を比べます。入力をそのまま命令へ連結すると、値の引用符が命令の構造を変える恐れがあります。

修正後は通常の名前で必要な本文が返り、命令を含むように見える文字列でも条件が変わらないかを確かめます。名前に引用符がある場合に文字を勝手に削って別の名前へ変えないことも大切です。

exercise.pyのlookupにあるexecuteを修正します。SQLはSELECT body FROM reports WHERE owner = ?という枠、検索値は(value,)という別の引数です。返された行の本文を結果へまとめる部分はそのまま使います。

パラメーターは値のための仕組みです。利用者に表名や列名を自由に書かせるためには使えません。その場合はアプリが用意した候補から選ぶなど別の設計が必要です。

データベースで削除や更新を禁止すると、被害を抑える助けになります。しかし、SELECTだけでも、他人の行を読めてしまう問題は残ります。返った行をアプリが本人の情報と思い込むと、ログインの確認をすり抜ける原因になることもあります。

データベースに与える権限を小さくする対策と、値を命令とは別に渡す対策は、どちらも必要です。この実習は検索を扱います。ログイン、パスワードの確認、セッションの発行は別の課題です。

実行ごとに作る架空のSQLiteの表を検索し、result.jsonへ結果を出します。最新の結果と修正したexercise.pyを提出します。サーバーで学習者のコードを実行する方式ではありません。

エラーで結果が出ない場合は、エラー文と修正した行を先に確認します。エラーがないのに他人の本文が混ざる場合は、SQLの枠と値を本当に別々に渡したかを見直します。