T09 · Insecure Skill Coding Practices
- Location
SKILL.md:49- Finding
SQL Injection in Generated Database Import Scripts
- Content
View full analysis
Vulnerability Details
File Location:
SKILL.md:49-55,SKILL.md:68-73,SKILL.md:87-95,SKILL.md:200-219
Vulnerability Type: Untrusted CSV values interpolated into executable SQL
Risk Level: HighVulnerable Code Snippets
Category names from the CSV are inserted directly into SQL string literals:
sql INSERT INTO skill_categories (name) VALUES ('Category1'), ('Category2'), ('Category3') ON CONFLICT (name) DO NOTHING;Skill and category names are inserted in the same unsafe manner:
sql INSERT INTO skills (name, category_id) VALUES ('C#', (SELECT id FROM skill_categories WHERE name = '.NET')), ('JavaScript', (SELECT id FROM skill_categories WHERE name = 'Front-end')) ON CONFLICT (name) DO NOTHING;Employee names and skill names are also placed directly into generated queries:
sql INSERT INTO employee_skills (employee_id, skill_id, years_of_experience) VALUES ( (SELECT id FROM employees WHERE TRIM(first_name) = 'John' AND TRIM(last_name) = 'Doe'), (SELECT id FROM skills WHERE name = 'C#'), 5 ) ON CONFLICT (employee_id, skill_id) DO UPDATE SET years_of_experience = EXCLUDED.years_of_experience;The generation workflow explicitly directs the agent to create SQL using corrected names without requiring SQL-literal escaping:
text - Generate INSERT statements using corrected employee names - Save SQL file and report to outputs directory - Present both files to userTechnical Analysis
Category names, skill names, and employee names originate from an uploaded CSV and are therefore untrusted. The documented generation process embeds those values between single quotes without requiring parameterization or escaping embedded apostrophes.
An attacker-controlled value can terminate its SQL string literal and append additional SQL syntax. The resulting payload does not execute while the CSV is parsed, but it becomes active when the user executes the generated script in the Supabase SQL edit ...[truncated 1386 chars]
- Remediation
View remediation
Remediation Suggestions
- Prefer parameterized inserts through a trusted PostgreSQL or Supabase client instead of generating executable SQL containing raw CSV values.
- If SQL files must be generated, implement one centralized PostgreSQL literal serializer that replaces every single quote with two single quotes before placing text into a literal.
- Apply that serializer to all category names, skill names, employee names, comments, and any other CSV-derived text.
- Parse
years_of_experienceusing a strict finite-number parser and emit only a canonical numeric representation. Reject nonnumeric values rather than copying them into SQL. - Validate generated scripts with a PostgreSQL-aware SQL parser before presenting them to the user.
- Clearly mark generated scripts as derived from untrusted input and require review before execution.
- Execute imports with a least-privileged database role restricted to the four intended tables.
- Add regression tests for ordinary apostrophes, such as
O'Brien, and adversarial inputs containing quotes, semicolons, comments, and statement terminators.