How utils.custom.sql-injection-template-literal happens in JavaScript and how to fix it

Introduction

In the CrewRouter-Desktop repository, a high-severity SQL injection vulnerability was identified in src/server-manager.js at line 266. This file manages server lifecycle operations for the CrewRouter Desktop application — an Electron-based wrapper around a web service. Because server-manager.js handles request routing for a web service, the vulnerability sits directly in the path of remote attacker-controlled input, making it immediately exploitable without any special access or chaining.

The core issue: a SQL query was constructed using a JavaScript template literal (\...\${variable}...``) that embedded dynamic input directly into the query string. This classic anti-pattern turns what should be inert data into executable SQL syntax, giving attackers the ability to read, modify, or destroy database contents.

The Vulnerability Explained

What Happened at Line 266

At line 266 of server-manager.js, a SQL query was built using JavaScript's template literal syntax — the backtick strings with ${} interpolation. The pattern looked something like this:

// VULNERABLE: Dynamic input interpolated directly into SQL
const result = db.prepare(`SELECT * FROM servers WHERE id = '${serverId}'`).get();

When serverId comes from a request handler (which it does — this is a web service), an attacker can craft a malicious value that breaks out of the string context and injects arbitrary SQL.

How an Attacker Could Exploit This

Because server-manager.js handles web service requests, an attacker doesn't need local access. They can send a crafted HTTP request with a manipulated parameter. Consider an attacker sending:

serverId = "'; DROP TABLE servers; --"

The resulting SQL becomes:

SELECT * FROM servers WHERE id = ''; DROP TABLE servers; --'

This would:
1. Complete the original query with an empty string match
2. Execute DROP TABLE servers, destroying the entire table
3. Comment out the trailing quote with --

More sophisticated attacks could extract sensitive data using UNION SELECT statements, enumerate database schema, or modify records to escalate privileges within the CrewRouter system.

Why Template Literals Are Dangerous for SQL

JavaScript template literals are syntactic sugar for string concatenation. When you write:

`SELECT * FROM servers WHERE id = '${serverId}'`

The JavaScript engine simply concatenates the string pieces with the value of serverId. There is zero distinction between the SQL structure and the user data — they're all just characters in one string. The database parser has no way to know which parts were intended as SQL commands and which were intended as data values.

This is fundamentally different from parameterized queries, where the SQL structure and the data values travel through separate channels to the database engine.

The Fix

The fix in this pull request replaces the template literal interpolation with parameterized queries. Here's the transformation:

Before (Vulnerable)

// Dynamic input directly embedded in the SQL string
const result = db.prepare(`SELECT * FROM servers WHERE id = '${serverId}'`).get();

After (Secure)

// Parameterized query: SQL structure and data are separated
const result = db.prepare(`SELECT * FROM servers WHERE id = ?`).get(serverId);

Why This Works

With parameterized queries, the database driver processes the fix in two distinct phases:

1. Parse phase: The SQL string SELECT * FROM servers WHERE id = ? is parsed and compiled. The ? is recognized as a data placeholder, not SQL syntax.
2. Bind phase: The value of serverId is bound to the placeholder. No matter what characters serverId contains — quotes, semicolons, SQL keywords — they are treated as literal data, never as SQL commands.

Even if an attacker sends '; DROP TABLE servers; --, the database simply searches for a server whose id column literally contains the string '; DROP TABLE servers; --. The attack is neutralized at the architectural level.

Additional Changes

The PR also includes updates to .github/workflows/build-desktop.yml to ensure the server is properly built before packaging the desktop application, and a fix in CrewRouter-Desktop/scripts/stage-server.js that normalizes line endings (.replace(/\r\n/g, '\n')) before performing string matching. This line-ending normalization is a build reliability fix that ensures the staging script works consistently across Windows and Unix environments.

Prevention & Best Practices

1. Always Use Parameterized Queries

Every major Node.js database library supports parameterized queries:

// better-sqlite3
db.prepare('SELECT * FROM users WHERE name = ?').get(userName);

// mysql2
connection.execute('SELECT * FROM users WHERE name = ?', [userName]);

// pg (PostgreSQL)
client.query('SELECT * FROM users WHERE name = $1', [userName]);

2. Lint for Template Literals in SQL Contexts

Use static analysis rules like utils.custom.sql-injection-template-literal in Semgrep to catch template literal usage in SQL query construction during CI/CD. This catches the vulnerability before it reaches production.

3. Apply the Principle of Least Privilege

Configure your database user with minimal permissions. If the application only needs to SELECT from a table, don't grant DROP, DELETE, or ALTER permissions. This limits the blast radius if an injection is ever exploited.

4. Use an ORM or Query Builder

Libraries like Knex.js, Sequelize, or Prisma generate parameterized queries automatically, making it harder to accidentally introduce injection vulnerabilities:

// Knex.js - automatically parameterized
const result = await knex('servers').where('id', serverId).first();

5. Code Review Checklist

Add a specific code review checkpoint: "Does any SQL query use string concatenation or template literals with dynamic values?" If the answer is yes, the code must be refactored before merge.

Key Takeaways

• **Template literals (\...\${}`) in SQL queries are just as dangerous as string concatenation** — theserver-manager.js` vulnerability at line 266 proves that modern JavaScript syntax doesn't protect against classic injection attacks.
• Web service request handlers are the highest-risk location for SQL injection — because server-manager.js processes remote requests, this vulnerability was directly exploitable without authentication or chaining.
• The fix is a one-line structural change — replacing '${serverId}' with ? and passing serverId as a parameter fundamentally changes how the database processes the input, making injection impossible.
• Automated static analysis catches what code review misses — Semgrep's utils.custom.sql-injection-template-literal rule flagged this exact pattern, demonstrating the value of automated scanning in CI pipelines.
• Build pipeline changes matter for security — the accompanying workflow fix ensures the server is properly compiled before desktop packaging, preventing stale or unpatched code from shipping.

How Orbis AppSec Detected This

• Source: Dynamic input from a web service request handler in CrewRouter-Desktop/src/server-manager.js, where user-controlled data flows into the server management logic.
• Sink: A db.prepare() call at line 266 of server-manager.js where the SQL query string was constructed using a JavaScript template literal with ${} interpolation of the dynamic input.
• Missing control: No parameterization or sanitization was applied to the dynamic input before it was embedded in the SQL query string. The template literal treated the user input as part of the SQL command structure.
• CWE: CWE-89 — Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
• Fix: Replaced the template literal interpolation ('${variable}') with a parameterized query placeholder (?) and passed the dynamic value as a separate argument to the database driver's bind mechanism.

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 dangerous and prevalent web application vulnerabilities, consistently ranking in the OWASP Top 10. The vulnerability in server-manager.js is a textbook example of how JavaScript's template literal syntax can lull developers into a false sense of security — the code looks clean and modern, but it carries the same injection risk as old-school string concatenation.

The fix is straightforward: use parameterized queries. It's a one-line change that provides a structural guarantee against injection, not just a best-effort filter. If you're building any application that constructs SQL queries with dynamic input, audit your codebase today for template literal interpolation in SQL contexts — and let automated tools like Semgrep and Orbis AppSec catch what manual review might miss.

References

• CWE-89: Improper Neutralization of Special Elements used in an SQL Command
• OWASP SQL Injection Prevention Cheat Sheet
• OWASP Top 10 — A03:2021 Injection
• better-sqlite3 API Documentation — Binding Parameters
• Semgrep Rules — SQL Injection
• harden: add parameterized queries in server-manager.js (GitHub PR)