Re: pg_[un]escape_bytea, pgsql 8.2.1, php 5.1.6, Linux

From: Marco Colombo <pgsql(at)esiway(dot)net>
To: ljb <lbayuk(at)pobox(dot)com>
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: pg_[un]escape_bytea, pgsql 8.2.1, php 5.1.6, Linux
Date: 2007-02-08 13:48:15
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-php

ljb wrote:
> gwchamb(at)gmail(dot)com wrote:
>> Apache 2.2.3, PostgreSQL 8.2.1, PHP 5.1.6, Linux
>> I have inserted (via pg_query_params) into a bytea field some binary
>> data (a JPEG image in this case) which I have escaped using
>> pg_escape_bytea. It appears, however, that the extracted data is
>> corrupt (NOT unescaped, more precisely), even after unescaping it with
>> pg_unescape_bytea. If I perform another (a subsequent)
>> pg_unescape_bytea, it appears to be partially unescaped, but there
>> still remain errors because the rest of the image is severely
>> distorted -- but minimally recognizeable as part of the original
>> image. What am I missing? I'm using the lo_* functions as an
>> alternative, but it's hard to dismiss the ease with which it appears
>> to deal with binary data with a bytea field.
> Interesting problem.
> The escaping done by pg_escape_bytea() is wrong for parameterized queries.
> With a binary-mode query parameter (which pg_query_params() can't do
> anyway), you want no escaping at all. With a text-mode parameter (as
> pg_query_params() does), you need to escape for only the bytea-input
> parsing, not the SQL parsing. So for example if your data has a byte with
> value 1, you need to pass that as the 4 characters: \001.
> pg_escape_bytea() returns that as the 5 characters: \\001 (unless the new
> 'standard conforming strings' is on), so it won't work. Nor can I think of
> another PHP escaping function that does work here.

Isn't it possible to partly undo the effect of pg_escape_bytea()?

Something like this:

/* some binary data */
$data = implode("", range("\000", "\377"));

echo "pg_escape_bytea():\n";
echo pg_escape_bytea($data), "\n\n";

echo "pg_escape_bytea(), corrected:\n";
echo str_replace("\\\\", "\\", pg_escape_bytea($data)), "\n\n";

Here's the result:

$ php -f test.php | fold -w 70
\\034\\035\\036\\037 !"#$%&''()*+,-./0123456789:;<=>?(at)ABCDEFGHIJKLMNOP

pg_escape_bytea(), corrected:
21\022\023\024\025\026\027\030\031\032\033\034\035\036\037 !"#$%&''()*

Isn't the latter ready for pg_query_params()?

The only doubt I have is about the double '. Is it needed for bytea
parsing or sql parsing? You may have to replace it with a single ', if
it's only for SQL:

str_replace(array("\\\\", "''"), array("\\", "'"),
pg_escape_bytea($data)), "\n\n";

The man page is not clear, the single quote is listed among the escaped
chars, but from the examples is seems that only SQL escaping is
performed. Compare with \:

SELECT E'\\\\'::bytea; -- after SQL parsing, you get \\
SELECT E'\''::bytea; -- after SQL parsing, you get '

it seems that the bytea parser is feeded with a double slash, but a
single quote. If so, the octal value 39 should be removed from the table
in the manual page, since it requires no escaping at bytea level.
pg_escape_bytea() seems to confirm this, it returns '' for ' but \\\\
for \. \ is escaped twice, ' only once.

Unfortunately I can't try it now with pg_query_params(), but I bet on
the second form... well no, maybe I can, yes, here we go:

$data = implode("", range("\000", "\377"));
$data_escaped = str_replace(
array("\\\\", "''"),
array("\\", "'"),

/* fill in the conn string, if required */
$db = pg_connect("");
pg_query($db, "CREATE TEMP TABLE atest (data bytea);");
pg_query_params($db, "INSERT INTO atest VALUES ($1)", array($data_escaped));

$res = pg_query($db, "SELECT data FROM atest");
$data2 = pg_unescape_bytea(pg_fetch_result($res, 0));

echo ($data2 == $data) ? "OK!" : "PANIC!", "\n";

$ php -f test.php

Seems to work...

In response to

Browse pgsql-php by date

  From Date Subject
Next Message Mihail Mihailov 2007-02-09 09:31:29 phpinfo(): postgresql versions' mismatch
Previous Message Vincent de Phily 2007-02-08 12:12:43 Re: pg_[un]escape_bytea, pgsql 8.2.1, php 5.1.6, Linux