How SQL injection happens in Node.js string interpolation and how to fix it

Introduction

The src/core/statistics.js file in this Node.js library handles database operations for statistics tracking, but a critical flaw in the getScript() method at line 56 created a severe security risk. The metadata_id variable was directly interpolated into DELETE and UPDATE SQL statements using JavaScript template literals—a pattern that opened the door to SQL injection attacks.

This vulnerability is particularly dangerous because this is a Node.js library, meaning every downstream consumer who uses this package inherits the security flaw. A single compromised dependency can cascade into hundreds or thousands of vulnerable applications.

The Vulnerability Explained

What Went Wrong

The vulnerable code in getScript() constructed SQL queries by directly embedding variables into template literal strings:

const metadata_id = this.lastStatistic.metadata_id;
const start = this.lastStatistic.start;

if (options.existingDataMode == 'update') {
    const updateSql1 = `update statistics set sum = sum + ${this.lastStatistic.sum.toFixed(3)} where metadata_id = ${metadata_id} and start_ts > unixepoch("${start}")\n\n`;
    const updateSql2 = `update statistics_short_term set sum = sum + ${this.lastStatistic.sum.toFixed(3)} where metadata_id = ${metadata_id} and start_ts > unixepoch("${start}")\n\n`;
    sql = sql + updateSql1 + updateSql2;
}

if (options.existingDataMode == 'delete') {
    const deleteSql1 = `delete from statistics where metadata_id = ${metadata_id}\n\n`;
    const deleteSql2 = `delete from statistics_short_term where metadata_id = ${metadata_id}\n\n`;
    // ...
}

The metadata_id and start variables are pulled directly from this.lastStatistic without any validation. If an attacker can control these values—whether through API input, configuration manipulation, or upstream data poisoning—they can inject arbitrary SQL.

Attack Scenario

Consider an attacker who manages to set metadata_id to the following value:

1; DROP TABLE statistics;--

When this value is interpolated into the DELETE statement, the resulting SQL becomes:

delete from statistics where metadata_id = 1; DROP TABLE statistics;--

This executes two commands: the intended DELETE (limited to ID 1), followed by a complete table drop. The -- comments out any remaining query text.

For data exfiltration, an attacker could use UNION-based injection:

1 UNION SELECT username, password, NULL FROM users--

This could expose sensitive data from other tables in the database, depending on how query results are handled.

Real-World Impact

For this statistics library, successful exploitation could:

1. Delete all statistics data across both statistics and statistics_short_term tables
2. Corrupt aggregated metrics by injecting malicious UPDATE values
3. Exfiltrate sensitive data if the database contains other tables with confidential information
4. Pivot to further attacks if database credentials have elevated privileges

The Fix

The fix implements strict input validation before any values reach the SQL query construction:

Before (Vulnerable)

const metadata_id = this.lastStatistic.metadata_id;
const start = this.lastStatistic.start;

After (Secure)

const metadata_id = parseInt(this.lastStatistic.metadata_id, 10);
if (isNaN(metadata_id)) {
    throw 'invalid metadata_id';
}
const start = String(this.lastStatistic.start);
if (!/^[\d\-: T.Z+]+$/.test(start)) {
    throw 'invalid start date';
}

How This Solves the Problem

For metadata_id:
- parseInt(value, 10) converts the input to a base-10 integer
- If the input contains SQL injection payloads like 1; DROP TABLE, parseInt() returns just 1
- The isNaN() check catches completely invalid inputs and throws an error
- Only clean integer values can proceed to the query

For start:
- The regex /^[\d\-: T.Z+]+$/ creates a whitelist of allowed characters
- Only digits, hyphens, colons, spaces, T, periods, Z, and plus signs are permitted
- This matches ISO 8601 date formats like 2024-01-15T10:30:00.000Z
- SQL metacharacters like semicolons, quotes, and dashes (when not part of dates) are rejected

The query construction was also changed from template literals to string concatenation, though the primary security improvement comes from the input validation:

const updateSql1 = 'update statistics set sum = sum + ' + this.lastStatistic.sum.toFixed(3) + ' where metadata_id = ' + metadata_id + ' and start_ts > unixepoch("' + start + '")\n\n';

Prevention & Best Practices

1. Use Parameterized Queries

The gold standard for SQL injection prevention is parameterized queries (prepared statements). Instead of string interpolation:

// Vulnerable
const sql = `SELECT * FROM users WHERE id = ${userId}`;

// Secure (using a library like better-sqlite3)
const stmt = db.prepare('SELECT * FROM users WHERE id = ?');
const result = stmt.get(userId);

2. Validate Input Types Strictly

When parameterized queries aren't available (such as when generating SQL scripts for later execution), enforce strict type validation:

// For integers
const id = parseInt(input, 10);
if (isNaN(id) || id < 0) throw new Error('Invalid ID');

// For strings with known patterns
if (!/^[a-zA-Z0-9_]+$/.test(tableName)) throw new Error('Invalid table name');

3. Use ORM Libraries

Object-Relational Mapping libraries like Sequelize, Prisma, or Knex.js handle query parameterization automatically:

// Knex.js example
await knex('statistics')
    .where('metadata_id', metadataId)
    .del();

4. Apply Defense in Depth

• Use database accounts with minimal required privileges
• Implement application-level query logging and monitoring
• Deploy Web Application Firewalls (WAF) with SQL injection rules
• Conduct regular security audits and dependency scanning

Key Takeaways

• Never interpolate variables directly into SQL strings, even in "internal" code—template literals (${variable}) are just as dangerous as string concatenation
• The parseInt() function is a simple but effective defense for integer parameters, as it strips all non-numeric content
• Regex whitelisting for date strings (/^[\d\-: T.Z+]+$/) prevents injection while allowing valid ISO 8601 formats
• Library vulnerabilities cascade downstream—this fix protects every application that depends on this package
• Input validation should happen at the point of use, not just at API boundaries

How Orbis AppSec Detected This

Orbis AppSec's multi-agent AI scanner identified this vulnerability through taint analysis:

• Source: The this.lastStatistic.metadata_id and this.lastStatistic.start properties, which could be influenced by external data
• Sink: The template literal SQL construction in getScript() at src/core/statistics.js:56, specifically the DELETE and UPDATE statements
• Missing control: No input validation, type checking, or parameterization between the data source and the SQL query construction
• CWE: CWE-89 (Improper Neutralization of Special Elements used in an SQL Command)
• Fix: Added parseInt() validation for metadata_id and regex pattern validation for the start date parameter

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 vulnerabilities in web applications, consistently ranking in the OWASP Top 10. This case demonstrates how easily it can occur—even experienced developers can overlook the risks of JavaScript template literals when constructing SQL queries.

The fix applied here—strict type validation with parseInt() and regex pattern matching—provides immediate protection. However, the long-term solution for any application handling SQL queries is to adopt parameterized queries or ORM libraries that handle escaping automatically.

Remember: every string that touches a SQL query is a potential attack vector. Validate early, validate strictly, and when in doubt, use parameterized queries.

References

• CWE-89: Improper Neutralization of Special Elements used in an SQL Command
• OWASP SQL Injection Prevention Cheat Sheet
• MDN parseInt() Documentation
• Semgrep SQL Injection Rules
• fix: add parameterized queries in statistics.js