T09 · Insecure Skill Coding Practices
- Location
generator.py:331- Finding
SQL Injection Through Unvalidated Table and Column Identifiers
- Content
View full analysis
Vulnerability Details
File Location:
generator.py, lines 331-354
Vulnerability Type: SQL injection in generated database statements
Risk Level: Mediumpython def to_sql(self, data: List[Dict[str, Any]], db_type: str = "mysql", table_name: str = None) -> str: """转换为SQL INSERT语句""" if not data: return "" if table_name is None: table_name = self.template_name sql_statements = [] for record in data: columns = ", ".join(record.keys()) values = [] for v in record.values(): if v is None: values.append("NULL") elif isinstance(v, bool): values.append("1" if v else "0") elif isinstance(v, (int, float)): values.append(str(v)) elif isinstance(v, str): # 转义单引号 escaped = v.replace("'", "''") values.append(f"'{escaped}'") else: values.append(f"'{str(v)}'") values_str = ", ".join(values) sql_statements.append(f"INSERT INTO {table_name} ({columns}) VALUES ({values_str});")Technical Analysis
The SQL exporter escapes single quotes in string values, but it does not validate or safely quote SQL identifiers. Both
table_nameand the keys from each generated record are concatenated directly into executable SQL syntax.Record keys originate from template field names. Custom templates loaded by
generate_from_templatecan therefore supply attacker-controlled column identifiers. Thetable_nameargument is also directly controllable whento_sqlis used as a module API.Escaping SQL values does not protect SQL identifiers. An identifier containing closing delimiters, parentheses, semicolons, comments, or other SQL syntax can alter the structure of the generated statement. The ineffective PostgreSQL branch elsewhere in this method also do ...[truncated 1288 chars]
- Remediation
View remediation
Remediation Suggestions
-
Validate all table and column identifiers before SQL generation. A conservative allowlist is:
python IDENTIFIER_RE = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$")Reject every identifier that does not match the policy.
-
Apply database-specific identifier quoting after validation:
- MySQL: backtick-quoted identifiers, with embedded backticks rejected.
- PostgreSQL: double-quoted identifiers, with embedded double quotes rejected or correctly doubled.
-
Do not attempt to protect identifiers with value-escaping logic. Keep identifier validation and value serialization as separate operations.
-
Restrict custom templates to a documented schema and validate the
fieldsobject, field names, field definitions, enum values, and numeric bounds before generation. -
Add tests covering malicious table and field names containing semicolons, comments, parentheses, quotes, backticks, whitespace, and SQL keywords. Verify that unsafe identifiers are rejected rather than emitted.
-
Clearly warn users that generated SQL should be reviewed and imported with a least-privileged database account that cannot administer users or unrelated schemas.
-