T09 · Insecure Skill Coding Practices
- Location
scripts/import_bazi_calendar.py:101- Finding
SQL Injection Through an Unvalidated Table Identifier
- Content
View full analysis
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: MediumVulnerable 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
--tableargument is passed tobuild_sql()and interpolated directly intoCREATE TABLEandINSERT INTOstatements. 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
View remediation
Remediation Suggestions
- Remove unnecessary configurability. The Skill requires the fixed
bazi_daily_calendartable, so the safest design is to remove--tableand use a constant:
python TABLE_NAME = "bazi_daily_calendar"- 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 valueValidate the argument before passing it to
build_sql():python table = validate_table_name(args.table) sql = build_sql(records, table)-
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. -
Reject qualified or special identifiers unless explicitly required. Do not allow dots, whitespace, comments, semicolons, quotes, brackets, or control characters.
-
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.
-
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.
-
Add negative tests. Confirm rejection of inputs such as identifiers containing semicolons, SQL comments, quotes, whitespace, qualified names, or statement keywords.
- Remove unnecessary configurability. The Skill requires the fixed