How to safely escape arbitrary strings for SQL in PostgreSQL using Java
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.replaceAll("\\", "\\\\");
    
      //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][1]. They do not have a PostgresSQL codec yet. I looked at the [OracleCodec][2] 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.

  [1]: https://www.owasp.org/index.php/Main_Page
  [2]: http://code.google.com/p/owasp-esapi-java/source/browse/trunk/src/main/java/org/owasp/esapi/codecs/OracleCodec.java
How to safely escape arbitrary strings for SQL in PostgreSQL using Java
===

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.replaceAll("\\", "\\\\");
    
      //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][1]. They do not have a PostgresSQL codec yet. I looked at the [OracleCodec][2] 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.

  [1]: https://www.owasp.org/index.php/Main_Page
  [2]: http://code.google.com/p/owasp-esapi-java/source/browse/trunk/src/main/java/org/owasp/esapi/codecs/OracleCodec.java