SQLクエリと識別子エスケープの話

Facebookでこんなやり取りをしました。元々公開設定で投稿した物で、議論させて頂いた浅川さんにも「ブログOK」と許可も頂いたので、そのやり取りの部分だけを紹介します。

テーマは「SQLクエリと識別子エスケープ」です。

とあるブログの結論として

“「安全な静的SQLの発行方法」を開発者に啓蒙すればいいだけだ。つまり

• プリペアドクエリでSQL発行は行うこと
  この場合変数のバインドは必ずプレースホルダを用いること。

SQL文の文字列操作は禁止であり、これほどシンプルなことも無いだろう”

と書かれていました。しかし、私はこの意見に対して

実践的なSQLクエリを書いたことが全くない開発者なのでしょう？？
ソートカラム、抽出カラムを指定するSQLクエリでは「識別子（カラム名）が変数」になります。
初歩的なSQLクエリですが”自分が書いたことないから”といって一般的ではないと勘違いしています。行のソート順、抽出カラムを指定するSQLクエリは業務アプリでは”一般的”です。当たり前ですが。

こうやってベストプラクティスもどきのアンチプラクティスが使われて、喜ぶのはサイバー犯罪者とセキュリティ業者だけですよ。

とコメントしました。SQLのテーブルやジョイン結果を「表」として表示した際に、任意のカラムのソート順を指定するUIは、自分で作ったことが無かったとしても、ごく一般的だと分かると思うのですが・・・

浅川さん：抽出カラムを指定するSQLクエリでは「識別子（カラム名）が変数」になります。

そういうこともあるだろうけど、心あるプログラマーは
カラム名を、外部から直接入力される様なプログラム(PHPMyAdminみたいなのは例外)はかなないでしょ？
自分で指定するリテラルを変数に代入するだけ。

私：ソートカラム、抽出カラムとかの指定です！！表データを扱うとごく普通に使います。

浅川さん：はい、ですが、そのカラム名を生でUIに渡す様なプログラムをしません

（備考：私のこのブログやこのブログのこと指してのコメントだと思われます）

私：それが当たり前です。
ただし、ORMなどのライブラリは”入力コンテクスト”を知らないので、エスケープするしかありません。（”入力”と”出力”は独立した処理とするのが原則）

RailsのActiveRecordは”識別子”を”シンボル”にして安全性を保証していますが、そういう物が全部ではありません。

また、シンボルなどには”DBがマルチバイト文字列識別子”をサポートしているのに、使えない、といったデメリットもあります。

UI（HTMLとか）に出鱈目な”識別子名”を渡すのはバグです。SQLにも、ですが。

（備考：このコメントを書いた時は、私のこのブログやこのブログのこと指してのコメントであろう事は気づいていませんでしたが、何とか浅川さんのコメントの意図通りの返信になっていました）

浅川さん：はい、ライブラリはそうですね。
なので、UIで生の内部識別子を渡すのはダメなのです。

大垣さんの例だと、極端なパススルーの例になる(説明の簡略に都合なのはわかります)ので、逆に、UI→SQLみたいな使い方の啓蒙になってしまってます。

まずは、HTMLレベル(UI)での、バリデーションを行うという汎用的な話を啓蒙してほしいと思います。
(その先がDBかライブラリなのか否かには関わらず。)
臭い匂いは元から立たなきゃダメですので。
責任分界として、ライブラリの手前を論じてる結果
リテラルと、外部変数の話が混じるので結果的に、話全体が無視されてると思います。

（備考：要するに”そもそもSQL識別子にエスケープが必要になるようなアプリはダメアプリで、入ってくるところ、元からダメなモノを断つ必要がある、のでそちら側から説明しないと理解できない／しない人には話が理解できない”、という指摘です。
確かにその通りで、私も”出力対策はフェイルセーフ目的である”場合がある、とも言っています。”SQL識別子エスケープ”はその良い例です。”確実に入力バリデーション”していれば、”変数”のSQL識別子をエスケープしなくても、SQL処理系の予約語などの細かい話を無視すれば、安全にRDBMSに出力できます。）

私：まあ、識別子エスケープだけでなくて、SQLの中には別のコンテクストもゴロゴロあるので「プリペアードクエリだけ」ではデータベースの安全性は保てないのですけどね。

「プリペアードクエリだけ使えばOK」は脆弱性ホイホイの考え方であるのは間違いないです。原理的に安全な考え方、でないので。

浅川さん：「プリペアードクエリだけ使えばOK」も、「だけ」と言ってるわけじゃなく、UI側でのバリデーション(大垣さんの言う出力バリデーションも含みますけど)されている前提で、DBに対しては、プリペアードクエリを使えばOKだと見てます。

「表を扱うプログラムを書いた事がない初心者とかライブラリ的なコードを書いた事がない初心者ばかり、ということでしょうか。」
FrameWorkや、CMSしか使ってない奴だらけ

そう言う意味では、知らなくてもいいので考えないと言う問題は逆にありますね。

私：私も基本プリペアードクエリを使いましょう、とは言っています。

それがコード検査をすると、先に書いたようにある程度の規模のコードだと、ほぼ生SQLがでてくるんです。
で、そこで「エスケープ要らない」と思っている開発者がいろいろと。。。。
なので「よく使う」かどうかは別として、基礎知識として持ってないとダメ、という話です。
基本／基礎機能なので。

浅川さん：そこが難しいところですねえ。

私：JavaやC#だけ！のプログラムなら、別にバッファーオーバーフローを基礎知識として持っていないくても何とかなります。
でもJNI（JavaからC/C++関数を呼ぶ）とかunmanagedライブラリ（C#からC/C++関数呼ぶ）とかを使うと、絶対に必要な知識です。

JavaやC#くらい、厳しく使いづらいように、しているとまだ良いのですが、SQLはカジュアルに環境を越えていろいろできる＆いろいろしたくなるモノなので。。。

浅川さん：SQLって、DBオペレータが、直接叩く会話型UIですからねえ。

入力処理時点で”SQL識別子”のように、どのような値が来るか分かりきっている（テーブル名やカラム名は任意入力ではない）モノは”入力バリデーションしていれば”出力時の安全性も保てます。しっかり入力バリデーションしている人だったら「SQL識別子をエスケープする必要がありますよ」と指摘しても話が平行線で終わりは見えないでしょう、という指摘でした。

確かにその通りで入力バリデーションをしていれば、SQL識別子のエスケープが無くても問題は発生しません。ただ、セキュアコーディングの原則では「入力対策と出力対策は独立した対策」なので、原則に従うと入力バリデーションの有無に関わらず、出力を無害化（エスケープ／エスケープが要らないAPI／バリデーション）を行うことになります。

現実のWebアプリが「しっかり入力バリデーションしているか？」というと正反対、不十分すぎるバリデーションしかないのが現状です。

これは、バリデーションは大別して3種類あること、バリデーションですべきこと、の理解が進んでいないことも大きな原因だと感じています。

まとめ

ひとつひとつ、ここの構造／コードはこういう理由で良くないコード／アンチプラクティスですよ、と指摘するのは大変な労力が必要です。基本的に悪い物を見つけ出して潰していく「ブラックリスト型」の作業は大変な労力（例えば、最近のブログだけでも、エンジニアなら分かる文字エンコーディングバリデーションの必要性、 「脆弱性を局所的に潰す」はアンチプラクティス、ソフトウェアはセキュリティの原理・原則から間違っている「入り口ノーガード設計」のままで良いのか？）が必要です。それでも積み上げて行けば、ベストプラクティスに近づいていく、とも言えるのですが無駄が多過ぎます。

「これはここがダメ」とアンチプラクティスを潰していくより、遥かに容易なのは原則に基づくベストプラクティスを知り、それを実践する方法です。この方が効率的かつ効果的です。

つまり、セキュアコーディングの原則、で考えて作るのが余計な無駄を省き、より安全なソフトウェアを作る近道です。

参考：そもそもエスケープとは何なのか？

最初に紹介した

“「安全な静的SQLの発行方法」を開発者に啓蒙すればいいだけだ。つまり

プリペアドクエリでSQL発行は行うこと
この場合変数のバインドは必ずプレースホルダを用いること。SQL文の文字列操作は禁止であり、これほどシンプルなことも無いだろう”

入力バリデーションを完璧に実施している場合なら通用する、とも言える考え方です。この方は「入力バリデーションを完璧に実施」しているのかも（？）知れませんね。（動的SQLを書かなければ良い、とも書かれているので”識別子が変数になるSQLを書いた事がない”可能性の方が高いですが。¹）

とは言っても、セキュアコーディングの原則では「入力対策と出力対策は独立した対策」なので出力時のSQL識別子のエスケープかバリデーションは必須要求事項です。ISMSやPCI DSSに準拠するプログラムなら要求されます。

参考：完全なSQLインジェクション対策

多くの開発者がセキュアコーディングの原則を知らないのはセキュリティ専門家の責任も大きいです。違うものをセキュアコーディングと啓蒙していたら開発者が知らなくても当然でしょう。

参考：IPAは基礎的誤りを明示し、正しい原則を開発者に啓蒙すべき

本当のジョブセキュリティ

話題にしたブログの最後では

“何故氏がそこまで「考えすぎた」のか、「開発者への静的SQLの啓蒙」という考え方をしなかったのかは未だに不明だ。
だが「正しい静的SQLの発行方法」を理解していれば、これまでのような不可思議なほどの「エスケープ対策押し」はあり得なかったのでは無いか。

結論。
大垣氏は簡単な静的SQLよりも複雑でテクニックも必要なエスケープを推奨しすぎると「ジョブセキュリティ」の疑惑をかけられかねないので、もう少し論理的になった方がいい。”

と括っていますが、実際にコード検査をすると”識別子エスケープ漏れ”があり、「仕事」としてそれを指摘しています。コード検査をしている私のジョブセキュリティを第一に考えるなら、黙ってアンチプラクティスを放置して「ここが悪いですね」と指摘し「そんな問題があったのか！」と感嘆されながら仕事をするのが最良の戦略であることは明らかです。私のブログを見れば一目瞭然ですが、考え方／方法として確実に安全なコード、コード検査で修正依頼にならないコード、を啓蒙しています。もう少し論理的になった方が良い人に「もう少し論理的になった方がいい」と言われても、です。

本当のジョブセキュリティ、とは外部からWeb脆弱性診断や”弱い入力バリデーション機能”のWAF²を販売しながら、セキュアコーディング第一原則の入力バリデーションを「入力バリデーションは要らない／セキュリティ対策とは言えない／効果的な対策でない」と啓蒙しながら販売すること、セキュアコーディングの第七原則は”入力バリデーションなどとは無関係に出力の無害化をする”ですが「SQL識別子のエスケープは必要ない」、などと啓蒙しながら販売することを言います。