How to safely escape arbitrary strings for SQL in PostgreSQL using Java

Question

I have a special case requiring that I generate part of a SQL WHERE clause from user supplied input values. I want to prevent any sort of SQL Injection vulnerability. I've come up with the following code:

private String encodeSafeSqlStrForPostgresSQL(String str) {

  //Replace all apostrophes with double apostrophes
  String safeStr = str.replace("'", "''");

  //Replace all backslashes with double backslashes
  safeStr = safeStr.replace("\\", "\\\\");

  //Replace all non-alphanumeric and punctuation characters (per ASCII only)
  safeStr = safeStr.replaceAll("[^\\p{Alnum}\\p{Punct}]", "");

  //Use PostgreSQL's special escape string modifier
  safeStr = "E'" + safeStr + "'";

  return safeStr;
}

Questions:

• Do you see any issues?
• Can you provide a better solution?
• Are there any existing libraries to help with this?

Notes:

• This is a common question on SO and elsewhere, but the only answer I've seen is to always use PreparedStatements. Fwiw, I'm using JasperReports. I want to keep the query inside of JasperReports. The built-in Jasper parameter functions for query parameter handling (including the X{} functions) are not sufficient for what I need to parametrize. I could try creating a custom Jasper QueryExecutor that would allow me to inject my own X{} functions, but that's more complicated than just generating a dynamic SQL where clause with Jasper's $P!{} syntax.

• I looked at the OWASP libraries. They do not have a PostgresSQL codec yet. I looked at the OracleCodec though and its escaping seemed simplistic. I'm not sure it would be of much helping preventing SQL injection attacks.

• In my code I'm adding the E so as to not be dependent on PostgreSQL's standard_conforming_strings setting. Ideally I wouldn't have to add that and then the function wouldn't have to be PostgreSQL specific. More info: http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE .

Ideally I would've liked a more generic and robust solution that I knew would be safe and support all possible UTF-8 strings.

Answer 1

The most easiest way would be to use PostgreSQL's Dollar Quoting in the combination with a small random tag:

• For each invocation calculate a small, random tag (e.g 4 characters) (redundant)
• Look whether or not the quote tag is part of the input string.
• If it is, recalculate a new random tag.

• Otherwise build your query like this:

  $tag$inputString$tag$
  

This way you escape the whole hassle of different nested quoting techniques and you also set up a moving target by using a random tag.

Depending on your security requirements this might do the job or not. :-)

Answer 2

I asked a similar question here, but I think that the best thing to do is to use org.postgresql.core.Utils.escapeLiteral. This is a Postgres library so using it should be safe. If/when Postgres adds new string delimiters this method should be updated.