Re: Control characters in sql statements close db connection

"Mushran, Vrinda" <Vrinda(at)netopia(dot)com> writes:
> "SELECT * FROM NEB_IPSNMPDEVICES WHERE NEB_IPSNMPDevices.PHY_ADDRESS =
> '^(at)`^]:u'"
> java.sql.SQLException: ERROR: Unterminated quoted string

You're using ^@ to denote \0 (character code 0), right? Postgres
generally deals with text strings as null-terminated C strings; a null
character will not work in either a SQL command, or the value of a text
field.

Other control characters are not a problem, but if you need to store
nulls then there's little choice but to use BYTEA data type and escape
the nulls as "\0" (probably actually "\\000", check the docs).

> Running this statement also causes the Connection to be closed.

That I would not expect ... hmm ... if the JDBC driver sends the entire
string then there'd be a protocol-level problem: the null would
terminate the Query message, and then the stuff after it would look like
an invalid protocol message to the backend.

It would probably be a good idea for the JDBC driver to forcibly cut off
query strings at nulls, or maybe better, reject them with an error in
the first place. There is no comparable problem in the C interface
library since it sees the query string as null-terminated data to start
with.

regards, tom lane