本当にプリペアードクエリだけを使っていますか？

Computer, Database, Development, PHP Security, Programming, Secure Coding, Security 9月 7, 2017 #MySQL, #PostgreSQL, #SQLインジェクション, #アンチプラクティス, #エスケープ, #セキュアコーディング技術
(Last Updated On: )
'SELECT '.pg_escape_idetifier($_GET['col']).' WHERE '.pg_escape_identifier('tbl').' ORDER BY '.pg_escape_idetifier($_GET['col'])

SQLクエリにはプリペアードクエリを使いましょう！と言われて久しいです。私もプリペアードクエリを積極的に使うべきだと考えています。

  • 多くの場合、速い
  • SQLパラメーターを分離して書くので「ついうっかり」が起こりにくい
    • 特に初心者は「ついうっかり」が多い

しかし、「プリペアードクエリだけを使っていれば良いので、エスケープは要らない」という意見には賛成できません。なぜ賛成できないのか？コードを見れば分かります。

何年か前に議論になった話題です。自分のエントリを検索して、たまたま見つけたのですが物がありました。

例えば、入力バリデーションなしで以下のようなクエリは絶対に安全に実行できません。

$result = pg_query_params('SELECT '.pg_escape_identifier($_GET['col]). ' FROM '.pg_escape_identifier($_GET['table']). ' WHERE id = $1', [$_GET['id']]);

こんなクエリをそのまま書く人は居ませんが、プリペアードクエリ”だけ”ではインジェクション対策にならない事はSQLを知っていれば学生でも理解ります。

特定カラムの抽出／ソート（これはエスケープでぼほOK）、テーブル指定をするクエリは当たり前に存在します。

プリペアードクエリとダイナミッククエリ

この話をする前にプリペアードクエリとダイナミッククエリについて紹介します。

長いので結論を書いておきます。プリペアードクエリを使うから安全になるのではありません。

静的クエリ（注意：プリペアードクエリ ≠ 静的クエリ）だけを使っている時だけは安全になります。

これ以外は”プリペアードクエリだけでOK！”、”エスケープを教える？とんでもない！”、これだとインジェクションされてしまいます。

プリペアードクエリ（静的クエリ）

  • プリペアードクエリ（静的クエリ、パラメータ化されたクエリ、プレイスホルダを使ったクエリ）

SQL文とSQL文の引数（変数／パラメーター）を分離して実行する方式です。PostgreSQLの場合、以下のように、静的なSQL文と引数を分離して実行します。

PREPARE fooplan (int, text, bool, numeric) AS
    INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

PREPARE usrrptplan (int) AS
    SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
    AND l.date = $2;
EXECUTE usrrptplan(1, current_date);

これらのSQL文の定義を見ると

INSERT INTO foo VALUES($1, $2, $3, $4);
SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
AND l.date = $2;

のように引数（変数／パラメーター）に置き換える場所の定義のみがあり、コードに記述された”静的な文字列”だけで構成され、SQL文が変ることがありません。コードの一部として記述され変更できない静的な文字列で構成されているので”静的クエリ”と呼ばれることもあります。※以後、プリペーアドクエリと静的クエリが区別される場合があります。

上記の例の場合、SQL文が静的で固定されているので実行されるSQL命令が変ることがありません。SQL文が仕組みとして変ることがない、なので不正なSQL命令を挿入するSQLインジェクション攻撃もできなくなります。

まとめると、SQL文の生成部分に変数がない物が静的クエリです。

ダイナミッククエリ（動的クエリ）

  • 動的クエリ（文字列連結で動的に生成されたクエリ）

SQL文を生成する際に、SQLクエリ文字列をコードで文字列連結してSQL文を生成する方式です。SQL文の生成をSQL命令とSQL識別子、SQLクエリ引数を文字列で連結します。

SQL識別子、SQLクエリ引数などが変わると生成されるSQL文が動的に変わります。この為、”動的クエリ”と呼ばれています。

動的クエリの場合、当然ですが、生成されるSQL文は動的に変化する変数によって動的に変化します。※以後、動的クエリ、と呼びます。

$sql = "INSERT INTO foo VALUES(". $int .", ". $text .", ". $bool .", ". $numeric .");";
$sql = "SELECT * FROM users u, logs l WHERE u.usrid=". $userid ." AND u.usrid=l.usrid
    AND l.date = ". $date .";";

このように変数をSQL文に埋め込むと、変数の値によってクエリが動的に変化します。上記の例だと、変数にSQL命令や余計な識別子／パラメーターが含まれていると、余計なSQL命令が実行されてしまいます。SQLインジェクションができる、ということです。※ 上記はエスケープがないのでインジェクションに脆弱です。

まとめると、SQL文の生成部分に変数がある物が動的クエリです。

本当に静的クエリだけを使っていますか？

プリペーアドクエリのAPIを使っているから静的クエリになるのではありません。クエリが静的か、動的か？はSQL文を構成する文字列に変数が含まれるか含まれないかで決まります。

重要なことなのでもう一度繰り返します。

  • SQL文の生成部分に変数がない物が静的クエリ
  • SQL文の生成部分に変数がある物が動的クエリ

多数のコード検査をしていますが、ある程度の規模のアプリケーションで静的クエリだけで作られたアプリケーションは見た事がありません。そのアプリケーションが全面的にプリペアードクエリを使っている場合でも、です。

プリペアードクエリを使っている ≠ 静的クエリ

プリペアードクエリAPIを使っているだけでは静的クエリではありません。以下のようなコードは全て動的クエリです。

識別子が動的

pg_prepare('myquery', "SELECT ". $target_colum ." WHERE id = $1;");
pg_execute('myquery', [$id]);

SQL命令が動的

pg_prepare('myquery', "SELECT col WHERE id = $1 ORDER BY ". $order ";");
pg_execute('myquery', [$id]);

SQL文の一部が動的

$where_cond = generate_complex_where($some_param);
pg_prepare('myquery', "SELECT col WHERE ". $where_cond .";");
pg_execute('myquery', []);

上記はPHPのPostgreSQLモジュールのプリペアードクエリAPIの

  • pg_prepqre()
  • pg_execute()

を使ったプリペアードクエリですが静的クエリではありません。

「プリペアードクエリを使いましょう」だけでは安全にならない

「プリペアードクエリを使いましょう」だけでは安全にならないです。理由は簡単です。

現実のアプリケーションは、プリペアードクエリAPIだけを使っていても、動的なクエリだらけだからです。

余程シンプルなアプリケーションでない限り、”静的クエリだけ”を使っているアプリケーションは見た事がありません。ある程度の規模を越えるアプリケーションの場合、「SQL文の一部が動的」となっている物がほとんどです。

自分が見ているコードにはないけど？と思っている場合でも、データベースレイヤーのストアードプロシージャーで動的クエリを使っていたりします。変数として返ってきた”SQLの部品”を動的に組み込んでいたりします。

プリペアードクエリだけを使ってSQLクエリの安全性を完全に保証するには

  • SQL文を構成する文字列に１つも変数を使ってはならない

というルールも一緒に適用しなければ不可能です。このルールがない限り「SQLにエスケープは必要ない」とする意見は大きな勘違いです。（不十分な部分はこちら1）

SQL命令を構成する文字列（プリペア文）に一切変数を使わないで開発しなさい、と言われて困るアプリケーションは多数です。

  • 識別子が変数の場合、エスケープするしかない。
  • ORDER BY DESC/ASC の”DESC/ASC”などのSQL語句が変数の場合、ほぼ同じプリペア文を2つ以上作ることになる
  • サブクエリなど、複雑な条件を抽出のクエリ文生成を関数などで抜き出すことが出来なくなり、プログラムの見通しも、SQLの可読性も落る

SELECT ‘.pg_escape_identifier($_GET[‘col’]). ‘ FROM ‘.pg_escape_identifier($_GET[‘tbl’]).’ ODER BY ‘.pg_escape_identifier($_GET[‘sort_col’])

というクエリを考えてみてください。バリデーションもエスケープも必須であることが解るはずです。抽出カラムの指定、ソートカラムの指定はRDBMSアプリケーションでは”当たり前”に在ります。

注：2

ジョブセキュリティ？！？

私がSQLセキュリティの要素としてエスケープとバリデーションの重要性を強調しているのは、プリペアードクエリばかりを使っていても動的クエリは多数使われており、そこでSQLインジェクションに脆弱になるアプリケーションが存在し「開発者はプリペアードクエリで大丈夫！」と思っているケースが多いからです。

「開発者はプリペアードクエリだけ知っていれは十分！」と勘違いしているようです。（この議論に対するコメントの様です）”ジョブセキュリティ”というキーワードがあったので、この項目を書いています。ツイッターでの議論が話題ようです。古いことなの良く覚えていないですが、この議論は教育論についてでSQLの仕様や問題点などが論点に上がらなかった、ことが解りづらくさせているようです。

“ジョブセキュリティ”とは私の”ジョブセキュリティ”のことだと理解しました。私も仕事の１つとしてソースコード検査をしています。絶対の自信を以て以下が言えます。

  • 「プリペアードクエリだけでOK！エスケープなど要らない！教える必要もない！」と啓蒙すると確実にセキュリティ業者のジョブセキュリティになります。（＝脆弱性が増える）
  • 「入力バリデーションはセキュリティでない」「仕様である」「効果的でない」「脆弱性を隠す対策で悪手だ」と啓蒙すると確実にセキュリティ業者のジョブセキュリティになります。（＝脆弱性が増える）

なぜなら、既に説明した通り、ある程度の規模のアプリのならプリペアードクエリ”だけ”を使っていても多数の動的クエリが実行されているからです。（入力バリデーションも必要です。長くなるので省略）

上記のブログを書いた方は、コード検査をした場合の指摘事項を増やす為のジョブセキュリティだと思えたのかも知れませんが正反対です。指摘しなくても良いようにする為に注意喚起をしています。

OSSではPHPのコミッターをしていますが、ユーザーが知らなくても安全になるような変更を自身で行ったり提案しています。

  • セッション管理の細かいリスクを知らなくても安全に利用できるようにする変更／提案
  • メールAPIの仕様を知らなくても安全に利用できるようにする変更／提案
  • シリアライズを安全に利用できるようにする提案
  • HKDFの有り得ないパラメータ順序の変更提案
  • 契約プログラミングサポート機能の提案
  • 入力バリデーション機能の提案
  • 大小含め様々なバグフィックス

セッション管理はWebセキュリティの要なのです。7年越しで修正した物もあります。今もよく解っていないPHP開発者の一部から「こんな機能は要らないだろ！？」と半分呆れられながらも、良く解っていない開発者が理解するまでは時間が必要だから仕方ない、と継続的にセッション管理の安全性向上の提案と実装を進めています。（PHPに限らず、世の中のセッションマネージャーは皆さんが思っているより堅牢ではないです。かなり脆弱、というより脆弱すぎます）

今現在もHKDFのあり得ないAPIの変更を、よく理解っていない方から逆ギレにされつつ提案しています。今のAPIのままだとコード検査でHKDFの利用を見る度に「脆弱な利用方法です」と指摘しなければならないかも知れません・・・

自分のジョブセキュリティどころか、ジョブ破壊を自ら率先していますね（苦笑）それも無償のボランティア、修正したり改善したりしても宣伝効果などもほぼ無し、勘違いしている開発者に逆ギレされ「話は聞く必要がない！」と言われながら。

セキュリティ検査などのジョブセキュリティを考えるなら「効果的な対策」や「必要な対策」は黙っておいて（もっとやるなら、これらは効果的でない、不必要と言っておいて）ソースコード検査をする度に「ここは攻撃できますね」いう方が良いでしょう。

効果的な対策、セキュリティ標準／ガイドラインが推奨する対策であっても、多くの人に勘違いされ出鱈目な批判に晒されながら啓蒙するのは損すぎるだろ？と自分でも思います（苦笑）

セキュリティ業者が困るセキュリティ対策とは？

何度も繰り返し説明していますがもう一度。

セキュリティ業者が困るセキュリティ対策は「セキュアーコーディング」の基本を実施されることです。 これは、当たり前ですが、攻撃者が困る対策でもあります。

具体的には

  • ゼロトラストの原則 ｰ 検証／保証されたモノ以外は絶対に信頼しない（安易に保証されていると仮定しない。入力と出力処理の中で確実に保証する）

これを踏まえた上で、入力と出力の対策を行われるとセキュリティ業者は困ります。

入力対策

全ての入力は完全にバリデーション（検証し妥当性を保証）する。入力検証は”入力のコンテクスト”が重要である。ソフトウェアが正常に動作するには、ソフトウェアに対する入力が妥当であることを保証することが欠かせない。

  • 全ての入力を長さ、利用する文字、形式、範囲、文字エンコーディングを可能な限り厳格にバリデーション（検証）し、入力として妥当であることを保証する

”入力として妥当であることを保証″には真正性の検証も含まれます。真正性の検証とはWebのリクエストの場合、CSRF対策になります。

出力対策

全ての外部システム（この中にはライブラリが含まれる場合がある）に対する出力を完全に無害化したこと保証する。出力無害化は”出力先のコンテクスト”が重要である。”出力先のコンテクスト”は出力先が同じでも複数の種類があることに注意する。例）SQL識別子、SQLパラメーター、SQL語句

次に説明する出力対策は”出力先のコンテクスト”が間違っている場合、無害化の意味を成さない場合があることに留意する。

上記を踏まえた上で、以下の何れか又は組み合わせで無害化する。

  • 全ての変数をエスケープして出力する（出力コンテクストが重要！）
  • 全ての変数をエスケープが必要ないAPIを用いて出力する（これもコンテクストが重要で意識しないと脆弱になる場合がある）
  • 全ての変数が出力先に対して無害であることをバリデーション（検証）／保証して出力する

出力対策はデータ型が改修などにより変更される、といった場合でも確実に無害化できるモノでなければなりません。

上記の入力／出力対策がセキュリティ業者が困る対策です。

実際、こういうコードになっている場合、ソースコード検査をしても攻撃可能性が高いとする指摘事項の8割9割以上が無くなります。ブラックボックス検査（外部からつついてソフトウェアを検査）をするセキュリティ業者、攻撃を行おうとしている犯罪者も9割以上の脆弱性がなくなり困ることになります。

たったコレだけでセキュリティ業者も攻撃してくる攻撃者も、脆弱性が大幅になくなり、非常に困ったことになります。

私はこの「セキュリティ業者も攻撃者も非常に困る」対策を長年かつ一環して提唱してきています。しかし、セキュリティ業界に居るにも方でも、上記の困る対策を「セキュリティ対策でない」「効果がない」「効果がないどころか危険」とアドバイスしたりしています。それを聞いて開発者が納得してしまっている状態を見ると、説明力の足りなさに忸怩たる思いがあります。

セキュアーコーディングの考え方は、私がではなく、CERT、OWASP、MITRE、SANS、ISOなども長年提唱してきていることではあるのですが・・・（最近、やっとIPAも参入しました。やっと、です）

まとめ

ソースコード検査の目標は「指摘事項が無くなる」＝「脆弱なコードが無くなる」ことです。それを目指して様々なアドバイスを行っています。この件もその一環です。「SQLインジェクション対策はプリペアードクエリでOK」と勘違いで脆弱になっているコードは少なくありません。

プリペアードクエリ ≠ 静的クエリ です。

そして動的クエリの利用率はかなり高いです。（APIがプリペアードクエリでも！）

これを解っている人なら、私がSQLインジェクション対策教育にエスケープが必須！としたアドバイスを無謀だと指摘したり、異論を唱えないでしょう。

プリペアードクエリ＝静的クエリ、のコードしか見た事がないなら、大きな規模のアプリを作っていないか、ストアードプロシージャーなどで隠蔽されているだと思います。隠蔽されているプロシージャーの中身を解っていれば異論はないはずです。

「指摘事項が無くなっていく」ソースコード検査とはどんな物か？ソースコード検査を頼んでみたい方はご連絡頂いても構いません。歓迎します！3

参考：

    1. 実はこれだけでも不十分です。今のSQLデータベースはSQL言語以外の物もサポートしているからです。正規表現、XML、JSONなどSQLとは別のテキストインターフェースをサポートしています。プリペアードクエリで静的クエリであっても、正規表現、XML、JSONなどのコンテクスト用にエスケープしないと、これらのコンテクストにインジェクションされます！どうするのか？エスケープかバリデーションですね。 ↩
    1. バリデーションでも構いませんが、素直に作るなら出力時点ではエスケープでしょう。入力バリデーションで形式、モデルなどのロジックでは妥当性をチェック、出力ではエスケープをします。 ↩
  1.  コードのセキュリティ検査を外部に委託するとリスク移転ができるメリットがあります。検査はあそこに依頼したので、と。なので責任重大です。コード検査に興味がある方はこちら。 ↩

投稿者: yohgaki