CODEWITHSUNDEEP
javasqloracle-databasesql-injection
tomasz-mer
tomasz-mer

Reputation: 3910

How to protect yourself against SQL Injection in dynamic queries?

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.

Upvotes: 5

Views: 5344

Answers (4)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153730

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:

Upvotes: 0

BadSkillz
BadSkillz

Reputation: 2003

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

Upvotes: -6

Joachim Sauer
Joachim Sauer

Reputation: 308021

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.

Upvotes: 9

dbf
dbf

Reputation: 6499

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

Upvotes: 0

Related Questions