Understanding SQL Injections: A Developer's Security Guide

Vulnerable Search Function Example

// A typical search endpoint in an Express.js application
app.get('/search', (req, res) => {
    const bookTitle = req.query.title;
    
    // ⚠️ VULNERABLE CODE - DO NOT USE IN PRODUCTION ⚠️
    const query = `
        SELECT * FROM books 
        WHERE title = '${bookTitle}'
    `;
    
    // If a user searches for: Harry Potter
    // The query becomes:
    // SELECT * FROM books WHERE title = 'Harry Potter'
    
    // But if a user inputs: ' OR '1'='1
    // The query becomes:
    // SELECT * FROM books WHERE title = '' OR '1'='1'
    // This will return ALL books because '1'='1' is always true!
});
                

In this example, the attacker has turned our specific search into a query that always returns true, effectively bypassing our search restrictions. It's like asking to see one specific book but tricking the system into showing all books instead.

Union-Based Injection

// Consider this vulnerable login system
app.post('/login', (req, res) => {
    const username = req.body.username;
    const password = req.body.password;
    
    // ⚠️ VULNERABLE CODE - DO NOT USE IN PRODUCTION ⚠️
    const query = `
        SELECT * FROM users 
        WHERE username = '${username}' 
        AND password = '${password}'
    `;
    
    // Normal usage:
    // username: "john_doe"
    // password: "secretpass"
    
    // Malicious usage:
    // username: "anything' UNION SELECT * FROM credit_cards; --"
    // This would attach credit card data to the result!
});
                

Time-Based Blind Injection

// Attackers might use time delays to extract information
// username: "admin' AND (SELECT CASE WHEN 
//           (username = 'admin') THEN pg_sleep(5) 
//           ELSE pg_sleep(0) END FROM users)--"

// If this causes a 5-second delay, the attacker knows 
// 'admin' is a valid username
                

E-Commerce Platform Example

// Vulnerable product search
app.get('/products', (req, res) => {
    const category = req.query.category;
    
    // ⚠️ VULNERABLE CODE - DO NOT USE IN PRODUCTION ⚠️
    const query = `
        SELECT name, price 
        FROM products 
        WHERE category = '${category}'
    `;
    
    // Malicious input:
    // category = "toys' UNION 
    //            SELECT creditcard_num, cvv 
    //            FROM orders; --"
    
    // This could expose sensitive customer data!
});
                

This example shows how a simple product search could be manipulated to expose sensitive customer information. It's like having a secure filing cabinet where someone finds a way to access not just product catalogs but also confidential customer files.

Common Vulnerable Points

// 1. Form Inputs
app.post('/register', (req, res) => {
    const { username, email } = req.body;
    // Vulnerable to injection through form fields
});

// 2. URL Parameters
app.get('/product/:id', (req, res) => {
    const productId = req.params.id;
    // Vulnerable to injection through URL manipulation
});

// 3. Cookie Values
app.get('/dashboard', (req, res) => {
    const userPref = req.cookies.preferences;
    // Vulnerable to injection through cookie tampering
});

// 4. HTTP Headers
app.get('/api/data', (req, res) => {
    const userAgent = req.headers['user-agent'];
    // Vulnerable to injection through header manipulation
});
                

Common Vulnerable Patterns

// Pattern 1: Direct String Concatenation
const query = "SELECT * FROM users WHERE id = " + userId;

// Pattern 2: Template Literals Without Parameterization
const query = `SELECT * FROM products WHERE category = '${category}'`;

// Pattern 3: Multiple Statement Execution
const query = `
    UPDATE users 
    SET last_login = NOW() 
    WHERE id = ${userId};
    ${additionalQueries}
`;

// Pattern 4: Dynamic Table Names
const query = `SELECT * FROM ${tableName}`;

// Each of these patterns can be exploited in different ways
// We'll learn safer alternatives in the next section
                

Attack Construction Examples

// Example 1: Authentication Bypass
username: "admin' --"
password: "anything"
// The -- comments out the password check

// Example 2: Data Extraction
search: "' UNION SELECT username, password FROM users --"
// Piggybacks a user table query onto a normal search

// Example 3: Database Manipulation
input: "'; DROP TABLE users; --"
// Attempts to destroy database tables

// Example 4: Information Gathering
input: "' AND (SELECT CASE WHEN 
        (SELECT count(*) FROM users) > 100 
        THEN pg_sleep(5) 
        ELSE pg_sleep(0) END) --"
// Uses timing to extract information
                

Quick Protection Measures

// 1. Use Parameterized Queries
const { Pool } = require('pg');
const pool = new Pool();

app.get('/search', async (req, res) => {
    const { title } = req.query;
    
    // ✅ SAFE CODE
    const result = await pool.query(
        'SELECT * FROM books WHERE title = $1',
        [title]
    );
});

// 2. Input Validation
function validateInput(input) {
    // Remove special characters
    return input.replace(/[^a-zA-Z0-9 ]/g, '');
}

// 3. Use ORMs When Possible
const { Book } = require('./models');
const books = await Book.findAll({
    where: { title: userInput }
});
                

Security Context

// Security Layers
1. Input Validation
2. Parameterized Queries
3. Proper Error Handling
4. Limited Database Permissions
5. Regular Security Audits
6. Monitoring and Logging

// Example Logging Setup
app.use(async (req, res, next) => {
    const startTime = Date.now();
    
    res.on('finish', () => {
        const duration = Date.now() - startTime;
        logger.info({
            method: req.method,
            path: req.path,
            params: req.params,
            duration,
            status: res.statusCode
        });
    });
    
    next();
});