How SQL injection via unsafe template literals happens in TypeScript database scripts and how to fix it

Introduction

In a database verification script, we discovered a critical SQL injection vulnerability in scripts/verify-db.ts at line 29. The countTable() function used the postgres client's unsafe() method with a template literal that directly interpolated the tableName parameter into a SQL query. This seemingly innocent pattern—common in scripts that developers assume are "internal only"—created a severe security risk. An attacker who could influence script execution parameters, environment variables, or configuration files could inject arbitrary SQL commands, potentially exfiltrating sensitive data, modifying records, or even dropping entire tables.

The vulnerability was particularly dangerous because verify-db.ts is production code, not a test-only script, and the postgres client's unsafe() method explicitly bypasses the library's built-in SQL injection protections.

The Vulnerability Explained

Let's examine the vulnerable code from scripts/verify-db.ts:

async function countTable(tableName: string) {
  const [row] = await client.unsafe<{ count: number }[]>(`select count(*)::int as count from ${tableName}`);
  return Number(row?.count ?? 0);
}

The problem lies in line 29 where client.unsafe() is combined with a template literal that directly embeds ${tableName} into the SQL query. The unsafe() method exists for rare edge cases where developers need to execute dynamic SQL, but it completely disables the postgres client's parameterization protections.

How could this be exploited?

Consider these attack scenarios:

1. Table Drop Attack: If an attacker controls the script's execution parameters, they could pass:
   tableName = "users; DROP TABLE users; --"
   The resulting SQL would be:
   sql select count(*)::int as count from users; DROP TABLE users; --
   This would first count the users table, then immediately drop it.

2. Data Exfiltration: An attacker could inject a UNION-based payload:
   tableName = "users UNION SELECT password FROM admin_credentials WHERE '1'='1"
   This would expose sensitive data from other tables.

3. Boolean-based Blind SQL Injection: Even if results aren't directly visible:
   tableName = "users WHERE 1=1 AND (SELECT COUNT(*) FROM sensitive_table) > 0 --"
   This allows attackers to infer information about the database structure.

Real-world impact: The verify-db.ts script appears to be part of database health checks or initialization routines. If this script runs with elevated database privileges (common for verification scripts), an attacker could compromise the entire database. The scanner flagged this as "Likely exploitable" because scripts often accept parameters from configuration files, environment variables, or command-line arguments—all potential attack vectors.

The Fix

The fix replaced the unsafe pattern with proper parameterized queries. Here's the before and after:

Before (vulnerable):

async function countTable(tableName: string) {
  const [row] = await client.unsafe<{ count: number }[]>(`select count(*)::int as count from ${tableName}`);
  return Number(row?.count ?? 0);
}

After (secure):

async function countTable(tableName: string) {
  const [row] = await client<{ count: number }[]>`select count(*)::int as count from ${client(tableName)}`;
  return Number(row?.count ?? 0);
}

What changed?

1. Removed client.unsafe(): The fix eliminates the unsafe method entirely, switching to the standard parameterized query syntax.

2. Added client(tableName) escaping: The tableName parameter is now wrapped in client(tableName), which tells the postgres library to treat this as an identifier that needs proper escaping, not raw SQL.

3. Preserved type safety: The query still uses TypeScript generics (client<{ count: number }[]>) to maintain type safety for the result.

How this solves the problem:

The postgres client library's client() function performs identifier escaping, which means:
- Special characters in tableName are properly escaped
- SQL keywords are quoted to prevent interpretation as commands
- Multi-statement injections are prevented because the identifier is treated as a single atomic value

If an attacker tries to inject "users; DROP TABLE users; --", the postgres client will escape it to something like "users; DROP TABLE users; --" (with quotes), treating the entire string as a literal table name. The database will look for a table with that exact name (which doesn't exist), rather than executing the DROP command.

Prevention & Best Practices

1. Never use .unsafe() with user-influenced input

The postgres client's unsafe() method should be reserved for truly static SQL where you have complete control over the query string. If any part of the query comes from external sources (parameters, environment variables, config files), use parameterized queries.

2. Use the postgres client's built-in escaping

For identifiers (table names, column names), use client(identifier):

client`SELECT * FROM ${client(tableName)} WHERE id = ${userId}`

For values, the postgres client automatically parameterizes them when you use template literals without unsafe():

client`SELECT * FROM users WHERE email = ${userEmail}`

3. Implement input validation as a secondary defense

While parameterization is the primary defense, validate that table names match expected patterns:

const VALID_TABLES = ['users', 'orders', 'products'];

async function countTable(tableName: string) {
  if (!VALID_TABLES.includes(tableName)) {
    throw new Error(`Invalid table name: ${tableName}`);
  }
  const [row] = await client<{ count: number }[]>`select count(*)::int as count from ${client(tableName)}`;
  return Number(row?.count ?? 0);
}

4. Use static analysis tools

Configure tools like Semgrep, ESLint with security plugins, or CodeQL to detect client.unsafe() patterns:

# Semgrep rule example
rules:
  - id: postgres-unsafe-usage
    pattern: client.unsafe(...)
    message: Avoid client.unsafe() - use parameterized queries instead
    severity: ERROR

5. Apply least privilege

Database scripts should run with minimal necessary permissions. If verify-db.ts only needs to count rows, the database user should only have SELECT privileges, not DROP or DELETE.

6. Review all database interaction code

SQL injection can occur in any layer:
- ORM configurations (TypeORM, Prisma raw queries)
- Migration scripts
- Admin tools and internal scripts
- GraphQL resolvers with raw SQL

References to security standards:

• CWE-89: This vulnerability maps to CWE-89 (SQL Injection)
• OWASP A03:2021: Injection vulnerabilities are #3 in the OWASP Top 10
• OWASP SQL Injection Prevention Cheat Sheet: Recommends parameterized queries as the primary defense

Key Takeaways

• The countTable() function in verify-db.ts used client.unsafe() with template literal interpolation, creating a direct SQL injection vulnerability at line 29
• "Internal" scripts are still attack surfaces: Just because code is in a /scripts directory doesn't mean it's safe from exploitation—attackers can manipulate environment variables, configuration files, or script parameters
• The postgres client's client(identifier) syntax is specifically designed for safe identifier escaping: Use it for table names, column names, and other SQL identifiers
• Never trust the .unsafe() method with any external input: The method name itself is a warning—it bypasses all SQL injection protections
• Parameterized queries prevent SQL injection more reliably than input validation: Validation can be bypassed with encoding tricks, but proper parameterization makes injection structurally impossible

How Orbis AppSec Detected This

• Source: The tableName parameter in the countTable() function, potentially controllable through script arguments, environment variables, or configuration files
• Sink: client.unsafe() method call at line 29 in scripts/verify-db.ts, which directly interpolates the unsanitized tableName into a SQL query using template literal syntax
• Missing control: No parameterization or identifier escaping—the tableName value flows directly into the SQL string without any sanitization
• CWE: CWE-89 (Improper Neutralization of Special Elements used in an SQL Command)
• Fix: Replaced client.unsafe() with parameterized query syntax and wrapped tableName in client(tableName) for proper identifier escaping

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 SQL injection vulnerability in verify-db.ts demonstrates why security cannot be an afterthought, even in "internal" scripts. The combination of client.unsafe() and template literal interpolation created a critical vulnerability that could have allowed attackers to execute arbitrary SQL commands. By switching to parameterized queries with proper identifier escaping, the fix eliminates the injection vector while maintaining the script's functionality. Remember: when working with databases, always use your client library's parameterization features, treat .unsafe() methods with extreme caution, and validate that security controls are present even in code that seems "internal only."

References

• CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
• OWASP SQL Injection Prevention Cheat Sheet
• postgres.js Documentation - Parameterized Queries
• Semgrep Rules for SQL Injection Detection
• fix: add parameterized queries in verify-db.ts