T09 · Insecure Skill Coding Practices
Error
- Location
- scripts/schema.sh:160
- Finding
- SQL Injection Through Unsanitized Table Identifiers<![CDATA[ ## Vulnerability Details **File Location**: `scripts/schema.sh:160-168`, `scripts/schema.sh:181-203`, and `scripts/schema.sh:240-246` **Vulnerability Type**: SQL injection in generated schema, migration, and seed statements **Risk Level**: High ### Vulnerable Code ```python sql = tables.get(name, """CREATE TABLE IF NOT EXISTS {name} ( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, description TEXT NULL, status VARCHAR(20) DEFAULT 'active', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;""".format(name=name)) ``` ```python print("""-- Migration: create_{name}_table -- Timestamp: {ts} -- ========== UP ========== CREATE TABLE IF NOT EXISTS {name} ( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, description TEXT NULL, status VARCHAR(20) DEFAULT 'active', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO migrations (name, batch) VALUES ('create_{name}_table', 1); -- ========== DOWN ========== -- DROP TABLE IF EXISTS {name}; -- DELETE FROM migrations WHERE name = 'create_{name}_table';""".format(name=name, ts=ts)) ``` ```python sql = seeds.get(name, """INSERT INTO {name} (name, description, status) VALUES ('Sample 1', 'First sample record', 'active'), ('Sample 2', 'Second sample record', 'active'), ('Sample 3', 'Third sample record', 'inactive'), ('Sample 4', 'Fourth sample record', 'active'), ('Sample 5', 'Fifth sample record', 'archived');""".format(name=name)) ``` ### Technical Analysis The value assigned to `name` originates from the command-line argument passed through `$ARG`. For unknown template names, it is interpolated directly into SQL identifiers and, in the m ...[truncated 2097 chars]
- Remediation
- <![CDATA[ ## Remediation Suggestions 1. Validate every user-provided SQL identifier before generating output: ```python import re IDENTIFIER = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$") if not IDENTIFIER.fullmatch(name): raise SystemExit("Invalid table name") ``` 2. Apply the same validation to table, column, index, schema, and migration identifiers wherever they can be supplied externally. 3. After validation, quote identifiers using the target database's supported quoting mechanism. Do not treat quoting as a substitute for the allowlist. 4. Do not interpolate identifiers into SQL string values. Use database parameter binding when the output will be executed programmatically. 5. If the tool only supports a fixed set of schemas, replace the fallback interpolation behavior with an explicit allowlist and reject unknown table names. 6. Add automated negative tests covering semicolons, quotes, whitespace, comments, backticks, control characters, Unicode confusables, and SQL keywords. 7. Ensure generated migrations are reviewed before execution and run under a least-privileged database account that cannot access unrelated schemas. ]]>