SQL injection with JSON

A few days back while doing an assessment, I came across an application that used JSON based HTTP POST requests to retrieve data from their backend database. The application itself issued these requests using AJAX. JSON (JavaScript Object Notation) is used for serializing data over HTTP being transmitted between an application and server. Here is quick guide to JSON.

Now, in this particular application when certain fields are modified with some bad characters like single quote (‘) or double dash (--) it results in a stack trace which refers to some form of SQL error. From the looks and feel of the trace it’s probably a Microsoft IIS backend. 

The Request : 

The Response:

 

So to proceed with SQL injection exploitation, I used the Boolean based SQL injection test. Appending the offending parameter with ‘ and 1=1-- results in a response with the correct values. Subsequently appending with ‘ and 1=2-- results in empty set. This confirms the SQL injection vulnerability in the application.

Now, to create custom SQL queries and make them execute. Since this is a classic case of error based SQL injection the output can be seen in the error itself. A HTTP POST request is crafted with the following payload in the vulnerable parameter: ' UNION SELECT 1,@@version-- 

 This displayed the version number of the backend database server.

When trying the payload ' UNION SELECT 1,name FROM master..sysdatabases--  I encountered the following error “Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Arabic_CI_AS" in the UNION operation.” 

The application belongs to a client in the Middle East and the text fields were stored in Arabic character set. Collation means the character set used to store fields in a table of the database. This is used for supporting multiple languages for the database. This link provides a solution for this problem.

Armed with the solution, the payload now becomes ' UNION SELECT 1,name COLLATE Arabic_CI_AS FROM master..sysdatabases--   

Now, to automate this injection I used python. The following python code was used for dumping all the names of the databases. This code can also be modified to names of tables, contents of the table etc.

The identification marks have been blackened to protect the interest of the client.

Links:
http://pentestmonkey.net/cheat-sheet/sql-injection/mssql-sql-injection-cheat-sheet