Escaping SQL Strings in Java

问题:

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()

回答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.

回答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.

回答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();

}