SQL Injection Prevention Techniques

SQL Injection Prevention Techniques

SQL injection remains one of the most dangerous and common API vulnerabilities. Attackers craft malicious input that, when incorporated into SQL queries, alters the query's logic to expose data, modify records, or execute administrative commands. Preventing SQL injection requires understanding attack vectors and implementing multiple defensive layers.

// Node.js SQL injection prevention examples
const mysql = require('mysql2/promise');
const { body, validationResult } = require('express-validator');

class DatabaseService {
    constructor() {
        this.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,
            queueLimit: 0
        });
    }
    
    // SECURE: Using parameterized queries
    async getUserById(userId) {
        const query = 'SELECT id, username, email FROM users WHERE id = ?';
        const [rows] = await this.pool.execute(query, [userId]);
        return rows[0];
    }
    
    // SECURE: Multiple parameters
    async authenticateUser(username, passwordHash) {
        const query = `
            SELECT id, username, role 
            FROM users 
            WHERE username = ? AND password_hash = ? AND active = true
        `;
        const [rows] = await this.pool.execute(query, [username, passwordHash]);
        return rows[0];
    }
    
    // SECURE: Dynamic query building with whitelisting
    async searchUsers(filters) {
        let query = 'SELECT id, username, email FROM users WHERE 1=1';
        const params = [];
        
        // Whitelist allowed filter fields
        const allowedFilters = ['username', 'email', 'role', 'created_after'];
        
        for (const [field, value] of Object.entries(filters)) {
            if (!allowedFilters.includes(field)) {
                continue; // Skip unknown fields
            }
            
            switch (field) {
                case 'username':
                case 'email':
                    query += ' AND ' + mysql.escapeId(field) + ' LIKE ?';
                    params.push('%' + value + '%');
                    break;
                case 'role':
                    query += ' AND role = ?';
                    params.push(value);
                    break;
                case 'created_after':
                    query += ' AND created_at > ?';
                    params.push(value);
                    break;
            }
        }
        
        query += ' ORDER BY created_at DESC LIMIT 100';
        
        const [rows] = await this.pool.execute(query, params);
        return rows;
    }
    
    // SECURE: Stored procedures for complex operations
    async transferFunds(fromAccount, toAccount, amount) {
        const connection = await this.pool.getConnection();
        
        try {
            await connection.beginTransaction();
            
            // Call stored procedure
            const [result] = await connection.execute(
                'CALL transfer_funds(?, ?, ?)',
                [fromAccount, toAccount, amount]
            );
            
            await connection.commit();
            return result[0];
            
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }
    
    // DANGEROUS: Example of what NOT to do
    async insecureSearch(searchTerm) {
        // NEVER DO THIS - Direct string concatenation
        const query = `SELECT * FROM users WHERE username LIKE '%${searchTerm}%'`;
        // This is vulnerable to SQL injection!
    }
}

// Express route with input validation
app.post('/api/users/search',
    [
        body('username').optional().isAlphanumeric().trim(),
        body('email').optional().isEmail().normalizeEmail(),
        body('role').optional().isIn(['user', 'admin', 'moderator']),
        body('created_after').optional().isISO8601()
    ],
    async (req, res) => {
        const errors = validationResult(req);
        if (!errors.isEmpty()) {
            return res.status(400).json({ errors: errors.array() });
        }
        
        try {
            const db = new DatabaseService();
            const users = await db.searchUsers(req.body);
            res.json(users);
        } catch (error) {
            console.error('Search error:', error);
            res.status(500).json({ error: 'Internal server error' });
        }
    }
);

Parameterized queries (prepared statements) provide the most effective defense against SQL injection. By separating SQL logic from data, parameterized queries ensure user input cannot alter query structure. Always use placeholders for any dynamic values, including those that seem safe. Even numeric inputs should use parameters to maintain consistent security practices.