Escaping SQL Strings in Java

MysteryMoose picture MysteryMoose · May 9, 2012 · Viewed 10.1k times · Source

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

Answer

ametren picture ametren · May 9, 2012

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.