T05 · Unauthorized Access and Privilege Escalation
Error
- Location
- scripts/duckdb_analyzer.py:547
- Finding
- Unrestricted DuckDB SQL Execution Permits Unauthorized Filesystem and Database Access<![CDATA[ ## Vulnerability Details **File Location**: `scripts/duckdb_analyzer.py:547-549` **Vulnerability Type**: Unrestricted execution of user-controlled SQL **Risk Level**: High ### Vulnerable Code ```python if sample_fraction is not None: if "WHERE" in sql.upper(): base_sql, where_part = sql.split("WHERE", 1) sampled_sql = f"{base_sql} WHERE RANDOM() < {sample_fraction} AND {where_part}" else: sampled_sql = f"{sql} WHERE RANDOM() < {sample_fraction}" result = self.conn.execute(sampled_sql).fetchdf() else: result = self.conn.execute(sql).fetchdf() ``` ### Technical Analysis The `--sql` argument is sent directly to `DuckDBPyConnection.execute()` without parsing the statement, enforcing read-only behavior, restricting accessible relations, or rejecting multiple statements. Although the documented purpose is querying the registered `data` table, DuckDB supports operations beyond analytical `SELECT` statements. Depending on the installed DuckDB version and process permissions, SQL can reference local files through table functions, attach databases, create or replace database objects, export data, or write files. The implementation does not reject dangerous SQL features such as: - Multiple statements - `COPY` - `ATTACH` and `DETACH` - `CREATE`, `DROP`, `INSERT`, `UPDATE`, and `DELETE` - `INSTALL` and `LOAD` - External file-reading table functions - Queries against objects other than the intended registered table Consequently, SQL generated from an untrusted request or influenced through prompt injection can exceed the legitimate data-analysis scope. The sampling transformation does not provide protection. It performs string manipulation and then executes the resulting SQL through the same unrestricted interface. ### Attack Path 1. An attacker supplies a natural-language request or direct `--sql` value containing a DuckDB statement that accesses an unintended local resource or performs a write operation. 2. The ...[truncated 1194 chars]
- Remediation
- <![CDATA[ ## Remediation Suggestions 1. Parse submitted SQL with a DuckDB-compatible SQL parser rather than validating it with regular expressions. 2. Permit exactly one statement and require its root operation to be a read-only `SELECT`. 3. Restrict referenced relations to an allowlist containing only the registered analysis table. 4. Reject DDL, DML, `COPY`, `ATTACH`, `DETACH`, `INSTALL`, `LOAD`, pragmas, external table functions, and other file or extension operations. 5. Reject semicolon-delimited multiple statements even if the first statement is allowed. 6. Open persistent databases in read-only mode when modification is not explicitly required. 7. Execute the analyzer in a sandbox with access only to the selected input and output paths. 8. Apply operating-system resource limits and narrowly scoped filesystem permissions. 9. Replace the current string-based sampling rewrite with an AST-based transformation or a safe wrapper query after validation. 10. Add security tests demonstrating that local file readers, file writers, extension loading, attachment, DDL, DML, and multiple statements are rejected. ]]>