API-Specific SQL Injection Vectors

REST and GraphQL APIs present unique SQL injection challenges. Unlike traditional web applications where form inputs are primary vectors, APIs accept complex JSON payloads, query parameters, and headers that all potentially interact with databases:

// Vulnerable Node.js Express API endpoint
app.post('/api/users/search', async (req, res) => {
    // Dangerous: Trusting JSON payload directly
    const { filters, sortBy, limit } = req.body;
    
    // Building dynamic query from untrusted input
    let query = 'SELECT * FROM users WHERE 1=1';
    
    // Vulnerable filter construction
    if (filters) {
        Object.keys(filters).forEach(key => {
            query += ` AND ${key} = '${filters[key]}'`;  // SQL injection vector!
        });
    }
    
    // Vulnerable sort construction
    if (sortBy) {
        query += ` ORDER BY ${sortBy}`;  // Column name injection!
    }
    
    const results = await db.query(query);
    res.json(results);
});

// Secure API implementation
app.post('/api/users/search', async (req, res) => {
    const { filters, sortBy, limit } = req.body;
    
    // Whitelist allowed fields
    const allowedFields = ['username', 'email', 'created_at', 'status'];
    const allowedSortFields = ['username', 'created_at'];
    
    // Build parameterized query safely
    const conditions = [];
    const params = [];
    
    if (filters) {
        Object.keys(filters).forEach(key => {
            if (allowedFields.includes(key)) {
                conditions.push(`${key} = $${params.length + 1}`);
                params.push(filters[key]);
            }
        });
    }
    
    let query = 'SELECT * FROM users';
    if (conditions.length > 0) {
        query += ' WHERE ' + conditions.join(' AND ');
    }
    
    // Safe sort handling
    if (sortBy && allowedSortFields.includes(sortBy)) {
        query += ` ORDER BY ${sortBy}`;
    }
    
    // Safe limit handling
    const safeLimit = Math.min(parseInt(limit) || 100, 1000);
    query += ` LIMIT ${safeLimit}`;
    
    try {
        const results = await db.query(query, params);
        res.json({ data: results.rows });
    } catch (error) {
        logger.error('Database query failed', { error, userId: req.user.id });
        res.status(500).json({ error: 'Internal server error' });
    }
});