Edmund & DBI folks,
Please find enclosed a patch for DBD::Pg. It vastly simplifies how the
quote() method works, making it faster and more precise, and eliminates
the need for POSIX::isprint(). I've discussed this method with the
PostgreSQL developers, and they agree that this simplified patch is all
that is needed to support the BYTEA data type. I ask that you consider
adding it to the next release of DBD::Pg. To apply the patch, cd to the
directory with Pg.pm in it, and simply
patch -Np1 Pg.pm.patch
Discussion of the patch with the PostgreSQL developers may be found
here:
http://www.geocrawler.com/mail/thread.php3?subject=%5BGENERAL%5D+DBD%3A%3APg+BYTEA+Character+Escaping&list=12
Comments are welcome.
Regards,
David
--
David Wheeler AIM: dwTheory
[EMAIL PROTECTED] ICQ: 15726394
Yahoo!: dew7e
Jabber: [EMAIL PROTECTED]
diff -Naur old/Pg.pm new/Pg.pm
--- old/Pg.pm Tue Nov 27 08:28:17 2001
+++ new/Pg.pm Tue Nov 27 08:28:34 2001
@@ -104,7 +104,17 @@
{ package DBD::Pg::db; # ====== DATABASE ======
use strict;
- use POSIX(qw(isprint)); # necessary for quote()
+
+ # Characters that need to be escaped by quote().
+ my %esc = ( "'" => '\\047', # '\\' . sprintf("%03o", ord("'")), # ISO SQL 2
+ '\\' => '\\134', # '\\' . sprintf("%03o", ord("\\")),
+ "\0" => '\\000' # '\\' . sprintf("%03o", ord("\0")),
+ );
+
+ # Set up lookup for SQL types we don't want to escape.
+ my @no_escape;
+ grep { $no_escape[$_] = 1 } DBI::SQL_INTEGER, DBI::SQL_SMALLINT, DBI::SQL_DECIMAL,
+ DBI::SQL_FLOAT, DBI::SQL_REAL, DBI::SQL_DOUBLE, DBI::SQL_NUMERIC;
sub prepare {
my($dbh, $statement, @attribs)= @_;
@@ -341,48 +351,13 @@
sub quote {
my ($dbh, $str, $data_type) = @_;
-
return "NULL" unless defined $str;
- unless ($data_type) {
- $str =~ s/'/''/g; # ISO SQL2
- # In addition to the DBI method it doubles also the
- # backslash, because PostgreSQL treats a backslash as an
- # escape character.
- $str =~ s/\\/\\\\/g;
- return "'$str'";
- }
-
- # Optimise for standard numerics which need no quotes
- return $str if $data_type == DBI::SQL_INTEGER
- || $data_type == DBI::SQL_SMALLINT
- || $data_type == DBI::SQL_DECIMAL
- || $data_type == DBI::SQL_FLOAT
- || $data_type == DBI::SQL_REAL
- || $data_type == DBI::SQL_DOUBLE
- || $data_type == DBI::SQL_NUMERIC;
- my $ti = $dbh->type_info($data_type);
- # XXX needs checking
- my $lp = $ti ? $ti->{LITERAL_PREFIX} || "" : "'";
- my $ls = $ti ? $ti->{LITERAL_SUFFIX} || "" : "'";
- # XXX don't know what the standard says about escaping
- # in the 'general case' (where $lp != "'").
- # So we just do this and hope:
- $str =~ s/$lp/$lp$lp/g
- if $lp && $lp eq $ls && ($lp eq "'" || $lp eq '"');
- # also, escape the backslashes, always
- $str =~ s/\\/\\\\/g;
- # if the type is SQL_BINARY, escape the non-printable chars
- if ($data_type == DBI::SQL_BINARY ||
- $data_type == DBI::SQL_VARBINARY ||
- $data_type == DBI::SQL_LONGVARBINARY) {
- $str=join("", map { isprint($_)?$_:'\\'.sprintf("%03o",ord($_)) }
- split //, $str);
- }
- return "$lp$str$ls";
+ return $str if $data_type && $no_escape[$data_type];
+ $str =~ s/(['\\\0])/$esc{$&}/g;
+ return "'$str'";
}
}
-
{ package DBD::Pg::st; # ====== STATEMENT ======
