How SQL Injection happens in JavaScript template literals and how to fix it

Introduction

In the NorthLunaPlugin for SiYuan Note, we discovered a critical SQL injection vulnerability at line 7932 in index.js that could have allowed attackers to execute arbitrary database commands. The plugin, which handles document icon retrieval by querying block metadata, was constructing SQL queries by directly interpolating user-controlled block IDs into template literals—a pattern that completely bypassed any meaningful security controls.

The vulnerable code processed files.map(f => f.id) to build an IN clause for a SQL query fetching icon data from the blocks table. While the developer attempted a naive single-quote escape with .replace(/'/g, "''"), this approach is fundamentally flawed for SQL injection prevention and left the door wide open for more sophisticated attacks.

The Vulnerability Explained

The Vulnerable Code

Before the fix, the code at lines 7928-7935 looked like this:

const ids = files.map(f => `'${String(f.id).replace(/'/g, "''")}'`).join(',');
const iconResp = await fetch('/api/query/sql', { 
    method: 'POST', 
    headers: h, 
    body: JSON.stringify({ 
        stmt: `SELECT id, ial FROM blocks WHERE id IN (${ids}) AND type = 'd'` 
    }) 
});

The critical flaw: The f.id values come directly from the UI's block IDs, which an attacker can control through:
- A malicious plugin injecting crafted block references
- Request manipulation to the /api/query/sql endpoint
- A compromised frontend sending manipulated file lists

The replace(/'/g, "''") escaping is insufficient because:
1. It only handles single quotes, missing other SQL metacharacters
2. Database-specific escaping rules vary (Unicode, encoding tricks)
3. The template literal ${ids} insertion point allows injection without quotes

Attack Scenario

An attacker could craft a block ID like:

12345678-1234-1234-1234-123456789012') OR '1'='1' UNION SELECT id, ial FROM blocks WHERE content LIKE '%password%'--

This would break out of the IN clause, inject a boolean OR condition, exfiltrate sensitive data via UNION, and comment out the remainder of the query. Since the query runs with whatever privileges the SiYuan API holds, this could expose arbitrary document contents, user credentials, or plugin configurations.

The Fix

The remediation implements whitelist validation before any SQL construction occurs:

// Before: Dangerous escaping attempt
const ids = files.map(f => `'${String(f.id).replace(/'/g, "''")}'`).join(',');

// After: Strict validation first
const validIds = files.map(f => String(f.id))
    .filter(id => /^[0-9A-Za-z-]{1,64}$/.test(id));
if (validIds.length) {
    const ids = validIds.map(id => `'${id}'`).join(',');
    // ... rest of query construction
}

Key Changes

The regex /^[0-9A-Za-z-]{1,64}$/ enforces that block IDs:
- Start to end contain only allowed characters (anchored with ^ and $)
- Use alphanumeric characters and hyphens (standard UUID-like format)
- Are between 1 and 64 characters in length
- Contain no SQL metacharacters, quotes, or special symbols

The if (validIds.length) guard prevents constructing malformed IN () clauses when all IDs are filtered out, maintaining API stability.

Prevention & Best Practices

1. Prefer Parameterized Queries

The ideal fix would use parameterized statements where the database driver handles escaping:

// Hypothetical parameterized approach
const placeholders = validIds.map(() => '?').join(',');
db.query(`SELECT id, ial FROM blocks WHERE id IN (${placeholders}) AND type = 'd'`, validIds);

2. Defense in Depth with Validation

When parameterization isn't available (as with this /api/query/sql endpoint), combine:
- Whitelist validation (as implemented)
- Length limits (prevents buffer/DoS issues)
- Type coercion (explicit String(f.id) conversion)

3. Security Standards

• OWASP SQL Injection Prevention Cheat Sheet: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
• CWE-89: Improper Neutralization of Special Elements in SQL Command
• CWE-20: Improper Input Validation (the underlying cause)

4. Detection Tools

Static analysis rules can catch this pattern:
- Semgrep: javascript.lang.security.audit.sqli.sqli — detects SQL injection in JavaScript
- ESLint security plugins: detect-sql-literal and similar rules

Key Takeaways

• Never rely on blacklisting or escaping for SQL injection prevention — the original replace(/'/g, "''") gave false confidence while remaining vulnerable
• The NorthLunaPlugin icon fetcher at index.js:7932 now validates all block IDs against /^[0-9A-Za-z-]{1,64}$/ before any SQL construction
• Template literal SQL construction requires whitelist validation of every interpolated value — JavaScript's ${} syntax offers no automatic protection
• Guard against empty results after validation — the if (validIds.length) check prevents runtime errors and potential information disclosure
• UUID-like identifiers should still be validated — predictable formats don't mean safe to use unchecked

How Orbis AppSec Detected This

Source: The files array containing block metadata objects with user-controlled id properties, populated from UI/plugin interactions.

Sink: The template literal `SELECT id, ial FROM blocks WHERE id IN (${ids}) AND type = 'd'` at index.js:7932, where the ids variable is constructed from unvalidated f.id values.

Missing control: No whitelist validation or parameterized query usage before string concatenation into the SQL statement. The replace(/'/g, "''") escaping was insufficient and provided no protection against non-quote-based injection vectors.

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

Fix: Implemented regex-based whitelist validation (/^[0-9A-Za-z-]{1,64}$/) to filter all block IDs before SQL construction, ensuring only alphanumeric characters and hyphens pass through to the query builder.

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 demonstrates how even "simple" data retrieval functions can become critical security risks when user input meets SQL construction. The NorthLunaPlugin fix shows that robust input validation—specifically whitelist-based pattern matching—can effectively mitigate injection risks even when full parameterization isn't available. For JavaScript developers working with database queries, remember: template literals and SQL are a dangerous combination without strict validation of every interpolated value.

References

• CWE-89: SQL Injection — https://cwe.mitre.org/data/definitions/89.html
• OWASP SQL Injection Prevention Cheat Sheet — https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
• Semgrep JavaScript SQL Injection Rules — https://semgrep.dev/r?q=javascript.lang.security.audit.sqli
• fix: fix security issue in index.js