SQL Injection

// Node.js/Express - Secure SQL queries with parameterization

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

// SECURE: Search with parameterized query
app.get('/users', async (req, res) => {
  const q = req.query.q || '';
  
  // SECURE: Validate and limit input length
  if (q.length > 100) {
    return res.status(400).json({ error: 'Search query too long' });
  }
  
  // SECURE: Escape LIKE special characters (%  _)
  const escapedQ = q.replace(/[%_\\]/g, '\\$&');
  const pattern = '%' + escapedQ + '%';
  
  // SECURE: Use parameterized query with placeholders
  // Database driver handles proper escaping automatically
  const sql = 'SELECT id, name FROM users WHERE name LIKE ?';
  const [rows] = await db.query(sql, [pattern]);
  
  res.json(rows);
});

// SECURE: Login authentication with prepared statements
app.post('/login', async (req, res) => {
  const { email, password } = req.body;
  
  // SECURE: Input validation
  if (!email || !password) {
    return res.status(400).json({ error: 'Email and password required' });
  }
  
  // SECURE: Parameterized query - no string concatenation!
  // Placeholders (?) prevent SQL injection
  const sql = 'SELECT id, password_hash FROM users WHERE email = ?';
  const [users] = await db.query(sql, [email]);
  
  if (users.length === 0) {
    return res.status(401).json({ error: 'Invalid credentials' });
  }
  
  // SECURE: Compare password hash (never store plaintext)
  const validPassword = await bcrypt.compare(password, users[0].password_hash);
  
  if (validPassword) {
    req.session.userId = users[0].id;
    res.json({ message: 'Login successful' });
  } else {
    res.status(401).json({ error: 'Invalid credentials' });
  }
});

// SECURE: Sorting with allow-list
app.get('/products', async (req, res) => {
  const sortBy = req.query.sort || 'name';
  const order = req.query.order || 'ASC';
  
  // SECURE: Validate sort column against allow-list
  const ALLOWED_SORT_COLUMNS = ['name', 'price', 'created_at', 'category'];
  if (!ALLOWED_SORT_COLUMNS.includes(sortBy)) {
    return res.status(400).json({ error: 'Invalid sort column' });
  }
  
  // SECURE: Validate sort order
  const ALLOWED_ORDER = ['ASC', 'DESC'];
  if (!ALLOWED_ORDER.includes(order.toUpperCase())) {
    return res.status(400).json({ error: 'Invalid sort order' });
  }
  
  // SECURE: Build query with validated identifiers
  // Column names can't be parameterized, but we validated them
  const sql = `SELECT id, name, price FROM products ORDER BY ${sortBy} ${order.toUpperCase()}`;
  const [rows] = await db.query(sql);
  
  res.json(rows);
});

// SECURE: Batch delete with parameterized IN clause
app.delete('/api/orders', async (req, res) => {
  const ids = req.body.ids;
  
  // SECURE: Validate input
  if (!Array.isArray(ids) || ids.length === 0) {
    return res.status(400).json({ error: 'Invalid IDs array' });
  }
  
  if (ids.length > 100) {
    return res.status(400).json({ error: 'Too many IDs (max 100)' });
  }
  
  // SECURE: Validate each ID is an integer
  const validIds = ids.filter(id => Number.isInteger(id) && id > 0);
  
  if (validIds.length === 0) {
    return res.status(400).json({ error: 'No valid IDs provided' });
  }
  
  // SECURE: Create parameterized placeholders for IN clause
  const placeholders = validIds.map(() => '?').join(',');
  const sql = `DELETE FROM orders WHERE id IN (${placeholders}) AND user_id = ?`;
  
  // SECURE: Pass validated IDs as parameters
  const [result] = await db.query(sql, [...validIds, req.session.userId]);
  
  res.json({ message: 'Orders deleted', count: result.affectedRows });
});

// SECURE: Dynamic table with allow-list
app.get('/api/reports/:table', async (req, res) => {
  const tableName = req.params.table;
  const limit = parseInt(req.query.limit) || 100;
  
  // SECURE: Table name allow-list
  const ALLOWED_TABLES = {
    'orders': 'orders',
    'products': 'products',
    'users': 'users'
  };
  
  if (!ALLOWED_TABLES[tableName]) {
    return res.status(400).json({ error: 'Invalid table name' });
  }
  
  // SECURE: Validate limit
  if (!Number.isInteger(limit) || limit < 1 || limit > 1000) {
    return res.status(400).json({ error: 'Invalid limit' });
  }
  
  // SECURE: Use validated table name from allow-list
  const safeTable = ALLOWED_TABLES[tableName];
  
  // SECURE: Parameterize LIMIT
  const sql = `SELECT * FROM ${safeTable} LIMIT ?`;
  const [rows] = await db.query(sql, [limit]);
  
  res.json(rows);
});

// SECURE: Pagination with parameterized values
app.get('/api/users/paginated', async (req, res) => {
  const page = parseInt(req.query.page) || 0;
  const size = parseInt(req.query.size) || 10;
  
  // SECURE: Validate pagination parameters
  if (!Number.isInteger(page) || page < 0) {
    return res.status(400).json({ error: 'Invalid page number' });
  }
  
  if (!Number.isInteger(size) || size < 1 || size > 100) {
    return res.status(400).json({ error: 'Invalid page size (1-100)' });
  }
  
  const offset = page * size;
  
  // SECURE: Use parameterized LIMIT and OFFSET
  const sql = 'SELECT id, name FROM users LIMIT ? OFFSET ?';
  const [rows] = await db.query(sql, [size, offset]);
  
  res.json(rows);
});

// SECURE: JSON field query with validation
app.post('/api/search-metadata', async (req, res) => {
  const field = req.body.field;
  const value = req.body.value;
  
  // SECURE: Allow-list JSON field paths
  const ALLOWED_FIELDS = ['category', 'author', 'tags', 'status'];
  if (!ALLOWED_FIELDS.includes(field)) {
    return res.status(400).json({ error: 'Invalid field' });
  }
  
  // SECURE: Validate value
  if (typeof value !== 'string' || value.length > 100) {
    return res.status(400).json({ error: 'Invalid value' });
  }
  
  // SECURE: Build JSON path safely with validated field
  const jsonPath = `$.${field}`;
  
  // SECURE: Parameterize the value
  const sql = 'SELECT * FROM documents WHERE JSON_EXTRACT(metadata, ?) = ?';
  const [rows] = await db.query(sql, [jsonPath, value]);
  
  res.json(rows);
});

// SECURE: Using ORM for additional safety (e.g., Sequelize)
const { Sequelize, Model, DataTypes } = require('sequelize');

class User extends Model {}
User.init({
  id: { type: DataTypes.INTEGER, primaryKey: true },
  name: DataTypes.STRING,
  email: DataTypes.STRING
}, { sequelize, modelName: 'user' });

app.get('/api/users-orm', async (req, res) => {
  const searchTerm = req.query.q || '';
  
  // SECURE: ORM handles parameterization automatically
  const users = await User.findAll({
    where: {
      name: {
        [Sequelize.Op.like]: `%${searchTerm}%`
      }
    },
    limit: 100
  });
  
  res.json(users);
});

// SECURE: Least privilege database user
// Configure database connection with read-only user for read operations
const readOnlyDb = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_READ_USER,  // User with only SELECT privilege
  password: process.env.DB_READ_PASSWORD,
  database: process.env.DB_NAME
});

app.get('/api/public-products', async (req, res) => {
  // SECURE: Use read-only connection for public queries
  // Even if injection occurs, can't modify data
  const sql = 'SELECT id, name, price FROM products WHERE published = ?';
  const [rows] = await readOnlyDb.query(sql, [true]);
  res.json(rows);
});