SQL Injection: The Complete Guide to Prevention and Mitigation

SQL Injection remains one of the most dangerous and widespread vulnerabilities in modern web applications. Consistently ranked in the OWASP Top 10, this vulnerability can lead to devastating consequences: theft of sensitive data, unauthorized modification of the database, and in the most severe cases, total system compromise.

In this in-depth guide, we’ll explore what SQL Injection is, how it works, and above all how to effectively protect your applications with modern techniques and proven best practices.

What Is SQL Injection?

SQL Injection (SQLi) is an attack technique that exploits poor handling of user input within SQL queries. An attacker inserts malicious SQL code into input fields, manipulating the logic of the original query to:

• Extract sensitive data from the database
• Modify or delete data
• Bypass authentication mechanisms
• Execute administrative commands on the database
• In some cases, execute commands on the operating system

Basic Attack Example

Consider a simple login form with this vulnerable query:

SELECT * FROM users 
WHERE username = '$username' AND password = '$password'

If an attacker enters the following as the username:

admin' OR '1'='1

The query becomes:

SELECT * FROM users 
WHERE username = 'admin' OR '1'='1' AND password = ''

Since '1'='1' is always true, the attacker bypasses authentication without knowing the password.

Types of SQL Injection

1. In-Band SQLi (Classic)

The result of the attack is displayed directly in the same HTTP response.

Error-Based SQLi

Exploits database error messages to extract information:

' UNION SELECT NULL, table_name FROM information_schema.tables--

Union-Based SQLi

Uses the UNION operator to combine results from multiple queries:

' UNION SELECT username, password FROM admin_users--

2. Blind SQLi

Doesn’t show direct results — the attacker infers information from the application’s behavior.

Boolean-Based

' AND 1=1-- (normal response)
' AND 1=2-- (different response)

Time-Based

' AND SLEEP(5)-- (a 5-second delay indicates a vulnerability)

3. Out-of-Band SQLi

Uses alternative channels (DNS, HTTP) to extract data when other methods fail.

Prevention: Fundamental Techniques

1. Prepared Statements / Parameterized Queries

The most effective defense against SQL Injection is the use of prepared statements.

Node.js with PostgreSQL (pg)

const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL
});

// ❌ VULNERABLE - never do this
async function loginVulnerable(username, password) {
  const query = `SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`;
  const result = await pool.query(query);
  return result.rows[0];
}

// ✅ SAFE - prepared statement
async function loginSecure(username, password) {
  const query = 'SELECT * FROM users WHERE username = $1 AND password = $2';
  const result = await pool.query(query, [username, password]);
  return result.rows[0];
}

// Usage
const user = await loginSecure(req.body.username, req.body.password);

Node.js with MySQL (mysql2)

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

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10
});

// ✅ Prepared statement with placeholder
async function getUser(userId) {
  const [rows] = await pool.execute(
    'SELECT id, username, email FROM users WHERE id = ?',
    [userId]
  );
  return rows[0];
}

// ✅ Complex query with multiple parameters
async function searchProducts(category, minPrice, maxPrice) {
  const [rows] = await pool.execute(
    `SELECT * FROM products 
     WHERE category = ? AND price BETWEEN ? AND ? 
     ORDER BY price ASC`,
    [category, minPrice, maxPrice]
  );
  return rows;
}

Python with SQLite

import sqlite3

# ❌ VULNERABLE
def get_user_vulnerable(username):
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()
    query = f"SELECT * FROM users WHERE username = '{username}'"
    cursor.execute(query)
    return cursor.fetchone()

# ✅ SAFE
def get_user_secure(username):
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()
    query = "SELECT * FROM users WHERE username = ?"
    cursor.execute(query, (username,))
    return cursor.fetchone()

PHP with PDO

<?php
// ✅ Prepared statement with PDO
function getUser($pdo, $userId) {
    $stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
    $stmt->bindParam(':id', $userId, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Usage
$pdo = new PDO('mysql:host=localhost;dbname=myapp', $user, $pass);
$user = getUser($pdo, $_GET['id']);
?>

2. ORM (Object-Relational Mapping)

Modern ORMs provide built-in protection against SQL Injection.

Sequelize (Node.js)

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize(process.env.DATABASE_URL);

// Model definition
const User = sequelize.define('User', {
  username: DataTypes.STRING,
  email: DataTypes.STRING,
  password: DataTypes.STRING
});

// ✅ Automatically safe queries
async function findUser(username) {
  return await User.findOne({
    where: { username: username }
  });
}

// ✅ Complex queries with operators
const { Op } = require('sequelize');

async function searchUsers(searchTerm) {
  return await User.findAll({
    where: {
      [Op.or]: [
        { username: { [Op.like]: `%${searchTerm}%` } },
        { email: { [Op.like]: `%${searchTerm}%` } }
      ]
    }
  });
}

Prisma (TypeScript/Node.js)

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

// ✅ Type-safe and protected against SQLi
async function getUser(email: string) {
  return await prisma.user.findUnique({
    where: { email }
  });
}

// ✅ Safe relational queries
async function getUserWithPosts(userId: number) {
  return await prisma.user.findUnique({
    where: { id: userId },
    include: {
      posts: {
        where: { published: true },
        orderBy: { createdAt: 'desc' }
      }
    }
  });
}

TypeORM (TypeScript)

import { DataSource } from 'typeorm';
import { User } from './entities/User';

const AppDataSource = new DataSource({
  type: 'postgres',
  host: 'localhost',
  port: 5432,
  username: 'test',
  password: 'test',
  database: 'test'
});

// ✅ Safe repository pattern
async function findUserByEmail(email: string) {
  const userRepository = AppDataSource.getRepository(User);
  return await userRepository.findOne({
    where: { email }
  });
}

// ✅ Parameterized query builder
async function searchProducts(keyword: string, minPrice: number) {
  return await AppDataSource
    .getRepository(Product)
    .createQueryBuilder('product')
    .where('product.name LIKE :keyword', { keyword: `%${keyword}%` })
    .andWhere('product.price >= :minPrice', { minPrice })
    .getMany();
}

3. Input Validation and Sanitization

In addition to prepared statements, always validate inputs:

const validator = require('validator');

function validateUserId(userId) {
  // Verify that it's an integer
  if (!validator.isInt(userId.toString(), { min: 1 })) {
    throw new Error('Invalid user ID');
  }
  return parseInt(userId, 10);
}

function validateEmail(email) {
  if (!validator.isEmail(email)) {
    throw new Error('Invalid email format');
  }
  return validator.normalizeEmail(email);
}

function validateSearchTerm(term) {
  // Limit length
  if (term.length > 100) {
    throw new Error('Search term too long');
  }
  // Remove dangerous characters
  return validator.escape(term);
}

// Usage in an Express route
app.get('/user/:id', async (req, res) => {
  try {
    const userId = validateUserId(req.params.id);
    const user = await getUser(userId);
    res.json(user);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

4. Principle of Least Privilege

Configure the database with minimal permissions:

-- Create a dedicated user for the application
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant only the necessary permissions
GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'app_user'@'localhost';
GRANT SELECT ON myapp.products TO 'app_user'@'localhost';

-- Do NOT grant:
-- DROP, CREATE, ALTER, GRANT, FILE, PROCESS, SUPER

5. Stored Procedures (with caution)

Stored procedures can help, but only if parameterized:

-- ✅ Safe stored procedure
DELIMITER //

CREATE PROCEDURE GetUserByEmail(IN userEmail VARCHAR(255))
BEGIN
    SELECT id, username, email, created_at
    FROM users
    WHERE email = userEmail;
END //

DELIMITER ;

// Call from the app
async function getUserByEmail(email) {
  const [rows] = await pool.execute('CALL GetUserByEmail(?)', [email]);
  return rows[0];
}

Advanced Best Practices

1. WAF (Web Application Firewall)

Implement a WAF to filter suspicious requests:

// Express middleware for SQLi pattern detection
const sqlInjectionPatterns = [
  /(\b(SELECT|UNION|INSERT|UPDATE|DELETE|DROP|CREATE|ALTER|EXEC)\b)/i,
  /(--|;|\/\*|\*\/|xp_|sp_)/i,
  /('|(\\')|(;)|(--)|(\s+OR\s+))/i
];

function sqlInjectionDetector(req, res, next) {
  const checkInput = (input) => {
    if (typeof input === 'string') {
      return sqlInjectionPatterns.some(pattern => pattern.test(input));
    }
    if (typeof input === 'object') {
      return Object.values(input).some(checkInput);
    }
    return false;
  };

  if (checkInput(req.query) || checkInput(req.body) || checkInput(req.params)) {
    return res.status(400).json({ error: 'Potential SQL injection detected' });
  }

  next();
}

app.use(sqlInjectionDetector);

2. Secure Error Handling

Don’t expose database details:

app.use((err, req, res, next) => {
  // Detailed logging server-side only
  console.error('Database error:', err);

  // Generic response to the client
  res.status(500).json({
    error: 'An error occurred processing your request',
    requestId: req.id // for debugging
  });
});

3. Monitoring and Alerting

const winston = require('winston');

const logger = winston.createLogger({
  level: 'info',
  format: winston.format.json(),
  transports: [
    new winston.transports.File({ filename: 'security.log' })
  ]
});

function logSuspiciousQuery(query, params, source) {
  logger.warn('Suspicious query detected', {
    query,
    params,
    source,
    timestamp: new Date().toISOString(),
    ip: source.ip,
    userId: source.userId
  });
}

Testing for SQL Injection

Manual Testing

Try these inputs in your forms:

' OR '1'='1
admin'--
' UNION SELECT NULL--
1' ORDER BY 10--
' AND SLEEP(5)--

Automated Testing with SQLMap

# Install sqlmap
git clone --depth 1 https://github.com/sqlmapproject/sqlmap.git

# Test endpoint
python sqlmap.py -u "http://localhost:3000/api/user?id=1" --batch

# Test with authentication cookie
python sqlmap.py -u "http://localhost:3000/api/products" \
  --cookie="session=abc123" \
  --data="category=electronics" \
  --batch

Unit Testing

const request = require('supertest');
const app = require('./app');

describe('SQL Injection Protection', () => {
  test('should reject malicious input in login', async () => {
    const maliciousInputs = [
      "' OR '1'='1",
      "admin'--",
      "' UNION SELECT NULL--"
    ];

    for (const input of maliciousInputs) {
      const response = await request(app)
        .post('/api/login')
        .send({ username: input, password: 'test' });

      expect(response.status).toBe(400);
    }
  });

  test('should handle prepared statements correctly', async () => {
    const response = await request(app)
      .get('/api/user/1');

    expect(response.status).toBe(200);
    expect(response.body).toHaveProperty('id');
  });
});

Security Checklist

✅ Never concatenate user input directly into SQL queries
✅ Always use prepared statements or an ORM
✅ Validate and sanitize all inputs
✅ Implement the principle of least privilege for database users
✅ Don’t expose detailed database error messages
✅ Use HTTPS to protect data in transit
✅ Implement rate limiting to prevent automated attacks
✅ Monitor and log suspicious queries
✅ Test regularly with automated tools
✅ Keep your database, framework, and dependencies up to date
✅ Implement a WAF where possible
✅ Conduct periodic security audits

Additional Resources

• OWASP SQL Injection Prevention Cheat Sheet
• CWE-89: SQL Injection
• PortSwigger SQL Injection Guide

Conclusion

SQL Injection is a serious vulnerability, but it’s entirely preventable with the right techniques. The golden rules are:

1. Always use prepared statements or an ORM
2. Never concatenate user input into queries
3. Validate all inputs
4. Test your applications regularly

By implementing these best practices, you’ll effectively protect your applications and your users’ data from one of the most dangerous attacks in the web landscape.

---

Have questions about web application security? Leave a comment or reach out to us on Twitter @CodeFortress