SQLインジェクション対策は正しくエスケープ処理を行うだけでいいのか？

Question

サーバアプリケーションがMySQLなどデータベースを利用する場合ブラウザからのリクエスト文字列を工夫することで意図しないSQL文が組み立てられて情報の改ざんや漏洩することあります。それをSQLインジェクションと呼ぶものと認識しております。

SQLインジェクションの基本的な対策方法として、ブラウザからのリクエストなど、ユーザが入力したデータをエスケープ処理するというものがあると思います。

私は、SQLインジェクションに詳しくないのでエスケープ処理をちゃんと行っっていれば基本的に安心できるのではないか？と考えているのですがエスケープ処理を正しく行っていた場合にもユーザ（クラッカー）に想定しないSQL文を実行されてしまうことはあるのでしょうか？

このような質問をした理由としましては、SQL文を作成するにあたり、テンプレートエンジンを利用するのはどうか？と考えたためです。

なぜSQL文の作成にテンプレートエンジンを利用しようと思ったのかと言いますと、基本的なSQL関連のモジュールには?マークを利用して値をエスケープしてフォーマットするような仕組みが備わっているものが多いと思いますが（参考：sqlstring）この単純な?マークによる値代入が不便に感じたからです。

例えば、ユーザデータから年齢を指定して条件を絞って検索したい場合に

SELECT * FROM user WHERE age = ?

といったステートメントを記述し?にブラウザのリクエストで受け取った年齢を挿入するという手順を踏むと思われますが、ブラウザからのリクエストに年齢データが含まれてない場合には全ての年齢のユーザを対象に検索をかけたいという場合にage = ?を取り除かなければなりません。

このような問題を対策する場合

let age_sql = '1'
if( age ) age_sql = 'age = ?'
let sql = `SELECT * FROM user WHERE ${age}`

のように文字列を組み合わせて作成する必要が生まれます。（もちろんSELECT * FROM user WHERE ${age ? 'age = ?' : '1'}で可能ですがfor文やif文を使いたい場合にはどうしてもコードが散らかってしまうかと思われます）

このような複雑なコードを書くならば、テンプレートエンジンを使ってスッキリ書いた方が良いのではないのか？と思った次第です。

lodashのtemplateを利用を想定したテンプレート文字列

SELECT *
FROM user
WHERE
<% if( age ) { %>
  age = <%- age %>
<% } %>

余計に複雑になってないか？とも感じますが、テンプレートエンジンを利用することでコード内に直接SQL文を書くことを避けることができますし、for文などを利用したい場合には確実にこちらの方がシンプルになるのではないかと考えています。

SQL文の作成にテンプレートエンジンを利用するといった話はあまり聞いたことがありませんが、もしこの実装方法で開発を進めていく場合、今まではDB関連のモジュールに標準で備わっていた?を利用してエスケープ処理を任せていたが、そのような処理を独自に開発することになります。

テンプレートエンジンを利用するため独自に開発というと語弊がありますがもともとテンプレートエンジンはSQL専用のモジュールではないし?のエスケープ処理がどのように行われているか詳しく理解せずに独自のやり方で実装すると思わぬ脆弱性を生んでしまうかもしれません。

そこで、SQLインジェクション対策は基本的にエスケープ処理をしっかり行っていれば良いのかみなさんにお聞きいたしました。

squelのようなライブラリの利用も検討したのですが、JSとSQLを記述するプログラマで役割をもし分けていたというようなことを考えるとあまり望ましくないのではないか？と思いました。

SQLインジェクション対策は基本的にエスケープ処理を行うだけで良いのでしょうか？

また、SQLの作成にテンプレートエンジンを使うことに対しても否定的な意見がありましたら是非ご指摘いただけるとありがたいです。

よろしくお願いいたします。

Answer 1

SQLインジェクション対策は正しくエスケープ処理を行うだけでいいのか？

パラメータ化クエリを使うのが必須だと思います。あと、可能であれば静的プレースフォルダも。

以下の記事によると "高いスキルを持つ決然たる攻撃者は、パラメータ化されたデータであっても操作できるのです" とのことですから、パラメータ化しないのは論外かもしれません。

SQL インジェクション
https://msdn.microsoft.com/ja-jp/library/ms161953(v=sql.100).aspx

SQL Server 関係であれば、自分のブログで恐縮ですが、以下の記事を見ていただければと思います。

パラメータ化クエリ
http://surferonwww.info/BlogEngine/post/2012/02/02/Parameterized-query.aspx

SQL Server の他の DB については、以下の記事がまとまっていると思います。

安全なSQLの呼び出し方 - IPA 独立行政法人 情報処理推進機構
http://www.ipa.go.jp/security/vuln/documents/website_security_sql.pdf

Comments

[hojo]

なるほど、パラメータ化クエリというものがあったのですね！
調べています！

[yuba]

これが唯一の正しいやり方なのでテンプレートエンジンなどといった方法は考えないようにしてください。

Answer 2

エスケープ処理のみでは意図しないSQLの発行を抑止するのは無理ですね。

以下のような場合、idにhoge、numval に1 or num = 2などとすると意図しないSQLが発行されます。
※prepared statementを利用した場合はbind時にエラーになってくれます。またはnumの値と1 or num = 2を比較してくれます。

<% /* lodashのtemplateを利用を想定したテンプレート文字列 */ %>
SELECT * FROM user WHERE
     id = '<%- id %>'
 and num = <%- numval %>

↓

SELECT * FROM user WHERE
     id = 'hoge'
 and num = 1 or num = 2

Comments

[hojo]

なるほど、盲点でした。

数値と文字列を判定して文字列の場合には必ずシングルコーテーションがつくような関数を用意することで対策できるのか？と一瞬思いましたが、何か腑に落ちないのでsqlstringの`?`のフォーマット処理が内部でどのようなことをやっているのか調査してみたいと思います。

ありがとうございましたm(_ _)m

Answer 3

自分でテンプレートエンジンを使うより、SQL専用のクエリビルダを使うほうが安全・確実です。

たとえば、knex.jsでは、knex(テーブル名).where({key: val})のような形でSQLを組み立てることができます。

Comments

[hojo]

やはりそうですか。
SQLに詳しいがJSに詳しくないプログラマと役割を分担することを考えると
選択肢としてないかなと思っていたのですが、もう一度検討してみます。

テンプレートエンジンに近いSQLクエリビルダのようなものがあれば良いのですが...
そこにあまりこだわらない方がいいのかもしれませんね。

ありがとうございます！

Answer 4

色々調査したところ自己解決（解決してませんが）しましたので報告させていただきます。

まず、SQLインジェクションの対処策となる唯一の方法というものは存在しないということです。つまり、これを使っていればSQLインジェクション対策は大丈夫だというような解決法は無いようです。

SQLインジェクション対策に有効な手段として以下の５つが浮上しました。

• 値のエスケープ
• プリペアドステートメント
• ストアドプロシージャ
• データアクセスフレームワーク
• バリデーション

値のエスケープはSQLインジェクション対策というよりは、これを行わなければシングルクオート(')を含む文字列がリクエストされた場合にSQLの文法エラーになってしまいますね。またエスケープした文字列をシングルクオートで囲っておけば、シングルクオートで文字列を区切られ、文法として認識される箇所にユーザが自由に文字列を記載することはできなくなるでしょう。しかしY.H.さんもおっしゃっていましたが、文字列ではなく数値を扱いたい場合には単純なエスケープだけではSQLインジェクション対策は行えないようです。

プリペアドステートメントはSQLインジェクション対策に対する最も強力な防御手段ですが、柔軟な文法生成ができません。環境によるところもありますが、プリペアドステートメントは値リストやテーブル識別子や列名やSQL予約語をパラメータにできません。そのような問題に直面したため、私はテンプレートエンジンを利用してSQL文の生成を行おうと考えたわけですが、こちらの方法では動的箇所全てを厳重に注意しながら構築する必要が生まれるため見落としがあった場合にさらなる危険性を生んでしまう可能性があります。おそらく、テンプレートエンジンを使うよりもプリペアドステートメントを利用して、パラメータにできない部分についてはユーザの値を直接利用するようなことは避け、厳重に注意しながらコードを書くしかないようです。

ストアドプロシージャもSQLインジェクション対策に効果的という意見があるようですが、安全性の低い動的SQLを利用することができるため唯一の解決策にはならないようです。結局正しく使わなければアプリーケーションで動的にSQLを使用するのと同じくらい危険らしいです。また、ストアドプロシージャを利用すればSQLのパフォーマンスの向上が上がると思いきや、アプリケーションサーバでできることはロードバランス可能なアプリケーションサーバで行った方がパフォーマンスの向上が図れるという意見がありました。

データアクセスフレームワークやオブジェクトリレーショナルマッピング（ORM）によってSQLインジェクションのリスクからコードを保護できるという意見があるそうです。しかし、これはSQLステートメントを文字列として直接記述できるフレームワークは当てはまらないようです。SQLステートメントを直接記述できないフレームワークの場合には、プリペアドステートメント同様の対策になり得る上、プリペアドステートメントでは不可能だった柔軟なSQL構築が可能となりますが、フレームワークの学習コストが発生するほか、SQLをコードから分離するということができなくなります。私はこのようなライブラリを利用してDBとやりとりした経験が少ないのですが、ActiveRecordを利用した時に地獄を見た経験があるため、ORMの利用は避けますがmaisumakunさんに紹介させていただいたknex.jsについては初めて知りましたのでもう少し調べて見たいと思っています。

ユーザが入力したデータをバリデーションすることにより、ユーザからの入力に危険な文字列が含まれていないかどうかを探るよりも、その入力にとって無効な文字列を初めから全て取り除くようにするという考え方もありました。不正な値がデータベースに記憶されてしまうと、それだけでアプリケーションサーバが予期せぬ不具合を起こしたりすることが想定できるため、バリデーションをより厳密に行うことでSQLインジェクション対策になるというものです。これは安全かつ安定したアプリケーションの構築につながるため、SQLインジェクション対策とはまた別に意識しなければいけませんね。

結論として私はプリペアドステートメントを利用し、パラメータにできない部分についてはユーザの入力値を直接利用しないよう厳重注意し、どうしてもユーザの入力値を利用しなければならない場面については想定されない値の侵入をフィルタリングすることで対策するのがいいのではないかと考えています。柔軟なSQL文を生成するにはSQLをコードから分離することは難しいと思われますが、学習コストが低い上に文字列結合による値代入などを行わなければ安全性も高い上、調査した中では最も有効な対策方法（SurferOnWwwさんが前もって教えてくださっていましたが）と言われていたためです。

もう少しデータアクセスフレームワークについて調査するつもりですが、ひとまずわかったことをここにまとめ、解決とさせていただきます。

何か指摘箇所がありましたらぜひ教えていただけると嬉しいです。

Comments

[退会済みユーザー]

理解が間違っていると思いますよ。

SQL インジェクション防止策としては、何はともあれパラメータ化クエリを使うということが基本のキで、優先順位としてはそれが一番に来るべきです。

先に紹介した記事に書いてある重要な部分を以下に挙げておきます。

「ユーザー入力から直接クエリを組み立ててコマンドテキストとして渡すのとは異なり、パラメータの入力は実行可能なコードとしてではなく、リテラル値として扱われます。これにより、攻撃者がサーバーのセキュリティを侵害するコマンドを "注入" しても、注入した値はリテラルの外にはみ出すことはないので、SQL インジェクション攻撃を防ぐことができます。」

パラメータ化クエリを使えば「値のエスケープ」は必要ありません。 優先順位としてはイの一番にパラメータ化クエリの使用が来るべきです。

以下の記事によると "高いスキルを持つ決然たる攻撃者は、パラメータ化されたデータであっても操作できるのです" とのことなので、パラメータ化クエリを使ったうえで（使わないのは論外）、その記事に書いてある「すべての入力の検証」などの対策も行うべきということです。

SQL インジェクション
https://msdn.microsoft.com/ja-jp/library/ms161953(v=sql.100).aspx

[hojo]

すみません、パラメータ化クエリというのは具体的に何を指すのでしょうか？

データベースに値注入を行わせるものが静的プレースホルダ、アプリケーションサーバに行わせるのが動的プレースホルダであり、静的プレースホルダは、データベースにステートメントを送信する代わりに値のみ送信するもので、それらのプレースホルダを利用してステートメントを抽象化する技術をパラメータ化クエリと言うものだと解釈していました。

そしてプレースホルダとプリペアドステートメントは環境により文法が異なるものの（?だったり:nameだったり@nameだったりnameだったり）役割は同じものだと解釈しています。

私は現在アプリケーションサーバでnode.jsを利用し、node-mysql2(https://github.com/sidorares/node-mysql2)を利用してMySQL5.6のRDBと接続した環境でシステム構築を行なっています。

例えば、そのような環境でパラメータ化クエリを実現するには具体的にどうすれば良いのでしょうか？
とっかかりだけでも教えていただけると有難いです。m(_ _)m

[退会済みユーザー]

質問者さんの言う「プリペアドステートメント」は私が言う「パラメータ化クエリ」と同じようですが、であれば、私の意見としては SQL インジェクション防止対策として「プリペアドステートメント」が一番に来るべきと言ってます。

（「値のエスケープ」の次ではなくて。前に書きましたが、注入した値はリテラルの外にはみ出すことはないので、基本的に「値のエスケープ」はしなくても済むはずです）

質問者さんの回答で、パラメータ化クエリは「柔軟な文法生成ができません」とのことですが、パラメータ化クエリが使えないとするとそれは一般的な話ではなく（＝質問者さんの事情）、そのような前提を質問の一番最初に書いてから（例えば「パラメータ化クエリを使えない状況での SQL インジェクション対策はどうすべきか？」とかの表題で）、質問を始めるべきだと思います。

> そのような環境でパラメータ化クエリを実現するには具体的にどうすれば良いのでしょうか？

私は Node.js は分かりません。（MySQL は Connector/NET 経由でパラメータ化クエリを利用して使っていますが、その話をしても仕方がないですよね）

なので、別に新しいスレッドを立てて、パラメータ化クエリが使えないなどの特殊事情があるならそれを明記して、質問されてはいかがでしょう？

[hojo]

ご回答ありがとうございます！

解釈が誤っているのではないかと困惑していたので、とても参考になりました。
おかげで、パラメータ化クエリ（プレースホルダまたはプリペアドステートメント）が最も強力な防御手段であることが理解できました。

プリペアドステートメントが柔軟な文法が生成できないことにつきましては記述しましたが、環境により異なる点はあると思いますが、値リスト、テーブル識別子、列名やSQL予約語をパラメータ化（プレースホルダ化）することができない仕様であることが多いと思われますのでやはり柔軟な文法生成ができない側面はあると思います。もちろんSQL文を工夫することである程度柔軟にすることは可能だと思われますが...。

例えば質問文に挙げさせていただいた「SELECT * FROM user WHERE age = ?」で年齢を指定してユーザを検索することが可能になりますが「?」の部分を工夫するだけで年齢の絞り込み条件を無効にする手法は僕にはわかりません。

もし「age = *」と指定することで、年齢の絞り込み条件を無効にできるのであれば良いのですがMySQLではできないようでした。

SurferOnWwwさんのおっしゃる通り、初めに質問た時にはSQLインジェクション対策にエスケープだけ行っていれば良いという考えだったのが、今では全く違った解釈になっていますので、もう一度情報を整理してから改めてどのように実装すべきか質問投稿することを検討してみます。

本当にありがとうございました！

[退会済みユーザー]

> 「SELECT * FROM user WHERE age = ?」で年齢を指定してユーザを検索することが可能になりますが「?」の部分を工夫するだけで年齢の絞り込み条件を無効にする手法は僕にはわかりません。

質問者さんが利用している MySQL のドライバ＆位置パラメータマーカーで可能かどうかは分かりませんが、SQL Server + SqlClient では以下の記事のようにクエリを工夫して、パラメータとして全件抽出になるような値を代入してやるという方法があります。

DropDownList を使って絞込み
http://surferonwww.info/BlogEngine/post/2011/07/17/Showing-records-selected-by-DropDownLists-into-GridView.aspx

上の記事の例では WHERE 句を以下のようにしており、例えば @CustomerID に 'ALL' を代入してやると @CustomerID='ALL' は true になるので、CustomerID での絞り込み条件は無効になります。

WHERE (@CustomerID='ALL' OR o.CustomerID=@CustomerID)
AND (@EmployeeID=0 OR e.EmployeeID=@EmployeeID)

[hojo]

回答にマイナスをつけていただいた方、ありがとうございます。
できれば、どの点がダメだったのか教えて頂けると有難いです。
よろしくお願いいたします。m(_ _)m