Drupal core: unauthenticated SQL injection via JSON:API filter array keys

# RCA Report: CVE-2026-9082 — Drupal Core SQL Injection (SA-CORE-2026-004)

## Summary

CVE-2026-9082 is a highly critical SQL injection vulnerability in Drupal core's Entity Query SQL backend that affects only PostgreSQL-backed sites. The vulnerability exists because attacker-controlled array keys from entity query condition values are directly concatenated into named SQL placeholder identifiers in the PostgreSQL-specific `Condition::translateCondition()` method. An unauthenticated attacker can inject arbitrary characters (such as backticks, quotes, or SQL fragments) into the raw SQL text by providing malicious keys through JSON:API collection filter query parameters or the JSON login endpoint, resulting in SQL syntax errors or — with more sophisticated payloads — information disclosure and potential privilege escalation.

## Impact

- **Package/component affected**: Drupal core, specifically the PostgreSQL entity query condition handler (`core/modules/pgsql/src/EntityQuery/Condition.php`) and upstream condition compilation in `core/lib/Drupal/Core/Entity/Query/Sql/Condition.php` and `ConditionAggregate.php`
- **Affected versions**: Drupal 8.9.0 through <10.4.10, 10.5.0 through <10.5.10, 10.6.0 through <10.6.9, 11.0.0 through <11.1.10, 11.2.0 through <11.2.12, 11.3.0 through <11.3.10
- **Risk level**: Highly critical (20/25 on Drupal's scale; CVSS 3.1 base 6.5)
- **Consequences**: Unauthenticated SQL injection on PostgreSQL sites. Can lead to information disclosure, privilege escalation, and in some configurations remote code execution. MySQL/MariaDB and SQLite backends are **not** affected.

## Root Cause

The root cause is a trust boundary violation in `core/modules/pgsql/src/EntityQuery/Condition.php`. When processing an `IN` condition with case-insensitive comparison on PostgreSQL, the code iterates over `$condition['value']` and appends each array key directly into the SQL placeholder name:

```php
foreach ($condition['value'] as $key => $value) {
  $where_id = $where_prefix . $key;
  $condition['where'] .= 'LOWER(:' . $where_id . '),';
  ...
}
```

When JSON:API filter parameters or JSON login bodies decode JSON objects into associative PHP arrays, the attacker controls both the keys and values. A malicious key like `` ` `` (backtick) becomes part of the placeholder name: `LOWER(:node_field_data_title`)`. PDO then cannot bind `:node_field_data_title`` because it was never added to the bound arguments list, producing `SQLSTATE[HY093]: Invalid parameter number`.

More advanced payloads can inject actual SQL fragments (e.g., using `)` to close the `LOWER()` function call and `||` for PostgreSQL string concatenation), enabling boolean-blind SQL injection or error-based data exfiltration.

The fix is three `array_values()` calls that strip attacker-controlled keys before the vulnerable loop is reached:
- `core/lib/Drupal/Core/Entity/Query/Sql/Condition.php`
- `core/lib/Drupal/Core/Entity/Query/Sql/ConditionAggregate.php`
- `core/modules/pgsql/src/EntityQuery/Condition.php`

## Reproduction Steps

1. **Reference script**: `repro/reproduction_steps.sh`

2. **What the script does**:
   - Ensures PostgreSQL is running and creates a fresh `drupal` database with user `drupal`/`drupal`
   - Prepares the Drupal core repository (clones if needed, installs Composer dependencies)
   - **Vulnerable phase**: Checks out Drupal 11.3.9, installs a minimal site with PostgreSQL, enables the JSON:API module, creates a `page` content type and node, starts PHP's built-in server, and sends an anonymous GET request to `/jsonapi/node/page?filter[t][condition][path]=title&filter[t][condition][value][%60]=x`
   - **Fixed phase**: Repeats the same setup on Drupal 11.3.10 and sends the identical exploit request

3. **Expected evidence**:
   - **Vulnerable 11.3.9**: The JSON:API endpoint returns HTTP 500 with a `SQLSTATE[HY093]: Invalid parameter number` error. The response body contains the malformed SQL with the injected backtick inside the placeholder name (`:node_field_data_title``).
   - **Fixed 11.3.10**: The same request returns HTTP 200 with a normal JSON:API response body (`data: []`), confirming the injection is blocked.

## Evidence

### Log files
- `logs/vulnerable_response.txt` — HTTP response from the exploit against 11.3.9
- `logs/fixed_response.txt` — HTTP response from the same exploit against 11.3.10
- `logs/repro_output.txt` — Full console output from the first reproduction run
- `logs/repro_output_2.txt` — Full console output from the second (idempotency) run

### Key excerpts

**Vulnerable 11.3.9** (`logs/vulnerable_response.txt`):
```
HTTP_CODE:500
{"errors":[{"title":"Internal Server Error","status":"500","detail":"SQLSTATE[HY093]: Invalid parameter number: parameter was not defined: SELECT ... WHERE ((LOWER(\"node_field_data\".\"title\") = (LOWER(:node_field_data_title`)))) ..."}]}
```

**Fixed 11.3.10** (`logs/fixed_response.txt`):
```
HTTP_CODE:200
{"jsonapi":{"version":"1.1",...},"data":[],"links":{"self":{"href":"http://localhost:8080/jsonapi/node/page?..."}}}
```

### Environment details
- **PHP**: 8.4.19 (CLI + built-in server)
- **PostgreSQL**: 16.13
- **OS**: Ubuntu 24.04
- **Drupal test profile**: Minimal install profile with JSON:API module enabled

## Recommendations / Next Steps

1. **Immediate upgrade**: All Drupal sites running on PostgreSQL should upgrade to the patched releases: 10.4.10, 10.5.10, 10.6.9, 11.1.10, 11.2.12, or 11.3.10 (whichever matches their branch).
2. **Verify backend**: Site operators should confirm their database backend. Sites on MySQL, MariaDB, or SQLite are not affected by this specific vulnerability.
3. **WAF tuning**: If immediate patching is not possible, consider blocking or sanitizing JSON:API filter query strings that contain special characters (`
`, `'`, `"`, `)`, `|`, `--`) in filter value keys, as these are the characters that reach the vulnerable placeholder name construction.
4. **Regression testing**: After upgrading, verify JSON:API filter functionality still works correctly for normal use cases, since the fix reindexes condition value arrays with `array_values()`.

## Additional Notes

- **Idempotency confirmed**: `repro/reproduction_steps.sh` was executed twice consecutively with identical results (HTTP 500 + HY093 on 11.3.9, HTTP 200 on 11.3.10).
- **Limitations**: The reproduction requires a real running Drupal site over HTTP with a PostgreSQL database. A minimal PHP harness or mock objects would not satisfy the ticket requirements and were explicitly rejected.
- **Edge cases**: The vulnerability only triggers on the PostgreSQL-specific case-insensitive `IN` condition path. MySQL/MariaDB sites are unaffected because they use a different condition translation path that does not wrap comparisons in `LOWER()`.