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.

Solution 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.

Solution 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)

Solution 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.

Solution 4

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.

View more solutions

Comments

[Aaronaught]

Please, no. Don't try to sanitize with some flaky blacklisting system. If you have some mysterious but intense aversion to prepared statements then escape the strings.

[Aaronaught]

Good point, I guess I should have been more specific - a prepared statement won't do you any good if you pass it one big concatenated string instead of one with parameter placeholders!

[Lasse V. Karlsen]

Which is why I said "parameterized query", not "prepared query".

[shylent]

I didn't -1 you, but it might seem, that the first sentence of your answer could warrant a +1 (since it mentions xkcd), but then you get to the second sentence. And that's where it all falls apart.

[Coincoin]

Banning reserved keywords is a strategy only the TSA can afford to spend billions on and get away with.

[user72150]

looking over the code, several of these 'finder' DAO methods generate 'IN clauses' (i.e. "WHERE STATUS in (1,2,3)"). No excuses, but these are more difficult with prepared statements.

[user72150]

Ah yes. In general true, but in these specific cases we've found otherwise. Here's the situation: you have a DAO method which has a specification/criteria object with 30 fields on it. You could have one stored procedure with 30 parameters on it with sql that looks like this: SELECT last_name, first_name FROM member WHERE (@pLastName IS NULL OR @pLastName=last_name) AND (@pFirstName IS NULL OR @pFirstName=first_name) AND (@pSerialNbr IS NULL OR @pSerialNbr=serial_nbr) AND (@pZodiacSign IS NULL OR @pZodiacSign=zodiac_sign) AND (@pPartyRank IS NULL OR @pPartyRank=party_rank)

[user72150]

from my experience, the SQL optimizers/engines perform very poorly on such procedures; they re-use a very generic access plan (usually full-table scan) rather than recognize on-the-fly to use a particular index . Additionally, some of the values are in fact lists (i.e. 'WHERE rank in (1,2,3)' ). I have in places passed in comma-separated lists to a stored procedure, parsed and populated a temporary table, and joined against the temporary table but this in practice performs MUCH WORSE than a simple sql with an in clause.

[Aaronaught]

Not really. Just parameterize the individual IN tokens (WHERE status IN (?, ?, ?)).

[user72150]

finally, to summarize, from my experience : simple sql performs best. Super-fancy sql makes for super-slow. Optimizers and access-plan-generators aren't that smart. (also we support a few different db's so have limits how many hints we can provide)

[user72150]

one wrinkle: the list size can vary. dynamic list sizes present a problem, AFAIK the preparedStatement sql can have a long list of '?' (i.e. upper bound of the list size); you can set the first 3 and use setNull to set the last upperbound-3. i.e., : WHERE status IN (?,?,?,?,?,?,?,?,?,?)

[Aaronaught]

This is a fully-solved problem: javaranch.com/journal/200510/Journal200510.jsp#a2 Any of those approaches are better than raw strings.

[MatBailie]

If one must therefor use dynamically generated SQL, sanitize the input. By which I mean escaping strings, and validating numeric types, etc. There is plenty of pre-existing code to do that for you.

[David Soroko]

Ha, I was blinded by the authority of xkcd :-) Using prepared statements instead is obviously the way to go.

[jammycakes]

Stored procedures won't protect you against SQL injection if you smash strings together in the stored procedure itself and then exec() the result. I have seen this done. The important thing to emphasise is parametrised queries.

[Noon Silk]

No, I think it's 'absolutely'. There are no exceptions. Anything you think is an exception is actually something you need to solve another way.

[L̲̳o̲̳̳n̲̳̳g̲̳̳p̲̳o̲̳̳k̲̳̳e̲̳̳]

"The correct way to handle SQL injection is to use parameterized queries. Everything else is just pissing in the wind" Thank you for telling the truth! This is the answer to almost any security programming problem actually.

[Andrei Rînea]

Nice! Good solution but mostly I like "pissing in the wind" :))))))