The JSON SQL Injection Vulnerability

tl;dr

Many SQL query builders written in Perl do not provide mitigation against JSON SQL injection vulnerability.

Developers should not forget to either type-check the input values taken from JSON (or any other hierarchical data structure) before passing them to the query builders, or should better consider migrating to query builders that provide API immune to such vulnerability.

Note: 問題の発見者による日本語での説明はこちらです.

Background

Traditionally, web applications have been designed to take HTML FORMs as their input. But today, more and more web applications are starting to receive their input using JSON or other kind of hierarchically-structured data containers thanks to the popularization of XMLHttpRequest and smartphone apps.

Designed in the old days, a number of Perl modules including SQL::Maker have been using unblessed references to define SQL expressions. The following example illustrate how the operators are being specified within the users' code. The first query being generated consists of an equality match. The second query is generated through the use of a hashref to specify the operator used for comparison.

use SQL::Maker;
my $maker = SQL::Maker->new(…);

# generates: SELECT * FROM `user` WHERE `name`=?
$maker->select('user', ['*'], {name => $query->param('name')}); 

# generates: SELECT * FROM `fruit` WHERE `price`<=?
$maker->select('fruit', ['*'], {price => {'<=', $query->param('max_price')}});

This approach did not receive any security concern at the time it was invented, when the only source of input were HTML FORMs, since it is represented as a set of key-value pairs where all values are scalars. In other words, it is impossible to inject SQL expressions via HTML FORMs due to the fact that there is a guarantee by the query parser that the right hand expression of foo (i.e. $query->param('foo')) is not a hashref.


JSON SQL Injection

But the story has changed with JSON. JSON objects are represented as hashrefs in Perl, and thus a similar code receiving JSON is vulnerable against SQL operator injection.

Consider the code below.

use SQL::Maker;
my $maker = SQL::Maker->new(…);

# find an user with given name
$maker->select('user', ['*'], {name => $json->{'name'}}); 

The intention of the developer is to execute an SQL query that fetches the user information by using an equality match. If the input is {"name": "John Doe"} the condition of the generated query would be name='John Doe', and a row related to the specified person would be returned.

But what happens if the name field of the JSON was an object? If the supplied input is {"name": {"!=", ""}}, then the query condition becomes name!='' and the database will return all rows with non-empty names. Technically speaking, SQL::Maker accepts any string supplied at the key part as the operator to be used (i.e. there is no whitelisting); so the attack is not limited to changing the operator. (EDIT: Jun 3 2014)

Similar problem exists with the handling of JSON arrays; if the name field of the JSON is an array, then the IN operator would be used instead of the intended = operator.

It should be said that within the code snippet exists an operator injection vulnerability, which is referred hereafter as JSON SQL injection. The possibility of an attacker changing the operator may not seem like an issue of high risk, but there are scenarios in which an unexpected result-set of queries lead to unintended information disclosures or other hazardous behaviors of the application.

To prevent such attack, application developers should either assert that the type of the values are not references (representing arrays/hashes in JSON), or forcibly convert the values to scalars as shown in the snippet below.

use SQL::Maker;
my $maker = SQL::Maker->new(…);

# find an user with given argument that is forcibly converted to string
$maker->select('user', ['*'], {name => $json->{'name'} . ''}); 

Programmers Deserve a Better API

As explained, the programming interface provided by the SQL builders including SQL::Maker is per spec. as such, and thus it is the responsibility of the users to assert correctness of the types of the data being supplied.

But it should also be said that the programming interface is now inadequate in the sense that it is prone to the vulnerability. It would be better if we could use a better, safer way to build SQL queries.

To serve such purpose, we have done two things:

• developed SQL::QueryMaker
• introduced strict mode to SQL::Maker

SQL::QueryMaker and the Strict Mode of SQL::Maker

SQL::QueryMaker is a module that we have developed and released just recently. It is not a fully-featured query builder but a module that concentrates in building query conditions. Instead of using unblessed references, the module uses blessed references (i.e. objects) for representing SQL expressions / exports global functions for creating such objects. And such objects are accepted by the most recent versions of SQL::Maker as query conditions.

Besides that, we have also introduced strict mode to SQL::Maker. When operating under strict mode, SQL::Maker will not accept unblessed references as its arguments, so that it would be impossible for attackers to inject SQL operators even if the application developers forgot to type-check the supplied JSON values.

The two together provides a interface immune to JSON SQL injection. The code snippet shown below is an example using the features. Please consult the documentation of the modules for more detail.

use SQL::Maker;
use SQL::QueryMaker;

my $maker = SQL::Maker->new(
   …,
   strict => 1,
);

# generates: SELECT * FROM `user` WHERE `name`=?
$maker->select('user', ['*'], {name => $json->{‘name'}}); 

# generates: SELECT * FROM `fruit` WHERE `price`<=?
$maker->select('fruit', ['*'], {price => sql_le($json->{‘max_price’})}); 

Similar Problem may Exist in Other Languages / Libraries

I would not be surprised if the same proneness exist in other modules of Perl or similar libraries available for other programming languages, since it would seem natural from the programmers' standpoint to change the behaviour of the match condition based on the type of the supplied value.

Generally speaking application developers should not except that a value within JSON is of a certain type. You should always check the type before using them. OTOH we believe that library developers should provide a programming interface immune to vulnerabilities, as we have done in the case of SQL::Maker and SQL::QueryMaker.


Note: the issue was originally reported by Mr. Toshiharu Sugiyama, my colleague working at DeNA Co., Ltd.