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

On Thursday 08 February 2007, ljb wrote:
> gwchamb(at)gmail(dot)com wrote:
> > Vincent...
> >
> >> This would be a problem related to php, not postgres. I'm handling
> >> binary data in parameterized and COPY queries just fine with c++.
> >
> > I'm not ruling-out PHP, but I've seen claims of pg_[un]escape_bytea
> > being only wrappers to the Pg functions.
>
> That is correct, the PHP function relies on the PostgreSQL library to do
> the work. But the problem of using a parameterized query with bytea data
> is specific to PHP. PostgreSQL allows you to either escape the bytea
> data for use as a text-mode parameter, or pass it 'raw' as a binary-mode
> parameter. I don't know a good way to do the first in PHP, and the PHP
> PostgreSQL interface doesn't support the second at all.

This sparked my interest, so I checked the libpq docs
(http://www.postgresql.org/docs/8.2/interactive/libpq-exec.html) and wrote
some php tests.

The postgres doc say that the escape functions should only be used "for
Inclusion in SQL Commands", and that "it is not necessary nor correct to do
escaping when a data value is passed as a separate parameter in
PQexecParams".
But when I try to pass the raw data using pg_query_params(), postgres
complains about UTF8 (on a supposedly bytea field). Makes you wonder which
postgres function does pg_query_params() really wrap around...

I reallize I went all this way to just re-discover what has been said in this
thread, but if I needed the extra research, maybe other will find it
insightfull.
--
Vincent de Phily