bytea型でバイナリデータを扱う際のワナ
やはり自称DB屋(何)としては、DBMSのことを書かないと始まらないだろうということで(謎)
PHPプログラムからPostgreSQLにいわゆるバイナリデータを放り込んでいろいろやる必要が生じたので、ちょっと調べてみると、主に以下の2通りがあるらしい。
- bytea型の列を作るやり方
- pg_catalog.pg_largeobjectテーブルを使うやり方
本当に巨大なデータを扱う際には、pg_largeobjectテーブルとそのためのAPIを使うのが楽なのだろうが、今回は以下の理由でbytea型を使うことにした。
- 格納するデータ量は大して大きくない(高々数MB)
- pg_largeobjectテーブルにはアクセス制御が利かないらしい
- pg_largeobjectテーブルの構造を見てみると結局はbytea型で、適当な大きさで分割するだけ
pg_largeobjectテーブルの構造は以下のような感じ。
test_db=# \d pg_catalog.pg_largeobject; テーブル "pg_catalog.pg_largeobject" カラム | 型 | 修飾語 --------+---------+---------- loid | oid | not null pageno | integer | not null data | bytea | インデックス: "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno) test_db=#
さて、前置きがちょっと長くなったが、ここから本題。
事前準備
今回の検証で使った環境は以下のとおり。
- CentOS 6.4
- PostgreSQL 8.4.18
- PHP 5.3.3
以下のようにテスト用のデータベースとテーブルを作成する。
postgres=# create database test_db; CREATE DATABASE postgres=# \l データベース一覧 名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権 -----------+----------+------------------+-------------+-------------------+----------------------- (中略) test_db | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | (4 行) postgres=# \c test_db psql (8.4.18) データベース "test_db" に接続しました。. test_db=# CREATE TABLE test1( test_db(# id SERIAL PRIMARY KEY, test_db(# data BYTEA NOT NULL test_db(# ); NOTICE: CREATE TABLEはシリアル列"test1.id"用に暗黙的なシーケンス"test1_id_seq"を作成します。 NOTICE: CREATE TABLE / PRIMARY KEYはテーブル"test1"に暗黙的なインデックス"test1_pkey"を作成します CREATE TABLE test_db#
最初にやったこと
以下のようなPHPプログラムを作って、データの投入と取り出し・表示を行った。
<?php $originalData = "Hello World\n\tHello PostgreSQL\nはろーわーるど\n"; $con = pg_connect("dbname=test_db user=postgres"); $data = pg_escape_bytea($con, $originalData); $res = pg_query_params($con, 'INSERT INTO test1(data) VALUES($1)', array($data)); $res = pg_query("SELECT * FROM test1"); while (($row = pg_fetch_assoc($res))) { var_dump(array($row['id'], pg_unescape_bytea($row['data']))); } pg_free_result($res); pg_close($con);
すると、実行結果は以下のような感じ。
array(2) { [0]=> string(1) "1" [1]=> string(127) "Hello World\012\011Hello PostgreSQL\012\343\201\257\343\202\215\343\203\274\343\202\217\343\203\274\343\202\213\343\201\251\012" }
‥あれ、エスケープされたものが戻ってない‥
そこで、エスケープ直後のデータを出力してみると‥
Escaped Data: Hello World\\012\\011Hello PostgreSQL\\012\\343\\201\\257\\343\\202\\215\\343\\203\\274\\343\\202\\217\\343\\203\\274\\343\\202\\213\\343\\201\\251\\012
なんか、バックスラッシュが多い気がする。
pg_query_paramsを使わない形にしてみる
さっきのプログラムをちょっと変えて、以下のようにする。
<?php $originalData = "Hello World\n\tHello PostgreSQL\nはろーわーるど\n"; $con = pg_connect("dbname=test_db user=postgres"); pg_query($con, 'DELETE FROM test1'); $data = pg_escape_bytea($con, $originalData); //$res = pg_query_params($con, 'INSERT INTO test1(data) VALUES($1)', array($data)); $res = pg_query($con, "INSERT INTO test1(data) VALUES('{$data}')"); $res = pg_query("SELECT * FROM test1"); while (($row = pg_fetch_assoc($res))) { var_dump(array($row['id'], pg_unescape_bytea($row['data']))); } pg_free_result($res); pg_close($con);
実行結果。
array(2) { [0]=> string(1) "5" [1]=> string(52) "Hello World Hello PostgreSQL はろーわーるど " }
今度はうまくいった。
結局‥
調べてみると、pg_escape_bytea関数は、クエリ文字列に直接埋め込むのに適切な形にエスケープするらしい。
PHPのドキュメントを見ると、以下のようなことが書いてある。
なるほど、確かにバックスラッシュとシングルクォートの二重エスケープを外してやれば、pg_query_paramsを使った場合でも期待通りの結果が得られる。
The reason pg_unescape_bytea() do not exactly reproduce the binary data created by pg_escape_bytea() is because the backslash \ and single quote ' are double escaped by the pg_escape_bytea() function. This will lead to image seems corrupted when retrieve from the bytea field. The proper way to escape&unescape a binary string into a PG bytea field as follow:<?php $escaped_data = str_replace(array("\\\\", "''"), array("\\", "'"), pg_escape_bytea($data)); /* and later unescape the escaped data from the bytea field with following to get the original binary data */ $original_data = pg_unescape_bytea($escaped_data)); ?>more details at: http://archives.postgresql.org/pgsql-php/2007-02/msg00014.php
でも、それだったら、「pg_escape_bytea⇔pg_unescape_bytea」の対称性を保持する意味でも、
- クエリ文字列に直接埋め込む(pg_queryを使う)場合は、pg_escape_byteaでエスケープした後にpg_escape_stringやpg_escape_literalでエスケープする。
- パラメータを分離する(pg_query_paramsを使う)場合は、pg_escape_byteaでエスケープした結果をそのまま使う。
っていう形の方が分かりやすい気がするけどな‥