SQL Injection in node-postgres: The Pattern Everyone Gets Wrong

Source: Dev.to

Vulnerable example

// ❌ This looks fine, right?
async function getUser(userId) {
  const query = `SELECT * FROM users WHERE id = '${userId}'`;
  const result = await pool.query(query);
  return result.rows[0];
}

Attack demonstration

// Attacker input:
const userId = "'; DROP TABLE users; --";

// Generated query:
// SELECT * FROM users WHERE id = ''; DROP TABLE users; --'

Running the above query drops the entire users table, destroying data and potentially causing major business impact.

Why it’s dangerous

• Input validation can be bypassed – relying on client‑side checks or simple regexes isn’t enough.
• Internal APIs are still exposed – an internal endpoint can be called by a compromised service.
• Template literals are just string concatenation – they do not escape SQL automatically.
• ORMs don’t protect you when you use raw queries – the safety only applies when you let the ORM handle parameter binding.

Safe parameterized query (the only reliable pattern)

// ✅ Parameterized query
async function getUser(userId) {
  const query = 'SELECT * FROM users WHERE id = $1';
  const result = await pool.query(query, [userId]);
  return result.rows[0];
}

The $1 placeholder tells PostgreSQL to treat the supplied value as data, not as executable code. No amount of malicious input can break out of the parameter.

Linting with eslint-plugin-pg

Install the plugin

npm install --save-dev eslint-plugin-pg

ESLint configuration

// eslint.config.js
import pg from 'eslint-plugin-pg';
export default [pg.configs.recommended];

Example lint error

src/users.ts
  4:17  error  🔒 CWE-89 OWASP:A03 CVSS:9.8 | Unsafe query detected
               Fix: Use parameterized query: client.query('SELECT * FROM users WHERE id = $1', [userId])

Handling dynamic table names safely

// ❌ Unsafe – user‑controlled table name
const table = userInput;
pool.query(`SELECT * FROM ${table}`);

// ✅ Safe – whitelist allowed tables
const ALLOWED_TABLES = ['users', 'orders', 'products'];
if (!ALLOWED_TABLES.includes(table)) throw new Error('Invalid table');
pool.query(`SELECT * FROM ${table}`);

Building queries with optional filters

Vulnerable approach

let query = 'SELECT * FROM users WHERE 1=1';
if (name) query += ` AND name = '${name}'`; // Injection!
await pool.query(query);

Secure approach using parameters

const params = [];
let query = 'SELECT * FROM users WHERE 1=1';
if (name) {
  params.push(name);
  query += ` AND name = $${params.length}`;
}
await pool.query(query, params);

Resources

• npm package: eslint-plugin-pg – 13 rules covering PostgreSQL security, connection management, and query optimization.
• Rule documentation: no-unsafe-query (see plugin docs for details).