SQL Injection

// DANGEROUS: Direct string concatenation
function loginUser(username, password) {
  const query = `SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`;
  return db.execute(query);
}

// Attack: username = "admin' --"
// Result: SELECT * FROM users WHERE username = 'admin' --' AND password = ''
// The -- comments out the password check, bypassing authentication!

// SAFE: Using parameterized queries
function loginUser(username, password) {
  const query = 'SELECT * FROM users WHERE username = ? AND password = ?';
  return db.execute(query, [username, password]);
}

// Or with named parameters (e.g., Node.js with pg)
function loginUser(username, password) {
  const query = 'SELECT * FROM users WHERE username = $1 AND password = $2';
  return db.query(query, [username, password]);
}

// Attack attempts are treated as literal strings, not SQL code

// DANGEROUS: Building queries with user input
function searchProducts(searchTerm) {
  const query = "SELECT * FROM products WHERE name LIKE '%" + searchTerm + "%'";
  return db.execute(query);
}

// Attack: searchTerm = "' UNION SELECT username, password, NULL FROM users --"
// Result: Extracts all usernames and passwords from users table

// SAFE: Using ORM (Sequelize example)
async function searchProducts(searchTerm) {
  return await Product.findAll({
    where: {
      name: {
        [Op.like]: `%${searchTerm}%`
      }
    }
  });
}

// Or with prepared statement
function searchProducts(searchTerm) {
  const query = 'SELECT * FROM products WHERE name LIKE ?';
  return db.execute(query, [`%${searchTerm}%`]);
}

// DANGEROUS: Using raw queries with string concatenation
async function getUser(userId) {
  const [results] = await sequelize.query(
    `SELECT * FROM users WHERE id = ${userId}`
  );
  return results;
}

// Attack: userId = "1 OR 1=1" exposes all users

// SAFE: Using Sequelize model methods
async function getUser(userId) {
  return await User.findOne({ where: { id: userId } });
}

// Or with raw queries using replacements
async function getUser(userId) {
  const [results] = await sequelize.query(
    'SELECT * FROM users WHERE id = :userId',
    {
      replacements: { userId },
      type: QueryTypes.SELECT
    }
  );
  return results[0];
}

// DANGEROUS: String concatenation in query builder
async function searchUsers(searchTerm: string) {
  return await dataSource.query(
    `SELECT * FROM users WHERE username LIKE '%${searchTerm}%'`
  );
}

// Attack: searchTerm = "%'; DROP TABLE users; --"

// SAFE: Using TypeORM query builder
async function searchUsers(searchTerm: string) {
  return await userRepository
    .createQueryBuilder('user')
    .where('user.username LIKE :searchTerm', {
      searchTerm: `%${searchTerm}%`
    })
    .getMany();
}

// Or with find options
async function searchUsers(searchTerm: string) {
  return await userRepository.find({
    where: { username: Like(`%${searchTerm}%`) }
  });
}

// DANGEROUS: $queryRaw with template literals
async function getProductsByCategory(category: string) {
  return await prisma.$queryRaw`
    SELECT * FROM products WHERE category = '${category}'
  `;
}

// Attack: category = "'; DELETE FROM products; --"

// SAFE: Using Prisma Client (automatically parameterized)
async function getProductsByCategory(category: string) {
  return await prisma.product.findMany({
    where: { category }
  });
}

// Or with $queryRaw using Prisma.sql (safe interpolation)
async function getProductsByCategory(category: string) {
  return await prisma.$queryRaw`
    SELECT * FROM products WHERE category = ${category}
  `;
  // Prisma automatically parameterizes tagged template values
}

// DANGEROUS: Dynamic ORDER BY
function getUsers(sortColumn, sortOrder) {
  const query = `SELECT * FROM users ORDER BY ${sortColumn} ${sortOrder}`;
  return db.execute(query);
}

// Attack: sortColumn = "1; DROP TABLE users; --"
// Could delete the entire users table!

// SAFE: Whitelist allowed columns
function getUsers(sortColumn, sortOrder) {
  const allowedColumns = ['id', 'name', 'email', 'created_at'];
  const allowedOrders = ['ASC', 'DESC'];

  if (!allowedColumns.includes(sortColumn)) {
    throw new Error('Invalid sort column');
  }
  if (!allowedOrders.includes(sortOrder.toUpperCase())) {
    throw new Error('Invalid sort order');
  }

  // Now safe to use in query (column name can't be parameterized)
  const query = `SELECT * FROM users ORDER BY ${sortColumn} ${sortOrder}`;
  return db.execute(query);
}