pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedDBD::Pg, bytea type
4+ messages / 3 participants
[nested] [flat]
* DBD::Pg, bytea type
@ 2001-03-08 19:48 Alex Pilosov <alex@pilosoft.com>
0 siblings, 2 replies; 4+ messages in thread
From: Alex Pilosov @ 2001-03-08 19:48 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: E.Mergl@bawue.de
Hiyas,
Currently, there's a problem using bytea type transparently with DBD::Pg
driver. Postgres requires you to quote things in a special way to handle
bytea data, for example, to insert a null character, you have to use
'\000'. Select, on other hand, will require you to uncode such data before
you can actually use it in perl.
I'm making patches for DBD::Pg which will properly quote bytea data
before insertion and unquote it on select. Let me know if its not a good
idea.
Another thing: Currently, Pg quote() method only will properly escape
backslash if the datatype is not specified. If the datatype is specified,
it'll give wrong result:
$dbh->quote('foo\\bar', SQL_CHAR) will result in 'foo\bar', but it should
result in 'foo\\bar'. Anyone minds if I fix this one as well?
-alex
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DBD::Pg, bytea type
@ 2001-03-08 22:04 ivan <ivan-pgsql-interfaces@420.am>
parent: Alex Pilosov <alex@pilosoft.com>
1 sibling, 0 replies; 4+ messages in thread
From: ivan @ 2001-03-08 22:04 UTC (permalink / raw)
To: Alex Pilosov <alex@pilosoft.com>; +Cc: pgsql-interfaces; E.Mergl@bawue.de
On Thu, Mar 08, 2001 at 02:48:16PM -0500, Alex Pilosov wrote:
> Hiyas,
>
> Currently, there's a problem using bytea type transparently with DBD::Pg
> driver. Postgres requires you to quote things in a special way to handle
> bytea data, for example, to insert a null character, you have to use
> '\000'. Select, on other hand, will require you to uncode such data before
> you can actually use it in perl.
>
> I'm making patches for DBD::Pg which will properly quote bytea data
> before insertion and unquote it on select. Let me know if its not a good
> idea.
Surely other 8-bit clean interfaces besides DBD::Pg run into this problem
also? Any insights?
I'd think that it should be transparant for both the quote() method and
placeholders. Placeholders also *segfault* the client (not something
we're used to in Perl-land) if you try to use more than 64k of data (not
sure if that's per-placeholder, statement, row or something else).
> Another thing: Currently, Pg quote() method only will properly escape
> backslash if the datatype is not specified. If the datatype is specified,
> it'll give wrong result:
> $dbh->quote('foo\\bar', SQL_CHAR) will result in 'foo\bar', but it should
> result in 'foo\\bar'. Anyone minds if I fix this one as well?
I've heard a variety of different problem reports about quote(). Here's a
patch one of my cow orkers sent me:
http://www.postgresql.org/mhonarc/pgsql-interfaces/2001-02/msg00199.html
--
meow
_ivan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DBD::Pg, bytea type
@ 2001-03-09 00:20 Alex Pilosov <alex@acecape.com>
parent: Alex Pilosov <alex@pilosoft.com>
1 sibling, 0 replies; 4+ messages in thread
From: Alex Pilosov @ 2001-03-09 00:20 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: E.Mergl@bawue.de
As described in the below email, here's the patch.
Few notes:
It will always properly unquote things of bytea type when retrieved from
select. If someone has code that relies on the old behaviour, you'll need
to change that.
It will only properly enquote things when you do
$sth->bind_param(1, $xxx, DBI::SQL_BINARY);
(ie you must specify binding type to bind_param).
On Thu, 8 Mar 2001, Alex Pilosov wrote:
> Hiyas,
>
> Currently, there's a problem using bytea type transparently with DBD::Pg
> driver. Postgres requires you to quote things in a special way to handle
> bytea data, for example, to insert a null character, you have to use
> '\000'. Select, on other hand, will require you to uncode such data before
> you can actually use it in perl.
>
> I'm making patches for DBD::Pg which will properly quote bytea data
> before insertion and unquote it on select. Let me know if its not a good
> idea.
>
> Another thing: Currently, Pg quote() method only will properly escape
> backslash if the datatype is not specified. If the datatype is specified,
> it'll give wrong result:
> $dbh->quote('foo\\bar', SQL_CHAR) will result in 'foo\bar', but it should
> result in 'foo\\bar'. Anyone minds if I fix this one as well?
>
> -alex
>
>
>
>
>
--
--
Alex Pilosov | http://www.acecape.com/dsl
CTO - Acecape, Inc. | AceDSL:The best ADSL in Bell Atlantic area
325 W 38 St. Suite 1005 | (Stealth Marketing Works! :)
New York, NY 10018 |
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DBD::Pg, bytea type
@ 2001-03-09 00:25 Alex Pilosov <alex@pilosoft.com>
0 siblings, 0 replies; 4+ messages in thread
From: Alex Pilosov @ 2001-03-09 00:25 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: E.Mergl@bawue.de
As described in the below email, here's the patch.
Few notes:
It will always properly unquote things of bytea type when retrieved from
select. If someone has code that relies on the old behaviour, you'll need
to change that.
It will only properly enquote things when you do
$sth->bind_param(1, $xxx, DBI::SQL_BINARY);
(ie you must specify binding type to bind_param).
On Thu, 8 Mar 2001, Alex Pilosov wrote:
> Hiyas,
>
> Currently, there's a problem using bytea type transparently with DBD::Pg
> driver. Postgres requires you to quote things in a special way to handle
> bytea data, for example, to insert a null character, you have to use
> '\000'. Select, on other hand, will require you to uncode such data before
> you can actually use it in perl.
>
> I'm making patches for DBD::Pg which will properly quote bytea data
> before insertion and unquote it on select. Let me know if its not a good
> idea.
>
> Another thing: Currently, Pg quote() method only will properly escape
> backslash if the datatype is not specified. If the datatype is specified,
> it'll give wrong result:
> $dbh->quote('foo\\bar', SQL_CHAR) will result in 'foo\bar', but it should
> result in 'foo\\bar'. Anyone minds if I fix this one as well?
>
> -alex
>
>
>
>
>
--
--
Alex Pilosov | http://www.acecape.com/dsl
CTO - Acecape, Inc. | AceDSL:The best ADSL in Bell Atlantic area
325 W 38 St. Suite 1005 | (Stealth Marketing Works! :)
New York, NY 10018 |
Attachments:
dbd-pg.patch (text/plain, 10.5K) download
diff --unified -r DBD-Pg-0.95.old/Pg.pm DBD-Pg-0.95/Pg.pm
--- DBD-Pg-0.95.old/Pg.pm Thu Mar 8 14:26:26 2001
+++ DBD-Pg-0.95/Pg.pm Thu Mar 8 15:31:32 2001
@@ -101,6 +101,7 @@
{ package DBD::Pg::db; # ====== DATABASE ======
use strict;
+ use POSIX(qw(isprint)); # necessary for quote()
sub prepare {
my($dbh, $statement, @attribs)= @_;
@@ -310,6 +311,7 @@
$names,
# name type prec prefix suffix create params null case se unsign mon incr local min max
#
+ [ 'bytea', -2, 4096, '\'', '\'', undef, 1, '1', 3, undef, '0', '0', 'BYTEA', undef, undef ],
[ 'bool', 0, 1, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'BOOLEAN', undef, undef ],
[ 'int8', 8, 20, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'LONGINT', undef, undef ],
[ 'int2', 5, 5, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'SMALLINT', undef, undef ],
@@ -329,7 +331,7 @@
[ 'timespan', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'INTERVAL', undef, undef ],
[ 'timestamp', 10, 19, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TIMESTAMP', undef, undef ]
#
- # intentionally omitted: char, bytea, all geometric types, all array types
+ # intentionally omitted: char, all geometric types, all array types
];
return $ti;
}
@@ -366,6 +368,15 @@
# 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";
}
}
Binary files DBD-Pg-0.95.old/blib/arch/auto/DBD/Pg/Pg.so and DBD-Pg-0.95/blib/arch/auto/DBD/Pg/Pg.so differ
diff --unified -r DBD-Pg-0.95.old/blib/lib/DBD/Pg.pm DBD-Pg-0.95/blib/lib/DBD/Pg.pm
--- DBD-Pg-0.95.old/blib/lib/DBD/Pg.pm Thu Mar 8 14:26:26 2001
+++ DBD-Pg-0.95/blib/lib/DBD/Pg.pm Thu Mar 8 15:31:32 2001
@@ -101,6 +101,7 @@
{ package DBD::Pg::db; # ====== DATABASE ======
use strict;
+ use POSIX(qw(isprint)); # necessary for quote()
sub prepare {
my($dbh, $statement, @attribs)= @_;
@@ -310,6 +311,7 @@
$names,
# name type prec prefix suffix create params null case se unsign mon incr local min max
#
+ [ 'bytea', -2, 4096, '\'', '\'', undef, 1, '1', 3, undef, '0', '0', 'BYTEA', undef, undef ],
[ 'bool', 0, 1, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'BOOLEAN', undef, undef ],
[ 'int8', 8, 20, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'LONGINT', undef, undef ],
[ 'int2', 5, 5, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'SMALLINT', undef, undef ],
@@ -329,7 +331,7 @@
[ 'timespan', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'INTERVAL', undef, undef ],
[ 'timestamp', 10, 19, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TIMESTAMP', undef, undef ]
#
- # intentionally omitted: char, bytea, all geometric types, all array types
+ # intentionally omitted: char, all geometric types, all array types
];
return $ti;
}
@@ -366,6 +368,15 @@
# 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";
}
}
diff --unified -r DBD-Pg-0.95.old/blib/man3/DBD::Pg.3 DBD-Pg-0.95/blib/man3/DBD::Pg.3
--- DBD-Pg-0.95.old/blib/man3/DBD::Pg.3 Thu Mar 8 14:26:26 2001
+++ DBD-Pg-0.95/blib/man3/DBD::Pg.3 Thu Mar 8 16:26:42 2001
@@ -93,7 +93,7 @@
.nr % 0
.rr F
.\}
-.TH Pg 3 "perl 5.005, patch 03" "10/Jul/2000" "User Contributed Perl Documentation"
+.TH Pg 3 "perl 5.005, patch 03" "8/Mar/2001" "User Contributed Perl Documentation"
.UC
.if n .hy 0
.if n .na
diff --unified -r DBD-Pg-0.95.old/dbdimp.c DBD-Pg-0.95/dbdimp.c
--- DBD-Pg-0.95.old/dbdimp.c Thu Mar 8 14:26:26 2001
+++ DBD-Pg-0.95/dbdimp.c Thu Mar 8 19:05:12 2001
@@ -86,6 +86,7 @@
/* basically we support types that can be returned as strings */
switch(dbtype) {
case 16: /* bool */
+ case 17: /* bytea */
case 18: /* char */
case 20: /* int8 */
case 21: /* int2 */
@@ -720,6 +721,15 @@
}
+/* if it LOOKS like a string, this function will determine whether the
+ type needs to be surrounded in single quotes */
+static int pg_sql_needquote(sql_type)
+ int sql_type;
+{
+ if (sql_type>1000 || sql_type == 17 ) { return 1; }
+ return 0;
+}
+
static int
pg_sql_type (imp_sth, name, sql_type)
imp_sth_t *imp_sth;
@@ -745,6 +755,8 @@
return 20; /* int8 */
case SQL_VARCHAR:
return 1043; /* varchar */
+ case SQL_BINARY:
+ return 17; /* bytea */
default:
if (DBIc_WARN(imp_sth) && imp_sth && name) {
warn("SQL type %d for '%s' is not fully supported, bound as VARCHAR instead");
@@ -998,8 +1010,8 @@
/* do we have input parameters ? */
if ((int)DBIc_NUM_PARAMS(imp_sth) > 0) {
/* we have to allocate some additional memory for possible escaping quotes and backslashes */
- /* Worst case is all character must be escaped and must be quoted */
- int max_len = imp_sth->all_params_len * 2 + DBIc_NUM_PARAMS(imp_sth) * 2 + 1;
+ /* Worst case is all character must be binary-escaped (\xxx) */
+ int max_len = imp_sth->all_params_len * 4 + DBIc_NUM_PARAMS(imp_sth) * 2 + 1;
statement = (char*)safemalloc(strlen(imp_sth->statement) + max_len );
dest = statement;
src = imp_sth->statement;
@@ -1086,25 +1098,38 @@
val = SvPV(phs->sv, len);
}
/* quote string attribute */
- if(!SvNIOK(phs->sv) && SvOK(phs->sv) && phs->ftype > 1000) { /* avoid quoting NULL, tpf: bind_param as numeric */
+ if(!SvNIOK(phs->sv) && SvOK(phs->sv) && pg_sql_needquote(phs->ftype)) { /* avoid quoting NULL, tpf: bind_param as numeric */
*dest++ = '\'';
}
while (len--) {
if (imp_dbh->pg_auto_escape) {
+ /* if the parameter was bound as SQL_BINARY, escape nonprintables */
+ if (phs->ftype == 17 && !isPRINT(*val)) { /* escape null character */
+ dest+=snprintf(dest, strlen(imp_sth->statement)+
+ max_len+(statement-dest), "\\\\%03o", *val);
+ val++;
+ continue; /* do not copy the null */
+ }
/* escape quote */
if (*val == '\'') {
*dest++ = '\'';
}
/* escape backslash except for octal presentation */
- if (*val == '\\' && !(isdigit(*(val+1)) && isdigit(*(val+2)) && isdigit(*(val+3))) ) {
+ if ( *val == '\\') {
+ if (phs->ftype == 17) { /* four backslashes. really. */
+ *dest++ = '\\'; *dest++ = '\\';
*dest++ = '\\';
+ } else {
+ if (!(isdigit(*(val+1)) && isdigit(*(val+2)) && isdigit(*(val+3))) )
+ *dest++ = '\\';
+ }
}
}
/* copy attribute to statement */
*dest++ = *val++;
}
/* quote string attribute */
- if(!SvNIOK(phs->sv) && SvOK(phs->sv) && phs->ftype > 1000) { /* avoid quoting NULL, tpf: bind_param as numeric */
+ if(!SvNIOK(phs->sv) && SvOK(phs->sv) && pg_sql_needquote(phs->ftype)) { /* avoid quoting NULL, tpf: bind_param as numeric */
*dest++ = '\'';
}
}
@@ -1192,10 +1217,33 @@
sv_setsv(sv, &sv_undef);
} else {
char *val = (char*)PQgetvalue(imp_sth->result, imp_sth->cur_tuple, i);
+ int val_len=strlen(val); /* unless we change it for binary types */
int type = PQftype(imp_sth->result, i); /* hopefully these hard coded values will not change */
if (16 == type) {
*val = (*val == 'f') ? '0' : '1'; /* bool: translate postgres into perl */
}
+ if (17 == type) { /* decode \001 -> chr(1),etc, in-place */
+ char *p = val; /* points to next available pos */
+ char *s = val; /* points to current scanning pos */
+ int c1,c2,c3;
+
+ while(*s) {
+ if (*s == '\\') {
+ if (*(s+1) == '\\') { /* double backslash */
+ *p++='\\'; s+=2; continue;
+ }
+ else if ( isdigit(c1=(*(s+1))) &&
+ isdigit(c2=(*(s+2))) &&
+ isdigit(c3=(*(s+3))) ) {
+ *p++=(c1-'0')*64 + (c2-'0')*8 + (c3-'0');
+ s+=4;
+ continue;
+ }
+ }
+ *p++=*s++;
+ }
+ val_len=(p-val);
+ }
if (1042 == type && DBIc_has(imp_sth,DBIcf_ChopBlanks)) {
int len = strlen(val);
char *str = val;
@@ -1204,7 +1252,7 @@
}
val[len] = '\0';
}
- sv_setpv(sv, val);
+ sv_setpvn(sv, val, val_len);
}
}
Binary files DBD-Pg-0.95.old/dbdimp.o and DBD-Pg-0.95/dbdimp.o differ
Only in DBD-Pg-0.95: ktrace.out
Only in DBD-Pg-0.95: x.pl
Only in DBD-Pg-0.95: x1
end of thread, other threads:[~2001-03-09 00:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-08 19:48 DBD::Pg, bytea type Alex Pilosov <alex@pilosoft.com>
2001-03-08 22:04 ` ivan <ivan-pgsql-interfaces@420.am>
2001-03-09 00:20 ` Alex Pilosov <alex@acecape.com>
2001-03-09 00:25 Re: DBD::Pg, bytea type Alex Pilosov <alex@pilosoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox