T09 · Insecure Skill Coding Practices
Warning
- Location
scripts/sqlite_connector.py:74- Finding
SQL Injection Through Unvalidated Identifiers and SQL Clauses
- Content
View full analysis
- Remediation
View remediation
str: if not isinstance(value, str) or not _IDENTIFIER.fullmatch(value): raise ValueError(f"Invalid SQL identifier: {value!r}") return value ``` 2. **Quote validated identifiers.** Use SQLite double-quoted identifiers and escape embedded quotes. Validation should still be applied to reject unexpected syntax: ```python def quote_identifier(value: str) -> str: validate_identifier(value) return f'"{value.replace(chr(34), chr(34) * 2)}"' ``` Apply this function separately to table names, column names, and generated index names. 3. **Do not accept free-form WHERE clauses in higher-level APIs.** Replace them with structured conditions whose values are always bound: ```python def delete_equal(self, table: str, conditions: Dict[str, Any]) -> int: quoted_table = quote_identifier(table) clauses = [ f"{quote_identifier(column)} = ?" for column in conditions ] sql = f"DELETE FROM {quoted_table} WHERE {' AND '.join(clauses)}" return self.conn.execute(sql, tuple(conditions.values())).rowcount ``` If advanced expressions must remain supported, expose them through an explicitly named trusted-input method such as `delete_raw_where()` and clearly document the security boundary. 4. **Allowlist schema definitions.** Do not accept arbitrary type and constraint strings. Parse requested types and options, then permit only supported values such as `INTEGER`, `REAL`, `TEXT`, `BLOB`, `PRIMARY KEY`, `NOT NULL`, and approved default forms. 5. **Validate batch rows consistently.** Ensure all rows have the same validated column set and build each value tuple according to the first row's explicit column ordering rathe ...[truncated 702 chars]