Escaping SQL strings in Java

Question

Background:

I am currently developing a Java front panel for the Enterprise CMS (Business Objects) database. At the moment, I am creating a function that allows the user to create a user database query. I have already implemented measures so that the user can select only a subset of the available columns and operators that have been approved for user access (for example, SI_EMAIL_ADDRESS can be selected, while stronger fields such as SI_CUID cannot be). So far, everything is going smoothly, but the time has come to protect this feature from possible SQL injection attacks.

Question:

I am looking for a way to avoid entering custom strings. I have already seen PerparedStatement, however I am forced to use third-party APIs to access the database. These APIs are immutable for me, and direct access to the database is out of the question. Individual methods accept strings representing the queries that will be executed, thereby invalidating the PreparedStatement (which, as far as I know, should be executed against a direct connection to the database).

I have considered using String.replace (), but I don't want to reinvent the wheel, if possible. In addition, I am far from the security experts who developed the PerparedStatement.

I also looked at the Java API reference for PerparedStatement, hoping to find some toString () method. Alas, I could not find anything like it.

Any help is appreciated. Thank you in advance.

Literature:

Java - escape string to prevent SQL injection

Java equivalent for mysql_real_escape_string ()

Answer 1

Of course, it would be easier and safer to use PreparedStatement.

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

'Joe' Caffee' 

So, theoretically, you only need to replace one quote with two single quotes. However, there are some problems. First, some databases (e.g. MySQL) also (or only) support backslashes as evacuation mechanisms. In this case, you will need to double the backslash (also).

For MySQL, I suggest using MySQLUtils . If you are not using MySQL, you need to check which particular evacuation mechanisms to use.

Answer 2

You can still use the prepared expression. See This post: get query from java sql readystatement . Also, based on this post, you can use Log4JDBC to handle this.

Any of these options should prevent you from worrying about escaping strings to prevent SQL injection, since the prepared statement does this for you.

Answer 3

Although, there is no standard way to handle PHP mysql_real_escape_string () in Java. What I did was bind the replaceAll method to handle all aspects that might be required to avoid any exceptions. 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(); } 

code>