A quick posting about a fun SQL injection I cracked last week (of course, it’s only when you’ve cracked them that they’re fun!). A colleague had found the classic sign of a problem – add a single quote and you get an error – but was having no luck doing anything more. I was getting nowhere with my test so I thought I’d take a look for a change of scene. The input field was in a search box so, for example, search=keyword' returned an error but search=keyword'' was fine. Anything more exciting than that, however, such as search=keyword' and '1'='1, didn’t seem to work as expected: in this case, an error was returned instead of the same set of results that the normal search=keyword produced.

The first thing I did was to try to terminate the query as simply as possible with no funny business. So in went search=keyword'-- but back came an error. It turned out that the injection point was inside a doubly nested query as search=keyword'))-- worked, producing the same results as search=keyword. After a bit of faffing about it occurred to me that spaces might be the issue. So I tried search=keyword'and'1'='1 (no spaces in there) and it worked! No error was returned – but it didn’t produce the same results as search=keyword, it returned no results at all. What produced the same results as search=keyword was search=keyword'or'1'='1. Okay, park that for now. I had found the main problem – and it was immediately clear what was going on.

With a developer’s hat on, what would you do if a user ran a search with multiple keywords? The obvious answer would be to split up the search terms with space as a delimiter, run a query on each one and then return all the results together. If that was true then search=keyword' and '1'='1 was running a database query against three terms: keyword', and, '1'='1. The first of these would fail (just like search=keyword' did), as would the last if it got that far. So next I tried search=keyword'/**/and/**/'1'='1 using the inline SQL comment characters and got the same result. Again, using AND returned no results but using OR was like a normal query with search=keyword. I had seen this kind of behaviour once before but I couldn’t remember what the context was, which is why I’ve written it down this time!

AND vs OR

In general, AND within a SQL statement (and thus in SQL injection too) is restrictive, narrowing the result set, whereas OR is inclusive, widening the result set. But, as with all SQL injection, it all depends on the underlying query. So what could be happening here?

Again, with the developer hat on, what else might you do with a user’s search terms? Well, it would be nice if you searched a little more widely, using them as stubs. In fact some of the SQL errors were giving this away (thanks, guys): Incorrect syntax near ‘%’. The % character is, of course, a wildcard used with LIKE. So when I searched for keyword, somewhere in the resulting query was LIKE '%keyword%'. This perfectly explains the AND vs OR behaviour…

When I injected search=keyword'and'1'='1 the resulting query included LIKE '%keyword'and'1'='1%'. So the AND clause I’d added was always evaluating to FALSE and hence no results were returned. Whereas injecting search=keyword'or'1'='1 produced LIKE '%keyword'or'1'='1%'. Even though one half of the OR clause was evaluating to FALSE, overall it returned TRUE when I got a positive hit on the keyword.

Since the injection point was inside a doubly nested query and this was a black box test, I had no idea what the real query was, but this certainly made sense. I tried a few more injections to test the theory just for the hell of it:

1. When I terminated the statement, AND and OR did their “usual” thing. Which is to say that search=keyword'/**/and/**/1=1))-- produced the same result as search=keyword whereas keyword'/**/or/**/1=1))-- produced lots of results. This is because I was now commenting out the final % along with the rest of the statement.
2. When I injected search=keyword'and'1%'='1 I got the same results as if there had been no injection. This was the real proof. Now the resulting query would have included LIKE '%keyword'and'1%'='1%' so my AND clause evaluated to TRUE when I got a positive hit on the keyword.
3. Finally, for what it was worth, search=word'and'1%'='1 produced the same result, showing that a % preceded the injection point.

sqlmap

One of the things that makes a great tool is the ability to customise it for a particular attack scenario. And sqlmap offers that in abundance. In this case a “tamper” script, which transforms the payloads in some way, worked a treat. One of the built-in tamper scripts is “space2comment” – bingo! In fact running sqlmap with this script allowed it to find the injection point. Without the script, though, sqlmap would have been stuck because, to quote the wiki page, “sqlmap itself does no obfuscation of the payload sent, except for strings between single quotes replaced by their CHAR()-alike representation”.

All this was a good reminder that, when things are getting tough, thinking like a developer can help to turn near-misses into exploitable flaws. Having said that, I’ve seen code in the past that I could never have guessed, when it was clear the developer wasn’t thinking at all!