T09 · Insecure Skill Coding Practices
Error
- Location
backend/providers/ledger.js:110- Finding
Authenticated SQL Injection in Ledger Drift Endpoint
- Content
View full analysis
= date('now', 'localtime', '-${days} days') GROUP BY day ORDER BY day `); jsonReply(res, 200, { provider, days, rows }); } ``` The affected route is registered as follows: ```js router.add('GET', '/api/ledger/drift', (req, res, q) => handleDrift(req, res, q)); ``` ### Technical Analysis The `provider` query parameter is copied directly into a single-quoted SQL expression without parameter binding, escaping, or allowlist validation: ```sql WHERE provider = '${provider}' ``` Although `sqliteJson()` invokes `sqlite3` through `execFileSync()` with an argument array, that only prevents shell metacharacter interpretation. It does not prevent SQL injection because the constructed string is still parsed as SQL by SQLite. An authenticated caller can terminate the `provider` string and alter the query. Depending on the injected statement and SQLite CLI behavior, this can include `UNION`-based extraction of other database contents or execution of additional statements. The `days` parameter is passed through `parseInt()`, which reduces direct SQL injection risk for that field, but it is not checked for finiteness or bounded to a reasonable range. ### Attack Path 1. The attacker obtains a valid dashboard Bearer token or an authenticated browser context. 2. The attacker sends a request to `/api/ledger/drift` with a crafted ` ...[truncated 1516 chars]- Remediation
View remediation
= date('now', 'localtime', ?) GROUP BY day ORDER BY day `).all(provider, `-${days} days`); ``` 2. If continued use of the `sqlite3` CLI is unavoidable, apply a strict allowlist to `provider`. Do not attempt to secure arbitrary input using only quote replacement. ```js const ALLOWED_PROVIDERS = new Set([ 'anthropic', 'openai', 'google', 'ollama', ]); if (!ALLOWED_PROVIDERS.has(provider)) { return errorReply(res, 400, 'Unsupported provider'); } ``` 3. Validate and clamp `days`: ```js const parsedDays = Number.parseInt(query.days || '30', 10); if (!Number.isInteger(parsedDays) || parsedDays < 1 || parsedDays > 365) { return errorReply(res, 400, 'Invalid days value'); } ``` 4. Open the ledger database in read-only mode for dashboard queries where supported. 5. Add regression tests that submit quotes, comments, `UNION`, and statement separators through `provider` and verify that each is rejected without reaching SQLite. ]]>