セキュリティについて学ぼうシリーズ。
前回はクロスサイトスクリプティング(XSS)についてまとめた。
前回からだいぶ時間が空いてしまった(反省)。
今回はSQLインジェクションについて。
これもよく聞くやつだからちゃんとまとめておく。
SQLインジェクションとは
SQLインジェクションはSQLの呼び出し方に不備がある場合に発生する脆弱性。
SQLの呼び出しで脆弱性が発生しやすい場所というのは、主に入力formだったり検索項目だったりユーザーが何かしらの入力をしてその情報をサーバーに投げる場所。
ここに脆弱性があると、以下のようなことが起こりうる。
・データベース内の情報が盗まれる
・データベース内の内容が書き換えられる
・認証を回避される(IDとパスワードが必要なくなる)
笑えないのでしっかり対策する。
実際の攻撃手法とその影響
具体的にSQLインジェクション攻撃はどういうふうに行われるのかを見ていく。
データが書き換えられる例
ユーザーを検索する実装を以下のように書いていたとする。
$sql = "SELECT * FROM users WHERE name = '$name'";
$ps = $db->query($sql); //クエリ実行処理ユーザーから名前の入力を受け取って、それをそのままSQL文の中に変数として入れている。
ユーザーが普通に検索したいユーザー名を入力すれば何の問題もないが、たとえば悪意のある攻撃者が以下のような入力をしたとする。
; DELETE FROM users WHERE id >= '1するとこの入力が埋め込まれて実際に処理されるクエリは以下のようになる。
$sql = "SELECT * FROM users WHERE name = '; DELETE FROM users WHERE id >= '1'";
$ps = $db->query($sql); //クエリ実行処理元々あったSELECT文が早々に終わってしまい、その後のDELETE文が実行されてしまう。
SQLインジェクションの脆弱性があると既存のデータが全部消去されるなんてことにもなりかねない。。
恐ろしすぎ。。。
認証を回避される例
認証をチェックするためのSQL文が以下のようにあったとする
$sql = "SELECT * FROM users WHERE name = '$name' AND password = '$password'";このSQLで該当レコードがあった場合に認証完了とするロジックだった場合、nameに適当な名前(hoge)、パスワードに以下のようなものを入力したとする。
' OR 'a' = 'aこれだけだと意味不明だが、挿入してみると理解できる。
SQLの全文は以下のようになる。
$sql = "SELECT * FROM users WHERE name = 'hoge' AND password = '' OR 'a' = 'a'";おわかりいただけただろうか。
これはpasswordが何もない or ‘a’ = ‘a’のどちらかを満たす場合に成立するSQL文になっている。
当然’a’ = ‘a’は常に成立する。
よって名前とパスワードを知らなくてもこれを入力することで認証が完了してしまう。
他にも例はあるが、これがSQLインジェクション攻撃の手法である。
原因
ここまでに見てきた脆弱性の原因を探ると、文字列リテラル「’」が適切に処理されていないことが原因と言える。
攻撃手法はいずれも' OR 'a' = 'aのような中途半端な入力になっている。
これによって元々組み込まれている文字列リテラルを無理やり終わらせて、別の意味をもつSQL文を付与させることができてしまう。
つまり、ユーザーによって入力された値が元々実装していた文字列リテラル内に収まっている限りは問題ない。
ここをはみ出したときに攻撃が有効になってしまう。
対策について
前項で見てきたようにユーザーの入力値が文字列リテラルをはみ出すことができる仕様だと、SQL文の意味が変わってしまうためまずい。
したがってユーザーの入力によってSQL文が変わらない仕組みが必要である。
その方法が以下のようなプレースホルダによるSQL文の組み立てである。
SELECT * FROM users WHERE name = ?;「?」がプレースホルダで、実際にユーザーが入力したデータは「?」の部分に割り当てられる(バインド)。
結局「？」の部分にバインドされるんだったら同じじゃね？という気もするけど、それは違う。
なぜプレースホルダだと大丈夫なのかを説明するためにSQL文の組み立てとバインドの仕組みを以下で見ていく。
プレースホルダを使わない場合
SQL文組み立てのタイミングとそれがDBエンジンによって処理される流れを図にしたものが以下。
SQL文が確定するのはコンパイルしたタイミングである。
そして上記ではコンパイル前にアプリケーションの方でSQL文が組み立てられてしまうため、
ユーザーの入力によってSQL文の構文そのものが変わってしまう危険性がある。
(これまでに見てきたような文字列リテラルを調整して別のSQLを組み込める)
(静的)プレースホルダ
プレースホルダには静的プレースホルダと動的プレースホルダがあるが、ここでは静的プレースホルダについて説明する。
原理的に静的プレースホルダではSQLインジェクションが起こらないため、セキュリティの文脈では静的プレースホルダの方が望ましい。
静的プレースホルダによってSQL文が組み立てられるタイミングとそれがDBエンジンによって処理される流れを図にしたものが以下。
ポイントはSQL文がまずコンパイルされて、その後にデータがバインドされていること
これによってSQL文そのものがまず確定するので、ユーザーの入力値によってSQLの内容自体が変わることはない。
ちょっとわからなかったのが、本に書いてあった図をパクって作図したんだけど、これだとSQLコンパイル前にSQL文をサーバに送って、その後実パラメータを送ってる？
リクエスト2回走るのかな？。。。知ってる人いたら教えてください。
SQL文を動的に変更したい場合
SQL文があらかじめ決まっていなく、ユーザーの入力値によって動的に変更したい場合を考える。
たとえば、検索フォームで様々な項目の組み合わせでSQL文が変わる場合など。
ユーザーを検索したい場合に、名前で検索するのかメールアドレスで検索するのか、生年月日で検索するのか…
あらゆる項目の組み合わせがあり、これらによってSQL文のwhere句は動的に変わる。
このような場合は、プレースホルダ「？」を含んだSQL文を文字列連結によって動的に組み立てる。
そして、その実パラメータを別で渡してバインドするようにすればよい。
実装的にはパラメータのあるなしによってif文で分岐させて、文字列連結していくイメージ。
Insertの場合のプレースホルダ
SELECT文は上記の通りで実装できる。
ただ、個人でINSERT文をプレースホルダで組み立てようとした時にうまくできなくてハマったので記載しておく。
環境はNode.js。
mysqlとの接続にはmysql2/promiseを使っている。
ORMを使っていなかったので、以下のようにしてクエリと実パラメータを渡す実装をしていた。
const [row] = await connection.query("SELECT * FROM users WHERE user_id = ?", [userId]);これは問題ないんだけど、バルクインサートで複数データを一気に入れたい場合に詰まった。
結論、以下のようにするとうまくいった。
const sql = "INSERT INTO users(name, email) VALUES ?";
const data = [
["太郎", "taro@example.com"],
["次郎", "jiro@example.com"],
["三郎", "saburo@example.com"]
]
await connection.query(sql, [data]);VALUESの後をそのまま「？」にしてしまって、そこにバインドさせる。
バインドさせる部分は挿入したい”レコードの配列”を配列で持っている入れ子構造にしておく。
あとはupdateとかdeleteはconnection.executeを使っているが、insertはconnection.queryでいいらしい。
あと第二引数の[]は必須っぽい。
これで複数データを一挙に登録できた。
おわりに
SQLインジェクションについてまとめた。
かなりよく聞く脆弱性なので言語化できてよかったと思うし、DBのコンパイラの仕組みとかタイミングも勉強できてよきだった。
プレースホルダの組み立てとかはORMとかを使えば多分よしなにやってくれて実装者が気にすることは無くなるんだろうなーという気もしてる。(ORMによるかもだけど)
とは言え実務でORMとか使ってるけど個人的にはあんま好きじゃない。。
なんか勝手に意図していない条件足したりしてくるし、ありがた迷惑に感じること多々ある。笑
たとえばgormでdeleted_atのカラムがある場合は勝手にdeleted_at IS NULLをwhere句に追加するとか。
あとORM使っちゃうと実行されているSQLが不透明になってしまう気がしている。
(テストちゃんと書いとけば解消するかも)
個人的にはORMはバインドをよしなにやってくれて取得データのマッピングをいい感じにやってくれれば十分な気がしている。
可読性の観点からも実装はできるだけ素のSQLっぽく書きたい。
けど最近prismaがNode.jsのORMとして人気出てるっぽいので時間ある時調べようと思う。
セキュリティに関してのまとめはまだ続く予定。
次回はクロスサイト・リクエストフォージェリ(CSRF)について。
ここは結構重そう。。。