SQL Injection Prevention in Node.js - Complete Security Guide

// Vulnerable endpoint
app.get('/user/:id', async (req, res) => {
  const userId = req.params.id;
  const query = `SELECT * FROM users WHERE id = ${userId}`;
  // If userId = "1 OR 1=1", this becomes:
  // SELECT * FROM users WHERE id = 1 OR 1=1
  // This returns ALL users!
});

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

// ✅ Secure - Parameterized query
async function getUser(userId) {
  const query = 'SELECT * FROM users WHERE id = ?';
  const [rows] = await connection.execute(query, [userId]);
  return rows;
}

// ✅ Secure - Multiple parameters
async function createUser(name, email, age) {
  const query = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
  const [result] = await connection.execute(query, [name, email, age]);
  return result;
}

// ✅ Secure - LIKE queries
async function searchUsers(searchTerm) {
  const query = 'SELECT * FROM users WHERE name LIKE ?';
  const [rows] = await connection.execute(query, [`%${searchTerm}%`]);
  return rows;
}

const { Pool } = require('pg');

// ✅ Secure - PostgreSQL uses $1, $2, etc.
async function getUser(userId) {
  const query = 'SELECT * FROM users WHERE id = $1';
  const result = await pool.query(query, [userId]);
  return result.rows;
}

// ✅ Secure - Multiple parameters
async function updateUser(id, name, email) {
  const query = 'UPDATE users SET name = $2, email = $3 WHERE id = $1';
  const result = await pool.query(query, [id, name, email]);
  return result;
}

// ✅ Secure - Complex queries
async function getUsersWithPagination(limit, offset) {
  const query = 'SELECT * FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2';
  const result = await pool.query(query, [limit, offset]);
  return result.rows;
}

const Joi = require('joi');

// Define validation schemas
const userSchema = Joi.object({
  name: Joi.string().min(2).max(50).required(),
  email: Joi.string().email().required(),
  age: Joi.number().integer().min(18).max(120).required()
});

const idSchema = Joi.object({
  id: Joi.number().integer().positive().required()
});

// Validation middleware
function validateUser(req, res, next) {
  const { error, value } = userSchema.validate(req.body);
  if (error) {
    return res.status(400).json({ 
      error: 'Validation failed', 
      details: error.details 
    });
  }
  req.validatedData = value;
  next();
}

// Usage in route
app.post('/users', validateUser, async (req, res) => {
  try {
    const { name, email, age } = req.validatedData;
    const query = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
    const [result] = await connection.execute(query, [name, email, age]);
    res.json({ id: result.insertId, message: 'User created' });
  } catch (error) {
    res.status(500).json({ error: 'Database error' });
  }
});

const { Model } = require('objection');
const Knex = require('knex');

// Configure Knex
const knex = Knex({
  client: 'mysql2',
  connection: {
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydb'
  }
});

Model.knex(knex);

// Define User model
class User extends Model {
  static get tableName() {
    return 'users';
  }
}

// ✅ Secure ORM queries
async function getUser(userId) {
  return await User.query().findById(userId);
}

async function createUser(userData) {
  return await User.query().insert(userData);
}

async function searchUsers(searchTerm) {
  return await User.query()
    .where('name', 'like', `%${searchTerm}%`);
}

async function updateUser(id, userData) {
  return await User.query()
    .findById(id)
    .patch(userData);
}

-- Create application user with minimal privileges
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant only necessary privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost';

-- Deny dangerous operations
REVOKE DROP, ALTER, CREATE ON *.* FROM 'app_user'@'localhost';

-- Flush privileges
FLUSH PRIVILEGES;

-- Example for PostgreSQL
CREATE USER app_user WITH PASSWORD 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

class SecureDatabaseService {
  constructor() {
    this.connection = mysql.createConnection({
      host: 'localhost',
      user: 'app_user',
      password: 'strong_password',
      database: 'mydb'
    });
  }

  async execute(query, params = []) {
    // Log all queries for monitoring
    console.log('Executing query:', query);
    console.log('With parameters:', params);
    
    // Check for suspicious patterns
    if (this.detectSuspiciousQuery(query)) {
      throw new Error('Suspicious query detected');
    }
    
    try {
      const [rows] = await this.connection.execute(query, params);
      return rows;
    } catch (error) {
      console.error('Database error:', error);
      throw error;
    }
  }

  detectSuspiciousQuery(query) {
    const suspiciousPatterns = [
      /union\s+select/i,
      /drop\s+table/i,
      /delete\s+from/i,
      /insert\s+into/i,
      /update\s+set/i,
      /--/,
      /\/\*/,
      /\*\//
    ];
    
    return suspiciousPatterns.some(pattern => pattern.test(query));
  }
}

const rateLimit = require('express-rate-limit');
const helmet = require('helmet');

// Rate limiting
const limiter = rateLimit({
  windowMs: 15 * 60 * 1000, // 15 minutes
  max: 100, // limit each IP to 100 requests per windowMs
  message: 'Too many requests from this IP'
});

// Security headers
app.use(helmet());

// Custom security middleware
function securityMiddleware(req, res, next) {
  // Check for suspicious headers
  const userAgent = req.get('User-Agent') || '';
  if (userAgent.includes('sqlmap') || userAgent.includes('havij')) {
    return res.status(403).json({ error: 'Forbidden' });
  }
  
  // Validate content type
  if (req.method === 'POST' && !req.is('application/json')) {
    return res.status(400).json({ error: 'Invalid content type' });
  }
  
  next();
}

app.use(limiter);
app.use(securityMiddleware);

// NEVER do this
const query = "SELECT * FROM users WHERE name = '" + userName + "'";
const query = `SELECT * FROM users WHERE name = '${userName}'`;
const query = "SELECT * FROM users WHERE name = '" + escape(userName) + "'";

// NEVER build queries dynamically with user input
function buildQuery(filters) {
  let query = "SELECT * FROM users WHERE 1=1";
  
  if (filters.name) {
    query += ` AND name = '${filters.name}'`; // VULNERABLE!
  }
  
  if (filters.orderBy) {
    query += ` ORDER BY ${filters.orderBy}`; // VULNERABLE!
  }
  
  return query;
}

// Safe dynamic query building
function buildSafeQuery(filters) {
  const conditions = [];
  const params = [];
  
  if (filters.name) {
    conditions.push('name = ?');
    params.push(filters.name);
  }
  
  if (filters.email) {
    conditions.push('email = ?');
    params.push(filters.email);
  }
  
  const whereClause = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';
  
  // Safe order by with whitelist
  const allowedOrderBy = ['name', 'email', 'created_at'];
  const orderBy = allowedOrderBy.includes(filters.orderBy) ? filters.orderBy : 'created_at';
  
  const query = `SELECT * FROM users ${whereClause} ORDER BY ${orderBy}`;
  
  return { query, params };
}

// Common test payloads
const testPayloads = [
  "' OR '1'='1",
  "' OR 1=1 --",
  "'; DROP TABLE users; --",
  "' UNION SELECT * FROM admin_users --",
  "1' OR '1'='1' --",
  "admin'--",
  "admin' /*",
  "' OR 'x'='x",
  "' AND (SELECT COUNT(*) FROM users) > 0 --"
];

// Test your endpoints with these payloads
async function testEndpoint(endpoint, payload) {
  try {
    const response = await fetch(`${endpoint}?id=${payload}`);
    const data = await response.json();
    
    // Check for unexpected results
    if (Array.isArray(data) && data.length > 1) {
      console.warn('Potential SQL injection vulnerability detected!');
    }
  } catch (error) {
    console.error('Test error:', error);
  }
}

const request = require('supertest');
const app = require('../app');

describe('SQL Injection Tests', () => {
  const maliciousInputs = [
    "' OR '1'='1",
    "'; DROP TABLE users; --",
    "' UNION SELECT * FROM admin_users --"
  ];

  test.each(maliciousInputs)('should reject malicious input: %s', async (input) => {
    const response = await request(app)
      .get(`/user/${input}`)
      .expect(400);
    
    expect(response.body.error).toBeDefined();
  });

  test('should accept valid input', async () => {
    const response = await request(app)
      .get('/user/123')
      .expect(200);
    
    expect(response.body).toBeDefined();
  });
});