T09 · Insecure Skill Coding Practices
Error
- Location
- SKILL.md:49
- Finding
- SQL Injection in Generated Database Import Scripts## 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**: High ### Vulnerable 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 user ``` ### Technical 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
- ## Remediation Suggestions 1. Prefer parameterized inserts through a trusted PostgreSQL or Supabase client instead of generating executable SQL containing raw CSV values. 2. 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. 3. Apply that serializer to all category names, skill names, employee names, comments, and any other CSV-derived text. 4. Parse `years_of_experience` using a strict finite-number parser and emit only a canonical numeric representation. Reject nonnumeric values rather than copying them into SQL. 5. Validate generated scripts with a PostgreSQL-aware SQL parser before presenting them to the user. 6. Clearly mark generated scripts as derived from untrusted input and require review before execution. 7. Execute imports with a least-privileged database role restricted to the four intended tables. 8. Add regression tests for ordinary apostrophes, such as `O'Brien`, and adversarial inputs containing quotes, semicolons, comments, and statement terminators.