SQL Injection: How It Happens and How to Prevent It

2026-02-03

TL;DR

SQL injection happens when user input goes directly into SQL queries. Never concatenate user input into SQL. Use parameterized queries, prepared statements, or ORMs. Validate input. Escape output. Test with SQLMap.

I got a 3am call. Our database had been wiped. A single input field with no validation let an attacker execute DROP TABLE users;. 150,000 user accounts gone. The backup was corrupted. We spent 72 hours recovering data from logs.

The vulnerability? One line of code: SELECT * FROM users WHERE id = ${req.params.id}. User input directly in SQL. The most preventable security vulnerability, yet it's still the #1 attack vector in 2026.

SQL injection has been on the OWASP Top 10 for 20+ years. It's completely preventable, yet I see vulnerable code in production constantly. Here's how SQL injection works, how to prevent it, and how to avoid the mistakes that get sites hacked.

What Is SQL Injection?

SQL injection is when an attacker inserts malicious SQL code into your queries by manipulating user input.

// Vulnerable code
app.get('/user', async (req, res) => {
    const userId = req.query.id;
    const query = `SELECT * FROM users WHERE id = ${userId}`;
    const user = await db.query(query);
    res.json(user);
});

// Normal request:
// GET /user?id=123
// Query: SELECT * FROM users WHERE id = 123

// Malicious request:
// GET /user?id=123 OR 1=1
// Query: SELECT * FROM users WHERE id = 123 OR 1=1
// Returns ALL users (1=1 is always true)

// Worse:
// GET /user?id=123; DROP TABLE users; --
// Query: SELECT * FROM users WHERE id = 123; DROP TABLE users; --
// Deletes the entire users table

The attacker controls part of your SQL query. They can read, modify, or delete any data in your database.

How SQL Injection Attacks Work

1. Authentication Bypass

// Login form vulnerable to SQL injection
app.post('/login', async (req, res) => {
    const { username, password } = req.body;

    const query = `
        SELECT * FROM users
        WHERE username = '${username}'
        AND password = '${password}'
    `;

    const user = await db.query(query);

    if (user) {
        res.json({ success: true });
    }
});

// Normal login:
// username: john
// password: secret123
// Query: SELECT * FROM users WHERE username = 'john' AND password = 'secret123'

// SQL Injection attack:
// username: admin'--
// password: anything
// Query: SELECT * FROM users WHERE username = 'admin'--' AND password = 'anything'
//        The -- comments out the rest, bypassing password check!

The attacker logs in as admin without knowing the password.

2. Data Exfiltration

// Search feature vulnerable to SQL injection
app.get('/search', async (req, res) => {
    const searchTerm = req.query.q;
    const query = `SELECT title, content FROM posts WHERE title LIKE '%${searchTerm}%'`;
    const results = await db.query(query);
    res.json(results);
});

// Attack using UNION to extract other tables:
// GET /search?q=test' UNION SELECT username, password FROM users--

// Query becomes:
// SELECT title, content FROM posts WHERE title LIKE '%test'
// UNION SELECT username, password FROM users--%'

// Returns post titles AND all usernames/passwords!

3. Database Destruction

// Vulnerable delete endpoint
app.delete('/post/:id', async (req, res) => {
    const postId = req.params.id;
    const query = `DELETE FROM posts WHERE id = ${postId}`;
    await db.query(query);
    res.json({ success: true });
});

// Attack:
// DELETE /post/1; DROP TABLE posts; DROP TABLE users; --

// Executes:
// DELETE FROM posts WHERE id = 1; DROP TABLE posts; DROP TABLE users; --
// Deletes multiple tables

4. Blind SQL Injection

When the app doesn't show query results but behaves differently on error:

// Vulnerable but doesn't show data
app.get('/check-username', async (req, res) => {
    const username = req.query.username;
    const query = `SELECT * FROM users WHERE username = '${username}'`;

    try {
        const user = await db.query(query);
        // Only returns true/false, not the data
        res.json({ exists: user.length > 0 });
    } catch (err) {
        res.json({ error: true });
    }
});

// Attacker can extract data bit by bit:
// /check-username?username=admin' AND SUBSTRING(password,1,1)='a'--
// Returns true if admin password starts with 'a'
// Repeat for each character to extract full password

Prevention: Parameterized Queries

The #1 defense: never concatenate user input into SQL.

Node.js with PostgreSQL (pg)

// BAD - Vulnerable
const userId = req.query.id;
const query = `SELECT * FROM users WHERE id = ${userId}`;
const result = await db.query(query);

// GOOD - Parameterized query
const userId = req.query.id;
const query = 'SELECT * FROM users WHERE id = $1';
const result = await db.query(query, [userId]);

// User input is escaped automatically
// Attack attempt: id = "1; DROP TABLE users"
// Becomes: SELECT * FROM users WHERE id = '1; DROP TABLE users'
// Treated as string, not SQL code

Node.js with MySQL (mysql2)

// BAD
const query = `SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`;

// GOOD - Placeholders
const query = 'SELECT * FROM users WHERE username = ? AND password = ?';
const [rows] = await db.query(query, [username, password]);

// BETTER - Named placeholders
const query = 'SELECT * FROM users WHERE username = :username AND password = :password';
const [rows] = await db.query(query, { username, password });

Python with psycopg2

# BAD - Vulnerable
user_id = request.args.get('id')
query = f"SELECT * FROM users WHERE id = {user_id}"
cursor.execute(query)

# GOOD - Parameterized
user_id = request.args.get('id')
query = "SELECT * FROM users WHERE id = %s"
cursor.execute(query, (user_id,))

Python with SQLAlchemy

from sqlalchemy import text

# BAD
user_id = request.args.get('id')
query = f"SELECT * FROM users WHERE id = {user_id}"
result = db.session.execute(query)

# GOOD
user_id = request.args.get('id')
query = text("SELECT * FROM users WHERE id = :id")
result = db.session.execute(query, {"id": user_id})

# BETTER - Use ORM
user = User.query.filter_by(id=user_id).first()

PHP with PDO

// BAD - Vulnerable
$userId = $_GET['id'];
$query = "SELECT * FROM users WHERE id = $userId";
$result = $db->query($query);

// GOOD - Prepared statements
$userId = $_GET['id'];
$stmt = $db->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$userId]);
$result = $stmt->fetch();

// GOOD - Named parameters
$stmt = $db->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => $userId]);

Go with database/sql

// BAD - Vulnerable
userID := r.URL.Query().Get("id")
query := fmt.Sprintf("SELECT * FROM users WHERE id = %s", userID)
rows, err := db.Query(query)

// GOOD - Parameterized
userID := r.URL.Query().Get("id")
query := "SELECT * FROM users WHERE id = $1"
rows, err := db.Query(query, userID)

Ruby on Rails

# BAD - Vulnerable
user_id = params[:id]
user = User.where("id = #{user_id}").first

# GOOD - Parameterized
user_id = params[:id]
user = User.where("id = ?", user_id).first

# BETTER - Use ActiveRecord methods
user = User.find(params[:id])

Using ORMs Safely

ORMs usually protect against SQL injection, but you can still mess up:

Sequelize (Node.js)

// SAFE - ORM methods
const user = await User.findOne({
    where: { id: userId }
});

// SAFE - Parameterized raw query
const users = await sequelize.query(
    'SELECT * FROM users WHERE id = ?',
    {
        replacements: [userId],
        type: QueryTypes.SELECT
    }
);

// DANGEROUS - Raw SQL with string interpolation
const users = await sequelize.query(
    `SELECT * FROM users WHERE id = ${userId}` // DON'T DO THIS
);

// DANGEROUS - Raw where conditions
const users = await User.findAll({
    where: sequelize.literal(`id = ${userId}`) // VULNERABLE!
});

Django ORM

# SAFE - ORM methods
user = User.objects.get(id=user_id)

# SAFE - Parameterized raw query
users = User.objects.raw(
    'SELECT * FROM users WHERE id = %s',
    [user_id]
)

# DANGEROUS - String formatting
users = User.objects.raw(
    f'SELECT * FROM users WHERE id = {user_id}' # DON'T
)

# DANGEROUS - extra() with user input
users = User.objects.extra(
    where=[f'id = {user_id}'] # VULNERABLE!
)

Prisma (Node.js)

// SAFE - Prisma is safe by default
const user = await prisma.user.findUnique({
    where: { id: userId }
});

// SAFE - Raw query with parameters
const users = await prisma.$queryRaw`
    SELECT * FROM users WHERE id = ${userId}
`;

// DANGEROUS - String interpolation in raw query
const query = `SELECT * FROM users WHERE id = ${userId}`;
const users = await prisma.$queryRawUnsafe(query); // AVOID

Input Validation (Defense in Depth)

Parameterized queries are the primary defense. Input validation is a backup layer:

// Validate input types
function validateUserId(id) {
    // Expect integer
    const userId = parseInt(id, 10);

    if (isNaN(userId) || userId < 1) {
        throw new Error('Invalid user ID');
    }

    return userId;
}

app.get('/user', async (req, res) => {
    try {
        const userId = validateUserId(req.query.id);
        const user = await db.query(
            'SELECT * FROM users WHERE id = $1',
            [userId]
        );
        res.json(user);
    } catch (err) {
        res.status(400).json({ error: err.message });
    }
});

Whitelist Validation

// For dynamic table/column names (can't parameterize these)
const ALLOWED_SORT_COLUMNS = ['created_at', 'username', 'email'];

function validateSortColumn(column) {
    if (!ALLOWED_SORT_COLUMNS.includes(column)) {
        throw new Error('Invalid sort column');
    }
    return column;
}

app.get('/users', async (req, res) => {
    const sortBy = validateSortColumn(req.query.sort || 'created_at');

    // sortBy is from whitelist, safe to interpolate
    const query = `SELECT * FROM users ORDER BY ${sortBy}`;
    const users = await db.query(query);
    res.json(users);
});

Regex Validation

// Validate format (additional safety layer)
function validateUsername(username) {
    // Only allow alphanumeric and underscores
    if (!/^[a-zA-Z0-9_]{3,20}$/.test(username)) {
        throw new Error('Invalid username format');
    }
    return username;
}

app.post('/register', async (req, res) => {
    const username = validateUsername(req.body.username);

    // Still use parameterized query
    await db.query(
        'INSERT INTO users (username) VALUES ($1)',
        [username]
    );
});

Escaping (Last Resort)

If you absolutely must build dynamic SQL (avoid this), escape properly:

const mysql = require('mysql2');

// Escape individual values
const username = mysql.escape(userInput);
const query = `SELECT * FROM users WHERE username = ${username}`;

// Escape identifiers (table/column names)
const tableName = mysql.escapeId(userInput);
const query = `SELECT * FROM ${tableName}`;

// Better: Use prepared statements instead

Common Mistakes

Mistake 1: Parameterizing Only Some Inputs

// BAD - Only parameterizing one input
const query = `
    SELECT * FROM users
    WHERE id = $1
    AND role = '${role}'
`;
await db.query(query, [userId]);
// 'role' is still vulnerable!

// GOOD - Parameterize everything
const query = `
    SELECT * FROM users
    WHERE id = $1
    AND role = $2
`;
await db.query(query, [userId, role]);

Mistake 2: Client-Side Validation Only

// BAD - Only validating on frontend
// Frontend:
if (!/^\d+$/.test(userId)) {
    alert('Invalid ID');
    return;
}
fetch(`/api/user?id=${userId}`);

// Backend has no validation!
// Attacker bypasses frontend, sends malicious request directly

Always validate on the server.

Mistake 3: Concatenating Parameterized Queries

// BAD - Building SQL with string concatenation
let query = 'SELECT * FROM users WHERE 1=1';

if (username) {
    query += ` AND username = '${username}'`; // VULNERABLE
}

if (email) {
    query += ` AND email = '${email}'`; // VULNERABLE
}

// GOOD - Building with parameters
const conditions = [];
const params = [];

if (username) {
    conditions.push('username = $' + (params.length + 1));
    params.push(username);
}

if (email) {
    conditions.push('email = $' + (params.length + 1));
    params.push(email);
}

const query = 'SELECT * FROM users WHERE ' + conditions.join(' AND ');
await db.query(query, params);

Mistake 4: Trusting "Internal" Data

// BAD - Assuming data from database is safe
const user = await db.query('SELECT username FROM users WHERE id = $1', [userId]);

// Using database value unsafely in another query
const logs = await db.query(
    `SELECT * FROM logs WHERE username = '${user.username}'` // VULNERABLE
);
// What if username was 'admin'; DROP TABLE logs--' ?

// GOOD - Parameterize everything, even database values
const logs = await db.query(
    'SELECT * FROM logs WHERE username = $1',
    [user.username]
);

Mistake 5: Stored Procedures Aren't Automatically Safe

-- VULNERABLE stored procedure
CREATE PROCEDURE get_user(IN user_id VARCHAR(50))
BEGIN
    SET @query = CONCAT('SELECT * FROM users WHERE id = ', user_id);
    PREPARE stmt FROM @query;
    EXECUTE stmt;
END;

-- SAFE stored procedure
CREATE PROCEDURE get_user(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END;

Second-Order SQL Injection

Attack payload is stored, then executed later:

// Step 1: Attacker registers with malicious username
app.post('/register', async (req, res) => {
    const username = req.body.username;
    // Safely stored with parameterized query
    await db.query(
        'INSERT INTO users (username) VALUES ($1)',
        ["admin'--"]
    );
    // Username "admin'--" is stored in database
});

// Step 2: Admin views user profile
app.get('/admin/user/:id', async (req, res) => {
    const user = await db.query(
        'SELECT * FROM users WHERE id = $1',
        [req.params.id]
    );

    // VULNERABLE - Using stored username unsafely
    const logs = await db.query(
        `SELECT * FROM logs WHERE username = '${user.username}'`
    );
    // Query: SELECT * FROM logs WHERE username = 'admin'--'
    // SQL injection triggered!
});

Prevention: Parameterize everything, even data from your own database.

Testing for SQL Injection

Manual Testing

# Test single quote
curl "https://api.example.com/user?id=1'"

# Test comment injection
curl "https://api.example.com/user?id=1--"

# Test UNION
curl "https://api.example.com/user?id=1 UNION SELECT null,null--"

# Test boolean injection
curl "https://api.example.com/user?id=1 AND 1=1"
curl "https://api.example.com/user?id=1 AND 1=2"
# Different responses = vulnerable

# Test time-based blind injection
curl "https://api.example.com/user?id=1 AND SLEEP(5)--"
# If response takes 5 seconds = vulnerable

SQLMap (Automated Testing)

# Install
pip install sqlmap

# Test a URL
sqlmap -u "https://api.example.com/user?id=1"

# Test POST request
sqlmap -u "https://api.example.com/login" \
       --data="username=admin&password=pass" \
       --method=POST

# Test with cookies
sqlmap -u "https://api.example.com/profile" \
       --cookie="session=abc123"

# Extract database
sqlmap -u "https://api.example.com/user?id=1" \
       --dbs  # List databases
       --tables  # List tables
       --dump  # Dump data

Automated Security Testing

// Jest test
describe('SQL Injection Protection', () => {
    const injectionPayloads = [
        "1' OR '1'='1",
        "1; DROP TABLE users--",
        "1' UNION SELECT null,null--",
        "1' AND SLEEP(5)--"
    ];

    injectionPayloads.forEach(payload => {
        it(`should reject SQL injection: ${payload}`, async () => {
            const response = await request(app)
                .get(`/user?id=${encodeURIComponent(payload)}`);

            // Should not return unexpected data
            expect(response.status).toBe(400);

            // Should not return multiple users
            if (response.status === 200) {
                expect(response.body).not.toBeInstanceOf(Array);
            }
        });
    });
});

Database Permissions (Defense in Depth)

Limit what the application database user can do:

-- DON'T use root/admin for application
-- Create limited user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';

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

-- NO DROP, CREATE, or admin privileges
-- Even if SQL injection succeeds, can't drop tables

// Application connects with limited user
const db = new Pool({
    user: 'app_user',  // Not 'root'!
    password: process.env.DB_PASSWORD,
    database: 'myapp',
    host: 'localhost'
});

Web Application Firewall (WAF)

Additional layer of protection:

// Cloudflare WAF rule
// Blocks requests with SQL keywords in parameters

// AWS WAF rule
{
    "Name": "SQLInjectionRule",
    "Priority": 1,
    "Statement": {
        "SqliMatchStatement": {
            "FieldToMatch": {
                "AllQueryArguments": {}
            }
        }
    },
    "Action": {
        "Block": {}
    }
}

WAF is not a replacement for secure coding, but adds defense.

Real-World Impact

SQL injection is serious:

Data breaches:

Equifax (2017): 147 million records exposed
Heartland Payment Systems (2008): 130 million credit cards
TalkTalk (2015): 157,000 customers affected

Cost:

Average data breach: $4.45 million (IBM 2023)
Legal fees, fines, customer notification
Brand reputation damage
Lost business

Common targets:

Authentication systems
Search features
Filter/sort parameters
API endpoints with IDs
Admin panels

Security Checklist

- [ ] Use parameterized queries for all SQL
- [ ] Never concatenate user input into SQL
- [ ] Use ORM methods when possible
- [ ] Validate and sanitize all input (defense in depth)
- [ ] Use whitelist validation for table/column names
- [ ] Limit database user permissions
- [ ] Enable WAF rules for SQL injection
- [ ] Test with SQLMap before deploying
- [ ] Review code for .raw() or .execute() calls
- [ ] Parameterize even data from your own database
- [ ] Use prepared statements in stored procedures
- [ ] Log and monitor for SQL injection attempts

Code Review Checklist

Look for these patterns in pull requests:

// RED FLAGS:
- String concatenation: `... WHERE id = ${id}`
- String interpolation: `... WHERE id = '${id}'`
- Template literals with user input: `SELECT * FROM ${table}`
- .raw() or .execute() with unparameterized SQL
- Dynamic query building without parameters

// SAFE:
- Parameterized queries: db.query('SELECT * WHERE id = $1', [id])
- ORM methods: User.findById(id)
- Whitelist validation for identifiers
- Prepared statements

The Bottom Line

SQL injection has been a known vulnerability for 25+ years. It's completely preventable. Yet it's still the #1 attack vector.

Never concatenate user input into SQL. Use parameterized queries, prepared statements, or ORM methods.

Validate all input as a backup layer. Whitelist allowed values. Reject anything suspicious.

Test your application with SQLMap and security scanners before deploying.

Limit database permissions so even a successful injection can't drop tables.

We lost 150,000 user accounts because of one line of vulnerable code. The recovery cost us over $100,000 in engineering time, legal fees, and lost business.

Review your codebase today. Search for string concatenation in SQL queries. Fix every instance. The attack is coming - make sure you're protected.