T09 · Insecure Skill Coding Practices
Warning
- Location
- scripts/import_bazi_calendar.py:101
- Finding
- SQL Injection Through an Unvalidated Table Identifier## Vulnerability Details **File Location**: `scripts/import_bazi_calendar.py`, lines 101-103, 111, and 132 **Vulnerability Type**: SQL injection in generated database statements **Risk Level**: Medium ### Vulnerable Code ```python def build_sql(records: List[Dict[str, str]], table: str) -> str: today = dt.datetime.now(dt.timezone.utc).isoformat() lines = [ "BEGIN;", f"CREATE TABLE IF NOT EXISTS {table} (", " date TEXT PRIMARY KEY,", " flow_year TEXT NOT NULL,", " flow_month TEXT NOT NULL,", " flow_day TEXT NOT NULL,", " source TEXT,", " updated_at TEXT", ");", ] for r in records: lines.extend( [ f"INSERT INTO {table} (date, flow_year, flow_month, flow_day, source, updated_at)", "VALUES (" + ", ".join( [ sql_quote(r["date"]), sql_quote(r["flow_year"]), sql_quote(r["flow_month"]), sql_quote(r["flow_day"]), sql_quote("xlsx_2026"), sql_quote(today), ] ) + ")", "ON CONFLICT(date) DO UPDATE SET", " flow_year=excluded.flow_year,", " flow_month=excluded.flow_month,", " flow_day=excluded.flow_day,", " source=excluded.source,", " updated_at=excluded.updated_at;", ] ) ``` ```python parser.add_argument( "--table", default="bazi_daily_calendar", help="Target table name", ) ``` ### Technical Analysis The caller-controlled `--table` argument is passed to `build_sql()` and interpolated directly into `CREATE TABLE` and `INSERT INTO` statements. The program does not restrict this argument to a valid SQL identifier or quote it using a database-specific identifier-quoting me ...[truncated 2118 chars]
- Remediation
- ## Remediation Suggestions 1. **Remove unnecessary configurability.** The Skill requires the fixed `bazi_daily_calendar` table, so the safest design is to remove `--table` and use a constant: ```python TABLE_NAME = "bazi_daily_calendar" ``` 2. **If custom table names are required, apply a strict allowlist.** Permit only conventional unqualified SQL identifiers: ```python import re IDENTIFIER_RE = re.compile(r"[A-Za-z_][A-Za-z0-9_]*\Z") def validate_table_name(value: str) -> str: if not IDENTIFIER_RE.fullmatch(value): raise ValueError("Invalid table name") return value ``` Validate the argument before passing it to `build_sql()`: ```python table = validate_table_name(args.table) sql = build_sql(records, table) ``` 3. **Quote the validated identifier correctly.** Where supported, use the target database engine's identifier-quoting mechanism after validation. Do not use `sql_quote()`, because it creates string literals rather than quoted identifiers. 4. **Reject qualified or special identifiers unless explicitly required.** Do not allow dots, whitespace, comments, semicolons, quotes, brackets, or control characters. 5. **Apply least privilege to imports.** Run the import with a database account restricted to creating and updating only the intended calendar table. It should not be able to access unrelated application tables. 6. **Validate generated SQL before execution.** Administrative automation should verify that the output contains only the expected transaction, table creation, and upsert statements for the fixed table. 7. **Add negative tests.** Confirm rejection of inputs such as identifiers containing semicolons, SQL comments, quotes, whitespace, qualified names, or statement keywords.