Tuesday, July 1, 2014

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:


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.

12 comments:

  1. Grammy 2017 will be at 5:00 PM - 8:30 PM on Sunday, February 12, 2017 in Los Angeles at the Staples Center. Read more:



    Grammys

    Grammy 2017

    Grammys 2017

    2017 Grammys

    Watch Grammy 2017

    Grammy Awards

    Grammy Awards 2017

    ReplyDelete
  2. This is the right blog for anyone who wants to find out about this topic. You realize so much its almost hard to argue with you (not that I actually would want…HaHa). You definitely put a new spin on a topic thats been written about for years.
    شركة نقل اثاث بالقطيف
    شركة نقل اثاث بالدمام
    شركة نقل اثاث بالدمام
    شركة نقل عفش بالدمام
    شركة نقل اثاث بالجبيل
    شركة نقل العفش بالخبر
    Thanks for this great post, i find it very interesting and very well thought out and put together. I look forward to reading your work in the future
    شركة نقل اثاث بالخبر
    ارخص شركة نقل اثاث بالدمام
    شركة نقل الاثاث بالدمام
    شركة نقل عفش بالدمام
    شركات نقل العفش والاثاث بالدمام والخبر على وجه الخصوص تعددت كثيرا ولكن القليل من شركات نقل الاثاث داخل الدمام والخبر ما زال يقدم الخدمة المنوط بها للعميل فنحن فى شركة نقل اثاث بالدمام وبالخبر افضل شركات نقل اثاث بالدمام والخبر والقطيف والجبيل على مستوي المملكه العربية السعودية وخاصة منها المنطقة الشرقية ونحن ارخص شركة نقل اثاث بالدمام وبالخبر

    ReplyDelete
  3. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
    شركة نقل اثاث بالقطيف
    نقل اثاث بالدمام رخيص
    Hey – great blog, just looking around some blogs, seems a really nice platform you are using. I’m currently using WordPress for a few of my blogs but looking to change one of them over to a platform similar to yours as a trial run. Anything in particular you would recommend about it?
    ارخص شركة نقل اثاث بالدمام
    شركة نقل عفش بالدمام
    شركة نقل اثاث بالخبر
    شركة نقل اثاث بالدمام

    ReplyDelete
  4. thank you so much for this very usefull information. It's just a very effectively written article. It will likely be useful to anyone who makes use of it
    I was very pleased to find this site.I wanted to thank you for this great read!! I definitely enjoying every little bit of it and I have you bookmarked to check out new stuff you post
    شركة تنظيف بالخبر
    شركة تنظيف بالجبيل
    شركة تنظيف بالدمام
    شركة تنظيف بالقطيف

    ReplyDelete
  5. I really enjoyed reading this post, big fan. Keep up the good work andplease tell me when can you publish more articles or where can I read more on the subject?
    شركة تنظيف بالجبيل
    شركة تنظيف بالدمام
    شركة تنظيف بالخبر
    شركة تنظيف بالقطيف

    ReplyDelete