Home > 保険システム開発室NEWS > SQLを外出しの設定ファイルにする理由

SQLを外出しの設定ファイルにする理由

2008-11-10 08:04

SQL文字列をハードコーディングせずに設定ファイルに外出しにしているシステムをよく見かけます。 弊社が独自に開発して使用しているWebフレームワーク及びバッチフレームワークでも同様にSQL文字列をソースコードと分離させて、SQLはXML形式の設定ファイルに記述するようになっています。

SQLとプログラムソースを分離させる理由は、開発者によって意図がそれぞれあると思いますが、 少なくとも弊社のフレームワークで言うと理由は下記の3つです。

  • SQLコマンドインジェクション対策
  • SQLの可読性・メンテナンスビリティの向上
  • SQLの一括調査対応

SQLコマンドインジェクション対策

SQLとソースコードを分離させる主目的はSQLコマンドインジェクション対策の徹底です。 上記に3つ理由を挙げましたが、主目的はこれで残り2つは副次的なメリットになります。

SQL定義ファイルの例 弊社のフレームワークでSQLを実行するには、まず右図のようなXMLファイルに定義します。 通常SQLを実行する場合、条件の値はその時々によって変わりますのでその変数の部分を「?」で記述しておき、 パラメータとして値を渡すとフレームワークはDBにSQLを実行し結果を返す仕組みになっています(下図参照)。

外部から渡されたパラメータをSQL文字列の一部とする際には、 必ずエスケープしなければならないというのが、SQLコマンドインジェクション対策のセオリーです。 弊社のフレームワークでは下図のような仕組みとなっており、①で設定したパラメータをフレームワーク側でエスケープしています。
※弊社のフレームワークはJavaで出来ており、内部的にはこの仕組みはPreparedStatementを使用しています。

SQLを実行させる際のフレームワークの仕組み

SQL定義ファイルに定義された変数の部分を変換するには必ずフレームワークにパラメータを渡す必要があり、 フレームワークはクライアントから渡されたパラメータを必ずエスケープします。
つまり弊社のフレームワークを使っている限り、 プログラマがうっかりSQLコマンドインジェクション対策をし忘れるということがありません。
静的なSQL文とパラメータを分離することにより、フレームワークは例外なく外部から渡されたパラメータをエスケープできるのです。 これがSQL文字列とソースコードを分離する理由です。

項目が多数存在する検索画面 しかし、この方式には一つ問題があります。
事前にSQL文字列として定義しておくには、非常に開発工数を要する場合があります。 例えば右図のように複雑な検索条件を指定出来る画面の場合、事前に全ての検索パターンのSQL文字列を定義ファイルに定義しておくことは困難です。 通常は入力された条件を基に動的にプログラムでSQLの検索文を生成します。

そのようなケースを考慮し、例外として弊社のフレームワークは動的に生成したSQL文字列を実行できる仕組みが用意されています。 このメソッドは推奨されないメソッドとして定義されており、必ず第三者によるソースコードレビューをするという運用にして SQLコマンドインジェクション対策漏れを防止するようにしています。

基本的にはSQLコマンドインジェクションは、オンラインから悪意のあるユーザがパラメータを入力する為、 オンラインのWebアプリのみ必要なもので、バッチ処理には不要と考えていました。 実際、弊社のバッチフレームワークの初期のものはオンラインと異なりSQLは定義ファイルではなくソースコードに埋め込んでいました。

しかし、バッチ処理だからSQLコマンドインジェクションによる事故は絶対に発生しないとは言い切れず、 また弊社のWebフレームワークとバッチフレームワークに互換性を持たせるように改良された為、 現在は弊社のバッチフレームワークもSQLは外出しの設定ファイルに記述するようになっています。

SQLの可読性・メンテナンスビリティの向上

SQL文字列を設定ファイルに外出しにする事で、SQLの可読性が向上しメンテナンスしやすくなるという副次的な効果があります。

SQL文を定義したソースコードとSQL定義ファイル

左上図の通りSQL文字列をソースコードに含めると"+"演算子で文字列を連結する必要があります。 このようにソースコードに記述されているSQLをSQL*Plus等のツールを使用して直接データベースに実行したい場合は この演算子やダブルクォーテーションを取り除く必要があり面倒です。 定義ファイルに記述されていれば、ほぼそのままコピー&ペーストで使用できます。
逆に開発する際にも、定義ファイルであればSQL*Plus等のツールで作ったSQL文字列をそのまま貼り付けるだけで JavaのStringに変換する必要がありません。

SQLの一括調査対応

SQLを外出しの設定ファイルにする理由として、これを一番の理由に挙げる開発者がいます。
つまり、後からSQLだけを一括して調査しやすくしたり、また万が一データベースのテーブル名が変更された場合等も一つのファイルだけを一括置換するだけでよいからというものです。

確かに過去のテスト工程で、あるSQLの結合文が好ましくないことが発覚し同様のSQL文が無いか一斉に調査した事がありました。 この時はSQL文が外出しのファイルで一元管理されていてなかなか便利だ感じことがあります。
しかし、スキルの低い開発者が多いプロジェクトならともかく、 私たちの現場ではこのようにSQLだけを一括して調査するケースはそう滅多にあることではありません。 データベースのテーブル名が変更されるなんてことも滅多にありません。そんなことされたら一大事です。

というわけで3つ挙げた目的のうち、後の2つはこじつけがましいと思われるかもしれませんが、 ここまで書いて私も若干こじつけがましいと思いました。

補足

この記事の続きとして、2008年11月26日にSQLを外出しファイルにした場合の注意点という記事も書きました。SQLとソースコードを分離する際の注意点を記述しておりますので、是非御覧ください。

Posted by T.S

このページの上部へ