Re: Control characters in sql statements close db connecti

Vrinda,

I would suggest that if your data contains \0 then it isn't text data,
but it is really binary data.  In postgres the BYTEA data type is for
handling binary data and it can store any octet including \0.  In the
jdbc driver you use PreparedStatements and the setBytes()/getBytes()
methods to work with binary data.

thanks,
--Barry

Mushran, Vrinda wrote:
> Thank you all for your help. Looks like if I remove ^@ from my select string
> then whether I use a prepared statement with bind variable or not (statement
> contains control characters in string constants), the statement executes
> without exceptions.
>
> Whether I use a bind variable as Barry suggested, I still get the same
> exception as long as the string contains ^@. The driver does not reject that
> character and instead throws the exception and closes the database
> connection.
>
> Given this, I guess, the application will have to filter out ^@ from data
> and not let the sql data contain it?
>
> As for ^@ being character code 0, that is correct I think. I was not aware
> that character code 0 or nul gets displayed as ^@. In java when I tried '^@'
> == 0x0000, I got back true so I assume they are the same.
>
> In the postgres 7.2 documentation, I found the following mentioned in
> section 1.1.2.1:
>
> "The character with the code zero cannot be in a string constant.".
>
> I did not find any escape sequence documented for this character. If you are
> aware of one, I would like to know.
>
> Thanks again.
>
> Vrinda
>
> -----Original Message-----
> From: Barry Lind
> To: Tom Lane
> Cc: Mushran, Vrinda; 'pgsql-jdbc@postgresql.org'
> Sent: 1/17/2003 12:49 PM
> Subject: Re: [JDBC] Control characters in sql statements close db connection
>
>
>
> Tom Lane wrote:
>
>>Barry Lind <blind@xythos.com> writes:
>>+
>
>
>
>>>Yes I would say this is expected behavior.  If you use the regular
>>>Statement object it is your responsibility to properly quote and
>
> escape
>
>>>the data.  However if you use a PreparedStatement then the driver
>>>handles it for you.
>>
>>
>>>So you would issue the following via a PreparedStatement:
>>>SELECT * FROM NEB_IPSNMPDEVICES WHERE NEB_IPSNMPDevices.PHY_ADDRESS =
>
> ?
>
>>>and then do a stmt.setString(1,"'^@`^]:u'");
>>
>>
>>But there is no escape for a \0, except in the limited context of
>>BYTEA values.  It'd probably be better for the driver to reject 'em.
>>
>>Does JDBC understand about BYTEA fields?  If so, it could generate the
>>correct escape when filling a parameter for a BYTEA value, and fail
>>otherwise.
>>
>
>
> Yes the driver understands bytea fields and deals with them
> appropriately.  But there are not currently any checks that prevent
> passing \0 in other areas.  The jdbc API uses different methods for
> binary data versus textual data and thus there are obvious places to add
>
> such checks.
>
> --Barry
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>