Abusing JSON-Based SQL

Overview

Modern databases, such as PostgreSQL, natively support JSON as data values that can be queried. This capability uses JSON-specific operators, including an operator to test for key presence. Imperva Threat Research has investigated these database native JSON operators and discovered numerous SQL injection (SQLi) bypasses.

For those that are already familiar with SQL injection, we have essentially found alternatives to the very basic “or 1=1” payload. As you will see in this blog post, these payloads are straightforward to compute. Ultimately, if a database has support for JSON data values and operators, an attacker could possibly compromise this data.

Generating the Bypasses

JSON operators in databases can be used by attackers to construct a SQL injection. A common SQL injection technique is to force a tautology (statement of truth, e.g. 1=1) while packing on additional commands to execute. 

For the purposes of this blog post, we will examine PostgreSQL. To find a list of the JSON operators, please see this: https://www.postgresql.org/docs/12/functions-json.html. As researchers know, the usage of each operator will have unique characteristics and behaviors.

Process

Like most SQLi attacks there is going to be a specific injection point. For traditional databases, this is typically literal values within quotation marks (‘, “, `). Once we have found an injection point, like for instance literal values, an attacker will escape the rest of the original query. This can be achieved by commenting out the rest of the query using a double hyphen (–).

Operators of Interest

The list of operators that could be used to evaluate a tautology, without the need for “=”, include the following:

? – Does the string exist as a top-level key within the JSON value

?| – Do any of the array strings exist as a top-level key within the JSON value

?& – Do all of the array strings exist as a top-level key within the JSON value

@> – Does the left JSON value contain the path at the top-level

Some operators that could be used but require “=” include the following:

#> – Get the JSON object on a specific path

#>> – Get the JSON object at the specified path as text

->> – Get JSON array element

Generally, Web Application Firewalls (WAFs) can catch the use of “=” in specific values (e.g. query parameters, headers, etc.) as an indicator of SQL injection. These specific attack payloads usually contain “=”, “<”, “>” and then the database-specific escape sequence (“–”). What makes JSON operators dangerous is that an attacker can craft a tautology that does not use an equal sign, which will evade and bypass traditional WAF SQLi detection.

Payload Summary

Suppose we have a query that will take a user’s name from an input field in the frontend and execute a query to search based on this name in the backend. 

Let’s assume the query is fairly simple:

Adding the simple tautology in the input field in this case could be: 

Imperva Cloud WAF and WAF GW will catch a simple SQLi attack like the above and much more advanced ones. This includes if an attacker tries to use JSON operators. An attacker might try to put this in the input field:

In this specific case, an attacker can synthetically build a JSON dictionary with the key, “key” that corresponds to a value of “value.” Next, using the ? operator, we check the presence of a key called “key” in the JSON dictionary. Obviously, this will be true. We have effectively created a database tautology without using 2 literal values (1=1) and instead using JSON specific operators.

Further Examples

Here are some basic examples of SQLi attacks that were easily generated:

String existence as a top-level key:

Array string existence as a top-level key:

Array strings existence as a top-level key:

Left JSON containing the JSON path:

Check if the second element in the array exists and equals 3:

An attacker may also chain these operators to attempt to evade detection. As an example, an attacker could use a getter operator with an operator that will return a boolean value.

Here are some chained examples:

Imperva Customers are Protected from JSON Operator SQLi

As mentioned in this blog post, Imperva customers are already protected from the aforementioned attacks. Imperva Threat Research is continuing to evaluate this attack vector and will be updating our protections to defend against any new methods of exploitation.

Credit

Noam Moshe, a Vulnerability Researcher at Claroty, discovered this new attack vector and provided an in-depth overview in a Claroty blog post.