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
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'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:
'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
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:
- 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
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.
- When I injected
search=keyword'and'1%'='1I 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.
- Finally, for what it was worth,
search=word'and'1%'='1produced the same result, showing that a % preceded the injection point.
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!