T09 · Insecure Skill Coding Practices
Error
- Location
- src/utils/export.ts:103
- Finding
- SQL Identifier Injection Through Attacker-Controlled Import Headers<![CDATA[ ## Vulnerability Details **File Location**: `src/utils/export.ts:103-116` and `src/utils/export.ts:140-161` **Vulnerability Type**: SQL identifier injection **Risk Level**: High ### Vulnerable Code CSV import: ```typescript const headers = lines[0].split(',').map(h => h.trim()); let imported = 0; const placeholders = headers.map(() => '?').join(', '); const stmt = db.prepare(` INSERT INTO ${tableName} (${headers.join(', ')}) VALUES (${placeholders}) `); ``` JSON import: ```typescript const content = fs.readFileSync(jsonPath, 'utf-8'); const records = JSON.parse(content) as Record<string, any>[]; if (records.length === 0) { return 0; } const headers = Object.keys(records[0]).filter(h => h !== 'id'); const placeholders = headers.map(() => '?').join(', '); const stmt = db.prepare(` INSERT INTO ${tableName} (${headers.join(', ')}) VALUES (${placeholders}) `); ``` ### Technical Analysis Record values are passed through SQLite placeholders, but CSV header names and JSON property names are interpolated directly into the SQL statement. Parameterized queries do not protect SQL identifiers, so untrusted identifiers must be independently validated and quoted. The CLI restricts the table name to a fixed set, which prevents table-name injection. However, the columns remain fully controlled by the imported file. An attacker can introduce SQL syntax into a CSV header or JSON key to modify the structure of the prepared statement. The exact statements available to an attacker are constrained by SQLite grammar and by `better-sqlite3` preparing a single statement. Nevertheless, imported data is improperly allowed to influence executable SQL syntax. At minimum, malicious headers can cause persistent denial of import functionality; crafted syntax may alter the intended insertion behavior where a valid single SQLite statement can be formed. ### Attack Path 1. An attacker creates a CSV file with malicious column headers or a JSON file with malicious ...[truncated 1194 chars]
- Remediation
- <![CDATA[ ## Remediation Suggestions 1. Define a strict column allowlist for every importable table: ```typescript const ALLOWED_COLUMNS: Record<string, Set<string>> = { blood_pressure: new Set([ 'systolic', 'diastolic', 'heart_rate', 'recorded_at', 'notes' ]), exercise: new Set([ 'type', 'duration_minutes', 'steps', 'calories_burned', 'distance_km', 'recorded_at', 'notes' ]), medication: new Set([ 'name', 'dosage', 'unit', 'taken_at', 'notes' ]) }; ``` 2. Reject unknown, empty, malformed, and duplicate headers before constructing SQL. 3. Safely quote identifiers after allowlist validation by doubling embedded quotation marks and surrounding identifiers with double quotes. 4. Require every JSON record to use the same validated schema as the first record. 5. Validate the number of CSV values against the number of validated headers. 6. Validate imported value types and required fields before opening the transaction. 7. Add tests using headers containing parentheses, comments, quotes, commas, and SQL keywords. ]]>