How SQL injection via template literals happens in Node.js SQLite and how to fix it

Introduction

In src/lib/codex-state.mjs, a Node.js module handling thread state persistence for a CLI tool, we discovered a high-severity SQL injection vulnerability at line 87. The readCodexStateResult() function dynamically constructed SQL SELECT statements using JavaScript template literals, interpolating column names derived from runtime set operations. While the code included an initial allow-list filter, the lack of secondary validation created an exploit primitive—code that, while not independently exploitable today, could be chained with other weaknesses by automated attack tools.

This vulnerability exemplifies a critical pattern in modern JavaScript applications: the false security of "mostly safe" dynamic SQL construction. Even when developers believe their inputs are controlled, the combination of template literals and database queries creates dangerous attack surfaces.

The Vulnerability Explained

The vulnerable code in src/lib/codex-state.mjs:83-87 constructed SQL queries like this:

const pick = new Set([
  'created_at', 'updated_at', 'recency_at',
  'created_at_ms', 'updated_at_ms', 'recency_at_ms',
].filter((c) => cols.has(c)));
const threads = new Map();
for (const row of db.prepare(`SELECT ${pick.join(', ')} FROM threads`).all()) {

The problem: The pick array was built by intersecting a hardcoded allow-list with the live database schema (cols), then directly interpolated into the SQL string via ${pick.join(', ')}.

While the allow-list approach appears safe, several risks exist:

1. Schema manipulation: If cols derives from attacker-influenced schema metadata, malicious column names could enter the intersection
2. Future code changes: A developer might modify the allow-list without understanding the security implications
3. Supply chain attacks: Compromised dependencies could manipulate the schema detection logic

Attack scenario: An attacker with control over the database schema (via a separate vulnerability or compromised migration) could create a column named threads; DROP TABLE threads; --. When cols.has() returns true for this column, it passes the initial filter and gets interpolated into the query, executing destructive SQL.

The Fix

The fix implements defense-in-depth with three layers of protection:

// Defense in depth: `pick` is already built from a hardcoded literal
// allow-list intersected with the live schema, but re-validate every
// identifier against a strict pattern before it reaches the SQL text so
// a future edit to the allow-list can't smuggle in unsafe column names.
const SAFE_IDENTIFIER = /^[a-z_][a-z0-9_]*$/i;
if (!pick.every((c) => SAFE_IDENTIFIER.test(c))) return null;
const sql = `SELECT ${pick.join(', ')} FROM threads`;
const threads = new Map();
for (const row of db.prepare(sql).all()) {

Key changes:

The SAFE_IDENTIFIER regex /^[a-z_][a-z0-9_]*$/i enforces:
- ^ Start of string
- [a-z_] First character must be letter or underscore
- [a-z0-9_]* Subsequent characters: letters, digits, underscores only
- $ End of string
- i Case-insensitive flag

This pattern whitelist-approves only legitimate SQL identifiers, rejecting any string containing special characters, spaces, semicolons, or SQL keywords that could alter query semantics.

Key Takeaways

• Never trust allow-lists alone: The pick array in readCodexStateResult() now requires runtime regex validation even after initial filtering
• Template literals in SQL are dangerous: The pattern `SELECT ${...} FROM ...` in codex-state.mjs:87 is an anti-pattern requiring strict controls
• Defense-in-depth beats single controls: Multiple validation layers prevent exploitation even when one control fails
• Fail securely: The fix returns null on validation failure rather than attempting partial query execution
• Document security assumptions: The inline comment explains why the regex exists, preserving security context for future maintainers

How Orbis AppSec Detected This

Source: Column names derived from database schema intersection in readCodexStateResult() function parameters and environment configuration

Sink: Template literal interpolation `SELECT ${pick.join(', ')} FROM threads` at src/lib/codex-state.mjs:87

Missing control: No secondary validation of SQL identifiers after initial allow-list filtering; identifiers reached query string without character set enforcement

CWE: CWE-89: Improper Neutralization of Special Elements in SQL Command ('SQL Injection')

Fix: Added SAFE_IDENTIFIER regex pattern validation with /^[a-z_][a-z0-9_]*$/i on all column names before SQL construction, returning null on any validation failure

Orbis AppSec automatically detected this vulnerability and opened a pull request with the fix. Try Orbis AppSec on your repositories to find and fix issues like this automatically.

Conclusion

The codex-state.mjs vulnerability demonstrates that even "safe-looking" dynamic SQL—protected by allow-lists and set intersections—requires defense-in-depth validation. The JavaScript template literal syntax, while convenient, creates invisible boundaries where security controls must be explicit.

By implementing strict identifier validation with well-documented regex patterns, developers can eliminate entire classes of SQL injection vulnerabilities while maintaining the flexibility needed for dynamic schema handling. The key is assuming your initial controls will fail and building validation that fails securely when they do.