Message-ID: <4AF1EB70.4000200@obviously.com>
Date: Wed, 04 Nov 2009 13:00:32 -0800
From: Bryce Nesbitt <bryce2@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.8.1.22) Gecko/20090605 SeaMonkey/1.1.17
	(Ubuntu-1.1.17+nobinonly-0ubuntu0.8.04.1)
MIME-Version: 1.0
To: sql pgsql <pgsql-sql@postgresql.org>
Subject: Mangled high bit characters with pg_server_prepare=0 in DBD::Pg
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I've just tracked down a serious bug with PG_BYTEA columns, which is probably perl specific.  But I thought
people might want to know:
 
Package: libdbd-pg-perl
Version: upgrading to 2.8.7 compared to 1.49.  Our 1.49 was patched for the memory leak in BYTEA.

With 2.8.7, if pg_server_prepare is set to zero, you can no longer store characters above
127 to the database.  pg_server_prepare has to be set to zero because the notoriously unstable
DBD:Pg distribution changed how bind parameters work, and pg_server_prepare=0 is the only way
to get back the original behavior.

The behavior is true if you declare it at connect time, or at statement handle time.


#!/usr/local/bin/perl -w
#
# Test script for character mangling bug in DBD:Pg
# Using this table:
# CREATE TABLE test (key serial,val1 text,val2 bytea);
#
use DBI;
use DBD::Pg;

printf("Testing %-20s: %s\n", DBD::Pg, DBD::Pg->VERSION );

# Prep string with values from 0 to 255
my $string;
for(my $i=0; $i<256; $i++) {
	$data .= chr($i);
	}

my($dbh) = DBI->connect("dbi:Pg:dbname=XXXX","XXXXXX","");
#$dbh->do("SET CLIENT_ENCODING TO 'LATIN1'") or die;
#$dbh->{pg_expand_array}++;

# Prepare 
my $context_key = 1;
$query_str = "UPDATE test SET val2=? WHERE key=?";
$sth = $dbh->prepare($query_str) or die;

# Should work, does work
$sth->bind_param(1, $data, {pg_type => DBD::Pg::PG_BYTEA});
$sth->bind_param(2, $context_key++);
$sth->execute() or die "Database Error: $DBI::err ... $DBI::errstr; query_str: $query_str";

# Should work, mangles characters above 128
$sth->{pg_server_prepare} = 0;
$sth->bind_param(1, $data, {pg_type => DBD::Pg::PG_BYTEA});
$sth->bind_param(2, $context_key++);
$sth->execute() or die "Database Error: $DBI::err ... $DBI::errstr; query_str: $query_str";

# Should fail due to mismatched primary key, but does not
$sth->bind_param(1, $data, {pg_type => DBD::Pg::PG_BYTEA});
$sth->bind_param(2, 9999999);
$sth->execute() or die "Database Error: $DBI::err ... $DBI::errstr; query_str: $query_str";

Here is a dump after a run. Note all \377 is returned for all high characters:
 key | val1 | val2
   1 |      | \000\001\002\003\004\005\006\007\010\011\012\013\014\015\016\017\020\021\022\023\024\025\026\027\030\031\032\033\034\035\036\037 !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~\177\200\201\202\203\204\205\206\207\210\211\212\213\214\215\216\217\220\221\222\223\224\225\226\227\230\231\232\233\234\235\236\237\240\241\242\243\244\245\246\247\250\251\252\253\254\255\256\257\260\261\262\263\264\265\266\267\270\271\272\273\274\275\276\277\300\301\302\303\304\305\306\307\310\311\312\313\314\315\316\317\320\321\322\323\324\325\326\327\330\331\332\333\334\335\336\337\340\341\342\343\344\345\346\347\350\351\352\353\354\355\356\357\360\361\362\363\364\365\366\367\370\371\372\373\374\375\376\377
   2 |      | \000\001\002\003\004\005\006\007\010\011\012\013\014\015\016\017\020\021\022\023\024\025\026\027\030\031\032\033\034\035\036\037 !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~\177\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377