How SQL Injection via Template Literals happens in Node.js and how to fix it

Introduction

In the server-agents Node.js library, we discovered a high-severity SQL injection vulnerability in server-agents/common/src/search/schema.ts at line 471. The insertRowsBatch function, which handles full-text search indexing for chat messages, was constructing SQL queries using JavaScript template literals with the variable SEARCH_FTS_MERGE_PAGES_PER_TXN directly embedded in the query string. This seemingly innocuous pattern—common in many Node.js codebases—created a critical security flaw that could allow attackers to manipulate database operations.

The vulnerability was particularly concerning because this is a library package consumed by downstream applications. Any security flaw here propagates to every application using this dependency, multiplying the potential attack surface across an entire ecosystem of services.

The Vulnerability Explained

The vulnerable code appeared in the insertRowsBatch function, specifically in this line:

db.exec(`INSERT INTO search_chunks_fts(search_chunks_fts, rank)
  VALUES ('merge', ${SEARCH_FTS_MERGE_PAGES_PER_TXN})`);

This code uses JavaScript template literals (backtick strings) to construct an SQL query, directly interpolating the SEARCH_FTS_MERGE_PAGES_PER_TXN constant into the query string. The db.exec() method then executes this constructed string as raw SQL.

Why is this dangerous?

While SEARCH_FTS_MERGE_PAGES_PER_TXN appears to be a constant in this specific case, the pattern itself is inherently unsafe. Template literal interpolation happens at the JavaScript level, before the database driver sees the query. This means:

1. The database has no way to distinguish between SQL structure and data
2. If the variable's value ever changes or comes from an external source, malicious SQL can be injected
3. The pattern creates an "exploit primitive"—a code structure that automated attack tools can potentially chain with other vulnerabilities

Concrete Attack Scenario:

Imagine if SEARCH_FTS_MERGE_PAGES_PER_TXN were ever modified to accept user input or configuration values (a common refactoring in growing codebases). An attacker could inject a value like:

1); DROP TABLE search_chunks_fts; --

The resulting executed query would become:

INSERT INTO search_chunks_fts(search_chunks_fts, rank)
  VALUES ('merge', 1); DROP TABLE search_chunks_fts; --')

This would execute the INSERT, then drop the entire full-text search table, destroying indexed chat data across the application. More sophisticated attacks could exfiltrate sensitive data, modify search rankings, or inject malicious content into search results.

Even though SEARCH_FTS_MERGE_PAGES_PER_TXN is currently a constant, this vulnerability represents what security researchers call an "exploit primitive"—a code pattern that, while not independently exploitable today, could be chained with other weaknesses by increasingly sophisticated automated exploit-development tools.

The Fix

The fix replaces the vulnerable template literal approach with proper parameterized queries:

Before (Vulnerable):

db.exec(`INSERT INTO search_chunks_fts(search_chunks_fts, rank)
  VALUES ('merge', ${SEARCH_FTS_MERGE_PAGES_PER_TXN})`);

After (Secure):

db.query(`INSERT INTO search_chunks_fts(search_chunks_fts, rank)
  VALUES ('merge', ?)`).run(SEARCH_FTS_MERGE_PAGES_PER_TXN);

What changed and why it matters:

1. Switched from db.exec() to db.query().run(): The exec() method executes raw SQL strings, while query() creates a prepared statement that supports parameter binding.

2. Replaced ${SEARCH_FTS_MERGE_PAGES_PER_TXN} with ? placeholder: The question mark is a parameter placeholder. The actual value is passed separately as an argument to .run().

3. Separated SQL structure from data: The SQL query structure is now fixed at compile time. The value of SEARCH_FTS_MERGE_PAGES_PER_TXN is passed as a parameter, which the database driver automatically escapes and treats as pure data, never as executable SQL code.

How this prevents SQL injection:

When you use parameterized queries, the database driver handles the value in two distinct phases:

1. Parse phase: The SQL structure (INSERT INTO search_chunks_fts...) is parsed and compiled
2. Bind phase: The parameter value is bound to the placeholder and properly escaped

This separation ensures that no matter what value is passed—even if it contains SQL syntax—it will always be treated as a literal string value for the rank column, never as executable SQL commands.

Prevention & Best Practices

1. Always Use Parameterized Queries

Make parameterized queries your default approach for all database operations:

// ❌ NEVER do this
db.exec(`SELECT * FROM users WHERE id = ${userId}`);

// ✅ ALWAYS do this
db.query(`SELECT * FROM users WHERE id = ?`).get(userId);

2. Avoid Template Literals for SQL Construction

JavaScript template literals are excellent for string formatting, but they're dangerous for SQL. Treat any SQL query with ${} interpolation as a code smell that requires immediate review.

3. Use TypeScript and Type-Safe Query Builders

Consider using type-safe query builders like Prisma, TypeORM, or Kysely that provide compile-time guarantees against SQL injection:

// Type-safe query with Kysely
const result = await db
  .insertInto('search_chunks_fts')
  .values({ search_chunks_fts: 'merge', rank: SEARCH_FTS_MERGE_PAGES_PER_TXN })
  .execute();

4. Implement Static Analysis in CI/CD

Integrate tools like Semgrep into your continuous integration pipeline to catch SQL injection patterns before code reaches production:

# .github/workflows/security.yml
- name: Run Semgrep
  uses: returntocorp/semgrep-action@v1
  with:
    config: p/security-audit

5. Apply Defense in Depth

While parameterized queries are the primary defense, implement multiple layers:

• Principle of least privilege: Database users should have minimal necessary permissions
• Input validation: Validate data types and ranges before database operations
• Web Application Firewall (WAF): Deploy WAF rules to detect SQL injection attempts
• Regular security audits: Review database access patterns periodically

Security Standards Reference

This vulnerability maps to several security frameworks:

• CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
• OWASP Top 10 2021: A03:2021 – Injection
• OWASP ASVS: V5.3.4 – Database Security Requirements

Key Takeaways

• The insertRowsBatch function in schema.ts line 471 used template literal interpolation (${SEARCH_FTS_MERGE_PAGES_PER_TXN}) which created an SQL injection vulnerability in the full-text search merge operation
• Template literals with db.exec() bypass all database-level protections because the SQL string is constructed in JavaScript before the database driver sees it
• The fix switched to db.query().run() with ? placeholders, ensuring the SQLite driver treats SEARCH_FTS_MERGE_PAGES_PER_TXN as data, not executable SQL
• Exploit primitives matter in library code: Even if a constant seems safe today, patterns that could enable SQL injection become security liabilities as code evolves and as automated exploit tools become more sophisticated
• Static analysis tools like Semgrep can detect these patterns before they reach production, making them essential for Node.js projects handling database operations

How Orbis AppSec Detected This

• Source: The SEARCH_FTS_MERGE_PAGES_PER_TXN variable embedded in a template literal
• Sink: db.exec() call in server-agents/common/src/search/schema.ts:471 executing the constructed SQL string
• Missing control: No parameterized query mechanism; direct template literal interpolation allowed variable content to be interpreted as SQL syntax
• CWE: CWE-89 (Improper Neutralization of Special Elements used in an SQL Command)
• Fix: Replaced db.exec() with parameterized db.query().run(), using ? placeholder to safely bind the SEARCH_FTS_MERGE_PAGES_PER_TXN value

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

SQL injection remains one of the most critical web application vulnerabilities, and template literal interpolation is a common vector in Node.js applications. The fix in server-agents/common/src/search/schema.ts demonstrates that secure coding doesn't require complex changes—switching from db.exec() with template literals to db.query().run() with parameterized queries is a simple pattern that eliminates an entire class of vulnerabilities.

For library maintainers, this fix is especially important. Security vulnerabilities in shared libraries multiply across every downstream consumer, making defensive hardening essential even when exploit paths aren't immediately obvious. By removing exploit primitives proactively, you raise the bar against increasingly capable automated attack tools and protect your entire ecosystem of users.

Make parameterized queries your default, integrate static analysis into your workflow, and treat SQL construction with template literals as a critical security smell that demands immediate remediation.

References

• CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
• OWASP SQL Injection Prevention Cheat Sheet
• better-sqlite3 Documentation - Prepared Statements
• Semgrep Rule: SQL Injection via Template Literals
• harden: add parameterized queries in schema.ts