Re: how to handle data containing '\0'

Hi experts,

I need to handle String data containing '\0' in my Java + JDBC program. Such data can happen at other systems and be sent to
PostgreSQL. Here is a sample to simulate it in Java code.

char c = '\0';
String val = "abc" + c + "def";

Now I have to support two versions of drivers, 7.4 and 8.1, but the behavior of driver depends on versions when I insert this String
value.

=== pg74.216.jdbc3.jar ===
java.lang.IllegalArgumentException: \0 not allowed
at org.postgresql.jdbc1.AbstractJdbc1Statement.escapeString(AbstractJdbc1Statement.java:1152)
at org.postgresql.jdbc1.AbstractJdbc1Statement.setString(AbstractJdbc1Statement.java:1133)
at org.postgresql.jdbc1.AbstractJdbc1Statement.setString(AbstractJdbc1Statement.java:1116)
at Bug5391.main(Bug5391.java:24)

=== postgresql-8.1-405.jdbc3.jar ===
Successfully finished without exception but the string is truncated at '\0' and only the first three characters, "abc", is inserted.
(I verified this with psql on the server side)

I understand the current protocol does not allow '\0' in String and basically I should remove '\0' before I try to insert but I and
team members may forget it somewhere. I'm feeling the 8.1 behavior is worse than 7.4 because user data is truncated without notice.
For the sample above, I expect "abcdef" rather than "abc".

What do you think about this?
My idea is to introduce new connection parameter to determine how to behave against '\0'.
Options may be

- throws an exception
- truncates at '\0'
- removes '\0'

Thanks,

ebi

"EBIHARA, Yuichiro" <ebihara(at)iplocks(dot)co(dot)jp> writes:

>
> I need to handle String data containing '\0' in my Java + JDBC program. Such data can happen at other systems and be sent to
> PostgreSQL. Here is a sample to simulate it in Java code.

As far as I know, it is not possible to store such strings in a
postgreSQL database (whatever the client interface used). The reason
seems to be that postgreSQL is written in C, and most C functions use
\0 as a string terminator.

On Wed, 17 May 2006, EBIHARA, Yuichiro wrote:

> I need to handle String data containing '\0' in my Java + JDBC program.
>
> Now I have to support two versions of drivers, 7.4 and 8.1, but the
> behavior of driver depends on versions when I insert this String value.
>

This was changed between 7.4 and 8.0 when prepared statements changed from
dynamically creating a sql string from the parameters to passing the
parameters separately. When creating a sql string the parameters had to
be carefully checked for ' and \ so checking for \0 was also done. When
passing the parameters separately there is no need for the ' and \ checks
so the \0 check was removed as well either unintentionally or for
perfomance reasons.

New server releases for all versions will be out this week that fix the
problem on the server side by explicitly rejecting \0 rather than
truncating.

Kris Jurka

Thanks, Kris.

I understand the behavior is server-side issue, not JDBC, and we are to be forced to explicitely remove '\0' with the latest server
releases.

It's not necessarily the best for me but better than now.

Finally, do you know the thread containing the discussion that triggered the change of server behavior?

Thanks,

ebi

> -----Original Message-----
> From: Kris Jurka [mailto:books(at)ejurka(dot)com]
> Sent: Monday, May 22, 2006 4:29 PM
> To: EBIHARA, Yuichiro
> Cc: pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] how to handle data containing '\0'
>
>
>
>
> On Wed, 17 May 2006, EBIHARA, Yuichiro wrote:
>
> > I need to handle String data containing '\0' in my Java + JDBC
> > program.
> >
> > Now I have to support two versions of drivers, 7.4 and 8.1, but the
> > behavior of driver depends on versions when I insert this
> String value.
> >
>
> This was changed between 7.4 and 8.0 when prepared statements
> changed from
> dynamically creating a sql string from the parameters to passing the
> parameters separately. When creating a sql string the
> parameters had to
> be carefully checked for ' and \ so checking for \0 was also
> done. When
> passing the parameters separately there is no need for the '
> and \ checks
> so the \0 check was removed as well either unintentionally or for
> perfomance reasons.
>
> New server releases for all versions will be out this week
> that fix the
> problem on the server side by explicitly rejecting \0 rather than
> truncating.
>
> Kris Jurka
>
>
>
>

On Mon, 22 May 2006, EBIHARA, Yuichiro wrote:

> I understand the behavior is server-side issue, not JDBC, and we are to
> be forced to explicitely remove '\0' with the latest server releases.
>
> It's not necessarily the best for me but better than now.
>
> Finally, do you know the thread containing the discussion that triggered
> the change of server behavior?
>

Original thread starts here:
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00743.php

Commit message here:
http://archives.postgresql.org/pgsql-committers/2006-05/msg00206.php

Kris Jurka