How to Use Parameterized Queries

The definitive defense against SQL injection attacks

Prerequisites:

• Basic SQL knowledge
• A database (PostgreSQL, MySQL, SQLite, etc.)
• Backend application code

Why This Matters

SQL injection has been the #1 web vulnerability for over two decades. It allows attackers to read your entire database, modify data, delete tables, or even execute system commands. The fix is simple: parameterized queries.

AI code generators often create vulnerable string concatenation code. This guide shows you how to spot and fix these issues.

Understanding the Problem

// VULNERABLE - String concatenation
const query = `SELECT * FROM users WHERE email = '${userInput}'`;

// If userInput is: ' OR '1'='1
// Query becomes: SELECT * FROM users WHERE email = '' OR '1'='1'
// This returns ALL users!

// Even worse, if userInput is: '; DROP TABLE users; --
// Query becomes: SELECT * FROM users WHERE email = ''; DROP TABLE users; --'
// Your users table is gone.

Step-by-Step Guide

import { Pool } from 'pg';
const pool = new Pool();

// WRONG - Vulnerable to SQL injection
async function getUserBad(email) {
  const result = await pool.query(
    `SELECT * FROM users WHERE email = '${email}'`  // NEVER DO THIS
  );
  return result.rows[0];
}

// CORRECT - Parameterized query
async function getUser(email) {
  const result = await pool.query(
    'SELECT * FROM users WHERE email = $1',  // $1 is a placeholder
    [email]  // Parameters passed separately
  );
  return result.rows[0];
}

// Multiple parameters
async function getUserByCredentials(email, status) {
  const result = await pool.query(
    'SELECT * FROM users WHERE email = $1 AND status = $2',
    [email, status]  // $1 = email, $2 = status
  );
  return result.rows[0];
}

import mysql from 'mysql2/promise';
const pool = mysql.createPool({ /* config */ });

// WRONG
async function getUserBad(email) {
  const [rows] = await pool.execute(
    `SELECT * FROM users WHERE email = '${email}'`
  );
  return rows[0];
}

// CORRECT - Use ? placeholders
async function getUser(email) {
  const [rows] = await pool.execute(
    'SELECT * FROM users WHERE email = ?',
    [email]
  );
  return rows[0];
}

// Named placeholders (mysql2 feature)
async function getUser(email) {
  const [rows] = await pool.execute(
    'SELECT * FROM users WHERE email = :email',
    { email }
  );
  return rows[0];
}

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// Prisma automatically parameterizes - this is safe
async function getUser(email) {
  return prisma.user.findUnique({
    where: { email }  // Automatically parameterized
  });
}

// Raw queries still need parameterization
async function searchUsers(searchTerm) {
  // WRONG - vulnerable
  return prisma.$queryRawUnsafe(
    `SELECT * FROM users WHERE name LIKE '%${searchTerm}%'`
  );

  // CORRECT - use $queryRaw with template literal
  return prisma.$queryRaw`
    SELECT * FROM users WHERE name LIKE ${`%${searchTerm}%`}
  `;
}

import { eq, like, sql } from 'drizzle-orm';
import { db } from './db';
import { users } from './schema';

// Safe - Drizzle parameterizes automatically
async function getUser(email) {
  return db.select().from(users).where(eq(users.email, email));
}

// For raw SQL, use sql template tag
async function searchUsers(searchTerm) {
  return db.execute(
    sql`SELECT * FROM users WHERE name LIKE ${`%${searchTerm}%`}`
  );
}

import { MongoClient } from 'mongodb';

// MongoDB has its own injection risks with operators
// WRONG - allows operator injection
async function getUserBad(query) {
  return collection.findOne(query);  // If query is { $gt: "" }, returns first user
}

// CORRECT - validate and sanitize
async function getUser(email) {
  // Ensure email is a string, not an object with operators
  if (typeof email !== 'string') {
    throw new Error('Invalid email format');
  }
  return collection.findOne({ email });
}

// Or use strict typing
async function getUser(email: string) {
  return collection.findOne({ email: { $eq: email } });
}

// Sometimes you need dynamic column names or ORDER BY
// These can't be parameterized - use allowlists instead

const ALLOWED_SORT_COLUMNS = ['created_at', 'name', 'email'];
const ALLOWED_SORT_ORDERS = ['ASC', 'DESC'];

async function getUsers(sortBy, sortOrder) {
  // Validate against allowlist
  if (!ALLOWED_SORT_COLUMNS.includes(sortBy)) {
    throw new Error('Invalid sort column');
  }
  if (!ALLOWED_SORT_ORDERS.includes(sortOrder)) {
    throw new Error('Invalid sort order');
  }

  // Now safe to use in query (still don't parameterize these)
  const result = await pool.query(
    `SELECT * FROM users ORDER BY ${sortBy} ${sortOrder}`
  );
  return result.rows;
}

// For dynamic WHERE conditions
async function searchUsers(filters) {
  const conditions = [];
  const params = [];
  let paramIndex = 1;

  if (filters.email) {
    conditions.push(`email = $${paramIndex++}`);
    params.push(filters.email);
  }
  if (filters.status) {
    conditions.push(`status = $${paramIndex++}`);
    params.push(filters.status);
  }

  const whereClause = conditions.length
    ? `WHERE ${conditions.join(' AND ')}`
    : '';

  return pool.query(`SELECT * FROM users ${whereClause}`, params);
}

How to Verify It Worked

1. Test with SQL injection payloads:

// Test your endpoint with these inputs:
const testPayloads = [
  "' OR '1'='1",
  "'; DROP TABLE users; --",
  "1; SELECT * FROM users",
  "admin'--",
  "' UNION SELECT * FROM passwords--"
];

// None of these should return unexpected data or cause errors
// They should be treated as literal search strings

1. Enable query logging: Check that parameters are being bound, not concatenated
2. Use security scanners: Tools like SQLMap or OWASP ZAP can test for injection

Common Errors & Troubleshooting

Error: "bind message supplies X parameters, but prepared statement requires Y"

Mismatch between placeholders and parameters. Count your $1, $2, etc. and make sure the array has the same number of elements.

Error: "could not determine data type of parameter $1"

PostgreSQL can't infer the type. Cast explicitly: WHERE id = $1::integer

LIKE queries not working

Include wildcards in the parameter, not the query: WHERE name LIKE $1 with parameter %search%

IN clauses with dynamic lists

Generate placeholders dynamically: WHERE id IN ($1, $2, $3) based on array length, or use ANY($1) with an array parameter in PostgreSQL.

Related guides:Prevent SQL Injection · Prisma Security · Validate User Input