T09 · Insecure Skill Coding Practices
Error
- Location
src/text2sql.py:95- Finding
LLM-Generated SQL Is Executed After Bypassable Blacklist Validation
- Content
View full analysis
bool: sql_upper = sql.upper().strip() # Must start with SELECT or WITH (Common Table Expressions) if not (sql_upper.startswith("SELECT") or sql_upper.startswith("WITH")): return False # Forbidden keywords that modify data or schema forbidden_keywords = [ "INSERT INTO", "UPDATE", "DELETE FROM", "DROP TABLE", "DROP DATABASE", "ALTER TABLE", "TRUNCATE TABLE", "CREATE TABLE", "CREATE DATABASE", "GRANT", "REVOKE", "COMMIT", "ROLLBACK" ] for keyword in forbidden_keywords: if re.search(r'\b' + re.escape(keyword) + r'\b', sql_upper): return False return True ``` ```python response = call_openai_sdk(messages) sql_query = self._extract_sql_from_llm(response) ``` ```python if not self._is_valid_select_statement(sql): error_msg = "安全警告: 仅允许SELECT查询, 检测到潜在DML/DDL命令" logger.warning(f"{error_msg} SQL: {sql}") return { "execution_result": None, "error_message": error_msg, "sql_query": sql } status, result = self.db_env._sync_query_gen_sql(sql) ``` ### Technical Analysis The application asks an LLM to generate SQL from caller-controlled natural language and then executes that SQL against the configured database. The only security boundary is a textual blacklist. Checking whether a string starts with `SELECT` or `WITH` does not prove that it is a side-effect-free query. PostgreSQL permits calls to privileged or volatile functions from a `SELECT`, including functions that may read server-side files, invoke extensions, or modify state. The blacklist is incomplete and does not validate statement count, syntax structure, referenced schemas, functions, tables, or resource scope. If the under ...[truncated 1107 chars]- Remediation
View remediation