Re: Control characters in sql statements close db connection

Virnda,

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,"'^(at)`^]:u'");

thanks,
--Barry

Mushran, Vrinda wrote:
> I am using PostgresSQL 7.2.1 and jdbc driver jar pg72jdbc2.jar that I
> downloaded from http://jdbc.postgresql.org/.
>
> Below is the select statement that fails:
>
> "SELECT * FROM NEB_IPSNMPDEVICES WHERE NEB_IPSNMPDevices.PHY_ADDRESS =
> '^(at)`^]:u'"
>
> Executing the statement results in:
>
> java.sql.SQLException: ERROR: Unterminated quoted string
>
> at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94)
> at org.postgresql.Connection.ExecSQL(Connection.java:398)
> at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
> at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
> at
> org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement
> .java:99)
> at TestPostgresJDBC.main(TestPostgresJDBC.java:92)
>
> Running this statement also causes the Connection to be closed.
>
> My questions are:
> 1. Is this expected behavior? I don't expect the connection to be lost.
> Ideally, unless there is such data, nothing is returned for this resultset
> or an exception is thrown but the connection is kept open.
> 2. Is there a way to escape such characters. I did not find any
> documentation on that.
> 3. Is this a jdbc driver issue or the server itself chokes on these
> characters? My guess is that the server chokes.
> 4. Is there any solution for this problem? The application does need to deal
> with control characters.
>
> Any help is appreciated.
>
> Vrinda Mushran
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>