T09 · Insecure Skill Coding Practices
Warning
- Location
- scripts/mock_data.py:249
- Finding
- SQL Injection Through Unvalidated SQL Identifiers in Generated Output## Vulnerability Details **File Location**: `scripts/mock_data.py`, lines 249–267; input and propagation at lines 319–320 and 355–356 **Vulnerability Type**: SQL injection through unvalidated table and column identifiers **Risk Level**: Medium The SQL formatter escapes generated string values but directly interpolates the user-controlled table name and record field names into executable SQL statements. ```python def format_sql(records, table): if not records: return "" lines = [] if isinstance(records[0], dict): fields = list(records[0].keys()) cols = ", ".join(fields) for rec in records: def sql_val(v): if v is None: return "NULL" if isinstance(v, bool): return "TRUE" if v else "FALSE" if isinstance(v, (int, float)): return str(v) return "'" + str(v).replace("'", "''") + "'" vals = ", ".join(sql_val(rec[f]) for f in fields) lines.append(f"INSERT INTO {table} ({cols}) VALUES ({vals});") else: for r in records: val = "'" + str(r).replace("'", "''") + "'" lines.append(f"INSERT INTO {table} (value) VALUES ({val});") return "\n".join(lines) + "\n" ``` The table name is accepted from the command line without validation: ```python parser.add_argument("--table", default="records", help="Table name for SQL output (default: records)") ``` It is then passed directly to the vulnerable formatter: ```python elif args.format == "sql": output = format_sql(records, args.table) ``` Custom record field names also reach the SQL column list without identifier validation: ```python fields = list(records[0].keys()) cols = ", ".join(fields) ``` ### Technical Analysis SQL data values and SQL identifiers require different ...[truncated 2528 chars]
- Remediation
- ## Remediation Suggestions 1. Validate table and column identifiers before generating SQL. A conservative portable policy is: ```python import re IDENTIFIER_RE = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$") def validate_identifier(value, kind): if not IDENTIFIER_RE.fullmatch(value): raise ValueError(f"Invalid SQL {kind}: {value!r}") return value ``` 2. Apply validation to `args.table` and every field name before constructing SQL: ```python table = validate_identifier(table, "table name") fields = [validate_identifier(field, "column name") for field in records[0].keys()] ``` 3. Reject unknown custom record fields rather than retaining them as arbitrary dictionary keys. If custom output names are a required feature, separate the output column name from the selected generator and validate the output name. 4. If support for database-specific identifiers is necessary, implement explicit SQL dialect selection and quote identifiers using the selected dialect’s rules. Do not attempt to protect identifiers with string-literal escaping. 5. Document that generated SQL should be reviewed before execution and should be run using a least-privileged database account in non-production environments. 6. Add automated negative tests for identifiers containing semicolons, comments, quotes, whitespace, parentheses, line breaks, Unicode control characters, and SQL keywords. Tests should verify that malicious identifiers are rejected rather than emitted.