SQL Injection Prevention: Protecting Your Database from Injection Attacks

In this tutorial, you will learn about SQL Injection Prevention: Protecting Your Database from Injection Attacks. We cover key concepts, practical examples, and best practices to help you master this topic.

SQL injection (SQLi) is an attack where malicious SQL statements are inserted into application queries through unsanitized user input. It remains one of the most critical web security risks because a successful injection can read, modify, or delete entire databases.

flowchart TB
    Attacker -->|Input: ' OR 1=1 --| UnsafeApp[Unsafe App]
    UnsafeApp -->|String Concatenation| SQL[SQL Query]
    SQL -->|SELECT * FROM users WHERE email = '' OR 1=1 --| DB[(Database)]
    DB -->|Returns ALL users| Attacker

    Attacker -.->|Same Input| SafeApp[Safe App]
    SafeApp -->|Parameterized Query| SafeSQL[SQL with ? placeholder]
    SafeSQL -->|email value treated as string| DB
    DB -->|Returns: no match| SafeApp

What You'll Learn

• How SQL injection attacks work and common injection vectors
• Parameterized queries and prepared statements
• ORM and query Builder safety features
• Second-order SQL injection and stored procedure safety

Why It Matters

SQL injection can lead to complete database compromise: data theft, data destruction, and in some cases, remote code execution on the database server. It is entirely preventable with consistent use of parameterized queries.

Real-World Use

An e-commerce platform was breached via SQL injection in the search endpoint. The attacker exfiltrated 10 million customer records including credit card numbers. The vulnerability existed because the search query used string interpolation: "SELECT * FROM products WHERE name LIKE '%" + userInput + "%'".

SQL Injection Prevention Techniques

Parameterized Queries with MySQL2

const mysql = require('mysql2/promise');
const connection = await mysql.createConnection({ /* config */ });

// SAFE: Parameterized query
async function getUserByEmail(email) {
  const [rows] = await connection.execute(
    'SELECT * FROM users WHERE email = ?',
    [email]
  );
  return rows[0];
}

// SAFE: Multiple parameters
async function getProducts(category, minPrice, maxPrice) {
  const [rows] = await connection.execute(
    'SELECT * FROM products WHERE category = ? AND price BETWEEN ? AND ?',
    [category, minPrice, maxPrice]
  );
  return rows;
}

Expected output:

The ? placeholders are replaced with escaped values. Input like "' OR 1=1 --" is treated as a literal string, not SQL.

Named Parameters with PostgreSQL

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

async function createUser(name, email, role) {
  const result = await pool.query(
    'INSERT INTO users (name, email, role) VALUES ($1, $2, $3) RETURNING id',
    [name, email, role]
  );
  return result.rows[0];
}

async function searchPosts(searchTerm, limit) {
  const result = await pool.query(
    'SELECT * FROM posts WHERE title ILIKE $1 OR content ILIKE $1 LIMIT $2',
    [`%${searchTerm}%`, limit]
  );
  return result.rows;
}

Expected output:

$1, $2, $3 are positional parameters. Values are automatically escaped. The query plan is cached for performance.

ORM Safety with Prisma

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

async function updateUserEmail(userId, newEmail) {
  // Prisma generates parameterized queries internally
  return prisma.user.update({
    where: { id: userId },
    data: { email: newEmail }
  });
}

// Raw queries with Prisma (use $queryRaw with template literal)
async function searchUsers(searchTerm) {
  return prisma.$queryRaw`
    SELECT * FROM users WHERE email LIKE ${'%' + searchTerm + '%'}
  `;
}

Expected output:

ORM-generated queries are parameterized by default. Prisma $queryRaw uses tagged template literals that prevent injection.

Common Mistakes

• Using string concatenation or template literals for SQL queries with user input.
• Believing that input validation or escaping is a substitute for parameterized queries.
• Using stored procedures that concatenate SQL strings internally.
• Logging SQL queries with user input, exposing sensitive data in logs.
• Using dynamic table or column names with user input (these cannot be parameterized; use a whitelist).

Practice Questions

1. How does a SQL injection attack work?
2. Why is input validation not sufficient to prevent SQL injection?
3. What is the difference between parameterized queries and prepared statements?
4. How do ORMs like Prisma or Sequelize prevent SQL injection?
5. What is second-order SQL injection?

Challenge

Audit a codebase with 10 SQL queries. Identify which are vulnerable to SQL injection. Fix each vulnerable query using parameterized queries. Test each fix with a sample injection payload like "' OR 1=1 --".

FAQ

Mini Project

Build a product search API with a SQLite database. Implement a vulnerable search endpoint (string concatenation) and a secure endpoint (parameterized queries). Write automated tests that attempt SQL injection payloads on both endpoints, verifying the vulnerable one returns all data and the secure one returns empty results.

What's Next

Continue to XSS Protection to learn about cross-site scripting prevention for APIs that render user content.