T09 · Insecure Skill Coding Practices
Error
- Location
- scripts/run_read_query.py:11
- Finding
- Read-Only SQL Validation Can Be Bypassed by Side-Effecting SELECT Operations<![CDATA[ ## Vulnerability Details **File Location**: `scripts/run_read_query.py`, lines 11-16 and 92-102 **Vulnerability Type**: Inadequate SQL validation and filesystem-capable database operations **Risk Level**: High ### Vulnerable Code ```python READ_ONLY_PREFIXES = ("select", "with", "show", "describe", "desc", "explain") BLOCKED_TOKENS = { "insert", "update", "delete", "replace", "alter", "drop", "truncate", "create", "grant", "revoke", "lock", "unlock", "set", "rename", "call", "load", "handler", "do", "prepare", "execute", "deallocate", } ``` ```python def _ensure_read_only(sql: str) -> None: normalized = _normalize_sql(sql) lowered = normalized.lower() if not lowered.startswith(READ_ONLY_PREFIXES): raise ValueError("Query must start with a read-only statement.") tokens = set(re.findall(r"[a-z_]+", lowered)) found = BLOCKED_TOKENS & tokens if found: raise ValueError(f"Blocked non-read-only token(s) found: {', '.join(sorted(found))}") ``` ### Technical Analysis The script attempts to enforce read-only behavior by checking the beginning of the SQL string and searching for a limited set of blocked lexical tokens. This is not equivalent to parsing the statement according to MySQL grammar. A statement beginning with `SELECT` is not necessarily free of side effects. In particular, MySQL supports `SELECT ... INTO OUTFILE` and `SELECT ... INTO DUMPFILE`, which can create files using the database server process and its privileges. Neither `outfile` nor `dumpfile` is blocked. The `LOAD_FILE()` function can also read files accessible to the database server. The tokenizer treats `LOAD_FILE` as the single token `load_file`, whereas the blocklist contains only `load`. Consequently, this function is not rejected. These operations remain subject to MySQL configuration, the database account's `FILE` privilege, filesystem permissions, and server-side path restrictions. Nevertheless, the script itself does not en ...[truncated 1414 chars]
- Remediation
- <![CDATA[ ## Remediation Suggestions 1. Replace prefix and regular-expression filtering with a MySQL-aware SQL parser. 2. Permit only a narrowly defined allowlist of statement AST forms, rather than trying to enumerate forbidden words. 3. Explicitly reject: - `INTO OUTFILE` - `INTO DUMPFILE` - `LOAD_FILE()` - locking clauses - stored procedure calls - user-defined functions with side effects - any construct that accesses the server filesystem 4. Execute queries with a dedicated database account that has only the minimum required `SELECT`, `SHOW VIEW`, and metadata permissions. 5. Revoke `FILE`, write, administrative, routine execution, and privilege-management permissions from that account. 6. Where supported, establish a transaction or session configured as read-only as an additional control. 7. Add tests containing side-effecting MySQL syntax, comments, unusual whitespace, common table expressions, quoted identifiers, and nested queries. 8. Treat SQL validation and database permissions as independent defense layers; client-side validation must not be the sole security boundary. ]]>