Re: Gripe: bytea_output default => data corruption - Mailing list pgsql-general

From Vick Khera
Subject Re: Gripe: bytea_output default => data corruption
Date
Msg-id AANLkTikV2u1+YwO+hzcoLyiWeH=hR0okY8Jz8YjwY0KA@mail.gmail.com
Whole thread Raw
In response to Re: Gripe: bytea_output default => data corruption  (ljb <ljb9832@pobox.com>)
List pgsql-general
On Thu, Oct 21, 2010 at 7:12 PM, ljb <ljb9832@pobox.com> wrote:
Again: My complaint is that pre-9.0 libpq-based clients mis-decode the new
default hex format bytea data without reporting an error, and this danger is
insufficiently documented in the release notes.

I had some hoops thru which I had to jump to make our app compatible with both 8.x and 9.x so we could safely migrate our servers without having to coordinate a code push.  It wasn't that bad, but part of the problem is that the DBD::Pg driver does not understand the new format... but it does unescape the "\x" marker for me to a simple "x", since that follows the traditional un-escaping rules for values returned from Pg.  I don't like overriding defaults in the DB settings unless I *really* have to.

So my code now looks something like this:

    if (substr($value,2,100) =~ m/^[0-9a-f]+$/) {
      # hex coding of bytea from Postgres 9.0+
      $self->log_debug('bytea hex decode');
      # remove the leading \x. DBD::Pg descapes \x to x, so just remove x
      $value =~ s/^[^0-9a-f]+//;
      $value = pack('H*',$value); # convert hex to bytes
    } else {
      $self->log_debug('bytea escape decoded');
      # Postgres < 9.0 encode auto handled by DBD::Pg
    }

Unfortunately, Greg is saying that he has no time right now to release an updated DBD::Pg despite the fact that the code is written and merged into the development source tree... so it may be a while before perl people are happy.  Luckily my above code will work even when DBD::Pg learns to do auto-escaping of bytea new format.

At least the current $dbh->quote() method still seems to create acceptable escaping for postgres 9.0 when you tell it you have a bytea field type.


pgsql-general by date:

Previous
From: Peter Bex
Date:
Subject: Re: Advice needed on application/database authentication/authorization/auditing model
Next
From: Dmitriy Igrishin
Date:
Subject: Re: Advice needed on application/database authentication/authorization/auditing model