How SQL Injection happens in Node.js SQLite CLI calls and how to fix it

How SQL Injection Happens in Node.js SQLite CLI Calls and How to Fix It

Vulnerability at a Glance

Summary

A critical SQL injection vulnerability was discovered in lib/ParamediciOSPermissions.js, where the service and app variables were interpolated directly into raw SQL strings passed to the sqlite3 command-line tool without any escaping or parameterization. An attacker with control over these inputs could manipulate the iOS simulator's TCC permission database, potentially granting unauthorized app permissions. The fix applies SQLite-standard single-quote escaping to both variables before they are embedded in the SQL strings.

Introduction

The lib/ParamediciOSPermissions.js file is responsible for managing iOS simulator permissions by writing directly to the TCC (Transparency, Consent, and Control) SQLite database — the same database macOS uses to track which apps have been granted access to sensitive resources like the camera, microphone, and contacts.

Inside a for loop starting around line 55, the code builds SQL INSERT and UPDATE statements using JavaScript template literals, then passes them as arguments to the sqlite3 CLI via spawnAsync. The two variables at the heart of the issue are service (a permission service name like kTCCServiceCamera) and app (the application bundle identifier, sourced from this.appName).

Neither variable was sanitized before being embedded in the SQL string. For any developer building iOS testing tooling or CI/CD pipelines that consume this library, this represents a meaningful attack surface — especially because the appName value can be supplied by the caller.

The Vulnerability Explained

The Vulnerable Code

Here is the original INSERT statement from the loop body:

const insetProc = await spawnAsync(
    'sqlite3',
    [
        destinationTCCFile,
        `"INSERT INTO access (service, client, client_type, allowed, prompt_count, csreq) VALUES('${service}', '${app}', 0, 1, 1, NULL)"`
    ]
);

And the fallback UPDATE statement:

const updateProc = await spawnAsync(
    'sqlite3',
    [
        destinationTCCFile,
        `"UPDATE access SET client_type=0, allowed=1, prompt_count=1, csreq=NULL WHERE service='${service}' AND client='${app}'"`
    ]
);

Both statements embed ${service} and ${app} directly inside single-quoted SQL string literals. If either variable contains a single quote character ('), the SQL string literal is terminated prematurely — and anything that follows becomes raw SQL.

A Concrete Attack Scenario

Suppose an attacker controls the appName passed to the ParamediciOSPermissions constructor (for example, via a CI configuration file, a test manifest, or a crafted npm package that consumes this library). They set:

appName = "com.evil.app', 0, 1, 1, NULL); DROP TABLE access; --"

The resulting SQL sent to sqlite3 would be:

INSERT INTO access (service, client, client_type, allowed, prompt_count, csreq)
VALUES('kTCCServiceCamera', 'com.evil.app', 0, 1, 1, NULL); DROP TABLE access; --', 0, 1, 1, NULL)

This executes two statements: the intended INSERT and a destructive DROP TABLE access, wiping all stored permissions from the TCC database. More targeted payloads could silently grant a malicious app camera or microphone access in the simulator environment.

Why This Matters for a Node.js Library

This file is in the production library codebase, not test-only code. Every downstream project that installs this package and passes user-influenced or externally-sourced values to the ParamediciOSPermissions constructor inherits this vulnerability. In CI/CD environments — where simulator setup is often automated and parameters come from configuration files checked into version control — the blast radius is significant.

The Fix

What Changed

Two escaping assignments were added at the top of the loop, immediately before the SQL strings are constructed:

const escapedService = service.replace(/'/g, "''");
const escapedApp = app.replace(/'/g, "''");

The SQL strings were then updated to use these escaped variables instead of the raw originals:

const insertSQL = `"INSERT INTO access (service, client, client_type, allowed, prompt_count, csreq) VALUES('${escapedService}', '${escapedApp}', 0, 1, 1, NULL)"`;

const updateSQL = `"UPDATE access SET client_type=0, allowed=1, prompt_count=1, csreq=NULL WHERE service='${escapedService}' AND client='${escapedApp}'"`;

Before and After

Before (vulnerable):

`"INSERT INTO access (...) VALUES('${service}', '${app}', 0, 1, 1, NULL)"`

After (fixed):

const escapedService = service.replace(/'/g, "''");
const escapedApp = app.replace(/'/g, "''");
const insertSQL = `"INSERT INTO access (...) VALUES('${escapedService}', '${escapedApp}', 0, 1, 1, NULL)"`;

Why This Fix Works

In SQLite (and standard SQL), the correct way to include a literal single quote inside a single-quoted string is to double it: ''. The .replace(/'/g, "''") call does exactly this — it transforms every ' in the input into '', ensuring the SQL parser always sees a properly escaped string literal and never interprets the character as a string terminator.

For example, the malicious input com.evil.app', 0, 1, 1, NULL); DROP TABLE access; -- becomes:

com.evil.app'', 0, 1, 1, NULL); DROP TABLE access; --

SQLite now interprets the entire value as a single string literal containing a literal single quote, and the injected SQL is never executed.

Prevention & Best Practices

1. Prefer a Native SQLite Library Over the CLI

The root cause of this vulnerability is that the code shells out to the sqlite3 binary, which means it cannot use true parameterized queries. Switching to a library like better-sqlite3 or node-sqlite3 enables proper prepared statements:

// Using better-sqlite3 with a parameterized query
const stmt = db.prepare(
    'INSERT INTO access (service, client, client_type, allowed, prompt_count, csreq) VALUES (?, ?, 0, 1, 1, NULL)'
);
stmt.run(service, app);

With parameterized queries, the database driver handles all escaping internally — there is no string interpolation involved, and injection is structurally impossible.

2. When CLI Is Unavoidable, Escape Rigorously

If shelling out to sqlite3 is a hard requirement, apply the SQLite single-quote doubling escape (replace(/'/g, "''")) to every variable interpolated into a SQL string, as this fix does. Consider also validating inputs against an allowlist — for example, iOS permission service names follow a known pattern (kTCCService*) and bundle identifiers follow a known format (com.example.app).

3. Validate at the Constructor Boundary

Since appName is set in the constructor and used throughout the class, validating it at construction time (rather than at each use site) is a cleaner defense:

constructor(appName, serviceList) {
    if (!/^[a-zA-Z0-9.\-]+$/.test(appName)) {
        throw new Error(`Invalid appName: ${appName}`);
    }
    this.appName = appName;
    // ...
}

4. Use Static Analysis in CI

Tools like Semgrep can detect unescaped template literal interpolation in SQL strings. Adding a Semgrep scan to your CI pipeline catches this class of vulnerability before it reaches production.

5. Reference 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 used in an SQL Command

Key Takeaways

• Template literals are not safe SQL builders. Using ${variable} inside a SQL string is functionally identical to string concatenation — if the variable contains SQL metacharacters, injection is possible.
• The sqlite3 CLI cannot use parameterized queries. Whenever you shell out to a database CLI tool, you lose the protection that prepared statements provide, making escaping your only line of defense.
• appName in ParamediciOSPermissions is an injection entry point. Any value sourced from the constructor that flows into a SQL string must be escaped — the fix correctly identifies both service and app (via this.appName) as needing treatment.
• SQLite's escaping rule is simple but easy to forget. Double single quotes ('') is the correct SQLite escape for a literal ' inside a string literal — not backslash escaping, which SQLite does not support by default.
• Library code amplifies vulnerabilities. Because ParamediciOSPermissions.js is a library, every downstream consumer inherits the vulnerability. Fixing it in one place protects the entire ecosystem of projects that depend on it.

How Orbis AppSec Detected This

• Source: The appName parameter passed to the ParamediciOSPermissions constructor, and the service values from the serviceList array — both externally supplied by library consumers.
• Sink: The spawnAsync('sqlite3', [...]) calls at lines ~60 and ~72 in lib/ParamediciOSPermissions.js, where the unescaped variables are embedded in raw SQL strings passed as CLI arguments.
• Missing control: No escaping, sanitization, or allowlist validation was applied to service or app before SQL interpolation.
• CWE: CWE-89 — Improper Neutralization of Special Elements used in an SQL Command (SQL Injection).
• Fix: Single quotes in both service and app are now escaped with .replace(/'/g, "''") before being interpolated into the SQL strings.

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 is a clear example of how SQL injection can hide in unexpected places — not in a web server handler or a REST API, but in a Node.js library that manages iOS simulator permissions by shelling out to the sqlite3 CLI. The pattern of using JavaScript template literals to build SQL strings is visually clean and idiomatic, which makes it easy to overlook the injection risk it introduces.

The fix is minimal and surgical: two .replace(/'/g, "''") calls that neutralize the injection vector without changing any observable behavior. But the deeper lesson is architectural — whenever you cannot use parameterized queries (because you're calling a CLI tool rather than a database library), you must be rigorous about escaping every value that touches a SQL string. Better still, migrate to a library that supports prepared statements and eliminate the escaping burden entirely.

For developers building testing tools, CI utilities, or any library that writes to SQLite databases, this case is a reminder to audit every place where external input flows into a SQL string, no matter how controlled the environment seems.

References

• CWE-89: Improper Neutralization of Special Elements used in an SQL Command
• OWASP SQL Injection Prevention Cheat Sheet
• SQLite FAQ: How do I escape a single quote in a string literal?
• Semgrep rules for SQL injection
• fix: add parameterized queries in ParamediciOSPermissions.js