iakioの日記 RSSフィード

2010-01-23

SQLで==を使う

| 00:07 | SQLで==を使う - iakioの日記 を含むブックマーク はてなブックマーク - SQLで==を使う - iakioの日記

"SQLで間違って==とか書いちゃうよね"的なことを某所でいわれたので作ってみました。

http://gist.github.com/281672

create operatorでanyelementが使えるなんてやってみて初めて知りましたが、「こんなに短かくできるよ」を強調したかったのでanyelementにしたのであって、本来はそれぞれの型毎に作るべきでしょう。

たとえば8.4だと

=# select oprname, oprcode from pg_operator where oprname = '=';

 oprname |         oprcode
---------+--------------------------
 =       | int48eq
 =       | booleq
 =       | chareq
 =       | nameeq
...
 =       | enum_eq
 =       | tsvector_eq
 =       | tsquery_eq
 =       | record_eq
(59 rows)

と、データ型毎に59個ほど"="演算子が定義されているのでこれらと同じ定義で"=="を定義してあげれば何の問題も無いはずです。

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20100123

2009-04-06

SQL Injectionを激減させるたった1行のテクニック

| 19:44 | SQL Injectionを激減させるたった1行のテクニック - iakioの日記 を含むブックマーク はてなブックマーク - SQL Injectionを激減させるたった1行のテクニック - iakioの日記

タイトルは釣りです。というか「 PHPの文字エンコード自動変換を使って大幅にセキュリティレベルを上げるたった3行のテクニック - よくきたはてダ」あたりを参考にしました。

1行っていってもgram.yを書き換えるんですけどね。

SQL Injection対策としてまず思いつくのはprepared statementを使うことですが、とはいえそれが正しく使われていなければ意味がありません。

<?php
// こう書くべきものを
$stmt = $con->prepare("select * from pg_database where datname= ?");
$rst = $stmt->execute(array($value));

// こう書いては意味が無い
$stmt = $con->prepare("select * from pg_database where datname= '$value'");
$rst = $stmt->execute();

であればいっそ、SQLの中にリテラルを書くのを禁止してしまえばいいんじゃないでしょうか。えーっとAexprConstを全部禁止すればいいのかな。いや、PARAMで置き換え可能な部分だけ禁止すればいいのか。

--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -8676,7 +8676,7 @@ b_expr:       c_expr
  * ambiguity to the b_expr syntax.
  */
 c_expr:        columnref                               { $$ = $1; }
-           | AexprConst                            { $$ = $1; }
+           | AexprConst                            { elog(ERROR, "error!!"); }
            | PARAM opt_indirection
                {
                    ParamRef *p = makeNode(ParamRef);

エラーが適当なことはともかく。

=> SELECT 1;
ERROR:  error!!

おお、これはひどいすごい。なんとセキュア。¥dも動きません。

=> CREATE TABLE t1(c varchar(5));
CREATE TABLE
=> CREATE TABLE t2(c varchar(5) DEFAULT '');
ERROR:  error!!

varchar(5)の5はOKだけどDEFAULT ''はアウトだったりして面白い。でもここはprepared statement使えるんでしょうか。

=> PREPARE q1(int) AS SELECT $1;
PREPARE
=> EXECUTE q1(1);
ERROR:  error!!

PREPAREはできてもEXECUTEできないという孔明の罠。でも拡張問い合わせはちゃんと動きますよ。

<?php
$con = new PDO("pgsql:");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $con->prepare("select * from pg_database where datname= ?");
$rst = $stmt->execute(array('template1'));
var_dump($stmt->rowCount());

$stmt = $con->prepare("select * from pg_database where datname= 'template1'");
$rst = $stmt->execute();
var_dump($stmt->rowCount());

実行:
int(1)

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[XX000]: Internal error: 7 ERROR:  error!!' in /path/to/x.php:9
Stack trace:
#0 /path/to/x.php(9): PDOStatement->execute()
#1 {main}
  thrown in /path/to/x.php on line 9

GUCにオプション追加して、接続毎に設定持てるようにすれば結構使えるんじゃないでしょうか。とはいえこれでSQL Injectionが絶対防げるというわけではないですが。

あとこのエントリを書いてる途中、下書き保存のつもりで一度公開してしまいました。すぐに削除したつもりだったんですがLDRあたりには補足されてしまったようです。アレ?と思った方すいません。

elfelf2009/04/07 00:17正確に理解していないけどおもしれー!!

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20090406

2008-12-31

大晦日だけどボウリングのスコア計算するよ

| 19:48 | 大晦日だけどボウリングのスコア計算するよ - iakioの日記 を含むブックマーク はてなブックマーク - 大晦日だけどボウリングのスコア計算するよ - iakioの日記

window functionがcommitされたので何かやってみようと思ったのですが、結局ボウリングしか思いつきませんでした。といってもスコア計算の部分は今までと一緒で、最後の表示部分にrow_number()とsum()を使っているだけですが。

with recursive
s(idx, pins1, pins2, pins3) as (
    select s1.idx, s1.pins, s2.pins, s3.pins
      from score s1
      left join score s2 on (s2.idx = s1.idx + 1)
      left join score s3 on (s3.idx = s1.idx + 2)
),
f(idx, pins1, pins2, pins3) as (
    select idx, pins1, pins2, pins3 from s where idx = 1
     union all
    select s.idx, s.pins1, s.pins2, s.pins3
      from s join f
        on (s.idx = f.idx + case when f.pins1 = 10 then 1 else 2 end)
),
sof(idx, pins1, pins2, pins3, score_of_frame) as (
    select idx
         , pins1, pins2, pins3
         , case when pins1 = 10 then pins1 + pins2 + pins3
                when pins1 + pins2 = 10 then pins1 + pins2 + pins3
                else pins1 + pins2
            end as score_of_frame
           from f
)
select row_number() over w as frame
     , pins1
     , pins2
     , case row_number() over w when 10 then pins3
       else null end as pins3
     , score_of_frame
     , sum(score_of_frame) over w
  from sof
window w as (order by idx)

実行

 frame | pins1 | pins2 | pins3 | score_of_frame | sum
-------+-------+-------+-------+----------------+-----
     1 |     1 |     4 |       |              5 |   5
     2 |     4 |     5 |       |              9 |  14
     3 |     6 |     4 |       |             15 |  29
     4 |     5 |     5 |       |             20 |  49
     5 |    10 |     0 |       |             11 |  60
     6 |     0 |     1 |       |              1 |  61
     7 |     7 |     3 |       |             16 |  77
     8 |     6 |     4 |       |             20 |  97
     9 |    10 |     2 |       |             20 | 117
    10 |     2 |     8 |     6 |             16 | 133
    11 |     6 |       |       |                | 133
(11 rows)

大分見やすくなったのではないでしょうか。

11フレームまで出ているのを気にしてはいけません。

参考:

AketiJyuuzouAketiJyuuzou2009/07/01 22:06http://oraclesqlpuzzle.hp.infoseek.co.jp/blog.html
2009年7月1日(月)のエントリでアレンジしてみました。

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20081231

2008-10-17

regression testでSQLをテストする

| 23:43 | regression testでSQLをテストする - iakioの日記 を含むブックマーク はてなブックマーク - regression testでSQLをテストする - iakioの日記

PostgreSQLでボーリングのスコア計算 - iakioの日記 - postgresqlグループ

誰も気にしてないでしょうが間違ってました。最終フレームで3投目まで投げないと合計がおかしいです。left joinしないとダメかなぁ。

ここはもうテストするしかない。ここはpgTAPか!と思ったけどもうひとつ試してみたい方法があったのでpgTAPはまた今度。

PostgreSQLには、PostgreSQL自身やcontribモジュールのテストを行うregression testの仕組みがあります。これを使ってsqlだけをテストしてみます。今回も長文です。

単純なテストの例

まずおもむろにMakefileを作ります。PGXSを使って拡張モジュールを作る要領です。

REGRESS = test

PGXS := $(shell pg_config --pgxs)
include $(PGXS)

これだけ。REGRESSにはテスト内容を書いたsqlファイルの名前を指定します。下二行はきまり文句です。まだテスト自体作ってませんがこれでmake installcheckしてみましょう。

$ gmake installcheck
/path/to/pgsql/current/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=. --psqldir=/path/to/pgsql/current/bin --dbname=contrib_regression test
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test test                 ... cannot open /tmp/bowling/sql/test.sql: No such file or directory
diff: /tmp/bowling/expected/test.out: No such file or directory
diff: /tmp/bowling/results/test.out: No such file or directory
diff command failed with status 512: diff -w "/tmp/bowling/expected/test.out" "/tmp/bowling/results/test.out" > "/tmp/bowling/results/test.out.diff"
gmake: *** [installcheck] Error 2

色々エラーが出ましたが、これでだいたいやるべきことはわかりました。

  • sql, expectedというディレクトリを作る
  • sql/test.sqlにテストを書く
  • expected/test.outに期待される結果を書く

では適当なテストと空っぽのexpectedを作ります。

$ mkdir sql expected
$ echo "select 1" > sql/test.sql
$ touch expected/test.out

実行

(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test test                 ... FAILED

======================
 1 of 1 tests failed.
======================

The differences that caused some tests to fail can be viewed in the
file "/tmp/bowling/regression.diffs".  A copy of the test summary that you see
above is saved in the file "/tmp/bowling/regression.out".

gmake: *** [installcheck] Error 1

いい感じで失敗しました。このテストのために毎回CREATE DATABASEするんで効率は良くないっちゃ良くないですが。テストの実行結果はpsqlの出力になります。results/test.outを見てみましょう。

$ cat results/test.out
select 1
 ?column?
----------
        1
(1 row)

期待した結果なのでこれをexpected/test.outにコピーしてもう一度実行。

(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test test                 ... ok

=====================
 All 1 tests passed.
=====================

パスしました。パチパチ。

受け入れテスト

ではボーリングスコア計算のsqlのテストをします。あの長いsqlをすべてのテストに書くのは大変なので、別ファイルに書いておきます。ここではMakefileと同じディレクトリのbowling.sqlというファイルに書いておくことにします。

次にまず受け入れテストを作ります。

-- sql/acceptance.sql
create table score(idx int, pins int);
copy score from stdin;
1   1
2   4
3   4
4   5
5   6
6   4
7   5
8   5
9   10
10  0
11  1
12  7
13  3
14  6
15  4
16  10
17  2
18  8
19  6
\.
\set ECHO
\i bowling.sql

psqlの\iコマンドでbowling.sqlを読み込んでいます。results/acceptance.outにあの長いsqlが含まれないようにするために、\set ECHOで出力を抑止しています。Makefileにテストを追加します。

REGRESS = acceptance

んで、テストを実行。expected/acceptance.outを作っていないので当然失敗します。results/acceptance.outは、

-- sql/acceptance.sql
create table score(idx int, pins int);
copy score from stdin;
\set ECHO
 sum
-----
 133
(1 row)

オーケー。これをexpectedにコピーしてテストがパスすることを確認しましょう。

すべて1ピンの場合

次に、20回すべて1ピンだった場合です。REGRESSに指定したsqlは順番に実行されるので、最初にscoreテーブルを空にしています。トランザクションを使ってrollbackしてもいいかもしれません。

-- all_one.sql
truncate score;
copy score from stdin;
1   1
2   1
3   1
4   1
5   1
6   1
7   1
8   1
9   1
10  1
11  1
12  1
13  1
14  1
15  1
16  1
17  1
18  1
19  1
20  1
\.
\set ECHO
\i bowling.sql

そこはgenerate_seriesダロという声も聞こえてきそうですが。Makefileにテストを追加します。あといい加減make installcheckと打つのが面倒になってきたのでデフォルトターゲットをinstallcheckにしておきます。

all: installcheck
REGRESS = acceptance all_one

PGXS := $(shell pg_config --pgxs)
include $(PGXS)

んでテスト実行。results/all_one.outは、

-- sql/all_one.sql
truncate score;
copy score from stdin;
\set ECHO
 sum
-----
  18
(1 row)

ダメダメですね。正解は20なので20に書き換えてexpectedにコピーしておきます。

直す

で、何がダメだったか解説はしませんが、直したsqlはコチラ。

with recursive
s(idx, pins1, pins2, pins3) as (
    select s1.idx, s1.pins, s2.pins, s3.pins
      from score s1
      left join score s2 on (s2.idx = s1.idx + 1)
      left join score s3 on (s3.idx = s1.idx + 2)
),
f(idx, pins1, pins2, pins3) as (
    select idx, pins1, pins2, pins3 from s where idx = 1
     union all
    select s.idx, s.pins1, s.pins2, s.pins3
      from s join f
        on (s.idx = f.idx + case when f.pins1 = 10 then 1 else 2 end)
),
sof(idx, pins1, pins2, pins3, score_of_frame) as (
    select idx
         , pins1, pins2, pins3
         , case when pins1 = 10 then pins1 + pins2 + pins3
                when pins1 + pins2 = 10 then pins1 + pins2 + pins3
                else pins1 + pins2
            end as score_of_frame
           from f
)
select sum(score_of_frame) from sof;

で、テスト。

============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test acceptance           ... ok
test all_one              ... ok

=====================
 All 2 tests passed.
=====================

通りました。パチパチ。本当かな。

追記:PostgreSQL8.2以上ぐらいじゃないと使えないかもしれません。

KamerynKameryn2012/01/11 18:23Smart thinking - a celver way of looking at it.

bdlzgazpoddbdlzgazpodd2012/01/13 23:407YJbDe , [url=http://lmrjhfyrvnxn.com/]lmrjhfyrvnxn[/url], [link=http://sjhrtoqoepta.com/]sjhrtoqoepta[/link], http://hegxfpzjchut.com/

wfnkmoylufkwfnkmoylufk2012/01/14 20:4132EApZ <a href="http://wuabqmmswmgn.com/">wuabqmmswmgn</a>

kbdnqlffpkbdnqlffp2012/01/16 20:577FHBaK , [url=http://wehgsrhmfdee.com/]wehgsrhmfdee[/url], [link=http://zbsiuwqfgvea.com/]zbsiuwqfgvea[/link], http://fbbyuuctiiij.com/

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20081017

2008-10-13

PostgreSQLでボーリングのスコア計算

| 18:40 | PostgreSQLでボーリングのスコア計算 - iakioの日記 を含むブックマーク はてなブックマーク - PostgreSQLでボーリングのスコア計算 - iakioの日記

PostgreSQLでボーリングのスコア計算をしてみます。元ネタは2008年2月に札幌で行なわれた「日本PostgreSQLユーザ会北海道支部 / Ruby札幌合同セミナー」での角谷さんのセッション「スはスペックのス~RSpecによるテスト駆動開発の実演」です。Rubyやテスト駆動に興味が無くても面白いので(角谷さんが)是非見てみて下さい。

また、PostgreSQLは開発中の8.4(2008-10-11くらいのCVS)を使っています。

以下長文注意。

データ構造

最初はpgTAPでも使ってテストを書いてみようかと思ったのですが、今回のメインはテストではないのでいきなり受け入れテストのパターンでいきます。元ネタでは以下のようなリストでした。

[1, 4, 4, 5, 6, 4, 5, 5, 10, 0, 1, 7, 3, 6, 4, 10, 2, 8, 6]

配列を使ってもできなくはないですが今回はこれをテーブルに格納して、

create table score(idx int, pins int);
copy score from stdin;
1   1
2   4
3   4
4   5
5   6
6   4
7   5
8   5
9   10
10  0
11  1
12  7
13  3
14  6
15  4
16  10
17  2
18  8
19  6
\.

ということにしたいと思います。

2つ先の投球を得る

ストライクやスペアのスコアを計算するには、2投先までのピン数が必要になり

ます。ここはself joinで、

select s1.idx, s1.pins, s2.pins, s3.pins
  from score s1
  join score s2 on (s2.idx = s1.idx + 1)
  join score s3 on (s3.idx = s1.idx + 2);

となります。

 idx | pins | pins | pins
-----+------+------+------
   1 |    1 |    4 |    4
   2 |    4 |    4 |    5
   3 |    4 |    5 |    6
   4 |    5 |    6 |    4
   5 |    6 |    4 |    5
   6 |    4 |    5 |    5
   7 |    5 |    5 |   10
   8 |    5 |   10 |    0
   9 |   10 |    0 |    1
  10 |    0 |    1 |    7
  11 |    1 |    7 |    3
  12 |    7 |    3 |    6
  13 |    3 |    6 |    4
  14 |    6 |    4 |   10
  15 |    4 |   10 |    2
  16 |   10 |    2 |    8
  17 |    2 |    8 |    6
(17 rows)

フレームに分ける

各フレームの1投目に10本倒せばストライクですが、2投目に10本倒した場合はスペアになります。このため、どの投球が1投目であったかを知る必要があります。1投目とは、

  • 一番最初の投球は1投目(あたりまえ)
  • 1投球目が10ピンであれば、次の投球は次のフレームの1投目
  • そうでなければ、次の次の投球が次のフレームの1投目

となります。ここでも先程のようにself joinを使うのですが、1投目の本数によってjoinの式が異ります。なのでここで、8.4の新機能であるrecursive query(再帰問い合わせ)を使ってみます。

with recursive f(idx, pins) as (
    select idx, pins from score where idx = 1
     union all
    select score.idx, score.pins
      from score join f
        on (score.idx = f.idx + case when f.pins = 10 then 1 else 2 end)
)
select * from f;
 idx | pins
-----+------
   1 |    1
   3 |    4
   5 |    6
   7 |    5
   9 |   10    -- strike
  10 |    0
  12 |    7
  14 |    6
  16 |   10    -- strike
  17 |    2
  19 |    6
(11 rows)

うん。よさそうです(説明したいけどいい説明が思い浮ばない)。11フレームまで投げてるようにも見えますがこの時点では気にしないことにしますw。

合体

では2つのsqlを合体させます。無理矢理1つにまとめることもできそうですが、今回は前半のsqlもcteに入れてみます。

with recursive
s(idx, pins1, pins2, pins3) as (
    select s1.idx, s1.pins, s2.pins, s3.pins
      from score s1
      join score s2 on (s2.idx = s1.idx + 1)
      join score s3 on (s3.idx = s1.idx + 2)
),
f(idx, pins1, pins2, pins3) as (
    select idx, pins1, pins2, pins3 from s where idx = 1
     union all
    select s.idx, s.pins1, s.pins2, s.pins3
      from s join f
        on (s.idx = f.idx + case when f.pins1 = 10 then 1 else 2 end)
)
select * from f;
 idx | pins1 | pins2 | pins3
-----+-------+-------+-------
   1 |     1 |     4 |     4
   3 |     4 |     5 |     6
   5 |     6 |     4 |     5
   7 |     5 |     5 |    10
   9 |    10 |     0 |     1
  10 |     0 |     1 |     7
  12 |     7 |     3 |     6
  14 |     6 |     4 |    10
  16 |    10 |     2 |     8
  17 |     2 |     8 |     6
(10 rows)

そして完成へ

材料は揃いました。いよいよスコア計算です。まずフレーム毎のスコアを計算します。

  • ストライクの場合は、pins1(=10) + pins2 + pins3
  • スペアの場合は、(pins1 + pins2)(=10) + pins3

あ、結局一緒か。

  • それ以外の場合は、pins1 + pins2
with recursive
s(idx, pins1, pins2, pins3) as (
    select s1.idx, s1.pins, s2.pins, s3.pins
      from score s1
      join score s2 on (s2.idx = s1.idx + 1)
      join score s3 on (s3.idx = s1.idx + 2)
),
f(idx, pins1, pins2, pins3) as (
    select idx, pins1, pins2, pins3 from s where idx = 1
     union all
    select s.idx, s.pins1, s.pins2, s.pins3
      from s join f
        on (s.idx = f.idx + case when f.pins1 = 10 then 1 else 2 end)
)
select idx
     , pins1, pins2, pins3
     , case when pins1 = 10 then pins1 + pins2 + pins3
            when pins1 + pins2 = 10 then pins1 + pins2 + pins3
            else pins1 + pins2
        end as score_of_frame
       from f;
 idx | pins1 | pins2 | pins3 | score_of_frame
-----+-------+-------+-------+----------------
   1 |     1 |     4 |     4 |              5
   3 |     4 |     5 |     6 |              9
   5 |     6 |     4 |     5 |             15
   7 |     5 |     5 |    10 |             20
   9 |    10 |     0 |     1 |             11
  10 |     0 |     1 |     7 |              1
  12 |     7 |     3 |     6 |             16
  14 |     6 |     4 |    10 |             20
  16 |    10 |     2 |     8 |             20
  17 |     2 |     8 |     6 |             16

勝利は目前。あとはsum()するだけです。普通にsum()してもいいけど折角なのでもうひとつcteを使ってみます。

with recursive
s(idx, pins1, pins2, pins3) as (
    select s1.idx, s1.pins, s2.pins, s3.pins
      from score s1
      join score s2 on (s2.idx = s1.idx + 1)
      join score s3 on (s3.idx = s1.idx + 2)
),
f(idx, pins1, pins2, pins3) as (
    select idx, pins1, pins2, pins3 from s where idx = 1
     union all
    select s.idx, s.pins1, s.pins2, s.pins3
      from s join f
        on (s.idx = f.idx + case when f.pins1 = 10 then 1 else 2 end)
),
sof(idx, pins1, pins2, pins3, score_of_frame) as (
    select idx
         , pins1, pins2, pins3
         , case when pins1 = 10 then pins1 + pins2 + pins3
                when pins1 + pins2 = 10 then pins1 + pins2 + pins3
                else pins1 + pins2
            end as score_of_frame
           from f
)
select sum(score_of_frame) from sof;
 sum
-----
 133
(1 row)

お疲れさまでしたー。

まとめのようなもの

  • Q. SQLでボーリングのスコアを求めることに意味あるの?
  • A. recursive queryを使うと何ができるようになるのかを理解する上では役立つと思います(というか役立ちました。僕は)。
  • A. あと、non-recursiveなcteは、長いsqlを書こうとした時にすごく便利だと思いました。

DainooDainoo2012/07/28 08:02Very valid, pithy, scucinct, and on point. WD.

nuznngnuznng2012/07/28 23:1134cwa1 <a href="http://bhrzmgutvgxx.com/">bhrzmgutvgxx</a>

wvxwizvylavwvxwizvylav2012/07/29 02:09UNbw3z , [url=http://jlthkaiixdwn.com/]jlthkaiixdwn[/url], [link=http://rxzryltstffr.com/]rxzryltstffr[/link], http://zpmtxlcksoln.com/

dbfopzfidbfopzfi2012/07/30 16:29cEGRXs , [url=http://zofvedemkaiz.com/]zofvedemkaiz[/url], [link=http://xxrvtjfzeupf.com/]xxrvtjfzeupf[/link], http://suodmmezyywt.com/