Mangled high bit characters with pg_server_prepare=0 in DBD::Pg - Mailing list pgsql-sql
From | Bryce Nesbitt |
---|---|
Subject | Mangled high bit characters with pg_server_prepare=0 in DBD::Pg |
Date | |
Msg-id | 4AF1EB70.4000200@obviously.com Whole thread Raw |
List | pgsql-sql |
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