Thread: postgresql-9.1.1 PQescapeByteaConn() generating incorrectly quoted output.

Since upgrading test systems to postgresql 9.1, I am seeing some inserts
to bytea fields giving errors such as "ERROR:  invalid byte sequence for
encoding "UTF8": 0xf9" Where the insert is from a C program using libpq
and is of the form "insert into xxx values(E'%s')" where the value is
the return of PQescapeByteaConn();

I noticed that with postgresql 9.0, the return string was of the form
"\\x...." but with postgresql 9.1 it is "\x..."

I can work around this by specifying "E'\\%s" in the format string to
generate the query, but this will only work with postgrseql 9.1.

The following program illustrates the issue.


#include <stdio.h>
#include <libpq-fe.h>
#include <sys/types.h>

int main(int argc, char *argv[])
{
   PGconn *conn;
   struct
   {
      u_int64_t byte1;
      u_int64_t byte2;
   } bindata;

   char *enc;
   size_t elen;

   conn =  PQconnectdb("");

   bindata.byte1=0x0102030405060708;
   bindata.byte2=0x090a0b0c0d0e0f10;

   enc = PQescapeByteaConn(conn, (unsigned char *)&bindata, sizeof(bindata), &elen);

   printf("Server version %d\nEncoded string = %s\n", PQserverVersion(conn), enc);
   PQfreemem(enc);
   exit(0);
}

Running the above program with postgres 9.0 & 9.1 generates the
following output.

graham@gmdev ~ $ ./byteatest
Server version 90101
Encoded string = \x0807060504030201100f0e0d0c0b0a09
graham@gmdev ~ $ ./byteatest
Server version 90005
Encoded string = \\x0807060504030201100f0e0d0c0b0a09

Graham Murray <gmurray@webwayone.co.uk> writes:
> Since upgrading test systems to postgresql 9.1, I am seeing some inserts
> to bytea fields giving errors such as "ERROR:  invalid byte sequence for
> encoding "UTF8": 0xf9" Where the insert is from a C program using libpq
> and is of the form "insert into xxx values(E'%s')" where the value is
> the return of PQescapeByteaConn();

That is incorrect coding.  The result of PQescapeByteaConn has never
been meant to be put into an E'' literal.  You might have managed to
get away with it so long as standard_conforming_strings was off in the
server, but it's on by default in 9.1.

            regards, tom lane

On Wed, 2011-11-02 at 18:21 +0000, Tom Lane wrote:
> Graham Murray <gmurray@webwayone.co.uk> writes:
> > Since upgrading test systems to postgresql 9.1, I am seeing some
> inserts
> > to bytea fields giving errors such as "ERROR:  invalid byte sequence
> for
> > encoding "UTF8": 0xf9" Where the insert is from a C program using
> libpq
> > and is of the form "insert into xxx values(E'%s')" where the value
> is
> > the return of PQescapeByteaConn();
>
> That is incorrect coding.  The result of PQescapeByteaConn has never
> been meant to be put into an E'' literal.  You might have managed to
> get away with it so long as standard_conforming_strings was off in the
> server, but it's on by default in 9.1.

Thanks.
 I have now changed it (for the next application release) to use
'plain' not E'' strings. Originally I was using non-E strings, but I
changed it because 9.0.x (with the default
standard_conforming_strings=off) gave a warning suggesting that they
should be E' escaped.