Protection against the WAITFOR DELAY injection attack?

Question

Problem

We need to protect ourselves from the injection of the "WAITFOR DELAY" sql attack in our Java application.

Background

[It is long. Go to Solution? section below if you are in a hurry]

Our application mainly uses prepared statements and called statements (stored procedures) when accessing the database.

In several places, we dynamically create and execute queries for selection. In this paradigm, we use the 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 query for the result 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 had to specify "joe" and "frazier" as their input values. If the user had a more or less critical value, we would have longer or shorter queries. We found that this approach is simpler than using prepared instructions and faster / more efficient than stored procedures).

Attack

An audit of the vulnerability reported a denial of SQL injection. The attacker entered the value 'frazier WAITFOR DELAY '00: 00: 20' for the parameter 'last_name', resulting in this sql:

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

Result: the request is successful, but it takes 20 seconds to complete. An attacker can bind all database connections in the db pool and effectively shut down your site.

Some observations on this WAITFOR DELAY attack

• I thought that since we used Statement executeQuery (String), we would be safe from SQL injection. executeQuery (String) will not execute DML or DDL (deletes or deletes). And executeQuery (String) throttles in half-columns, so the "Bobby Tables" paradigm will not work (that is, the user enters "frazier, member of DROP TABLE" for the parameter. See http://xkcd.com/327/ )

• The WAITFOR attack has one important aspect: WAITFOR modifies an existing SELECT command and is not a separate command.

• The attack works only with the last parameter in the received request. that is, "WAITFOR" must be present at the very end of the sql statement

Solution, cheap hack or both?

The most obvious solution entails simply imposing "AND 1 = 1" on the where clause.

The resulting sql immediately crashes and resets 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 

Questions

• Is this a viable WAITFOR attack solution?
• Does it protect against other similar vulnerabilities?
• I think the best option would entail the use of trained operators. More work, but less vulnerable.

Answer 1

The right way to handle SQL injection is to use parameterized queries. Everything else just pisses in the wind. It may work once, even twice, but in the end you will get hit by such a warm feeling that says: "You messed up, bad!"

No matter what you do, besides parameterized queries, it will be suboptimal, and you decide that your solution does not have other holes that you need to plan for.

Parameterized queries, on the other hand, work out of the box and prevent all of these attacks.

Answer 2

SQL injection is SQL injection - there is nothing special about WAITFOR DELAY .

There is absolutely no excuse for not using prepared instructions for such a simple request on this day and age.

(Edit: Good, not "absolutely" - but almost never justified)

Answer 3

I think you yourself proposed a solution: Parameterized queries .

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

Answer 4

To answer all your questions:

Is this a viable WAITFOR attack solution?

Not. Just add attacks to the line and ignore your fix.

Does it protect against other similar vulnerabilities?

Not. See above.

I think the best option would entail the use of trained operators. More work, but less vulnerable.

Yes. You yourself do not fix SQL injection. You use what already exists, and use it correctly, that is, by parameterizing any dynamic part of your request.

Another smaller solution is to avoid any line that will be inserted in your query, however you forgot one day, and you only need one to attack.

Answer 5

All the others attached it (we parameterize!), But just touch here a few points:

• Is this a viable WAITFOR attack solution?
• Does it protect against other similar vulnerabilities?

No, it is not. The WAITFOR trick is most likely just used to “sniff” the vulnerability; after they find a vulnerable page, they can do much more without DDL or (non-SELECT parts) DML. For example, consider whether they passed the following as last_name

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

Even after you add AND 1 = 1, you will still be hosed. Most databases have many malicious things that you can only do with SELECT access ...

Answer 6

How about following xkcd and sanitizing the entrance. You can check reserved words in general and for WAITFOR in particular.