2014共通表式(共通テーブル式)共通表式はSQL:1999で標準化された機能である。PostgreSQLの文書では「その問い合わせのみに存在する一時テーブルを定義するもの」とある。以下のようなテーブルを考える。supは供給業者、areaは地域、amountは売り上げ金額である。52各業者について、地域、売り上げ金額に加えて地域における金額ベースのシェアを表示したい場合、共通表式を使うとSQLは以下のようになる。10上記の場合WITHを使わなくても同じ結果を得るSQL書くことはできる。WITHを使うことによるメリットは以下が考えられる。副問い合わせが複雑な場合にはWITHを使った方がSQLが理解しやすくなる場合があることである。同じ副問い合わせが複数出現するような場合はSQLの記述量の削減になる。いう構文で再帰的な問い合わせができる。これはツリー構造のデータに対処するのに使用される。いうことらしいが、よくわからない^^;下記のSQLのように、先に定義したWITH問い合わせを後のWITH問い合わせ内で使うこともできる。共通表式を使ったSELECTはできたが、UPDATEはできなかった。9.2.4のドキュメントにはINSERTとUPDATEもできると書いてあった。なんじゃこりゃと思っていたが、リファレンスを見たらこれはキャストの記号だった。以下の2つは同じこと。now()::date結果nowdate2014-02-13また、次のようなテーブルがあるとする。abctbl1型にキャストすると、単なる1と'abc'ではなくtbl1を検索した結果という扱いになるようだ。col3というSQLを書いていた。同じ値を指定するプレースホルダが3つもあって、大した条件でもない割に長い。短く次のように書けるということを最近知った。col3)また、いくつかの値をセットにしてIN句を作ることもできる。4))上記のSQLではINの後の括弧内に値を記述しているが、次のようにサブクエリを書くこともできる。一致するという条件で検索する場合は次のようになる。'3')と一致しないという条件では次のようになる。'')))2014/02/12追記:この記事で書いた列と列の比較を複数列に一般化したものを「行式」という。SQL-92で標準化された機能である。IN以外の比較演算子にも拡張できる(実装されているかどうか別である)。行式はOracleとDB2で実装されているそうだ。PostgreSQLでも使える。文字列リテラルの問題SQLインジェクション脆弱性を快勝するにはSQLを組み立てる際にSQLの変更を防ぐことである。その具体策には以下の2つがある。プレースホルダによりSQL文を組み立てる。アプリケーション側でSQL文を組み立てる際に、リテラルを正しく構成するなどSQL文が変更されないようにする。bは完全な対応は難しく、aのプレースホルダを使用することを強く推奨する。PHPからDBを利用する場合、PEARのMDB2を推奨する。プレースホルダを利用すると先の蔵書検索のSQLは以下のようになる。holder)で、変数屋敷など可変パラメータの場所に埋め込んでおくものである。「場所取り」という意味。MDB2を利用してコードは以下のようになる(DBはPostgreSQL)。第2引数の配列でプレースホルダの方を指定する。切断?>プレースホルダプレースホルダには2種類の方式がある。静的プレースホルダプレースホルダを含むSQLがそのままDBエンジンに送られ、DBエンジン側でコンパイルされる。次にバインド値がDBエンジンに送られてDBエンジン側で値を当てはめられた後にSQL文を実行される。バインド値を入れる前にコンパイルされるのでSQLインジェクション脆弱性は原理的に発生ない。動的プレースホルダアプリケーションのライブラリ側で値をバインドしてからDBエンジンに送られる。リテラルは適切に処理されるので処理系にバグがなければSQLインジェクション脆弱性は発生しない。原理的にSQLインジェクションの可能性がない静的プレースホルダ方式の方が安全である。動的プレースホルダの実装の問題でSQLインジェクション脆弱性が発生した例がある。検査する場合はこれらの文字をエスケープする必要がある。このエスケープを怠ることとSQLインジェクションは別物だが混同している人が多い。自体を検索したい場合はESCAPE句を指定してWHERE句はいかのようになる。'#'DBエンジンによってエスケープが必要な文字は異なる。エスケープの必要がある。文字列リテラルの問題SQLでは文字列リテラルはシングルクウォートで囲む。文字列リテラル中にシングルクウォートを含めたい場合はシングルクウォートを重ねてエスケープする。パラメータとして渡した場合に以下のようなSQLが組み立てられる。author='O'Reilly'"O"の後ろでシングルクウォートで文字列リテラレルが終了され、後続の"Reilly'"が文字列リテラルをはみ出した状態となる。このはみ出した部分に意味を持つ文字列を入れるのがSQLインジェクション攻撃である。数値項目に対するSQLインジェクション数値リテラルに起因するSQL脆弱性もある。スクリプト言語の多くは変数に方の制約がない。例として以下のSQLを考える。$ageには数値が入ることを想定している。$ageここで変数$ageに以下の文字列が入った場合、SQLインジェクション攻撃となる。employeesこの場合、組み立てられるSQLは以下のようになるり、表employeesが全削除される。テクノポップのカリスマ。