Defending against a 'WAITFOR DELAY' sql injection attack?

Question

Hi all,

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

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 2

How about follow xkcd and sanitize the input. You could check for reserved words in general and for WAITFOR in particular.

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.

Answer 4

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 5

To answer all your questions:

Is this a viable solution for the WAITFOR attack?

No. Just add -- to the attack string and it will ignore your fix.

Does it defend against other similar vulnerabilities?

No. See above.

I think the best option would entail using prepared statements. More work, but less vulnerable.

Yes. You don't fix SQL injection yourself. You use what's already existing and you use it right, that is, by parametrizing any dynamic part of your query.

Another lesser solution is to escape any string that is going to get inserted in your query, however, you will forget one one day and you only need one to get attacked.

Answer 6

Everyone else has nailed this (parameterize!) but just to touch on a couple of points here:

• Is this a viable solution for the WAITFOR attack?
• Does it defend against other similar vulnerabilities?

No, it doesn't. The WAITFOR trick is most likely just being used for 'sniffing' for the vulnerability; once they've found the vulnerable page, there's a lot more they can do without DDL or (the non-SELECT parts of) DML. For example, think about if they passed the following as last_name

' UNION ALL SELECT username, password FROM adminusers WHERE 'A'='A

Even after you add the AND 1 = 1, you're still hosed. In most databases, there's a lot of malicious things you can do with just SELECT access...