How do you properly escape strings without PreparedStatement?

Question

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.

Why not prepared statements? :( Whenever I have done dynamic-SQL, the string is only dynamic wrt. the structure/schema and not the data. (It is just as easy to build up the placeholders/data-bindings with the dynamic-SQL string.) — user166390
– user166390, Commented Jul 26, 2011 at 22:07

Jonas · Accepted Answer · 2011-07-26 22:11:36Z

5

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"

edited Jul 26, 2011 at 22:11

answered Jul 26, 2011 at 22:06

Jonas

131k103 gold badges330 silver badges408 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

randomguy Over a year ago

Thanks! Tho technically, it is part of JDBC, since it internally does it.

randomguy · Accepted Answer · 2011-07-26 22:58:50Z

1

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.

answered Jul 26, 2011 at 22:58

randomguy

12.3k16 gold badges77 silver badges102 bronze badges

Collectives™ on Stack Overflow

How do you properly escape strings without PreparedStatement?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related