PostgreSQL 9.0から使える識別子とリテラルのエスケープ

PostgreSQL Advent Calender用のエントリです。

エスケープ処理が必要なのにエスケープ用のAPIが無い状態は良くありません。エスケープしないために動かないのはまだ良い方です。エスケープが必要なのにエ スケープをしなくても動いてしまい、セキュリティ上の問題となる場合もあります。全てのアプリケーション・ライブラリはエスケープが必要なデータに対するAPIを持っておくべき です。今回はPostgreSQL 9.0から追加されたエスケープ関数を紹介します。

PostgreSQL使い始めて最初の頃に気づくのはuserなどの予約語がフィールド名に使えない事かも知れません。例えば、

と失敗してしまいます。これはuserがPostgreSQLの予約語であるためSQL文の識別子として使用できないからです。MS Accessからデータベースに入った方には識別子に日本語を使う場合も多いので、PostgreSQLでは日本語のテーブル名やフィールド名はそのままでは使えない事に気が付いた方も多いのではないでしょうか? “PostgreSQL 9.0から使える識別子とリテラルのエスケープ” の続きを読む

OSC Tokyo – 今更聞けないSQLインジェクションの現実と対策

明日のOSC東京Fallでは「SQLインジェクション”ゼロ”のPostgreSQL利用法 – 今更聞けないSQLインジェク ションの現実と対策」と題したセッションを日本PostgreSQLユーザ会の講師として話をさせて頂きます。

SQLインジェクションはとうの昔に枯れた話題と思われていますが、古くても今の問題です。何年か前、日本PostgreSQLユーザ会のセミナーで手作業でのブラインドSQLインジェクションのデモをした事がありますが今回はツールを使ったデモもあります。書いている間に当初作ろうと思っていたプレゼンとは異なる物なってしまいました。多少紹介から期待する内容とは異なっているかも知れません。既にSQLインジェクションについては十分知っている方でも、それなりに(?)楽しめる内容になっていると思います。おかげ様で満員だそうですが、飛び込みでも少しは入れるのかな?

45分なのに60枚もスライドがある上、デモもあります。かなりハイペースで話すことになります。ネットで直ぐに見つかるような基本的な事はあまり書かなかったのですが、無いようで書くとSQLインジェクションについて色々在る物です。多少スライドは飛ばす事になります。

ほぼ同じ内容でOSC高知でも話をさせて頂く予定です。
来たくても来れなかった方は是非高知でお会いしましょう。

PostgreSQLカンファレンス2008

PostgreSQLカンファレンス2008が今週金曜日(6/6)に開催されます。

http://www.postgresql.jp/events/postgresql-conference-2008

例年通り参加費が必要ですが懇親会費込みです。

参加費:
カンファレンス ならび に懇親会 4,000 円
チュートリアルも含むカンファレンス ならびに 懇親会 10,000 円

今回のカンファレンスの目玉は色々ありますが、その一つはチュートリアルセッションです。まだ、空席が残っているようなのでライセンスもBSDでMySQLよりも使いやすいPostgreSQLを始めてみたい方には良いチャンスだと思います。新人研修の一環としても良いと思います。

* MySQLユーザのためのPostgreSQL入門
(リナックスアカデミー 学校長 濱野 賢一朗 氏)

興味がある方は是非カンファレンスにお越し下さい。

PostgreSQL 8.3の全文検索機能(TSearch2)を日本語で利用する

PostgreSQL 8.3.0から、ユーザから提供されている追加機能(contrib)として利用できた全文検索機能(TSearch2)が本体に取り込まれました。

本体に取り込まれたため、PostgreSQL 8.3.0以降ではソースから構築する場合に

./configure
make
make install

と実行するだけで全文検索機能が利用できるようになりました。
“PostgreSQL 8.3の全文検索機能(TSearch2)を日本語で利用する” の続きを読む

日本PostgreSQLユーザ会北海道支部 / Ruby札幌 合同セミナーのお知らせ

2月16日(土)に、日本PostgreSQLユーザ会(JPUG)北海道支部とRuby札幌の合同セミナーが開催されます。

日本PostgreSQLユーザ会北海道支部 / Ruby札幌 合同セミナーのお知らせ

私も講師の一人として参加させて頂きます。PostgreSQLとMySQLのベンチマークについて話す予定です。ご都合がよい方はお越しください。

有料と聞いていないので無料セミナーだと思います。アナウンス文には無料と記載されていないので主催者に問い合わせてみます。

追記:
現在は無料であることがアナウンス文に追加されています。

FreeBSD7はPostgreSQL, MySQLユーザにとって救いになるか?

http://people.freebsd.org/~kris/scaling/7.0%20Preview.pdf

にFreeBSD7上でのPostgreSQLとMySQLのベンチマークが載っています。

PostgreSQL 8.2.4 – 11ページ

ピーク性能でおよそ5400transactions/secほど。

MySQL 5.0.45 – 15ページ

ピーク性能でおよそ3800transactions/secほど。

Kernelの主要な部分すべてがパラレルに動作するようになったため、かなり高速(数値にして数倍)になったようです。

グラフからもPostgreSQLの方がかなり良い性能であることが分かりますが、PDFファイル(16ページ)によると

On this benchmark PostgreSQL is 35% – 45% faster thanMySQL at all loads

とPostgreSQLの方が全般的に良い性能だったそうです。PostgreSQL 8.3は確実に8.2よりもさらに良い性能を期待できると思います。MySQLも5.1や6.0を利用した方が良い性能が期待できるのかも知れません。

このPDFのベンチマークはデータベースの性能を計る為のベンチマークではなく、OSの性能を計る為のベンチマークです。データベースサーバ設定、SQL文やテーブル構成などが不明なのでデータベースの性能のベンチマークとしては参考値くらいでしかありません。MySQLのテストではMyISAMを使っていると思われますが、MyISAMならこれくらいの性能差は普通です。

SET NAMESは禁止

MySQLには文字エンコーディングを変更する「SET NAMES」SQL文が用意されています。(PostgreSQLも同様のSQL文、SET CLIENT_ENCODINGがあります)この機能はSQLコンソールからは使ってよい機能ですが、アプリケーションからは使ってはならない機能です。SQLインジェクションに脆弱になる場合があります。

Ruby on Railsの本を読んでいて、ActiveRecordを説明している部分にMySQLの文字エンコーディングを変更する場合の例としてSET NAMESが利用されていました。アプリケーションからはSET NAMESは使ってはならない事を周知させるのは結構時間が必要かなと思いました。

PHPも5.2の途中からMySQLモジュールにlibmysqlの文字エンコーディング設定APIのラッパー関数が追加されていたりするので、たまたま最近読んだRoRの本だけでなく、多くの開発向け情報ソースにSET NAMESを利用した例が載っていると思います。

ストアドプロシージャだけ使っていれば安全ですが、アプリケーションからDBMSの文字エンコーディングを設定する場合、SQL文ではなく必ず文字エンコーディング設定APIを利用するよう紹介しなければならないです。MySQL4はストアドプロシージャが使えないので、フレームワークなどではエミュレートしています。ストアドプロシージャだけ使って防御している「つもり」で防御になっていない場合もあります。これもフレームワークを使っていてもアプリケーションが脆弱になる良い例ですね。

脆弱性の説明は面倒ですが注意事項は簡単です。「DBMSをアプリケーションから利用する場合、文字エンコーディング設定は必ずAPIを利用する」つまり「SET NAMES(PostgreSQLのSET CLIENT_ENCODING等も)は禁止」です。

PHPのMySQL:

PHPのPostgreSQL:

PHPのPDO:

Rails:

 

PostgreSQLカンファレンス2007

明日開催されるPostgreSQL2007は会場費等の為に2000円でローソンチケットでチケットを販売していました。ローソンチケット分は完売で現在購入できないそうです。しかし、当日券を会場にて販売(当日券は3000円だそうです)するそうです。もしチケットを入手できなかった方は現金で購入できるそうです。領収書も発行できるので仕事の都合がつく方は是非お越しください。

PostgreSQLカンファレンス2007

最近忙しすぎてブログの更新が全くできない状態がつづいていますが、PostgreSQLカンファレンス2007が2007/6/5(火)に秋葉原UDXにて開催されます。私もRoom C(定員60名: 16:00~16:55)で講師を務めさせていただきます。私がメールを読んでいなかった為、私の資料は印刷物には入っていません… この為当日自分で持って行くことになってしました…
# 関係者の皆様、ご迷惑をおけしました。

Room C(60名)16:00~16:55
WEB+DBアプリケーションのセキュリティ(仮)
PHPユーザ会/日本PostgreSQLユーザ会
大垣靖男 様

セキュリティ対策はまず基礎知識から、ということでPostgreSQLを安全に利用するための基礎知識を解説させていただきます。今回は初めてデモを予定しています。SQLインジェクションに脆弱だといかに簡単に不正にデータを取得したり、データベース設計を解析できるかをデモンストレーションする予定です。SQLインジェクションに再確認されたい方、SQLインジェクションは知っているがどの程度の攻撃が可能か詳しく知らない方であれば、PostgreSQLユーザのみでなく、全てのSQLデータベースを利用されている開発者に有用な講演になると思います。

ローソンチケットによるとまだチケット(2000円です。会場費などに利用されます)は買えるようです。いろいろ有用なセミナーがそろっています。都合が良い方は是非お越しください。

Sigres – PostgrSQLの高速化

新しいバージョンがリリースされたようです。

PostgreSQLのINSERT/UPDATEを高速化するSigresの0.1.3をリリースします。

http://sourceforge.jp/projects/sigres/

SigresはUPSの存在を前提に、信頼性を若干犠牲にする代わりに、挿入処理に関して大幅な性能向上を実現します。

いまなおpgsql-hackersからコメントをもらう段階にありますので、使用には注意してください

コミットされたデータが絶対にないと困る、アプリもありますがそうでないもの多くあります。例えば、何かのログなどは電源トラブルで最後の方のコミット済みのログレコードがいくつか無くなるかも知れない程度で高速化できるのであれば十分なアプリも多くあると思います。Pgpoolなどでレプリケーションしているので一つ壊れてもOKな場合もあると思います。

SigresはデフォルトインストールのPostgreSQLに対しては数倍~数十倍の性能向上を示します。しかしWALファイルをtmpfsにおいた場合では、せいぜい10%から19%程度の性能向上しか示しません。

「数倍~数十倍の性能向上」だとMySQLより随分速いですね。得意・不得意があるので簡単に比較できないですが、2,3倍でも十分MySQL以上の性能になります。気になるのはMySQLのBerkeleyDBと比べて安全性がどれくらい犠牲になっているか?という点です。そのうち調べよう。

安定版になったら使ってみます。

PostgreSQLでSHA1

MS Access 2003でSHA1を使おうと思ったらどうもうまく動作しませんでした。
.NETのmbcorlibにMD5,SHA1等のハッシュ関数が定義されていてVBからはMSDNに書いてるサンプルで動作するのですがMS AccessのVBAからはいろいろ試しても動作しませんでした。サンプル通りだとオブジェクトを生成する行でシンタックスエラーになり、多少変更しても動作しませんでした。
# 普段VBAでプログラミングはしないので私が無知なだけかもしれませんが…
# Windowsプログラマの方はハッシュはあまり使わない??

VBAでハッシュが使える方が良かったのですが、しかた無くPL/Pythonでハッシュ値を取得し返す関数を定義することにしました。

PL/Pythonの場合、

CREATE OR REPLACE FUNCTION SHA1HASH( TEXT ) RETURNS CHAR(40) AS ‘
import sha
hash = sha.new( args[0] )
return hash.hexdigest()
‘ LANGUAGE plpythonu;

言語を登録していない場合

CREATE LANGUAGE plpythonu;

を先に実行しておきます。8.0からplpythonはplpythonuに名前が変更されています。(Untrustedな言語なのでuが付く)

PL/Perlの場合は多分こんな感じ(こちらは試していません)

CREATE OR REPLACE FUNCTION SHA1HASH( TEXT ) RETURNS CHAR(40) AS ‘
use Digest::SHA1 qw(sha1);
return sha1($args[0]);
‘ LANGUAGE plperl;

同じようなコードでMD5, SHA256, SHA512等が簡単に作れます。

普通パスワードはハッシュ化したパスワードを保存するのでユーザ情報テーブルのパスワードフィールドはハッシュ値になっています。

SELECT * FROM user WHERE username = ‘USERNAME’ AND password = sha1hash(‘PASSWORD’);

といった感じでクエリできるようになります。アプリの言語側でハッシュが使える場合はアプリ側でハッシュ化する方が好ましいのですがVB等でライブラリが制限されている場合には便利かも知れません。

PostgreSQL 8.2 リリース

PostgreSQL 8.2がリリースされました。

主要な変更点

Among the features of this new version are:

* Higher performance (+20% on OLTP tests)
* Improved Warm standby databases
* Online index builds
* SQL2003 aggregates
* Improved TSearch2 with Generalized Inverted Indexes
* Support for DTrace probes
* Advisory Locks
* New ISN/ISBN and pgCrypto modules
* Selective pg_dump options

参考: アプリプログラマに影響が大きい変更点
http://blog.ohgaki.net/index.php/yohgaki/2006/09/29/postgresql_8_2_beta

PostgreSQL 8.2 Beta

PostgreSQL 8.2のリリースノートは非常に長いのですがアプリケーションプログラマのコーディングスタイルに大きく影響するのは次の変更(追加)だと思います。

Add INSERT/UPDATE/DELETE RETURNING (Jonah Harris, Tom)

This allows these commands to return values, such as the computed serial key for a new row. In the UPDATE case, values from the updated version of the row are returned.

MySQL等で挿入後のID番号が取得できる機能がPostgreSQLでも作れるようになりました。

create table test (
id serial,
msg text,

primary(id)
)

としてidフィールドの値が何になったのか分からないと困る場合が多かったので

create table test (
id int8,
msg text,

primary(id)
)

としてsequenceの次の値を取得してidにセットしていたと思います。今後はより気軽に

INSERT INTO test(msg) VALUES (‘abc’) RETURNING *;

として挿入後のデータが参照できるようです。
Ruleやストアドプロシージャでデータを加工している場合にも便利ですね。

PostgreSQLの場合、6.xのころからRuleを使うと比較的簡単に全ての変更をログできるのですが、この機能を使えばアプリケーション側でデータのログを取るコードが簡略化できます。データベースレベルではアプリケーションを操作しているユーザIDが分からない場合が多いので便利になります。

PostgreSQLに関する間違い情報

PostgreSQL 8.0は2005年1月にリリースされました。SAVEPOINTは8.0からサポートされている機能ですが、2006年4月の記事で「サポートしていない」と間違った情報が記載されているページを見つけました。

 ロールバックはトランザクションの開始処理まで戻るのが基本であるが,「セーブポイント」機能を利用すれば部分的な処理の取り消しが可能になる。図4[拡大表示]上に示したようにトランザクションの中に「SAVEPOINT(セーブポイント名)」を設定し,「ROLLBACK TO(セーブポイント名)」とすれば,ROLLBACKからSAVEPOINT間の処理を取り消すことができる。1つのトランザクションには複数のセーブポイントが設定可能である。セーブポイント機能はOracleにはあるがPostgreSQLには無い

しかし、よく見ると

出典:日経オープンシステム 2003年2月号 142ページより
(記事は執筆時の情報に基づいており,現在では異なる場合があります)

とあり書いた時点では正しい情報です。3年も経つとかなり変わりますから技術記事の再利用は難しいですね。この記事は新人対象のようですがこれを読むとそのまま信じてしまいそうです。

PostgreSQLのプリペアードクエリとUnitテスト

PostgreSQLのプリペアードクエリを使ったプログラムのユニットテストを書いてユニットテストの書き方の問題に気がつきました。普通にユニットテストを書くと

relation with OID ##### does not exist

というPL/PgSQLで良く見かけるエラーが発生してしまう場合があります。

通常ユニットテストを書く場合、テストのセットアップ関数(メソッド)で

create table test (a text, i int);

テスト終了時に

drop table test;

と書きます。

プリペアードクエリの場合、コンパイルされたSQL文がバックエンドにキャッシュされるので「Web環境などで永続的な接続」を行っている環境でこの様な初期化、終了ルーチンを持つユニットテストでPostgreSQLのプリペアードクエリを実行するテストを「2回以上」行うと

relation with OID ##### does not exist

とエラーが表示されてしまいます。キャッシュされた(プリペアされた)SQL文に対応するテーブルが削除されてしまった後もクエリはキャッシュされているので削除されたの存在しないテーブルアクセスしようとしているので上記のエラーがでます。
# プリペアされたクエリは名前でなくOIDでテーブルにアクセスするので
# このエラーが発生します。

コマンドラインからのユニットテストの場合、接続の種類は影響しませんがPHPでWebサーバ上でユニットテストを実行する場合、pg_prepare, pg_execute を使ったプログラムのユニットテストにはpg_connectを使うほうが良いです。pg_connectを使っていても同じ接続を使っている間にテーブルの削除/作成を行ってもpg_pconnectを使っている場合と同じ理由でこのエラーが発生します。コマンドラインでもすべてのテストを実行するスクリプトを利用している場合は注意が必要だと思います。PL/PgSQLのエラーが何故発生するのか理解していれば直ぐに原因に気が付くと思いますが、そうでなければなかなか気が付かないかもしれません。

私も最初は何の事だか解らずググってもPL/PgSQLのエラーに関連するページが表示されていました。pg_prepare/pg_executeについては少なくとも上位には表示されなかったので書いておきます。

PostgreSQLのプリペアードクエリの実装(と言うか実際にOID参照しているもの)を詳しく調べてから書いていないので「実装と違う」という場合は是非指摘してください。

# Javaのユニットテストでこの問題が発生する、というページ
# も無かったような気がします。当り前すぎ(FAQ)だから?

# 問題を回避するもう別の方法にDEALLOCATEを使ってプリペアされた
# SQL文を削除しておく方法もあります。削除するのは面倒ですがこちら
# の方法ならどのような環境でも困らないと思います。

MySQLからPostgreSQLに乗り換えた理由。MySQLより10倍速かったから?!

hackers-jp@ml.postgresql.jp に掲載されていたメールの話です。
色々興味深いです。

MyISAM -> InnoDBで容量が約10倍。InnoDBとPostgreSQLを比べても約3倍。
キャパシティプランニングはDB設計の重要な要素ですが、知らないと困ったことになりますね。

MySQLであまり大きなDBを取り扱ったことが無かったので知らなかったですがMyISAM->InnoDBで10倍にもなるケースもあるとは…

JPUGのMLに書かれていた以外の理由としては1/3のメモリで3倍速く、データのパーテションニング、パーシャルインデックス等のPostgreSQL機能を近い将来利用する事が出来ることもスイッチする理由だそうです。

For an application like FeedLounge, the faster counting and smaller row size of PostgreSQL are compelling reasons to use it instead of MySQL with InnoDB tables. Add that it runs in 1/3 the time, using 1/3 the memory (making it essentially 10x faster for us) and that we can start taking advantage of other Postgres features, like data partitioning and partial indices in the near future, and you have the reason we decided to make the switch.

軽くて速いPostgreSQLは彼らにとって「10倍速い」と言っています。何だか一般的に言われていること(MySQLは軽くて速い)と反対ですね。

どのDBを選択するか?は利用条件、要求事項によって最適な選択が異なります。MySQLの方が自分にとっては「10倍速い」と考えるユーザも多いと思います。どちらにしてもこの事例は参考になります。

以下、メール本文のコピーです。

—–
寺本@横須賀 です。

最近メールがないので…
雑談ですが、ちょっと面白いのでご紹介です。(via Planet Postgresql)

FeedLoungeという、RSSフィードのオンラインリーダーを提供している(らしい)サービスがあるのですが、そこがDBをMySQLからPostgreSQLに乗り換えた、という話です。

http://feedlounge.com/blog/2005/11/20/switched-to-postgresql/

何が理由で乗り換えたのかに興味があったので読んでみたんですが、

—-

—-

という今まで聞いたことも無い理由で乗り換えたとのことでした。
# PGよりも3倍も容量が多くなっちゃったなんて!

その後にPG派とMy派でいろいろコメントがついてます。
どうもでっかいPrimary Keyがついてるらしく、それがまずいんじゃないのか?的話になっていうように読めました。(斜め読みです)

— Junji Teramoto / teramoto.junji (a) lab.ntt.co.jp Master Yoda : Don’t think…feel…be as one with the Source. Help you, it will.

pgpool 3.0.0 & global development team

http://pgfoundry.org/forum/forum.php?forum_id=668

によると

PgPool team is about to form “PgPool Global Development Group” whose concept is pretty similar to PostgreSQL Global Development Group. i.e. a virtual entity on the Internet. PgPool is currently being developed by Tatsuo Ishii. We want to switch to “PgPool Global Development Group” which will lower the barrier for hackers who wants to join this project.

と、チーム体制の開発に移行するそうです。素晴らしい  :)

PostgreSQL 8.1.2リリース

PostgreSQL 8.1.2, 8.0.6, 7.4.11 と 7.3.13がリリースされています。

8.0と8.1の変更

— Character string locale comparison bug. This may require a REINDEX
on text column indexes in some locales, such as Hungarian.
— Prevent accidental changes of locale by plperl
— Two fixes for Japanese encodings
— Two fixes for COPY CSV
— Fixes for functions returning RECORD
— Fixes to autovacuum, dblink and pgcrypto

pgsql-jpに載っていますが問題が発生した場合分かりづらい日本語エンコーディング問題の修正が含まれています。

PostgreSQL 8.1.1リリース

PostgreSQL 8.1.1がリリースされました。

http://www.postgresql.org/ のLATEST RELEASESからダウンロードできます。

Outer Join、CHECK文の不具合、sub selectの不具合などが修正されているそうです。他にも色々修正されているので不具合でお困りの方は試してみてはいかがでしょうか?

Athlon64 3200+/3GB/SATA HDD/1GbE/MomongaLinux x86_64開発版 にPostgreSQL 8.1.1をインストールしてPentiumDのマシンからpgbenchを実行した結果 (スケールファクタ 10、TCP接続設定以外はデフォルト)

[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000
starting vacuum…end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 282.428887 (including connections establishing)
tps = 282.667102 (excluding connections establishing)
[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000 -S
starting vacuum…end.
transaction type: SELECT only
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 4822.840200 (including connections establishing)
tps = 4892.360725 (excluding connections establishing)
[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000 -N
starting vacuum…end.
transaction type: Update only accounts
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 301.807334 (including connections establishing)
tps = 302.361132 (excluding connections establishing)

share_buffers=20000 (デフォルトは1000)

[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000
starting vacuum…end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 329.863325 (including connections establishing)
tps = 330.396068 (excluding connections establishing)
[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000 -S
starting vacuum…end.
transaction type: SELECT only
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 5771.386160 (including connections establishing)
tps = 5875.513153 (excluding connections establishing)
[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000 -N
starting vacuum…end.
transaction type: Update only accounts
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 397.720948 (including connections establishing)
tps = 398.210553 (excluding connections establishing)