HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

bytea型でバイナリデータを扱う際のワナ

やはり自称DB屋(何)としては、DBMSのことを書かないと始まらないだろうということで(謎)


PHPプログラムからPostgreSQLにいわゆるバイナリデータを放り込んでいろいろやる必要が生じたので、ちょっと調べてみると、主に以下の2通りがあるらしい。

  • bytea型の列を作るやり方
  • pg_catalog.pg_largeobjectテーブルを使うやり方

本当に巨大なデータを扱う際には、pg_largeobjectテーブルとそのためのAPIを使うのが楽なのだろうが、今回は以下の理由でbytea型を使うことにした。

  1. 格納するデータ量は大して大きくない(高々数MB)
  2. pg_largeobjectテーブルにはアクセス制御が利かないらしい
  3. 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=# 


さて、前置きがちょっと長くなったが、ここから本題。

事前準備

今回の検証で使った環境は以下のとおり。

以下のようにテスト用のデータベースとテーブルを作成する。

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のドキュメントを見ると、以下のようなことが書いてある。


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_query_paramsを使った場合でも期待通りの結果が得られる。

でも、それだったら、「pg_escape_bytea⇔pg_unescape_bytea」の対称性を保持する意味でも、

  • クエリ文字列に直接埋め込む(pg_queryを使う)場合は、pg_escape_byteaでエスケープした後にpg_escape_stringやpg_escape_literalでエスケープする。
  • パラメータを分離する(pg_query_paramsを使う)場合は、pg_escape_byteaでエスケープした結果をそのまま使う。

っていう形の方が分かりやすい気がするけどな‥