CODEWITHSUNDEEP
postgresqlencodingjdbcescaping
randomguy
randomguy

Reputation: 12242

How do you properly escape strings without PreparedStatement?

I'm using the Statement's for executeUpdate and executeQuery. The string values I'm concatenating into the SQL query can contain at least the '\ character as well as Unicode.

PreparedStatement seems to do this automatically, but is there some utility function in the JDBC library to escape an arbitrary string for use in a SQL query?

Example errors I've ran into:

org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near

and

org.postgresql.util.PSQLException: ERROR: invalid Unicode escape
  Hint: Unicode escapes must be \uXXXX or \UXXXXXXXX.

Upvotes: 1

Views: 3211

Answers (2)

randomguy
randomguy

Reputation: 12242

The way JDBC does it is:

QueryExecutorImpl.parseQuery()

  • Breaks the string into fragments, handles single quotes, double quotes, line comments, block comments, dollar signs and takes note of ? for substitution.
  • Creates a SimpleQuery object

SimpleParameterList

  • Handles string encoding (UTF-8, bytea, etc.)

Unfortunately, to get the full string escaping (with encoding, handling of special chars, etc.), you'd probably need to somehow access and use QueryExecutorImpl and I wasn't able to figure out how to do that. PreparedStatement does internally use QueryExecutorImpl.

Conclusion, the best and easiest way is to probably use PreparedStatement.

Upvotes: 1

Jonas
Jonas

Reputation: 128807

No, it's not part of JDBC, and it's different for different database management systems. You should really use PreparedStatement for queries with parameters. This is more secure and it can perform better since the query can be compiled.

See 4.1. SQL Syntax - Lexical Structure in the PostgreSQL manual.

E.g.

The following less trivial example writes the Russian word "slon" (elephant) in Cyrillic letters:

U&"\0441\043B\043E\043D"

Upvotes: 5

Related Questions