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("\\" | "\"", "");