T09 · Insecure Skill Coding Practices
Warning
- Location
- scripts/compatibility_checker.py:235
- Finding
- Untrusted Schema Values Embedded in Generated SQL Statements<![CDATA[ ## Vulnerability Details **File Location**: `scripts/compatibility_checker.py`, lines 235–243, 350–356, 408–411, and 526–529 **Vulnerability Type**: SQL injection in generated migration and rollback scripts **Risk Level**: Medium ### Vulnerable Code ```python # Lines 235–243 for table_name in after_tables: if table_name not in before_tables: migration_scripts.append(MigrationScript( script_type="sql", description=f"Create new table {table_name}", script_content=self._generate_create_table_sql(table_name, after_tables[table_name]), rollback_script=f"DROP TABLE IF EXISTS {table_name};", dependencies=[], validation_query=f"SELECT COUNT(*) FROM information_schema.tables WHERE table_name = '{table_name}';" )) ``` ```python # Lines 350–356 scripts.append(MigrationScript( script_type="sql", description=f"Add column {col_name} to table {table_name}", script_content=f"ALTER TABLE {table_name} ADD COLUMN {self._generate_column_definition(col_name, col_def)};", rollback_script=f"ALTER TABLE {table_name} DROP COLUMN {col_name};", dependencies=[], validation_query=f"SELECT COUNT(*) FROM information_schema.columns WHERE table_name = '{table_name}' AND column_name = '{col_name}';" )) ``` ```python # Lines 408–411 script_content=f"ALTER TABLE {table_name} ALTER COLUMN {col_name} TYPE {after_type} USING {col_name}::{after_type};", rollback_script=f"ALTER TABLE {table_name} ALTER COLUMN {col_name} TYPE {before_type};", dependencies=[f"backup_{table_name}"], validation_query=f"SELECT COUNT(*) FROM {table_name} WHERE {col_name} IS NOT NULL;" ``` ```python # Lines 526–529 script_content=f"ALTER TABLE {table_name} ADD CONSTRAINT {constraint_type}_{table_name} {constraint_type.upper()} ({constraint});", rollback_script=f"ALTER TABLE {table_name} DROP CONSTRAINT {constraint_type}_{table_name};", dependencies=[], validation_query=f"SELECT COUNT(*) FROM info ...[truncated 2863 chars]
- Remediation
- <![CDATA[ ## Remediation Suggestions 1. **Validate every SQL identifier** - Restrict table, column, type, and constraint names to a documented allowlist grammar. - Reject identifiers containing quotes, semicolons, comments, control characters, whitespace in unexpected positions, or multiple SQL tokens. - Apply length limits appropriate to the target database. 2. **Use database-aware SQL composition** - Build statements with the target driver’s identifier-composition API, such as `psycopg.sql.Identifier` for PostgreSQL. - Do not treat identifiers as ordinary query parameters because parameter placeholders generally apply only to values. 3. **Parameterize SQL literal values** - Validation queries containing table or column names as string values should use bound parameters when executed. - If the tool must emit standalone SQL text, escape literals through a trusted database-specific quoting implementation. 4. **Allowlist data types** - Map schema types to a fixed set of supported database types. - Do not insert arbitrary `before_type` or `after_type` strings into `ALTER TABLE` statements. 5. **Represent constraints structurally** - Parse constraints into validated fields such as type, columns, referenced table, and referenced columns. - Generate SQL from those fields rather than accepting and embedding raw constraint expressions. 6. **Separate analysis from executable output** - Clearly mark generated SQL as untrusted and requiring manual review. - Consider producing a structured migration representation rather than immediately emitting executable statements. - Require an explicit opt-in mode before producing destructive rollback commands. 7. **Add adversarial tests** - Test identifiers and constraints containing single quotes, double quotes, semicolons, SQL comments, newline characters, and appended statements. - Verify that malicious or malformed schema values are rejected rather than included in output. ]]>