Hi, On Wed, Dec 11, 2002 at 09:26:38AM -0800, David Wheeler wrote: > On Wednesday, December 11, 2002, at 09:18 AM, Baldur Kristinsson wrote: > > > Here is an issue regarding DBD::Pg (I wouldn't exactly call it a bug, > > but it can be irritating nonetheless): > > > > If you try to pass a text value containing a null character as a bind > > value to a statement handle during execute(), you get the error > > > > "parser: unterminated quoted string at or near ..." > > > > There seems to be no way to escape the null byte without truncating the > > string that is being passed (I would guess this happens at the libpq > > level rather than DBD::Pg, though). > > > > If you call $dbh->quote on the value and try to insert it directly into > > the statement, the null byte is tacitly removed. > > > > This seems to me to be better behaviour than the above. So I think it's > > sensible to remove null bytes from values for other types of columns > > than BYTEA. > > Interesting. However, the null byte is by definition a string > terminator, so it makes no sense to me to include any data in a string > beyond the null byte. The behavior of quote() to tacitly strip out a > null byte is actually a bug (left over from an earlier implementation) > that should be fixed.
It may normally be a string terminator in C programs, but in Perl strings it is not. People using DBD::Pg are not writing C, they're writing Perl - in fact, probably in most cases they know next to no C. Besides, there is a glaring inconsistency with the way $dbh->quote works. In my opinion nothing which "works" using $dbh->quote and direct embedding in a SQL statement should fail to work using placeholders and bind values. If people learn that embedding works and placeholders don't, they adopt programming habits that are even worse than those they already have ;-) In DBD::Informix the behaviour differs somewhat: Strings containing null bytes are tacitly truncated in the case of char, varchar and lvarchar. However, if you're using custom datatypes such as "informix".html and "informix".ifxmrdata, the null bytes make it into the database. In fact, I discovered this issue when migrating from Informix to PostgreSQL - I was moving data from an ifxmrdata column in Informix into a text column in PostgreSQL. I'm not saying that DBD::Informix behaviour is better, but that there should be more consistency between DBD drivers in this regard. > > I think that if you have a string with a null byte in it and you want > to put it into a field other than BYTEA, your code should be > responsible for removing it before it passes it to DBI. > > dbi-dev'ers, please correct me if I'm wrong. :-) Hmmm... Certainly it's not difficult to add a "s/\0//g" whenever you're handling data from a source which could pass the damned thing on to you. But isn't it one of Perl's founding principles to Do What I Mean - without always being told exactly how it should do it...? (As an aside, I don't see why text columns in PostgreSQL should not be allowed to contain null bytes. After all, it cannot be a question of storage format, as there always are four bytes at the start of a PostgreSQL TEXT field telling us how long the string is. In some of my attempts I seemed to successfully escape the string as '\\000' so it made it to the Pg parser where it was truncated. If it made it that far, why not take note of the escape and just bloody well insert the whole thing? But of course that has nothing to do with DBD::Pg...) -- Regards, - Baldur
