How SQL Injection Happens in Node.js Template Literals and How to Fix It

Introduction

In a Node.js library handling event ledger operations, we discovered a high-severity SQL injection vulnerability in ts/src/state-ledger.ts at line 208. The StateLedger class's readEvents() method was constructing SQL queries using JavaScript template literals with dynamic input—a pattern that creates what security researchers call an exploit primitive. While not independently exploitable in the current codebase, this pattern could be chained with other weaknesses by automated exploit-development tooling to achieve full SQL injection attacks.

The vulnerable code lived in a critical data access method that retrieves event records for multi-tenant applications. Any downstream consumer using this package with modified input validation or in unexpected deployment contexts could face serious security risks.

The Vulnerability Explained

The Problematic Code Pattern

Before the fix, the readEvents() method used this dangerous pattern:

readEvents(kind?: string, limit = 100): LedgerEventRow[] {
  const where = kind ? 'WHERE tenant_id = ? AND kind = ?' : 'WHERE tenant_id = ?'
  const rows = this.db.prepare(
    `SELECT seq, tenant_id, ts, actor, kind, subject_id, payload, idem_key, run_id
     FROM events ${where} ORDER BY seq DESC LIMIT ?`,
  ).all(...(kind ? [this.tenant, kind, limit] : [this.tenant, limit])) as LedgerEventRow[]
  return rows
}

The critical issue: The ${where} template literal interpolation injects raw SQL fragments directly into the query string. While this specific implementation uses ? placeholders for some values, the query structure itself is dynamically assembled via string concatenation.

How Exploitation Could Occur

The kind parameter flows into the where clause construction:

const where = kind ? 'WHERE tenant_id = ? AND kind = ?' : 'WHERE tenant_id = ?'

If an attacker could influence the logical path or if future code modifications introduced additional template interpolations, they could manipulate the SQL structure. Consider a hypothetical future developer adding:

// DANGEROUS: If this pattern were introduced
const where = kind ? `WHERE tenant_id = ? AND kind = '${kind}'` : 'WHERE tenant_id = ?'

This would create immediate SQL injection. The existing pattern establishes a precedent and infrastructure for unsafe query construction that automated tools can identify and exploit through variant analysis.

The purge() Method: A Related Concern

The PR also hardened purge() at line 674, where dynamic IN clause construction required careful handling:

// Before: Template literal for table name (if introduced) would be dangerous
// The fix ensures only VALUES are parameterized, not structure
const placeholders = s.kinds.map(() => '?').join(',')

The Fix

Before and After: readEvents() Method

Before (vulnerable):

readEvents(kind?: string, limit = 100): LedgerEventRow[] {
  const where = kind ? 'WHERE tenant_id = ? AND kind = ?' : 'WHERE tenant_id = ?'
  const rows = this.db.prepare(
    `SELECT seq, tenant_id, ts, actor, kind, subject_id, payload, idem_key, run_id
     FROM events ${where} ORDER BY seq DESC LIMIT ?`,
  ).all(...(kind ? [this.tenant, kind, limit] : [this.tenant, limit])) as LedgerEventRow[]
  return rows
}

After (hardened):

readEvents(kind?: string, limit = 100): LedgerEventRow[] {
  const rows = kind
    ? this.db.prepare(
        `SELECT seq, tenant_id, ts, actor, kind, subject_id, payload, idem_key, run_id
         FROM events WHERE tenant_id = ? AND kind = ? ORDER BY seq DESC LIMIT ?`,
      ).all(this.tenant, kind, limit)
    : this.db.prepare(
        `SELECT seq, tenant_id, ts, actor, kind, subject_id, payload, idem_key, run_id
         FROM events WHERE tenant_id = ? ORDER BY seq DESC LIMIT ?`,
      ).all(this.tenant, limit)
  return rows as LedgerEventRow[]
}

Key Security Improvements

The purge() Method Fix

The purge() method also received hardening:

// Before
`DELETE FROM events WHERE subject_id = ? AND tenant_id = ? AND kind IN (${placeholders})`

// After  
'DELETE FROM events WHERE subject_id = ? AND tenant_id = ? AND kind IN (' + placeholders + ')'

This change is subtle but important: it uses string concatenation (+) for the placeholders variable (which contains only safe, generated ? characters) rather than template literal interpolation, making the distinction between structure (concatenated) and values (parameterized) visually explicit.

Key Takeaways

• Template literals in SQL queries create exploit primitives: The ${where} pattern in StateLedger.readEvents() established infrastructure that automated attack tools could leverage through variant analysis.

• Explicit branches beat dynamic assembly: The fix replaces single-path dynamic SQL with explicit conditional branches—slightly more verbose but dramatically more secure and auditable.

• Value parameterization requires structural discipline: The purge() method fix demonstrates that even "safe" dynamic elements (the ? placeholder list) should use explicit concatenation rather than template interpolation to maintain clear security boundaries.

• Multi-tenant data access demands defense in depth: The tenant_id filtering in these queries protects data isolation; SQL injection would bypass this critical security control.

• Static analysis findings warrant proactive remediation: Semgrep's utils.custom.sql-injection-template-literal rule correctly identified this pattern before it could be exploited in the wild.

How Orbis AppSec Detected This

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

This vulnerability in StateLedger illustrates how seemingly minor patterns—using template literals for SQL fragment assembly—create lasting security risks. The fix demonstrates that robust SQL security requires structural discipline: complete query strings, explicit code paths, and rigorous separation between query structure and user data.

For developers working with SQLite in Node.js, remember: better-sqlite3's parameterized query API is your strongest defense. Use it completely—parameterize values, yes, but also eliminate dynamic query structure assembly. The small cost in code verbosity pays enormous dividends in security assurance.