[Bug]: SQL allowed_tables bypass — quoted/schema-qualified identifiers extract nothing, compliance check fails open

[rahuldass19]

QWED-MCP Audit Finding: MED-06

Severity: 🟡 MEDIUM
Component: sql_engine.py — extract_tables / verify_sql_query schema-compliance check
Audit ID: MED-06
Re-audit of v0.2.1: Finding is NEW

---

Description

The allowed_tables compliance boundary is fail-open for quoted and schema-qualified identifiers. Empirically confirmed on v0.2.1:

extract_tables("SELECT * FROM `secret_table`")       # -> []      (nothing extracted)
extract_tables('SELECT * FROM "secret_table"')       # -> []
extract_tables("SELECT * FROM [secret_table]")       # -> []
extract_tables("SELECT * FROM main.users")           # -> ['main']  (schema, not table!)
extract_tables("SELECT * FROM db.schema.users")      # -> ['db']

Because nothing is extracted for quoted identifiers, the unauthorized-table check has nothing to compare — and silently passes:

verify_sql_query("SELECT * FROM `hidden_users`", allowed_tables=["users"])
# -> {'verified': True, 'message': 'SQL query passed verification', 'issues': []}

verify_sql_query("SELECT * FROM public.secret_data", allowed_tables=["public"])
# -> {'verified': True, ...}  # validated the SCHEMA name, not the table

An attacker with a query template that reaches verify_sql_query can read from any table by backtick-quoting it or by qualifying it with an allowed schema name.

---

Why This Violates QWED Philosophy

• Principle 2 — Fail Closed: When table extraction cannot identify the accessed tables, the compliance decision must be UNVERIFIABLE → block, not pass-by-absence-of-evidence.
• Principle 4 — Explicit Boundaries: The schema-compliance boundary currently depends on a regex that does not cover the identifier syntax of the databases it claims to protect (MySQL backticks, ANSI double quotes, SQL Server brackets, schema qualification).

---

Expected Behavior

• Extraction must handle: backtick-quoted, double-quoted, and bracket-quoted identifiers; schema.table and db.schema.table qualification (the rightmost component is the table); aliases after AS; subqueries (already partially handled).
• If any FROM/JOIN/UPDATE/INTO/DELETE FROM clause is present but no table can be extracted from it, the query must fail closed: verified=False, "could not determine accessed tables".
• Comparison must be against the resolved table name, not the qualifier.

---

Suggested Fix Direction

_TABLE_RE = re.compile(
    r"""(?:FROM|JOIN|UPDATE|INTO)\s+
        (?:[`"\[]?)                # optional quote/bracket
        ([a-zA-Z_][\w$]*\s*\.\s*)* # optional qualifiers (schema., db.schema.)
        [`"\[]?([a-zA-Z_][\w$]*)[`"\]]?  # table name (rightmost component)
    """,
    re.IGNORECASE | re.VERBOSE,
)

Plus a cross-check: count clause keywords vs extracted tables; mismatch → fail closed. Regression tests for every payload in this issue.

---

Environment

Field	Value
QWED-MCP Version	0.2.1
Component	SQL Verification Engine
File	src/qwed_mcp/engines/sql_engine.py (L110–134, L80–85)