SQL injection remains the most reliably exploited web vulnerability in 2026. According to the Verizon 2025 Data Breach Investigations Report, injection attacks account for 17% of all confirmed data breaches, and Node.js applications are frequently targeted because developers often reach for string concatenation instead of parameterized queries. A single unparameterized query can hand an attacker your entire database in seconds.

This tutorial walks through 12 concrete steps to eliminate SQL injection from your Node.js application, covering raw drivers (mysql2, pg, better-sqlite3), input validation with express-validator, and ORM-based protection with Sequelize and Prisma. Each step includes working code, common pitfalls, and verification commands you can run immediately.

What SQL Injection Actually Does

SQL injection happens when attacker-controlled input is concatenated directly into a SQL query string. The classic example is a login form:

// VULNERABLE: never do this
const query = `SELECT * FROM users WHERE username = '${req.body.username}' AND password = '${req.body.password}'`;

If an attacker submits admin' -- as the username, the query becomes:

SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'

The double-dash comments out the password check. The attacker logs in as admin without knowing the password. More destructive payloads can dump entire tables, delete records, or in some database configurations execute operating system commands.

The PortSwigger Web Security Academy documents hundreds of SQLi techniques. OWASP classifies injection as A03:2021 in the Top Ten, meaning it has remained critical for five consecutive years. In Node.js, the risk is amplified because JavaScript’s loose typing means numeric inputs like user IDs can arrive as strings, and developers do not always validate types before building queries.

Prerequisites

  • Node.js 20 LTS or later (check with node --version)
  • npm 10 or later
  • A running MySQL 8, PostgreSQL 16, or SQLite database for testing
  • Basic Express.js knowledge (routing, middleware)
  • A terminal with write permissions to your project directory

All code in this tutorial uses ES modules ("type": "module" in package.json). If you use CommonJS, replace import/export with require/module.exports.

Step 1: Project Setup and Dependencies

Create a fresh project and install the dependencies you will need across all 12 steps:

mkdir sqli-prevention-demo && cd sqli-prevention-demo
npm init -y
npm install express mysql2 pg better-sqlite3 sequelize express-validator joi dotenv
npm install --save-dev nodemon

Add the following to package.json:

{
  "type": "module",
  "scripts": {
    "dev": "nodemon index.js",
    "start": "node index.js"
  }
}

Create a .env file with your database credentials. Never commit this file to version control:

MYSQL_HOST=localhost
MYSQL_USER=appuser
MYSQL_PASSWORD=strongpassword
MYSQL_DB=testdb
PG_HOST=localhost
PG_USER=appuser
PG_PASSWORD=strongpassword
PG_DB=testdb
NODE_ENV=development

Add .env to your .gitignore immediately:

echo ".env" >> .gitignore

Step 2: Reproduce the Vulnerability

Before building defenses, see the attack with your own eyes. Create vulnerable.js with a deliberately insecure endpoint:

// vulnerable.js — FOR DEMONSTRATION ONLY
import express from 'express';
import mysql from 'mysql2/promise';
import 'dotenv/config';

const app = express();
app.use(express.json());

const pool = await mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DB,
});

// VULNERABLE: string concatenation
app.get('/users', async (req, res) => {
  const { id } = req.query;
  const [rows] = await pool.query(`SELECT * FROM users WHERE id = ${id}`);
  res.json(rows);
});

app.listen(3000);

Request GET /users?id=1 OR 1=1 and you get every row in the table. Request GET /users?id=1 UNION SELECT table_name,null,null FROM information_schema.tables-- and you get the entire database schema. These are the attacks you will eliminate in the next steps.

Pitfall 1: Developers believe casting to integer protects them. It does not on its own. If your code runs parseInt(req.query.id) and the result is NaN because the input was 1;DROP TABLE users--, some query builders substitute an empty string, which still breaks the query in unexpected ways. Always validate first, then parameterize.

Step 3: Parameterized Queries with mysql2

The single most effective SQL injection prevention is parameterized queries (also called prepared statements). The SQL structure is sent to the database separately from the user data. The database driver handles escaping internally, so malicious characters can never alter the query’s structure.

With mysql2, use ? placeholders:

// secure-mysql.js
import express from 'express';
import mysql from 'mysql2/promise';
import 'dotenv/config';

const app = express();
app.use(express.json());

const pool = await mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DB,
  waitForConnections: true,
  connectionLimit: 10,
});

// SECURE: parameterized query
app.get('/users/:id', async (req, res) => {
  try {
    const [rows] = await pool.execute(
      'SELECT id, username, email FROM users WHERE id = ?',
      [req.params.id]
    );
    if (rows.length === 0) return res.status(404).json({ error: 'Not found' });
    res.json(rows[0]);
  } catch (err) {
    // Log internally, never expose DB errors to client
    console.error(err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// SECURE: multiple parameters
app.post('/login', async (req, res) => {
  const { username, password } = req.body;
  const [rows] = await pool.execute(
    'SELECT id, password_hash FROM users WHERE username = ?',
    [username]
  );
  // compare password_hash with bcrypt separately
  res.json({ found: rows.length > 0 });
});

app.listen(3000);

Use pool.execute() instead of pool.query() when possible. execute() sends a true prepared statement to MySQL, caching the parsed query plan and providing stronger isolation. query() with placeholders also escapes values, but does not use the server-side prepared statement protocol.

Pitfall 2: Using ? placeholders in the SQL string but passing the wrong number of values. If you have three ? marks and pass two values, mysql2 throws ER_PARSE_ERROR at runtime. Count your placeholders carefully, or use named bindings where your driver supports them.

MethodDriverPlaceholderPrepared StmtCaches Plan
pool.execute(sql, vals)mysql2?YesYes
pool.query(sql, vals)mysql2?NoNo
client.query(sql, vals)pg$1, $2…Yes (named)Session-scoped
db.prepare(sql).run()better-sqlite3? or @nameYesYes
Model.findOne(where)SequelizeAutomaticYesYes
prisma.user.findFirst()PrismaAutomaticYesYes

Step 4: Parameterized Queries with pg (PostgreSQL)

The pg driver (node-postgres) uses numbered placeholders: $1, $2, and so on. This approach is explicit about which value maps to which position, which reduces confusion in queries with many parameters:

// secure-postgres.js
import { Pool } from 'pg';
import 'dotenv/config';

const pool = new Pool({
  host: process.env.PG_HOST,
  user: process.env.PG_USER,
  password: process.env.PG_PASSWORD,
  database: process.env.PG_DB,
  ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: true } : false,
});

// SECURE: numbered placeholders
async function getUserById(id) {
  const result = await pool.query(
    'SELECT id, username, email FROM users WHERE id = $1',
    [id]
  );
  return result.rows[0] ?? null;
}

// SECURE: LIKE wildcard wrapped in JS, not in SQL template
async function searchUsers(username, role) {
  const result = await pool.query(
    'SELECT id, username, email FROM users WHERE username ILIKE $1 AND role = $2',
    [`%${username}%`, role]
  );
  return result.rows;
}

// SECURE: INSERT with RETURNING
async function createUser(username, email, hashedPassword) {
  const result = await pool.query(
    'INSERT INTO users (username, email, password_hash) VALUES ($1, $2, $3) RETURNING id',
    [username, email, hashedPassword]
  );
  return result.rows[0].id;
}

Notice the searchUsers function: the % wildcard characters are placed in the JavaScript string, not in the SQL template. Writing WHERE username ILIKE '%$1%' would treat $1 as a literal string instead of a placeholder, breaking the query entirely. Wrap the value itself with % in JavaScript and pass it as the parameter value to keep wildcard behavior while staying safe.

Pitfall 3: Reusing placeholder numbers incorrectly. Writing WHERE id = $1 OR parent_id = $1 safely reuses the same value. But accidentally writing $1 twice when you meant $1 and $2 with different values passes one value where pg expects two, throwing bind message supplies 1 parameters, but prepared statement requires 2.

Step 5: Parameterized Queries with better-sqlite3

SQLite is common in testing, local development, and embedded applications. The better-sqlite3 package provides synchronous, high-performance access. All operations are synchronous, so there is no await:

// secure-sqlite.js
import Database from 'better-sqlite3';

const db = new Database('./test.db');

// Enable WAL mode for better concurrent read performance
db.pragma('journal_mode = WAL');

// Create table
db.exec(`CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL,
  password_hash TEXT NOT NULL
)`);

// Prepare once, reuse many times
const getUserById = db.prepare('SELECT id, username, email FROM users WHERE id = ?');
const getUserByUsername = db.prepare(
  'SELECT id, username, email FROM users WHERE username = @username'
);
const insertUser = db.prepare(
  'INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)'
);

// Usage
function findUser(id) {
  return getUserById.get(id); // returns undefined if not found
}

function findByUsername(username) {
  return getUserByUsername.get({ username }); // named binding
}

// Transaction for atomic inserts
const createUserTx = db.transaction((username, email, hash) => {
  const info = insertUser.run(username, email, hash);
  return info.lastInsertRowid;
});

Call db.prepare() once per query at module level and reuse the prepared statement object throughout your application’s lifecycle. Calling it inside a hot loop re-parses the SQL on every execution, wasting CPU cycles on what should be a one-time operation.

Step 6: Input Validation with express-validator

Parameterized queries prevent SQL injection at the database layer. Input validation is a complementary defense at the application layer. It rejects malformed or unexpected input before it ever reaches your database driver, reducing attack surface and improving error messaging for legitimate users.

// validation-middleware.js
import { body, param, query, validationResult } from 'express-validator';

export const validateUserId = [
  param('id').isInt({ min: 1 }).withMessage('User ID must be a positive integer'),
];

export const validateUserSearch = [
  query('username')
    .optional()
    .isString()
    .isLength({ min: 1, max: 50 })
    .matches(/^[a-zA-Z0-9_.-]+$/)
    .withMessage('Username contains invalid characters'),
  query('role')
    .optional()
    .isIn(['admin', 'user', 'moderator'])
    .withMessage('Invalid role'),
];

export const validateCreateUser = [
  body('username')
    .isString()
    .trim()
    .isLength({ min: 3, max: 50 })
    .matches(/^[a-zA-Z0-9_.-]+$/)
    .withMessage('Username must be 3-50 alphanumeric characters'),
  body('email')
    .isEmail()
    .normalizeEmail()
    .withMessage('Must be a valid email address'),
  body('password')
    .isLength({ min: 12 })
    .matches(/^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[@$!%*?&])/)
    .withMessage('Password must be at least 12 characters with upper, lower, number, symbol'),
];

export function handleValidationErrors(req, res, next) {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({
      error: 'Validation failed',
      details: errors.array().map(e => ({ field: e.path, message: e.msg })),
    });
  }
  next();
}

Use the validation chains in your routes:

// routes/users.js
import express from 'express';
import {
  validateUserId,
  validateCreateUser,
  handleValidationErrors,
} from '../validation-middleware.js';

const router = express.Router();

router.get(
  '/:id',
  validateUserId,
  handleValidationErrors,
  async (req, res) => {
    const user = await getUserById(parseInt(req.params.id, 10));
    if (!user) return res.status(404).json({ error: 'Not found' });
    res.json(user);
  }
);

router.post(
  '/',
  validateCreateUser,
  handleValidationErrors,
  async (req, res) => {
    const { username, email, password } = req.body;
    const id = await createUser(username, email, await hashPassword(password));
    res.status(201).json({ id });
  }
);

export default router;

Pitfall 4: Using express-validator‘s .escape() sanitizer as a SQL injection defense. It converts < to &lt; for HTML output, not for database input safety. If you later render the value where HTML entities are not decoded, users see garbled data. Use .trim() for whitespace normalization, and leave SQL escaping entirely to your parameterized query driver.

Step 7: Schema Validation with Joi

Joi provides a schema-based validation style that many teams find easier to read and maintain than express-validator’s chain API. The two are interchangeable for security purposes; choose based on team preference:

// joi-schemas.js
import Joi from 'joi';

export const createUserSchema = Joi.object({
  username: Joi.string()
    .alphanum()
    .min(3)
    .max(50)
    .required(),
  email: Joi.string()
    .email({ tlds: { allow: false } })
    .required(),
  password: Joi.string()
    .min(12)
    .pattern(/^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[@$!%*?&])/)
    .required(),
  role: Joi.string()
    .valid('user', 'admin', 'moderator')
    .default('user'),
});

export const searchSchema = Joi.object({
  username: Joi.string().max(50).optional(),
  page: Joi.number().integer().min(1).default(1),
  limit: Joi.number().integer().min(1).max(100).default(20),
});

// Express middleware factory
export function validateBody(schema) {
  return (req, res, next) => {
    const { error, value } = schema.validate(req.body, {
      abortEarly: false,
      stripUnknown: true,
    });
    if (error) {
      return res.status(400).json({
        error: 'Validation failed',
        details: error.details.map(d => ({ field: d.path.join('.'), message: d.message })),
      });
    }
    req.body = value;
    next();
  };
}

The key option is stripUnknown: true. Without it, a client could send extra fields like role: 'admin' that your application might unintentionally use. With this option enabled, only schema-declared fields reach your route handler, eliminating mass-assignment vulnerabilities alongside injection risks.

LibraryStyleBest forAsync validatorsTypeScript types
express-validatorChain / middlewareExpress-heavy appsYesGood
joiSchema objectClean schema definitionsYesGood
zodSchema + inferenceTypeScript-first projectsYesExcellent
yupSchema objectReact/Formik form validationYesGood

Step 8: ORM Protection with Sequelize

Sequelize’s query builder generates parameterized SQL automatically for all standard model operations. When you use Model.findOne(), Model.create(), or where options, Sequelize handles escaping transparently:

// models/User.js (Sequelize)
import { DataTypes, Model, Op, QueryTypes } from 'sequelize';
import sequelize from '../database.js';

class User extends Model {}

User.init({
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  username: {
    type: DataTypes.STRING(50),
    allowNull: false,
    unique: true,
    validate: {
      is: /^[a-zA-Z0-9_.-]+$/,
      len: [3, 50],
    },
  },
  email: {
    type: DataTypes.STRING(255),
    allowNull: false,
    validate: { isEmail: true },
  },
  role: {
    type: DataTypes.ENUM('user', 'admin', 'moderator'),
    defaultValue: 'user',
  },
}, { sequelize, modelName: 'User' });

// SECURE: Sequelize generates parameterized SQL for all model methods
const user = await User.findOne({ where: { username: req.body.username } });

// SECURE: Complex conditions with operators
const admins = await User.findAll({
  where: {
    role: 'admin',
    createdAt: { [Op.gte]: new Date('2026-01-01') },
  },
  attributes: ['id', 'username', 'email'],
  limit: 20,
});

// SECURE: Raw query with named replacements (not concatenation)
const results = await sequelize.query(
  'SELECT * FROM users WHERE username = :username',
  {
    replacements: { username: req.body.username },
    type: QueryTypes.SELECT,
  }
);

Pitfall 5: Using sequelize.query() with string interpolation instead of replacements or bind parameters. The ORM’s safety guarantees apply only to its query builder methods. When you drop to raw SQL, you take full responsibility for parameterization. The difference between replacements (client-side escaping) and bind (server-side prepared statement) matters for performance in tight loops: use bind for frequently-called queries.

Step 9: ORM Protection with Prisma

Prisma’s type-safe client generates parameterized queries for all model operations. The schema-first approach keeps TypeScript types and the database schema synchronized:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id           Int      @id @default(autoincrement())
  username     String   @unique @db.VarChar(50)
  email        String   @unique @db.VarChar(255)
  passwordHash String   @map("password_hash")
  role         Role     @default(USER)
  createdAt    DateTime @default(now()) @map("created_at")

  @@map("users")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}
// routes/users-prisma.js
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  log: process.env.NODE_ENV === 'development' ? ['query'] : ['error'],
});

// SECURE: all model methods generate parameterized SQL
async function getUser(id) {
  return prisma.user.findUnique({
    where: { id },
    select: { id: true, username: true, email: true, role: true },
  });
}

// SECURE: contains search — Prisma handles escaping
async function searchUsers(username) {
  return prisma.user.findMany({
    where: {
      username: { contains: username, mode: 'insensitive' },
    },
    select: { id: true, username: true, email: true },
    take: 20,
  });
}

// SECURE: $queryRaw uses tagged template — interpolated values become parameters
async function getRawUser(id) {
  const users = await prisma.$queryRaw`SELECT id, username FROM users WHERE id = ${id}`;
  return users[0] ?? null;
}

// DANGEROUS — never use this with user input:
// const users = await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE id = ${id}`);

Prisma’s $queryRaw uses JavaScript’s tagged template literal syntax. The template tag intercepts interpolated values and sends them as separate parameters to the database driver. The $queryRawUnsafe() method accepts a plain string and bypasses this mechanism. Reserve $queryRawUnsafe() only for cases where you must build SQL from a server-controlled allowlist of identifiers, never from user input.

Step 10: Stored Procedures as an Additional Defense Layer

Stored procedures define all allowed SQL operations at the database level. Even if an attacker manipulates a parameter, the procedure’s fixed SQL structure prevents query restructuring:

-- MySQL: create a stored procedure
DELIMITER //
CREATE PROCEDURE GetUserById(IN userId INT)
BEGIN
  SELECT id, username, email, role
  FROM users
  WHERE id = userId;
END //
DELIMITER ;
// Call stored procedure from Node.js (mysql2)
const [rows] = await pool.execute('CALL GetUserById(?)', [userId]);
const users = rows[0]; // stored proc results are in rows[0]

Stored procedures enable least-privilege enforcement at the database level: the application user needs only EXECUTE permission on the procedures, not SELECT/INSERT/UPDATE/DELETE on the underlying tables. This limits what an attacker can do even in the unlikely event of partial query control. The practical tradeoff is maintenance overhead: schema changes require updating both table definitions and stored procedures, which makes them less common in rapid-iteration applications but valuable in high-security environments like banking and healthcare systems.

Step 11: Least Privilege Database Accounts

Most Node.js tutorials connect to the database as the root user or a superuser. In production, this is a critical mistake. If your application is compromised, the attacker inherits all database permissions, including the ability to drop tables, create admin users, or read tables your application does not use.

Create a restricted database user that can only perform operations your application actually needs:

-- MySQL: create a restricted application user
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongRandomPassword123!';

-- Grant only the tables and operations the app requires
GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'appuser'@'%';
GRANT SELECT, INSERT ON myapp.orders TO 'appuser'@'%';
GRANT SELECT ON myapp.products TO 'appuser'@'%';

-- No DELETE, no DROP, no TRUNCATE, no access to other databases
FLUSH PRIVILEGES;

-- Verify the grants
SHOW GRANTS FOR 'appuser'@'%';
-- PostgreSQL: equivalent setup
CREATE USER appuser WITH PASSWORD 'StrongRandomPassword123!';
GRANT CONNECT ON DATABASE myapp TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE ON TABLE users TO appuser;
GRANT SELECT, INSERT ON TABLE orders TO appuser;
GRANT SELECT ON TABLE products TO appuser;
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO appuser;
PrivilegeWhen to GrantRisk if Over-Granted
SELECTAll read operationsData exposure
INSERTUser registration, loggingData injection
UPDATEProfile edits, status changesRecord tampering
DELETEOnly if app genuinely deletes rowsData destruction
DROPNever in app accountsTable deletion
CREATEMigrations only (separate user)Schema poisoning
FILENever in app accountsOS file read/write
SUPERNever in app accountsFull DB compromise

Step 12: Testing Your SQL Injection Defenses

Defense is worthless without verification. Test your parameterized queries and input validation with manual payloads before deploying, and automate the checks in CI.

Manual test payloads: Send each of these as the value for string and numeric parameters and verify that your application returns HTTP 400 (validation rejection) or the correct empty/not-found response, never unexpected data or error messages that expose schema information:

# Classic OR-based bypass
curl "http://localhost:3000/users?id=1 OR 1=1"

# Comment-based bypass
curl "http://localhost:3000/users?id=1--"
curl "http://localhost:3000/users?id=1 #"

# UNION-based data extraction
curl "http://localhost:3000/users?id=0 UNION SELECT table_name,null FROM information_schema.tables--"

# Stacked queries (MySQL/PostgreSQL)
curl "http://localhost:3000/users?id=1;DROP TABLE users--"

# Time-based blind injection (MySQL) — response should not be delayed
curl "http://localhost:3000/users?id=1 AND SLEEP(5)"

# Boolean-based blind injection — both should return identical responses
curl "http://localhost:3000/users?id=1 AND 1=1"
curl "http://localhost:3000/users?id=1 AND 1=2"

With a properly secured application, all of these return HTTP 400 (if numeric validation catches them before the query runs) or HTTP 404 with no data (if the parameterized query returns zero rows for the literal string 1 OR 1=1 as an ID value). The database receives the entire string as a value, not as executable SQL.

Automate testing with a Jest suite that runs on every pull request:

// tests/sqli.test.js
import request from 'supertest';
import app from '../index.js';

const injectionPayloads = [
  "1 OR 1=1",
  "1' OR '1'='1",
  "1; DROP TABLE users--",
  "1 UNION SELECT null--",
  "1 AND SLEEP(0.01)",
  "admin'--",
  "' OR ''='",
];

describe('SQL injection prevention', () => {
  test.each(injectionPayloads)('rejects or neutralizes payload: %s', async (payload) => {
    const res = await request(app).get(`/users/${encodeURIComponent(payload)}`);
    expect([400, 404]).toContain(res.status);
    expect(JSON.stringify(res.body)).not.toMatch(/table_name|information_schema/i);
  });
});

For deeper automated scanning, run sqlmap against a staging environment:

# Install sqlmap
pip3 install sqlmap

# Test a GET parameter
sqlmap -u "http://staging.example.com/users?id=1" --level=3 --risk=2

# Test POST body
sqlmap -u "http://staging.example.com/login" \
  --data="username=test&password=test" \
  --level=3 --risk=2

Run sqlmap only against environments you own or have explicit written authorization to test. Never target production systems or systems you do not control.

Error Handling Without Information Leakage

Database error messages are a goldmine for attackers. MySQL errors like You have an error in your SQL syntax near '...' confirm the injection point and expose the query structure. Never return raw database errors to API clients. Always route all database errors through a sanitizing handler:

// error-handler.js
import { randomUUID } from 'crypto';

export function databaseErrorHandler(err, req, res, next) {
  const correlationId = randomUUID();
  console.error({ correlationId, message: err.message, code: err.code, stack: err.stack });

  // MySQL specific codes
  if (err.code === 'ER_DUP_ENTRY') {
    return res.status(409).json({ error: 'A record with those values already exists' });
  }

  // PostgreSQL specific codes
  if (err.code === '23505') {
    return res.status(409).json({ error: 'A record with those values already exists' });
  }
  if (err.code === '23503') {
    return res.status(400).json({ error: 'Referenced record does not exist' });
  }

  // Generic: return correlation ID for support lookup, never internal details
  return res.status(500).json({ error: 'Internal server error', correlationId });
}

// Register after all routes: app.use(databaseErrorHandler);

The correlationId pattern lets support staff look up the full error in your log aggregator (Datadog, Grafana Loki, CloudWatch Logs) without exposing anything to the client. Log the correlation ID, error code, message, and stack trace internally; return only the ID externally.

Second-Order SQL Injection

First-order SQL injection attacks the query that processes user input immediately. Second-order injection is subtler: malicious data is stored safely (the INSERT is parameterized), then retrieved and used in a non-parameterized query elsewhere in the application.

Example: A user registers with username admin'--. The INSERT uses a parameterized query, storing the string literally. Later, an admin dashboard feature retrieves the username and builds a report query via string concatenation. The apostrophe and double-dash in the stored username then break this second query, giving the attacker control over it.

The defense is identical: parameterize every query that uses any database-retrieved value, not only queries that accept direct user input. Data from the database is not inherently safe because the database is not the trust boundary. Your application code is the trust boundary, and every SQL query it executes must use parameterized values.

ORDER BY and Dynamic SQL Patterns

One genuine limitation of parameterized queries is that placeholders cannot represent SQL identifiers (column names, table names, sort directions). This creates a real challenge for user-controlled sorting:

// BROKEN: ORDER BY cannot be parameterized — DB receives 'username' as a string value
const rows = await pool.execute('SELECT * FROM users ORDER BY ?', [req.query.sortBy]);

// CORRECT: validate against an allowlist first
const ALLOWED_SORT_COLUMNS = {
  username: 'username',
  email: 'email',
  created: 'created_at',
  role: 'role',
};
const ALLOWED_SORT_DIRS = ['ASC', 'DESC'];

function buildSafeSort(sortBy, sortDir) {
  const col = ALLOWED_SORT_COLUMNS[sortBy] ?? 'created_at';
  const dir = ALLOWED_SORT_DIRS.includes(sortDir?.toUpperCase()) ? sortDir.toUpperCase() : 'DESC';
  return { col, dir };
}

app.get('/users', async (req, res) => {
  const { col, dir } = buildSafeSort(req.query.sortBy, req.query.sortDir);
  // col and dir come from a hardcoded allowlist, not from raw user input
  const [rows] = await pool.execute(
    `SELECT id, username, email FROM users ORDER BY \`${col}\` ${dir} LIMIT 50`
  );
  res.json(rows);
});

Pitfall 6: Using an object lookup allowlist but forgetting to handle missing keys. If ALLOWED_SORT_COLUMNS[sortBy] returns undefined and you interpolate that directly, the SQL becomes ORDER BY undefined DESC, which throws a database error. Always provide a safe fallback default.

Complete Working Project

This is the full secure implementation combining parameterized queries, input validation, error handling, and least-privilege connection setup:

// index.js — complete secure Node.js/MySQL application
import 'dotenv/config';
import express from 'express';
import mysql from 'mysql2/promise';
import { body, param, validationResult } from 'express-validator';
import { randomUUID } from 'crypto';

const app = express();
app.use(express.json());

const pool = await mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DB,
  connectionLimit: 10,
  connectTimeout: 10000,
  waitForConnections: true,
});

const validateId = [param('id').isInt({ min: 1 })];
const validateSearch = [
  param('username').optional().isString().isLength({ max: 50 }).matches(/^[a-zA-Z0-9_.-]*$/),
];

function checkValidation(req, res, next) {
  const errors = validationResult(req);
  if (!errors.isEmpty()) return res.status(400).json({ errors: errors.array() });
  next();
}

const SORT_COLS = { username: 'username', email: 'email', created: 'created_at' };

app.get('/users/:id', validateId, checkValidation, async (req, res) => {
  const [rows] = await pool.execute(
    'SELECT id, username, email, role FROM users WHERE id = ?',
    [req.params.id]
  );
  if (!rows.length) return res.status(404).json({ error: 'Not found' });
  res.json(rows[0]);
});

app.get('/users', async (req, res) => {
  const col = SORT_COLS[req.query.sort] ?? 'created_at';
  const dir = req.query.dir?.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
  const [rows] = await pool.execute(
    `SELECT id, username, email FROM users ORDER BY \`${col}\` ${dir} LIMIT 50`
  );
  res.json(rows);
});

app.post('/login', [
  body('username').isString().isLength({ min: 3, max: 50 }),
  body('password').isString().isLength({ min: 1 }),
], checkValidation, async (req, res) => {
  const [rows] = await pool.execute(
    'SELECT id, password_hash FROM users WHERE username = ?',
    [req.body.username]
  );
  if (!rows.length) return res.status(401).json({ error: 'Invalid credentials' });
  res.json({ id: rows[0].id });
});

// Global error handler
app.use((err, req, res, next) => {
  const correlationId = randomUUID();
  console.error({ correlationId, message: err.message });
  res.status(500).json({ error: 'Internal server error', correlationId });
});

app.listen(3000, () => console.log('Secure server running on :3000'));

Common Pitfalls Summary

  1. String concatenation in any query. Template literals look clean but are the root cause of SQL injection. Every value that touches a database query must go through a parameterized placeholder.
  2. Trusting integer casting alone. parseInt() returns NaN for non-numeric input. Validate that the input is a valid integer with express-validator or Joi, then cast, then parameterize the result.
  3. Wrong placeholder count. Placeholder count must match the values array length exactly. mysql2 throws ER_PARSE_ERROR; pg throws a bind message error. Count them on every query change.
  4. Raw ORM queries without bind parameters. sequelize.query() and prisma.$queryRawUnsafe() bypass all ORM protection. Use them with explicit bind parameters or replace with model-based methods.
  5. HTML-escaping as a SQL defense. express-validator‘s .escape() converts characters for HTML output, not SQL safety. Only parameterized queries prevent SQL injection.
  6. Leaking raw error messages. Database error text exposes table names, column names, and query structure. Route all errors through a sanitizing handler that returns only a correlation ID externally.
  7. Second-order injection. Values retrieved from the database are not automatically safe to interpolate. Parameterize every query using any retrieved value, not just queries that accept direct user input.
  8. ORDER BY without an allowlist. Identifiers cannot be parameterized. Validate column names against a server-side allowlist object and provide a safe default when the value is absent or invalid.

Troubleshooting

1. ER_PARSE_ERROR: You have an error in your SQL syntax

Cause: Placeholder count does not match values array length, or you used the wrong placeholder syntax (e.g., $1 instead of ? with mysql2).
Fix: Count your ? marks and array elements. With mysql2 use ?; with pg use $1, $2, ...; with better-sqlite3 use ? or @name.

2. Error: bind message supplies N parameters, but prepared statement requires M

Cause: pg-specific error. Numbered placeholders do not match the values array length.
Fix: Ensure placeholder numbers run sequentially from $1. Each distinct position must have a unique number even if two positions use the same value.

3. ValidationError from Sequelize (not from express-validator)

Cause: Sequelize model-level validators rejected the value before the query was sent. This is expected behavior for invalid data.
Fix: Catch ValidationError from the sequelize package separately and return HTTP 400. Check err instanceof ValidationError before falling through to the generic 500 handler.

4. Prisma $queryRaw returns BigInt that breaks JSON serialization

Cause: Prisma raw query results return BigInt for integer columns, which does not serialize to JSON by default.
Fix: Convert BigInt fields after the query: result.map(r => ({ ...r, id: Number(r.id) })). Alternatively use Prisma model methods, which handle type coercion automatically.

5. sqlmap reports “not injectable” but code still concatenates

Cause: sqlmap did not test all endpoints, or rate limiting blocked the scan, producing false negatives.
Fix: Run sqlmap with --delay=1 --level=5 --risk=3 for more thorough coverage. Combine with the manual curl test payloads from Step 12.

6. express-validator errors not showing in response

Cause: The handleValidationErrors middleware was not added after the validation chain in the route definition.
Fix: Ensure the order is: router.get('/:id', validateUserId, handleValidationErrors, handler). The error check middleware must come after the validation chain and before the route handler.

7. Joi rejects valid emails with uncommon TLDs

Cause: Joi.string().email() validates TLDs by default. New TLDs like .dev, .io, and .ai are sometimes not recognized.
Fix: Pass { tlds: { allow: false } } to disable TLD validation: Joi.string().email({ tlds: { allow: false } }). This validates RFC 5322 format without TLD checking.

8. MySQL stored procedure returns empty result set

Cause: mysql2 returns stored procedure results as an array of result sets. The actual rows are in rows[0], not rows itself.
Fix: Destructure correctly: const [results] = await pool.execute('CALL GetUser(?)', [id]); const rows = results[0];

9. SQLITE_BUSY errors during concurrent writes

Cause: SQLite has file-level locking. Multiple concurrent write transactions cause busy errors in higher-traffic applications.
Fix: Enable WAL mode: db.pragma('journal_mode = WAL');. This allows one writer and multiple concurrent readers. For sustained high-concurrency write workloads, migrate to PostgreSQL or MySQL.

10. LIKE wildcard not working after parameterization

Cause: Placing % wildcards inside the SQL string around a placeholder like LIKE '%?%' treats the placeholder as a literal string, not a bound parameter position.
Fix: Add the % characters to the JavaScript value before passing it: pool.execute('SELECT * FROM users WHERE name LIKE ?', [`%${userInput}%`]).

Advanced Tips

Database Activity Monitoring

Enable query logging in production to detect anomalous patterns. MySQL’s General Query Log and PostgreSQL’s pg_stat_statements extension record every query. Forward logs to a SIEM (Splunk, Elastic Security, Datadog) and alert on queries that contain UNION SELECT, information_schema, long hex-encoded strings, or multiple stacked semicolons. This detects breaches even when they originate from endpoints you did not test in CI.

Web Application Firewall as Defense-in-Depth

A WAF (Cloudflare, AWS WAF, ModSecurity) can detect and block common SQL injection patterns before requests reach your application. WAFs are not a substitute for parameterized queries as they can be bypassed with encoding obfuscation, but they add a useful layer that blocks automated mass-scanning tools and reduces noise in application logs. Configure WAF rules specific to your database dialect (MySQL, PostgreSQL, SQLite patterns differ).

NoSQL Injection in MongoDB

If you use MongoDB with Mongoose, you face a related but different attack. An attacker can send { "$gt": "" } as a JSON body field, which MongoDB interprets as a query operator instead of a literal value, bypassing equality checks. Defend by setting mongoose.set('sanitizeFilter', true) in Mongoose 8+, which strips query operators from user-supplied filter objects. Combine this with schema validation in Mongoose models and input validation with express-validator or Joi on all request bodies.

Connection Timeouts Limit Time-Based Blind Attacks

Time-based blind injection (payloads like SLEEP(5) or pg_sleep(5)) can exhaust your connection pool if an attacker sends many concurrent requests. Set query timeouts at the driver level to limit per-query execution time:

// mysql2: set query timeout per pool
const pool = await mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DB,
  connectionLimit: 10,
  connectTimeout: 10000,
  // Statement timeout via SQL on connection init:
  // 'SET SESSION MAX_EXECUTION_TIME=5000;'
});

// PostgreSQL: set statement timeout
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  options: '-c statement_timeout=5000', // 5 seconds max per query
});

Combine query timeouts with rate limiting in Node.js to cap the number of requests per IP address per time window. A time-based injection attack requires many requests to extract data bit by bit; rate limiting makes this impractically slow.

SQL injection is one of many attack surfaces in a Node.js application. These tutorials cover the adjacent defenses that complete your security posture:

Frequently Asked Questions

Is an ORM enough to prevent SQL injection in Node.js?

ORMs like Sequelize and Prisma prevent SQL injection for their auto-generated queries, but developers frequently write raw queries using sequelize.query() or prisma.$queryRawUnsafe(). Those raw queries carry the same risks as raw driver queries if you concatenate user input instead of using bind parameters. An ORM reduces but does not eliminate the risk. Always combine ORM use with input validation and careful handling of raw query escape hatches.

What is the difference between parameterized queries and prepared statements?

Parameterized queries is the general term for separating SQL structure from data values. Prepared statements are one implementation: the query is compiled by the database server, then values are bound and executed separately, with the compiled plan cached. Both prevent SQL injection. In Node.js, mysql2‘s pool.execute() uses the MySQL prepared statement protocol (plan caching); pool.query() with an array uses client-side escaping (no plan caching). Both are safe; execute() is faster for repeated queries.

Can escaping single quotes prevent SQL injection?

Escaping single quotes by doubling them was historically used as a defense, but it is unreliable. Different database encodings, multi-byte character sets (like GBK in MySQL), and edge cases in escape functions have all produced bypasses. Parameterized queries are the only reliably safe approach because the database driver handles value separation at the protocol level, not through string transformation.

Does HTTPS prevent SQL injection?

No. HTTPS encrypts data in transit between client and server. SQL injection exploits server-side processing of input values after they arrive at your application. An attacker sending malicious payloads over HTTPS still reaches your application code with the full payload intact. HTTPS and SQL injection prevention address completely different threat vectors.

How do I handle dynamic column names safely?

Parameterized placeholders cannot represent column names or table names because placeholders represent values, not SQL identifiers. The safe approach is an allowlist: define a map of permitted column names in your code and look up the user-provided string against that map before interpolating it. Reject any value not found in the map with a 400 error. Never interpolate raw user input as a column name.

What about NoSQL injection in MongoDB Node.js applications?

MongoDB is not immune to injection. Attackers can pass JSON objects with operators like { "$gt": "" } as query field values, causing MongoDB to interpret them as query operators. Defend with mongoose.set('sanitizeFilter', true) in Mongoose 8+, schema validation on all models, and strict input type enforcement via express-validator or Joi before values reach any MongoDB query or filter object.

How often should I test for SQL injection?

Run automated tests with injection payloads on every pull request in CI. Run sqlmap against staging environments before each major release. Schedule a manual penetration test from a third-party security firm at least once per year, or after any significant change to your data access layer. Refer to the OWASP SQL Injection Prevention Cheat Sheet for a testing checklist to guide each review cycle.

Can a WAF alone protect my Node.js app from SQL injection?

A WAF adds a useful defense-in-depth layer by blocking known injection signatures, but WAFs can be bypassed with encoding tricks, comment variations, and unconventional whitespace that evade signature matching. Treat a WAF as supplemental, never as a substitute for parameterized queries in your application code. The PortSwigger SQL injection reference documents dozens of WAF bypass techniques in common use.