Defending against a 'WAITFOR DELAY' sql injection attack?

Question

The problem

We need to defend against a 'WAITFOR DELAY' sql injection attack in our java application.

Background

[This is long. Skip to 'Solution?' section below if you're in a rush ]

Our application mostly uses prepared statements and callable statements (stored procedures) in accessing the database.

In a few places we dynamically build-and-execute queries for selection. In this paradigm we use a criteria object to build the query depending on the user-input criteria. For example, if the user specified values for first_name and last_name, the result querying always looks something like this:

SELECT first_name,last_name FROM MEMBER WHERE first_name ='joe' AND last_name='frazier'

(In this example the user would have specified "joe" and "frazier" as his/her input values. If the user had more or less critieria we would have longer or shorter queries. We have found that this approach is easier than using prepared statements and quicker/more performant than stored procedures).

The attack

A vulnerability audit reported an sql injection failure. The attacker injected the value 'frazier WAITFOR DELAY '00:00:20' for the 'last_name' parameter, resulting in this sql:

   SELECT first_name,last_name FROM MEMBER WHERE first_name ='joe' AND last_name='frazier' WAITFOR DELAY '00:00:20'

The result: the query executes successfully, but takes 20 seconds to execute. An attacker could tie up all your database connections in the db pool and effectively shut down your site.

Some observations about this 'WAITFOR DELAY' attack

• I had thought that because we used Statement executeQuery(String) we would be safe from sql injection. executeQuery(String) will not execute DML or DDL (deletes or drops). And executeQuery(String) chokes on semi-colons, thus the 'Bobby Tables' paradigm will fail (i.e. user enters 'frazier; DROP TABLE member' for a parameter. See. http://xkcd.com/327/)

• The 'WAITFOR' attack differs in one important respect: WAITFOR modifies the existing 'SELECT' command, and is not a separate command.

• The attack only works on the 'last parameter' in the resulting query. i.e. 'WAITFOR' must occur at the very end of the sql statement

Solution, Cheap Hack, or Both?

The most obvious solution entails simply tacking "AND 1=1" onto the where clause.

The resulting sql fails immediately and foils the attacker:

   SELECT first_name,last_name FROM MEMBER WHERE first_name ='joe' AND last_name='frazier' WAITFOR DELAY '00:00:20' AND 1=1

The Questions

• Is this a viable solution for the WAITFOR attack?
• Does it defend against other similar vulnerabilities?
• I think the best option would entail using prepared statements. More work, but less vulnerable.

Answer 1

The correct way to handle SQL injection is to use parameterized queries. Everything else is just pissing in the wind. It might work one time, even twice, but eventually you'll get hit by that warm feeling that says "you screwed up, badly!"

Whatever you do, except parameterized queries, is going to be sub-optimal, and it will be up to you to ensure your solution doesn't have other holes that you need to patch.

Parameterized queries, on the other hand, works out of the box, and prevents all of these attacks.

Answer 2

SQL injection is SQL injection - there's nothing special about a WAITFOR DELAY.

There is absolutely no excuse for not using prepared statements for such a simple query in this day and age.

(Edit: Okay, not "absolutely" - but there's almost never an excuse)

Answer 3

I think you suggested the solution yourself: Parameterized Queries.

How did you find that your dynamically built query is quicker than using a stored procedure? In general it is often the opposite.