Monday, January 26, 2009

Why you should not use ‘OR 1=1’ when testing for SQL injection (part 1)

Everybody who has read a paper on SQL injection has seen the ‘OR 1=1’ example (or the similar ‘ OR ‘’=’). It is the classic method for bypassing authentication when an application does not sanitize user input before using it in SQL queries.

I think it is somewhat overused. Often I see pentesters throwing this kind of string into each input field, hoping to trigger an SQL injection or some kind of error. Some tools which test for SQL injection (for example Nessus) also do it.

So why do I think it’s bad? Well, because it can have a major impact on the query that’s executed and has the potential to break things. Production systems are often the target of pentests and we should try not to break those.

Unless you have the source code to the application, you can’t know exactly what happens with your input string. Most people assume they are injecting into SELECT statements, but of course, applications also use DELETE, UPDATE and INSERT, which modify the database. Let me give you a real life example, we once cam e across an application which did this ($articleID comes from user input):

‘SELECT title,text,hitcount FROM articles WHERE id=’ + $articleID

A prime target for SQL injection! But a little further in the code something else happens:

‘UPDATE articles SET hitcount=’ + $hitcount+1 ‘ WHERE id=’ + $articleID

So the application first retrieves the title,text and hitcount for a certain article from the database, all is well here, the only thing that happens if we enter ‘OR 1=1’ after the articleID is that the application will receive all articles from the database and will most likely pick the first one.

The next statement is a different story, as the application tries to change something. It has retrieved the ‘hitcount’ for the article in the previous query (the hitcount being the number of times the article has been viewed) and uses it in a different query, to update the hit counter with a new view. Our articleID is used again as well, but in this case the resulting query becomes something like:

UPDATE articles SET hitcount=1338 WHERE articleID=1234 OR 1=1

This will set the hitcount to 1338, but instead for just just article 1234, it changes it for all articles in the table, not what we intended to do! Of course this is in most cases a relatively harmless scenario (and an example of lousy software engineering), but had our articleID been used in a DELETE statement, all articles would have been deleted from the table.

In the next part of this article, I will describe an alternative approach.


Keith Lea said...

"Production systems are often the target of pentests and we should try not to break those."

This is a bit presumptuous. I guess you mean to say "never use OR 1=1 on your OWN website."

Niels Teusink said...

@Keith Lea

Thank you for your comment. I perform pentests for a living so I don't usually test my own systems.

I usually try to avoid to completely break the systems I am testing, especially if there are safer and better techniques available.

Rory McCune said...

it's a good point to say that pen testers should consider the likely queries that they're passinginjection strings to, before using them.
The most worrying trend I've seen is web app security testing tools, designed to be used by developers, that provide potentially destructive injection vectors...

TBH I wouldn't have ranked ' OR 1=1;-- as one of the worst potential candidates, but I can see your point that it could be dangerous in some cases...

Niels Teusink said...

Thanks for your commment Rory. I would love to hear some examples of dangerous injection vectors used by automated tools.

I have heard some stories of junior pentesters trying the ";DROP TABLE customers" vector from the SQL injection papers :)