SQL Injection Prevention

Understanding SQL Injection

SQL Injection occurs when attackers insert malicious SQL code into queries through user input. It can lead to data theft, data manipulation, and complete database compromise.

How SQL Injection Works

// VULNERABLE: String concatenation
const username = req.body.username;
const query = "SELECT * FROM users WHERE username = '" + username + "'";

// Normal input: "john"
// Query: SELECT * FROM users WHERE username = 'john'

// Malicious input: "' OR '1'='1"
// Query: SELECT * FROM users WHERE username = '' OR '1'='1'
// This returns ALL users!

// Even worse: "'; DROP TABLE users; --"
// Query: SELECT * FROM users WHERE username = ''; DROP TABLE users; --'
// This DELETES the entire users table!

Prevention: Parameterized Queries

1. Node.js with pg (PostgreSQL)

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

// VULNERABLE
async function getUser(username) {
  const query = `SELECT * FROM users WHERE username = '${username}'`;
  return pool.query(query);
}

// SAFE: Parameterized query
async function getUser(username) {
  const query = 'SELECT * FROM users WHERE username = $1';
  return pool.query(query, [username]);
}

// SAFE: Multiple parameters
async function authenticateUser(username, password) {
  const query = 'SELECT * FROM users WHERE username = $1 AND password_hash = $2';
  const passwordHash = await bcrypt.hash(password, 10);
  return pool.query(query, [username, passwordHash]);
}

2. MySQL with mysql2

const mysql = require('mysql2/promise');

// SAFE: Using placeholders
async function getUser(username) {
  const [rows] = await connection.execute(
    'SELECT * FROM users WHERE username = ?',
    [username]
  );
  return rows[0];
}

// SAFE: Named placeholders
async function searchUsers(criteria) {
  const [rows] = await connection.execute(
    'SELECT * FROM users WHERE name LIKE :name AND age > :age',
    { name: `%${criteria.name}%`, age: criteria.minAge }
  );
  return rows;
}

3. Using ORMs (Prisma)

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

// Prisma automatically uses parameterized queries
async function getUser(username: string) {
  return prisma.user.findUnique({
    where: { username },
  });
}

async function searchUsers(name: string, minAge: number) {
  return prisma.user.findMany({
    where: {
      name: { contains: name },
      age: { gte: minAge },
    },
  });
}

// Even raw queries are safe with Prisma
async function customQuery(userId: string) {
  return prisma.$queryRaw`
    SELECT * FROM users WHERE id = ${userId}
  `;
}

4. Using ORMs (Drizzle)

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

// Safe queries with Drizzle
async function getUser(username: string) {
  return db.select().from(users).where(eq(users.username, username));
}

async function searchUsers(name: string, minAge: number) {
  return db.select().from(users).where(
    and(
      like(users.name, `%${name}%`),
      gte(users.age, minAge)
    )
  );
}

Input Validation

import { z } from 'zod';

// Define strict schemas
const userQuerySchema = z.object({
  username: z.string()
    .min(3)
    .max(30)
    .regex(/^[a-zA-Z0-9_]+$/), // Only alphanumeric and underscore
  limit: z.number().int().min(1).max(100).default(10),
  offset: z.number().int().min(0).default(0),
});

async function searchUsers(input: unknown) {
  // Validate and sanitize input
  const { username, limit, offset } = userQuerySchema.parse(input);

  // Now safe to use in query
  return prisma.user.findMany({
    where: { username: { contains: username } },
    take: limit,
    skip: offset,
  });
}

Stored Procedures

-- Create a stored procedure
CREATE PROCEDURE GetUserByUsername(IN p_username VARCHAR(50))
BEGIN
  SELECT * FROM users WHERE username = p_username;
END;

-- Call from Node.js
async function getUser(username) {
  const [rows] = await connection.execute('CALL GetUserByUsername(?)', [username]);
  return rows[0];
}

Defense in Depth

// 1. Use parameterized queries (primary defense)
const query = 'SELECT * FROM users WHERE id = $1';

// 2. Validate and sanitize input
const userId = parseInt(req.params.id, 10);
if (isNaN(userId) || userId < 1) {
  throw new Error('Invalid user ID');
}

// 3. Use least privilege database accounts
// Create read-only user for SELECT queries
// CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'password';
// GRANT SELECT ON mydb.* TO 'readonly'@'localhost';

// 4. Escape output (defense against second-order injection)
function escapeForSQL(str) {
  return str.replace(/'/g, "''");
}

// 5. Use Web Application Firewall (WAF)
// Configure WAF to detect SQL injection patterns

Testing for SQL Injection

// Common SQL injection test payloads
const sqlInjectionPayloads = [
  "' OR '1'='1",
  "' OR '1'='1' --",
  "'; DROP TABLE users; --",
  "1; SELECT * FROM users",
  "' UNION SELECT * FROM passwords --",
  "admin'--",
  "1' AND '1'='1",
];

// Use tools like sqlmap for comprehensive testing
// sqlmap -u "http://example.com/user?id=1" --dbs

SQL Injection Prevention Checklist

• Always use parameterized queries or prepared statements
• Use an ORM that handles escaping automatically
• Validate all input with strict schemas
• Use least privilege database accounts
• Keep database software updated
• Monitor and log database queries
• Use a Web Application Firewall