T09 · Insecure Skill Coding Practices
Warning
- Location
- templates/mysql-table-template.sql:1
- Finding
- Generated SQL Unconditionally Drops Existing Tables## Vulnerability Details **File Locations**: - `templates/mysql-table-template.sql:1` - `SKILL.md:65` - `SKILL.md:132` - `README.md:69` **Vulnerability Type**: Destructive SQL generation without explicit confirmation **Risk Level**: Medium **Vulnerable Code Snippet**: ```sql DROP TABLE IF EXISTS `[module]_[table_name]`; ``` The documentation also includes concrete variants of the same pattern: ```sql DROP TABLE IF EXISTS `scm_purchase_contract`; ``` ```sql DROP TABLE IF EXISTS `sm_sales_contract`; ``` ```sql DROP TABLE IF EXISTS `pm_project`; ``` ### Technical Analysis The Skill's standard template and examples unconditionally place `DROP TABLE IF EXISTS` before the generated `CREATE TABLE` statement. Dropping an existing table is not required to design or create a new schema and is a destructive operation. If generated SQL is copied into a database client, deployment pipeline, or migration process, the statement deletes the existing table definition and all records before recreating an empty table. The use of `IF EXISTS` only suppresses an error when the table is absent; it does not protect existing data. No confirmation gate, backup verification, migration procedure, environment restriction, or explicit user opt-in is included. Because the Skill also recommends avoiding foreign-key constraints, database-level dependency checks may be less likely to prevent execution. ### Attack Path 1. A user asks the Skill to design a table whose generated name matches an existing table. 2. The Skill follows its template and emits `DROP TABLE IF EXISTS` before `CREATE TABLE`. 3. The user or an automated process executes the generated script against a development, staging, or production database. 4. MySQL drops the existing table and deletes its stored records. 5. The following `CREATE TABLE` statement recreates an empty table, potentially concealing the destructive effect until missing data is detected. ...[truncated 988 chars]
- Remediation
- ## Remediation Suggestions 1. Remove `DROP TABLE IF EXISTS` from the default template and all documentation examples. 2. Use non-destructive creation for ordinary design output: ```sql CREATE TABLE IF NOT EXISTS `[module]_[table_name]` ( -- Column definitions ); ``` 3. When an existing schema must be changed, generate explicit, reviewable `ALTER TABLE` migration statements instead of dropping and recreating the table. 4. Only provide destructive replacement SQL when the user explicitly requests it. Place it in a separately labeled section with a prominent data-loss warning. 5. Require confirmation that: - The target environment has been identified. - A tested backup or rollback plan exists. - The executing account is authorized to destroy the table. - Dependent objects and data migration requirements have been reviewed. 6. Encourage least-privilege deployment accounts that do not have `DROP` permission unless a reviewed migration specifically requires it. 7. Add automated linting or tests that reject unconditional `DROP TABLE`, `TRUNCATE TABLE`, and similarly destructive statements in default generated output.