列の値を使って日時に「+n日」する

テーブルtblにtimestamp型の列dtと文字列型の列nがある。nは文字列型だけど入ってる値は"1"とか"12"のような数字のみからなる文字列。
nの値を使って「dt＋n日」という計算をしたい。例えばdtの値が "2015-01-01 12:30:45" でnの値が "3" ならば
"2015-01-01 12:30:45"＋3日で "2015-01-04 12:30:45" という結果が欲しい。
最初、以下のようにしたが構文エラーになった。

SELECT dt + interval (n || ' days') FROM tbl

次のようにしたら欲しい結果が得られた。

SELECT dt + CAST(n || ' days' AS interval) FROM tbl

もしnが整数型ならこうかな。

SELECT dt + CAST(TO_CHAR(n, 'FM9999') || ' days' AS interval) FROM tbl

参考ページ：
Postgresでの時間加算select current_timestamp + interval '1 hours';... - Yahoo!知恵袋

TIMESTAMP WITH TIME ZONE型

既存のWEBアプリケーションに対して国際化対応というか現地時間対応みたいなことをする可能性が浮上してきた。国際化なんて全く考慮してなくて、テーブルの日時のカラムは TIMESTAMP WITHOUT TIME ZONE 型だ。
TIMESTAMP WITHOUT TIME ZONE 型（以下、TZなしと記述） を TIMESTAMP WITH TIME ZONE 型（以下、TZありと記述）にキャストしたらどうなるか、やってみたメモ。
キャストの書き方は、標準SQLで書くと長いのでPostgreSQLの方言で記述。

dbtest=> \d t_test
              テーブル "public.t_test"
  カラム   |             型              |  修飾語
-----------+-----------------------------+----------
 test_id   | numeric                     | not null
 contents  | character varying(20)       |
 test_date | timestamp without time zone |
インデックス:
    "t_test_pkey" PRIMARY KEY, btree (test_id)

dbtest=> SHOW timezone;
  TimeZone
------------
 Asia/Tokyo
(1 行)

dbtest=> SELECT * FROM t_test;
 test_id | contents |      test_date
---------+----------+---------------------
       1 | abcde    | 2014-07-14 07:00:00
(1 行)

dbtest=> SELECT test_date::timestamptz FROM t_test;
       test_date
------------------------
 2014-07-14 07:00:00+09
(1 行)

dbtest=> SELECT test_id FROM t_test WHERE test_date::timestamptz = '2014-07-13 22:00:00+0000'::timestamptz;
 test_id
---------
       1
(1 行)

dbtest=> SELECT test_id FROM t_test WHERE test_date::timestamptz = '2014-07-13 15:00:00-0700'::timestamptz;
 test_id
---------
       1
(1 行)

どうやらTZなしをTZありにキャストするとDBに設定されたタイムゾーンの時間となるらしい。
上記の例なら、TZなしの 2014/07/14 07:00 をTZありにキャストすると

2014/07/14 07:00+0900(JST)
= 2014/07/13 22:00+0000(UTC)
= 2014/07/13 15:00-0700(PDT)

という風に変換される。
＃上に書いた3つの時間はすべて同じ時間。表現の仕方はタイムゾーンの数だけある。

WEBアプリケーションの現地時間対応をもしするとしたら、テーブルのTZなしのカラムをTZありに変更し、合わせてコードも修正するのがまともなやり方なんだろう。
でもそんな工数は取れないだろうから、SQL文のいろんなところにキャストを入れる「なんちゃって国際化」になりそうな気がする。

PL/pgSQL

PL/pgSQLはPostgreSQLで使える手続き型言語である。OracleにおけるPL/SQLに当たる。PL/pgSQLはPL/SQLを参考に実装したらしい。
PL/pgSQLの構造は下記のようなブロック構造になっている。

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

コメントは2種類ある
・"--" からその行の終わりまで。
・"/*" で始まり "*/" で終わる。

例1：2012/01/01～2013/01/31までの日付をINSERTする。値は何も返さない。

CREATE OR REPLACE FUNCTION test_func_01()
RETURNS void AS '
DECLARE
  tmp date;
BEGIN
  tmp := ''2012-01-01''::date;
  FOR i IN 1..731 LOOP
    INSERT INTO t_result (id, type, date) VALUES (i, ''A'', tmp);
    tmp := tmp + interval ''1 day'';
  END LOOP;
END
' LANGUAGE plpgsql;

実行結果

dbtest=> SELECT test_func_01();
 test_func_01
--------------

(1 行)

dbtest=> SELECT * FROM t_result ORDER BY id;
 id  | type |        date
-----+------+---------------------
   1 | A    | 2012-01-01 00:00:00
   2 | A    | 2012-01-02 00:00:00
…
 730 | A    | 2013-12-30 00:00:00
 731 | A    | 2013-12-31 00:00:00
(731 行)

dbtest=>

例2：SELECTを3回実行し、その結果をまとめて返す。

CREATE OR REPLACE FUNCTION test_func_02(OUT a integer, OUT b char(1), OUT c timestamp)
RETURNS SETOF RECORD AS '
BEGIN
  FOR i IN 1..3 LOOP
    SELECT * from t_result WHERE id = i INTO a, b, c;
    RETURN NEXT;
  END LOOP;
  RETURN;
END
' LANGUAGE plpgsql;

実行結果

dbtest=> SELECT * FROM test_func_02();
 a | b |          c
---+---+---------------------
 1 | A | 2012-01-01 00:00:00
 2 | A | 2012-01-02 00:00:00
 3 | A | 2012-01-03 00:00:00
(3 行)

dbtest=>

例3：カーソルを使う。

CREATE OR REPLACE FUNCTION test_func_03(OUT a timestamp, OUT dummy integer)
RETURNS SETOF RECORD AS '
DECLARE
  cur CURSOR FOR SELECT * FROM t_result ORDER BY id;
  r t_result;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO r;
    SELECT r.date INTO a;
    RETURN NEXT;
    IF r.id = 3 THEN
      EXIT;
    END IF;
  END LOOP;
  RETURN;
END
' LANGUAGE plpgsql;

実行結果

dbtest=> SELECT * FROM test_func_03();
          a          | dummy
---------------------+-------
 2012-01-01 00:00:00 |
 2012-01-02 00:00:00 |
 2012-01-03 00:00:00 |
(3 行)

dbtest=>

複数のクエリーの結果を返す場合で、よくわからないな現象があった。
2列を返す下記の関数は特に問題なく定義できて実行もできた。

CREATE OR REPLACE FUNCTION test1(OUT a integer, OUT b integer)
RETURNS SETOF RECORD AS '
BEGIN
  FOR i IN 1..10 LOOP
    SELECT i, i+1 INTO a, b;
    RETURN NEXT;
  END LOOP;
  RETURN;
END
' LANGUAGE plpgsql;

しかし、返す値を1列にすると定義自体がエラーになる。

CREATE OR REPLACE FUNCTION test2(OUT a integer)
RETURNS SETOF RECORD AS '
BEGIN
  FOR i IN 1..10 LOOP
    SELECT i INTO a;
    RETURN NEXT;
  END LOOP;
  RETURN;
END
' LANGUAGE plpgsql;

エラーメッセージは以下。

ERROR:  OUTパラメータのため、関数の戻り値型はintegerでなければなりません。

"SELECT i" の i に "::integer" と付けても同じエラー。意味がわからない。
例3のOUTパラメータdummyはこのエラーを避けるために付けた。

参考サイト：
PostgreSQL 8.4.4文書 第38章 PL/pgSQL - SQL手続き言語
複数行複数列を返すPL/pgSQL関数 - iakioの日記 - postgresqlグループ
PostgreSQL初心者の悪戦苦闘ぶりをメモする pl/pgsql篇 | 読書と技術となんか色々のログ - 楽天ブログ

PostgreSQLで自作の関数を定義する

PostgreSQLの関数は CREATE FUNCTION で定義する。
以下、例を示す。例2、例3については説明どおりに動くか自信はないが^^;

例1：加算
IMMUTABLE はデータベースに変更をしないことを示す。
RETURNS NULL ON NULL INPUT は引数にNULLがある場合NULLを返すことを示す。

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

例2：指定された日付の月の日数を返す関数
double precision は倍精度浮動小数点。extract() の返り値がこの型なのでこの関数の返り値の型も double precision にしている。
処理の部分はBEGIN～END で処理を記述している。これはSQLではなくPL/pgSQLなので、LANGUAGEは plpgsql としている。
PL/pgSQLについては次の記事を参照。

CREATE OR REPLACE FUNCTION get_month_last_day(date) RETURNS double precision AS '
BEGIN
  RETURN extract(DAY FROM date_trunc(''month'', $1 + interval ''1 month'') - interval ''1 day'');
END;
' LANGUAGE plpgsql;

例3：指定された日付が月の最終日かどうかを返す関数

CREATE OR REPLACE FUNCTION is_month_last_day(date) RETURNS boolean AS '
BEGIN
  RETURN date_trunc(''day'', $1) = date_trunc(''month'', $1 + interval ''1 month'') - interval ''1 day'';
END;
' LANGUAGE plpgsql;

参考サイト： PostgreSQL 8.4.4文書 Ⅳ.リファレンス Ⅰ.SQLコマンド CREATE FUNCTION

PostgreSQLのPREPARE

PREPAREは事前に準備されたSQL文を作成する。SQLの一時的な関数化と考えることができる。
準備されたSQL文はセッションが切れるか割り当てを解除するまで有効である。

PREPARE name [ ( datatype [, ...] ) ] AS statement

下記の例は指定した日付以降のレコードを取得するSQLをPREPAREで準備する。

PREPARE fooplan(date) AS
  SELECT * FROM sales WHERE date >= $1;

実行には EXCEUTE を使用する。

EXECUTE fooplan('2012-10-02'::date);

準備されたSQLを破棄するには DEALLOCATE を使用する。

DEALLOCATE fooplan;

参考サイト：
PostgreSQL 8.4.4文書 Ⅳ.リファレンス Ⅰ.SQLコマンド PREPARE
Studio ODIN - blog風小ネタ集 > PostgreSQL の PREPARE

共通表式（共通テーブル式）

共通表式はSQL:1999で標準化された機能である。PostgreSQLの文書では「その問い合わせのみに存在する一時テーブルを定義するもの」とある。

以下のようなテーブルを考える。
supは供給業者、areaは地域、amountは売り上げ金額である。

sup	area	amount
A	東京	70
B	東京	30
C	東京	80
D	東京	20
E	大阪	83
F	大阪	36
G	大阪	29
H	大阪	52

各業者について、地域、売り上げ金額に加えて地域における金額ベースのシェアを表示したい場合、共通表式を使うとSQLは以下のようになる。

WITH sum_item AS (
       SELECT area, SUM(amount) AS sum_amount
       FROM suppliers
       GROUP BY area
     )
SELECT t1.*, CAST(t1.amount AS real) / t2.sum_amount * 100 AS share
FROM suppliers t1
INNER JOIN sum_item t2
ON t1.area = t2.area
ORDER BY area, share DESC

結果

sup	area	amount	share
E	大阪	83	41.5
H	大阪	52	26
F	大阪	36	18
G	大阪	29	14.5
C	東京	80	40
A	東京	70	35
B	東京	30	15
D	東京	20	10

上記の場合WITHを使わなくても同じ結果を得るSQL書くことはできる。
WITHを使うことによるメリットは以下が考えられる。

• 副問い合わせが複雑な場合にはWITHを使った方がSQLが理解しやすくなる場合があることである。
• 同じ副問い合わせが複数出現するような場合はSQLの記述量の削減になる。

"WITH RECURSIVE" という構文で再帰的な問い合わせができる。これはツリー構造のデータに対処するのに使用される。
ということらしいが、よくわからない^^;

下記のSQLのように、先に定義したWITH問い合わせを後のWITH問い合わせ内で使うこともできる。

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

2014/02/26追記
PostgreSQL 8.4.18 では共通表式を使ったSELECTはできたが、UPDATEはできなかった。
9.2.4のドキュメントにはINSERTとUPDATEもできると書いてあった。

参考文献：WEB+DB PRESS Vol.66 SQL緊急救命室 第5回

参考サイト：PostgreSQL 8.4.4文書 7.8 WITH問い合わせ

PostgreSQLの"::"はキャスト

PostgreSQLのことをググっていると、よくSQLに "::" というのが出てくる。なんじゃこりゃと思っていたが、リファレンスを見たらこれはキャストの記号だった。
以下の２つは同じこと。

CAST ( expression AS type )
expression::type

演算子の優先順位の表に載っていたからキャスト演算子なんだろうか？

キャストなしのSQL

SELECT now()

結果

now timestamp with timezone
2014-02-13 20:35:45.678+09

キャストを付けたSQL

SELECT now()::date

結果

now date
2014-02-13

また、次のようなテーブルがあるとする。

CREATE TABLE tbl1 (
  id Integer PRIMARY KEY, 
  value varchar(20)
)

キャストなしのSQL

SELECT 1, 'abc'

結果

?column? integer	?column? unknown
1	abc

キャストを付けたSQL

SELECT ((1, 'abc')::tbl1).*

結果

id integer	value character varying(20)
1	abc

tbl1型にキャストすると、単なる1と'abc'ではなくtbl1を検索した結果という扱いになるようだ。

参考文献：WEB+DB PRESS Vol.66 SQL緊急救命室 第5回

参考サイト：PostgreSQL 8.4.4文書 4.2.9. 型キャスト

SQLのIN句の色々な例

SQLで "列col1,col2,col3のいずれか1つにでも合致する" という条件で検索する場合、

SELECT * FROM tbl
WHERE ? = col1 OR ? col2 OR ? = col3

というSQLを書いていた。同じ値を指定するプレースホルダが3つもあって、大した条件でもない割に長い。
もっと短く次のように書けるということを最近知った。

SELECT * FROM tbl
WHERE ? IN (col1, col2, col3)

また、いくつかの値をセットにしてIN句を作ることもできる。下記の例では "col1とcol2が（1,3)または(2,4)" という条件である。

SELECT * FROM tbl
WHERE (col1, col2) IN ((1, 3), (2, 4))

上記のSQLではINの後の括弧内に値を記述しているが、次のようにサブクエリを書くこともできる。

SELECT * FROM tbl
WHERE (col1, col2) IN (
        SELECT subCol1, subCol2
        FROM subTable
        WHERE id > 10)

カラムid1, status1, id2, status2, id3, status3 を持つテーブルで (id1, status1)、(id2, status2)、(id3, status3) という3つの組み合わせのうちどれか1つでも ('ab012', '3') と一致するという条件で検索する場合は次のようになる。

SELECT * FROM tbl
WHERE ('ab012', '3') IN (
        (id1, status1), (id2, status2), (id3, status3))

前の例と同じテーブルで、今度は前記の3つの組み合わせがいずれも('ab012', '3')と一致しないという条件では次のようになる。NULLの場合を考慮して COALESCE を使う。

SELECT * FROM tbl
WHERE ('ab012', '3') NOT IN (
        (COALESCE(id1, ''), COALESCE(status1, ''))
      , (COALESCE(id2, ''), COALESCE(status2, ''))
      , (COALESCE(id3, ''), COALESCE(status3, '')))

2014/02/12追記：
この記事で書いた列と列の比較を複数列に一般化したものを「行式」という。SQL-92で標準化された機能である。IN以外の比較演算子にも拡張できる（実装されているかどうか別である）。
行式はOracleとDB2で実装されているそうだ。PostgreSQLでも使える。MySQLは不明。

参考文献：WEB+DB PRESS Vol.66 SQL救急救命室 第5回

参考サイト:
PostgreSQL IN句での複数条件指定 | Fusic Developers' Weblog

SQLインジェクション その7

関連記事：
SQLインジェクション その1
SQLインジェクション その2
SQLインジェクション その3
SQLインジェクション その4
SQLインジェクション その5
SQLインジェクション その6

脆弱性への対策

文字列リテラルの問題

SQLインジェクション脆弱性を快勝するにはSQLを組み立てる際にSQLの変更を防ぐことである。その具体策には以下の2つがある。

1. プレースホルダによりSQL文を組み立てる。
2. アプリケーション側でSQL文を組み立てる際に、リテラルを正しく構成するなどSQL文が変更されないようにする。

bは完全な対応は難しく、aのプレースホルダを使用することを強く推奨する。
PHPからDBを利用する場合、PEARのMDB2を推奨する。

プレースホルダを利用すると先の蔵書検索のSQLは以下のようになる。

SELECT * FROM books WHERE author = ? ORDER BY id

SQL文中の"?"がプレースホルダ（place holder）で、変数屋敷など可変パラメータの場所に埋め込んでおくものである。"place holder" とは「場所取り」という意味。
MDB2を利用してコードは以下のようになる（DBはPostgreSQL）。

<?php
require_once 'MDB2.php';
header('Content-Type: text/html; charset=UTF-8);
$author = $_GET['author'];
// 接続時に文字コード（UTF-8）を指定
$mdb2 = MDB2::connect('pgsql://＜ユーザ名＞:＜パスワード＞@＜ホスト名＞/＜データベース名＞？charset=utf8');
$sql = 'SELECT * FROM books WHERE author = ? ORDER BY id';
// SQL呼び出し準備。第2引数の配列でプレースホルダの方を指定する。
$stmt = $mdb2->prepare($sql, array('text'));
// SQL文実行
$rs = $stmt->execute(array($author));
// 表示処理は省略
$mdb2->disconnect();	// 切断
?>

プレースホルダ

プレースホルダには2種類の方式がある。

静的プレースホルダ

プレースホルダを含むSQLがそのままDBエンジンに送られ、DBエンジン側でコンパイルされる。次にバインド値がDBエンジンに送られてDBエンジン側で値を当てはめられた後にSQL文を実行される。
バインド値を入れる前にコンパイルされるのでSQLインジェクション脆弱性は原理的に発生ない。

動的プレースホルダ

アプリケーションのライブラリ側で値をバインドしてからDBエンジンに送られる。リテラルは適切に処理されるので処理系にバグがなければSQLインジェクション脆弱性は発生しない。

原理的にSQLインジェクションの可能性がない静的プレースホルダ方式の方が安全である。
動的プレースホルダの実装の問題でSQLインジェクション脆弱性が発生した例がある。

LIKE述語とワイルドカード

LIKE述語を使って "_" や "%" を検査する場合はこれらの文字をエスケープする必要がある。
このエスケープを怠ることとSQLインジェクションは別物だが混同している人が多い。

"_" や "%" を自体を検索したい場合はESCAPE句を指定してWHERE句はいかのようになる。

WHERE name LIKE '%#%%' ESCAPE '#'

DBエンジンによってエスケープが必要な文字は異なる。OracleやDB2では全角文字の "％" と "＿" もエスケープの必要がある。

参考文献：体系的に学ぶWebアプリケーションの作り方 4.4.1 SQLインジェクション

体系的に学ぶ 安全なWebアプリケーションの作り方　脆弱性が生まれる原理と対策の実践 徳丸 浩 ソフトバンククリエイティブ 2011-03-03 売り上げランキング : 4070 Amazonで詳しく見る by G-Tools

SQLインジェクション その6

関連記事：
SQLインジェクション その1
SQLインジェクション その2
SQLインジェクション その3
SQLインジェクション その4
SQLインジェクション その5

脆弱性の原因

文字列リテラルの問題

SQLでは文字列リテラルはシングルクウォートで囲む。文字列リテラル中にシングルクウォートを含めたい場合はシングルクウォートを重ねてエスケープする。
SQLインジェクション脆弱性のあるアプリケーションではエスケープが抜けているため、例えば "O'Reilly" をパラメータとして渡した場合に以下のようなSQLが組み立てられる。

SELECT * FROM books WHERE author='O'Reilly'

"O"の後ろでシングルクウォートで文字列リテラレルが終了され、後続の"Reilly'"が文字列リテラルをはみ出した状態となる。このはみ出した部分に意味を持つ文字列を入れるのがSQLインジェクション攻撃である。

数値項目に対するSQLインジェクション

数値リテラルに起因するSQL脆弱性もある。
スクリプト言語の多くは変数に方の制約がない。例として以下のSQLを考える。$ageには数値が入ることを想定している。

SELECT * FROM employees WHERE age < $age

ここで変数$ageに以下の文字列が入った場合、SQLインジェクション攻撃となる。

1;DELETE FROM employees

この場合、組み立てられるSQLは以下のようになるり、表employeesが全削除される。

SELECT * FROM employees WHERE age < 1;DELETE FROM employees

参考文献：体系的に学ぶWebアプリケーションの作り方 4.4.1 SQLインジェクション

体系的に学ぶ 安全なWebアプリケーションの作り方　脆弱性が生まれる原理と対策の実践 徳丸 浩 ソフトバンククリエイティブ 2011-03-03 売り上げランキング : 4070 Amazonで詳しく見る by G-Tools