カテゴリー
Computer Database Development Programming Secure Coding

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

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

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

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

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

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

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

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

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

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

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

カテゴリー
Computer Database Development PHP Security Programming Secure Coding Security

出力対策”のみ”のセキュリティはアンチプラクティス

「しっかり出力対策”だけ”するのがセキュリティ対策のベストプラクティス」とする考え方1があります。しかし、これはベストプラクティスどころかアンチプラクティスです。

アンチプラクティスをベストプラクティスと勘違いしている限り、満足のいくセキュリティ対策(=リスク管理)は不可能です。セキュリティ対策は総合的なリスク対策です。「これ”だけ”やれば良い」とするセキュリティ対策は大抵アンチプラクティス2です。

※ 今まで出力対策”だけ”がセキュリティ対策だと勘違いしていた方には確証バイアスが働きやすいようです。論理的/構造的にどうすればリスクを効果的に削減できるのか?を考えると理解るはずです。

カテゴリー
Computer Database Development Linux

PostgreSQLを使うならZFS

PostgreSQLを使うならZFSで決まりです。数値で明らかです。ZFS以外を使うのは論外なくらいの性能差があります。

カテゴリー
Computer Database Programming

PostgreSQL 10のICUコレーションとJIS X 4061

PostgreSQL Advent Calendar 9日目用のエントリです。

PostgreSQL 10のICUコレーション(照合順序)サポートの概要と基本的な使い方は以下のエントリに記載しています。ICUコレーションの使い方は以下を参照してください。

PostgreSQL 10のICUコレーションを使うと日本語を普通にソートでき、更に文字順序までカスタマイズできる

今回は日本語ソート順のJIS規格である JIS X 4061-1996にどの程度対応しているのか確かめてみます。

カテゴリー
Computer Database Development

PostgreSQL 10のICUコレーションを使うと日本語を普通にソートでき、更に文字順序までカスタマイズできる

PostgreSQL 10からICU(International Components for Unicode)のロケール/コレーションがサポートされました。

これまでサポートされてきた、libcのja_JPロケールの貧弱な日本語ソート機能とは比べ物にならないくらい高機能な文字比較をサポートしています。日本語や他の言語での照合順序を柔軟に変更できます。

  • マトモな日本語ソート順でソートする(かなり重要)
  • 数字を後にソートする
  • 大文字を先にソートする
  • 仮名を先にソートする
  • 自然ソートする
  • これらをまとめて特別なソート順にする

といったことがPostgreSQL 10から行えます。

カテゴリー
Computer Database Development PHP Security Programming Secure Coding Security

セキュリティ対策が論理的に正しいか検証する方法

全てのセキュリティ対策は緩和策だと考えるべきです。これは個々の対策が完全であるか検証することが容易ではないからです。例えば、SQLインジェクション1つとっても本当に完全であるか?検証することは容易ではありません。プログラムが本当に思っているように動作するのか?検証する研究は、まだまだ研究段階です。

しかし、容易ではないからといって諦める訳にもいきません。不完全であっても形式的な論理検証は容易にできます。

カテゴリー
Computer Database Development PHP Security Programming Secure Coding Security

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

'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)、テーブル指定をするクエリは当たり前に存在します。