How to protect yourself against SQL Injection in dynamic queries?

Question

My application gets 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.

Answer 1

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.