Prepared Statements - Week 12 Example

SQL injection is a code injection technique where an attacker inserts malicious SQL code through user input. It's one of the most dangerous and common web vulnerabilities.

VULNERABLE CODE - Never Do This!

// DANGEROUS: User input directly concatenated into SQL
const username = req.body.username;
const password = req.body.password;

// This is VULNERABLE to SQL injection!
const query = `SELECT * FROM users
    WHERE username = '${username}'
    AND password = '${password}'`;

connection.query(query, (err, results) => {
    if (results.length > 0) {
        console.log('Login successful');
    }
});

How an Attacker Exploits This:

Normal input:

username: alice

password: secret123

Generated query: SELECT * FROM users WHERE username = 'alice' AND password = 'secret123'

Malicious input:

username: admin' --

password: anything

Generated query: SELECT * FROM users WHERE username = 'admin' -- ' AND password = 'anything'

The -- comments out the password check! Attacker logs in as admin!

More SQL Injection Examples

Attack Type: Data Theft

// Vulnerable search function
const searchTerm = req.query.search;
const query = `SELECT * FROM products WHERE name LIKE '%${searchTerm}%'`;

// Attacker input: ' UNION SELECT username, password, null FROM users --
// Result: Returns all usernames and passwords from users table!

Attack Type: Data Deletion

// Vulnerable delete function
const id = req.params.id;
const query = `DELETE FROM orders WHERE id = ${id}`;

// Attacker input: 1; DROP TABLE orders; --
// Result: Deletes the entire orders table!

Attack Type: Bypassing Authentication

// Vulnerable login check
const query = `SELECT * FROM users WHERE username = '${user}' AND password = '${pass}'`;

// Attacker input for username: ' OR '1'='1
// Attacker input for password: ' OR '1'='1
// Result: SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1'
// This returns ALL users, bypassing authentication!

The Solution: Prepared Statements

Prepared statements (also called parameterized queries) separate SQL code from data, making injection impossible.

SAFE CODE - Always Use This!

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

// Method 1: Using ? placeholders (positional)
async function safeLogin(username, password) {
    const [rows] = await pool.execute(
        'SELECT * FROM users WHERE username = ? AND password = ?',
        [username, password]  // Parameters are safely escaped
    );
    return rows[0] || null;
}

// Method 2: Using named placeholders
async function safeLoginNamed(username, password) {
    const [rows] = await pool.execute(
        'SELECT * FROM users WHERE username = :user AND password = :pass',
        { user: username, pass: password }
    );
    return rows[0] || null;
}

// Even if attacker enters: admin' --
// The query becomes: SELECT * FROM users WHERE username = 'admin\' --' AND password = '...'
// The ' is escaped, making it a harmless string!

Comparison: Vulnerable vs Safe

Vulnerable (String Concatenation)

// DON'T DO THIS
const query = `SELECT * FROM products
WHERE category = '${category}'
AND price < ${maxPrice}`;

connection.query(query);

Safe (Prepared Statement)

// DO THIS
const query = `SELECT * FROM products
WHERE category = ?
AND price < ?`;

pool.execute(query, [category, maxPrice]);

Practical Examples

Safe INSERT Operation

async function createUser(userData) {
    const { username, email, password } = userData;

    // All user inputs are parameterized
    const [result] = await pool.execute(
        `INSERT INTO users (username, email, password, created_at)
         VALUES (?, ?, ?, NOW())`,
        [username, email, password]
    );

    return result.insertId;
}

// Even malicious input is treated as data, not SQL code
createUser({
    username: "hacker'; DROP TABLE users; --",
    email: "test@test.com",
    password: "password"
});
// This safely inserts the literal string "hacker'; DROP TABLE users; --" as username

Safe UPDATE Operation

async function updateUserProfile(userId, profileData) {
    const { name, bio, website } = profileData;

    const [result] = await pool.execute(
        `UPDATE users
         SET name = ?, bio = ?, website = ?
         WHERE id = ?`,
        [name, bio, website, userId]
    );

    return result.affectedRows > 0;
}

Safe DELETE Operation

async function deleteComment(commentId, userId) {
    // Only delete if the comment belongs to the user
    const [result] = await pool.execute(
        `DELETE FROM comments
         WHERE id = ? AND user_id = ?`,
        [commentId, userId]
    );

    return result.affectedRows > 0;
}

Safe Search with LIKE

async function searchProducts(searchTerm) {
    // The % wildcards are part of the parameter, not the query
    const [rows] = await pool.execute(
        `SELECT * FROM products
         WHERE name LIKE ?
         OR description LIKE ?
         ORDER BY name`,
        [`%${searchTerm}%`, `%${searchTerm}%`]
    );

    return rows;
}

// Safe even with malicious input
searchProducts("phone' UNION SELECT * FROM users --");
// Searches for literal string "phone' UNION SELECT * FROM users --"

Safe IN Clause

async function getProductsByIds(ids) {
    if (!Array.isArray(ids) || ids.length === 0) {
        return [];
    }

    // Create placeholders dynamically
    const placeholders = ids.map(() => '?').join(', ');

    const [rows] = await pool.execute(
        `SELECT * FROM products WHERE id IN (${placeholders})`,
        ids
    );

    return rows;
}

// Usage
getProductsByIds([1, 2, 3, 4, 5]);

Safe Dynamic Column Selection

async function getUsers(sortColumn = 'name', sortOrder = 'ASC') {
    // Whitelist allowed columns to prevent injection
    const allowedColumns = ['name', 'email', 'created_at', 'id'];
    const allowedOrders = ['ASC', 'DESC'];

    // Validate inputs against whitelist
    if (!allowedColumns.includes(sortColumn)) {
        sortColumn = 'name';
    }
    if (!allowedOrders.includes(sortOrder.toUpperCase())) {
        sortOrder = 'ASC';
    }

    // Column names can't be parameterized, so we use whitelisting
    const [rows] = await pool.execute(
        `SELECT id, name, email FROM users ORDER BY ${sortColumn} ${sortOrder}`
    );

    return rows;
}

Additional Security Best Practices

1. Input Validation

function validateEmail(email) {
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    return emailRegex.test(email);
}

function validateAge(age) {
    const num = parseInt(age);
    return !isNaN(num) && num >= 0 && num <= 150;
}

async function createUser(data) {
    // Validate before database operation
    if (!validateEmail(data.email)) {
        throw new Error('Invalid email format');
    }
    if (!validateAge(data.age)) {
        throw new Error('Invalid age');
    }

    // Now proceed with parameterized query
    const [result] = await pool.execute(
        'INSERT INTO users (email, age) VALUES (?, ?)',
        [data.email, data.age]
    );
    return result.insertId;
}

2. Least Privilege Principle

-- Create a database user with limited permissions
-- Don't use root for your application!

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Only grant necessary permissions
GRANT SELECT, INSERT, UPDATE ON school.students TO 'app_user'@'localhost';
GRANT SELECT ON school.courses TO 'app_user'@'localhost';

-- No DELETE permission - prevents accidental/malicious deletions
-- No DROP permission - prevents table destruction

3. Error Handling (Don't Expose Details)

async function safeQuery(query, params) {
    try {
        const [rows] = await pool.execute(query, params);
        return { success: true, data: rows };
    } catch (error) {
        // Log detailed error for debugging
        console.error('Database error:', error);

        // Return generic message to user (don't expose SQL details!)
        return {
            success: false,
            error: 'An error occurred. Please try again.'
        };
    }
}

Practice	Status
Use prepared statements with ? placeholders	Always Do
Concatenate user input into SQL strings	Never Do
Validate and sanitize all user inputs	Always Do
Use least privilege database accounts	Always Do
Expose detailed error messages to users	Never Do
Whitelist allowed values for dynamic SQL parts	Always Do

Prepared Statements & SQL Injection Prevention

What is SQL Injection?