Escaping SQL Strings in Java

Question

Background:

I am currently developing a Java front end for an Enterprise CMS database (Business Objects). At the moment, I am building a feature to allow the user to build a custom database query. I have already implemented measures to ensure the user is only able to select using a subset of the available columns and operators that have been approved for user access (eg. SI_EMAIL_ADDRESS can be selected while more powerful fields like SI_CUID cannot be). So far things have been going on swimmingly, but it is now time to secure this feature against potential SQL injection attacks.

The Question:

I am looking for a method to escape user input strings. I have already seen PerparedStatement, however I am forced to use third party APIs to access the database. These APIs are immutable to me and direct database access is out of the question. The individual methods take strings representing the queries to be run, thus invalidating PreparedStatement (which, to my knowledge, must be run against a direct database connection).

I have considered using String.replace(), but I do not want to reinvent the wheel if possible. In addition, I am a far cry from the security experts that developed PerparedStatement.

I had also looked at the Java API reference for PerparedStatement, hoping to find some sort of toString() method. Alas, I have been unable to find anything of the sort.

Any help is greatly appreciated. Thank you in advance.

References:

Java - escape string to prevent SQL injection

Java equivalent for PHP's mysql_real_escape_string()

Erickaericksen

Answer 1

Of course it would be easier and more secure to use PreparedStatement.

ANSI SQL requires a string literal to begin and end with a single quote, and the only escape mechanism for a single quote is to use two single quotes:

'Joe''s Caffee'

So in theory, you only need to replace a single quote with two single quotes. However, there are some problems. First, some databases (MySQL for example) also (or only) support a backslash as an escape mechanism. In that case, you would need to double the backslashes (as well).

For MySQL, I suggest to use the MySQLUtils. If you don't use MySQL, then you need to check what are the exact escape mechanisms to use.

Indult

Answer 2

You may still be able to used a prepared statement. See this post: get query from java sql preparedstatement. Also, based on that post, you may be able to use Log4JDBC to handle this.

Either of these options should prevent you from needing to worry about escaping strings to prevent SQL injection, since the prepared statement does it for you.

Reest

That first link of yours is mighty interesting. I am concerned with the potentially undefined behavior of the implicitly defined toString() method. The enterprise architects have made impressive efforts to ensure that the database itself is well-hidden (as right they should). I don't even know what DBMS it is running or if it will be changed in the future. – Erickaericksen

Actually, come to think of it, it doesn't look like PreparedStatement even has a publicly accessible constructor. Since I don't have a JDBC connection object, I am not sure how I might even get a PreparedStatment object to begin with. Hm. – Erickaericksen

Yeah, I can see how that would be a conundrum. I haven't used Log4JDBC but it looks like it might be able to generate the SQL for you based on the prepared statement. – Reest

If you can't use a prepared statement, the other answer is correct -- you should be able to use a double-apostrophe to escape apostrophes. Another option would be to URL-encode the text before you insert it. You'd do that using java.net.URLEncoder to encode and java.net.URLDecoder to decode again later. Of course, you have to remember to decode every time you pull data out if you do this. – Reest

Answer 3

Although, there is no standard way to handle PHP's mysql_real_escape_string() in Java What I did was to chain replaceAll method to handle every aspect that may be necessary to avoid any exception. Here is my sample code:

public void saveExtractedText(String group,String content) { try { content = content.replaceAll("\", "\\") .replaceAll("\n","\n") .replaceAll("\r", "\r") .replaceAll("\t", "\t") .replaceAll("\00", "\0") .replaceAll("'", "\'") .replaceAll("\"", "\\"");

    state.execute("insert into extractiontext(extractedtext,extractedgroup) values('"+content+"','"+group+"')");
} catch (Exception e) {
    e.printStackTrace();

}

Seaman