SQL Injection in Search Fields

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!

6 thoughts on “SQL Injection in Search Fields

  1. Cury

    Could I ask a dumb question? What if u escape the ” character with a back slash?
    For example, x” (I’m searching an online foreign dictionary) gives a SQL error:

    Searching for: x”Database errorYou have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘%”‘ at line 1

    Bur when I do it like this: x\” it works.

    Thanks alot

    Reply
  2. Mr Khan

    how can i bypass this database error

    Error Number: 1064

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘%’ and status=1 order by id desc limit 0,30′ at line 1

    select * from movies where name like ‘%’%’ and status=1 order by id desc limit 0,30

    Filename: controllers/En.php

    Line Number: 129

    Reply
  3. zonduu

    I have the same exact behavior in one website i am testing, but when trying it with sqmal using “space2comment” i had no luck. Could you please tell me how was the payload used by sqlmap that worked? I want to try a sleep command or anything that really confirms this issue! Please!!!

    Reply
    1. Jerome Post author

      Sorry, but I wrote this article over 5 years ago so I can’t remember anything more about this particular case. In general, what I’d recommend is first focusing on getting a simple proof-of-concept working using manual injection. This will ensure you understand the nature of the query in which the injection point resides e.g. in the above case I had to cater for nested queries by adding )). Ideally, only once you’re getting reliable results from manual techniques is it worth throwing an automated tool like sqlmap at the problem. In this way, if the tool fails, you know either it’s your configuration of the tool or the tool itself. Without proving the case manually, another reason for the tool failing is that there isn’t a vulnerability there at all!

      Reply

Leave a Reply to zonduu Cancel reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>