Обсуждение: Control characters in sql statements close db connection
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 = '^@`^]: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
AFAIK, if the backend accepts these characters, PreparedStatement.setString(column, "^@`^]:u") should escape any control characters. []'s Daniel Serodio On Fri, 2003-01-17 at 15:28, 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 = > '^@`^]: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@postgresql.org) -- Daniel Serodio <daniel@checkforte.com.br> CheckForte
"Mushran, Vrinda" <Vrinda@netopia.com> writes:
> "SELECT * FROM NEB_IPSNMPDEVICES WHERE NEB_IPSNMPDevices.PHY_ADDRESS =
> '^@`^]: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
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,"'^@`^]: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 = > '^@`^]: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@postgresql.org) >
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.
regards, tom lane
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