Re: Escaping Binary Data in DBD::Pg

[David Wheeler]

On Sunday, November 24, 2002, at 12:28  PM, Tim Bunce wrote:

Looking at this again, I find that the code already does this, after a
fashion. If you use bind_param($col, $val, { pg_type => 17 }) (where 17
represents the PostgreSQL BYTEA type), then it does the proper
escaping. The thing is, if you use bind_param($col, $val, { pg_type => SQL_BINARY }),

Don't do that, it's wrong.

Oops, typo. What I meant is that, right now, if you use bind_param($col, $val, SQL_BINARY), DBD::Pg acts as if you'd used bind_param($col, $val, { pg_type => 17 }).

The value of the pg_type attribute is supposed to be the *PostgreSQL*
type value.  To use the ANSI standard type values you'd use the
TYPE attribute, ie { TYPE => SQL_BINARY }.

I take it that bind_param($col, $val, SQL_BINARY) is equivalent to bind_param($col, $val, { TYPE => SQL_BINARY})?

The TYPE should mainly be used to describe the value being passed from
the application to the driver. If a driver supports two types of
incompatible strings and the app uses SQL_BINARY, for example, then
the driver needs to default to using one of those string types when
it passes the value on to the database. If that default may not suit all
cases then the driver needs to provide a way to let the app specify
which type it should use. For example:

bind_param($col, unpack("H*",$val), { TYPE => SQL_BINARY, pg_type => 17 })

PostgreSQL should probably assume BYTEA when someone uses SQL_BINARY, so this wouldn't be necessary. But then, I seen no reason to really support SQL_BINARY in DBD::Pg at all at this point.

But in practice that would rarely be used because perl is happy
dealing with binary data so there's little need for binding a value
as a hex string. The app could just do:

  bind_param($col, $val, { pg_type => 17 })

Right, that's what it does now -- that works. What I think we should do is eliminate the equivalence of bind_param($col, $val, { pg_type => 17 }) to bind_param($col, $val, SQL_BINARY), since they're not actually the same. Using bind_param($col, $val, SQL_BINARY) would be an error -- I would remove that syntax altogether, since it isn't properly supported, anyway.

But, having said all that, supporting SQL_BINARY probably shouldn't
be high on your list of priorities. Very few apps use it.

Right, I want to dump DBD::Pg's current bogus support for SQL_BINARY. The question is, should it be done now, for the next release, or should we document that it will disappear in the release after next, and maybe generate warnings in this release.

I guess the question really is, how prevalent is the user of the broken SQL_BINARY binding in DBD::Pg today? I personally have never used it, and so would be inclined to dump it right now.

Regards,

David

--
David Wheeler AIM: dwTheory
[EMAIL PROTECTED] ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: [EMAIL PROTECTED]