How to protect yourself against SQL Injection in dynamic queries?

My application get开发者_开发知识库s in a String object query to the database. E.g. String query = EMAIL like '% test%' and USER_NAME like '% user%'. The query is built dynamically and I do not know its structure, so I can not take advantage of PrepareStatement. Does anyone know of a way that in this case to guard against SQL injection?

Database: Oracle

Language: Java 1.6

Please help.

Even if you do not know the structure, you can use a PreparedStatement. Let me demonstrate with a simple example:

List<Object> arguments = new ArrayList<Object>();
String sql = "SELECT * FROM user WHERE someCondition = ?";
if (queryOnEmail) {
  sql = sql + " AND email LIKE ?";
  arguments.add(email);
}
if (queryOnUserName) {
  sql = sql + " AND user_name LIKE ?";
  arguments.add(userName);
}
PreparedStatement stmt = con.prepareStatement(sql);
int i = 1;
for(Object o : arguments) {
  stmt.setObject(i, o);
  i++;
}

Of course you can wrap this SQL + arguments construct into its own class to simplify its usage.

You can dynamically build list of variables for statement when you build query and use PreparedStatement.

As explained in this post, there are more bad things that can happen to your application than the classic table DROP:

• call a sleep function so that all your database connections will be busy, therefore making your application unavailable
• extracting sensitive data from the DB
• bypassing the user authentication

Bottom line, you should never use string concatenation when building SQL statements. Use a dedicated API for that purpose:

• JPA Criteria API
• jOOQ

In addition: it is ALWAYS good practice to NEVER trust a users input. Before you process any input data first remove all escape and special characters: String.replace("\\" | "\"", "");