SQL Injection Prevention: A Developer's Complete Guide

SQL injection has appeared in OWASP's Top 10 web vulnerabilities for over two decades and remains one of the most exploited vulnerabilities in web applications. In 2023, SQL injection was responsible for some of the largest data breaches.

The good news: it's completely preventable with a single consistent technique — parameterized queries.

How SQL Injection Works

SQL injection occurs when user input is concatenated directly into a SQL query. The database can't distinguish between the intended query structure and the attacker-supplied SQL.

Classic example

// ❌ VULNERABLE: string concatenation
const query = `SELECT * FROM users WHERE email = '${userEmail}' AND password = '${password}'`;

Normal input: alice@example.com / mypassword

SELECT * FROM users WHERE email = 'alice@example.com' AND password = 'mypassword'

Attacker input: ' OR '1'='1 / anything

SELECT * FROM users WHERE email = '' OR '1'='1' AND password = 'anything'

'1'='1' is always true, so this returns all users. The attacker logs in as the first user (often admin).

The comment-based bypass

Input: admin'--

SELECT * FROM users WHERE email = 'admin'--' AND password = '...'
-- Everything after -- is a comment; the password check is skipped

UNION-based extraction

If the result is displayed to the user:

Input for email field: ' UNION SELECT username, password, null FROM users--

SELECT id, name FROM products WHERE category = '' UNION SELECT username, password, null FROM users--'

The response now contains the entire users table.

Types of SQL Injection

Type	How attacker gets data
In-band (Classic)	Response returns query results directly
Error-based	Database error messages reveal data
UNION-based	Append additional SELECT to retrieve extra data
Blind - Boolean	Ask true/false questions, observe app behavior differences
Blind - Time-based	Use `SLEEP()` to infer data character by character
Out-of-band	Database makes DNS/HTTP requests to exfiltrate data

Blind SQL injection is slower but just as dangerous — automated tools like sqlmap can extract entire databases through boolean responses in hours.

The Fix: Parameterized Queries

Parameterized queries (also called prepared statements) separate SQL code from data. The database receives the query structure and the data separately, so data can never be interpreted as SQL code.

Node.js with pg (PostgreSQL)

// ❌ Vulnerable
const result = await client.query(
  `SELECT * FROM users WHERE email = '${email}'`
);

// ✅ Safe — data is passed as a separate parameter
const result = await client.query(
  'SELECT * FROM users WHERE email = $1 AND active = $2',
  [email, true]
);

Node.js with mysql2

// ✅ Safe
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE email = ? AND active = ?',
  [email, true]
);

Node.js with Prisma (ORM)

// ✅ Safe — Prisma parameterizes automatically
const user = await prisma.user.findFirst({
  where: { email: email, active: true },
});

// ✅ Safe — even raw queries with Prisma.$queryRaw use tagged templates
const users = await prisma.$queryRaw`
  SELECT * FROM users WHERE email = ${email}
`;

// ❌ Vulnerable — Prisma.$queryRawUnsafe does NOT escape
const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM users WHERE email = '${email}'`
);

Python with psycopg2 (PostgreSQL)

# ❌ Vulnerable
cursor.execute(f"SELECT * FROM users WHERE email = '{email}'")

# ✅ Safe
cursor.execute(
    "SELECT * FROM users WHERE email = %s AND active = %s",
    (email, True)
)

Python with SQLAlchemy

from sqlalchemy import text

# ✅ Safe — named parameters
result = db.execute(
    text("SELECT * FROM users WHERE email = :email"),
    {"email": email}
)

# ✅ Safe — ORM
user = db.query(User).filter(User.email == email).first()

# ❌ Vulnerable — don't concatenate into text()
result = db.execute(text(f"SELECT * FROM users WHERE email = '{email}'"))

Go with database/sql

// ❌ Vulnerable
query := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)
rows, err := db.Query(query)

// ✅ Safe
rows, err := db.Query(
    "SELECT * FROM users WHERE email = $1 AND active = $2",
    email, true,
)

What About ORMs?

ORMs like Prisma, Sequelize, TypeORM, SQLAlchemy, and ActiveRecord generate parameterized queries by default when you use their query builders. This makes them generally safe — but with important caveats:

Raw query escapes: $queryRawUnsafe(), sequelize.query() with interpolation, execute() with f-strings — all can be vulnerable if you insert user input directly.
order and group clauses: Many ORMs don't parameterize ORDER BY and GROUP BY. User-controlled column names can still be injectable.

// ❌ Potentially vulnerable — column name from user
const users = await prisma.user.findMany({
  orderBy: { [userSortColumn]: 'asc' },
});

// ✅ Safe — validate against allowlist first
const ALLOWED_SORT_COLUMNS = ['name', 'email', 'createdAt'];
if (!ALLOWED_SORT_COLUMNS.includes(userSortColumn)) {
  throw new Error('Invalid sort column');
}
const users = await prisma.user.findMany({
  orderBy: { [userSortColumn]: 'asc' },
});

Stored Procedures

Stored procedures can be safe or vulnerable depending on how they're implemented.

-- ✅ Safe: uses parameterized queries internally
CREATE PROCEDURE GetUser(@Email NVARCHAR(255))
AS
  SELECT * FROM Users WHERE Email = @Email;

-- ❌ Vulnerable: builds SQL string internally
CREATE PROCEDURE SearchUser(@Input NVARCHAR(255))
AS
  EXEC('SELECT * FROM Users WHERE Email = ''' + @Input + '''');

Input Validation as Defense in Depth

Parameterized queries are your primary defense. Input validation is a secondary layer that reduces your attack surface:

import { z } from 'zod';

const emailSchema = z.string().email().max(254);
const idSchema = z.string().uuid();
const limitSchema = z.number().int().min(1).max(100);

// Validate before any database query
const validEmail = emailSchema.parse(userInput);

Validation won't prevent injection if someone bypasses it, but it catches a lot of obviously malicious input and provides defense in depth.

Testing Your Application

# sqlmap: automated SQL injection testing tool
sqlmap -u "https://yourapp.com/api/users?id=1" --dbs

# Test a POST endpoint
sqlmap -u "https://yourapp.com/api/login" \
  --data='{"email":"test@test.com","password":"test"}' \
  --headers='Content-Type: application/json' \
  --level=3

# With authenticated session
sqlmap -u "https://yourapp.com/api/profile" \
  --cookie='session=your_session_token' \
  --dbs

Run sqlmap against your own endpoints in a test environment before deploying. If sqlmap can extract data, so can an attacker.

Least Privilege Database Accounts

Your application's database credentials should only have the permissions it needs:

-- Application user: SELECT, INSERT, UPDATE, DELETE only
CREATE USER app_user WITH PASSWORD 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Migrations run separately with a more privileged user
-- Revoke ability to DROP, CREATE, ALTER from the app user

Even if SQL injection occurs, an attacker with a SELECT-only account can't drop tables or create backdoor users.