// VULNERABLE Node.js code — NEVER do this
const query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

-- What MySQL actually executes:
SELECT * FROM users
WHERE username = 'admin' --' AND password = 'anything'
-- The -- comments out the password check entirely

-- Original query the app builds:
SELECT name, price FROM products WHERE category_id = 3

-- Step 1: attacker finds column count — 2 columns
-- Inject: 3 ORDER BY 1 -- (no error), ORDER BY 3 -- (error) => 2 columns

-- Step 2: extract database version and user
-- Inject: 3 UNION SELECT version(), user() --
SELECT name, price FROM products WHERE category_id = 3
UNION
SELECT version(), user() --
-- Returns: ("8.0.32", "webapp@10.0.0.5") mixed into product list

-- Step 3: enumerate tables
-- Inject: 3 UNION SELECT table_name, table_schema FROM information_schema.tables LIMIT 1 --
-- Step 4: extract sensitive data
-- Inject: 3 UNION SELECT username, password FROM users --

-- Step 1: attacker registers with username: admin'--
INSERT INTO users (username, password)
VALUES ("admin'--", "attacker_pass");
-- The INSERT itself is safe (parameterized)

-- Step 2: later, the app retrieves the username and concatenates it
-- into a password-change query WITHOUT parameterization:
SET @username = "admin'--";  -- retrieved from DB
-- Vulnerable query built by app:
-- UPDATE users SET password = 'new' WHERE username = 'admin'--' AND old_pass = '...'
-- The -- comments out the old_pass check — attacker changes admin's password

-- If the page loads normally, condition is TRUE
-- If the page errors or returns empty, condition is FALSE

-- Is the first character of the admin password 'a'? (page loads = yes)
1 AND SUBSTRING((SELECT password FROM users WHERE username='admin'),1,1) = 'a'

-- Repeat for each character position to extract the full password
1 AND SUBSTRING((SELECT password FROM users WHERE username='admin'),2,1) = 'd'
-- After ~300 requests, full password is known

-- If MySQL sleeps 5 seconds, the database is MySQL (not PostgreSQL/MSSQL)
1 AND SLEEP(5)

-- Extract password using time: delays = '1', no delay = not '1'
1 AND IF(
  SUBSTRING((SELECT password FROM users LIMIT 1),1,1) = 'a',
  SLEEP(5),
  0
)

-- Each true condition takes 5 extra seconds — attacker monitors response time

// SAFE — mysql2 prepared statement
const mysql = require('mysql2/promise');
const conn = await mysql.createConnection(config);

// The ? placeholders are never interpreted as SQL
const [rows] = await conn.execute(
  'SELECT id, name, email FROM users WHERE username = ? AND active = ?',
  [username, 1]
);

// Vulnerable equivalent (NEVER do):
// await conn.query(`SELECT * FROM users WHERE username = '${username}'`);

# SAFE Python parameterized query
import mysql.connector
conn = mysql.connector.connect(**config)
cursor = conn.cursor(dictionary=True)

cursor.execute(
    "SELECT id, name FROM users WHERE username = %s AND password_hash = %s",
    (username, hashed_password)   # tuple of parameters
)
user = cursor.fetchone()

// SAFE PHP PDO — named placeholders
$stmt = $pdo->prepare(
    'SELECT * FROM users WHERE username = :username AND password_hash = :pw'
);
$stmt->execute([
    ':username' => $username,
    ':pw'       => $hashedPassword,
]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// SAFE Java PreparedStatement
String sql = "SELECT * FROM users WHERE username = ? AND password_hash = ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setString(1, username);
    stmt.setString(2, hashedPassword);
    ResultSet rs = stmt.executeQuery();
    if (rs.next()) { /* user found */ }
}

// SAFE Go parameterized query
rows, err := db.QueryContext(ctx,
    "SELECT id, name FROM users WHERE username = ? AND active = ?",
    username, 1,
)
if err != nil { log.Fatal(err) }
defer rows.Close()

// Sequelize (Node.js) — SAFE: uses parameterized query internally
const user = await User.findOne({
  where: { username: username, active: true }
});

// Sequelize raw query — STILL SAFE if you use replacements
const [results] = await sequelize.query(
  'SELECT * FROM users WHERE username = :name',
  { replacements: { name: username }, type: QueryTypes.SELECT }
);

// Sequelize raw query — DANGEROUS if you interpolate directly
const [results] = await sequelize.query(
  `SELECT * FROM users WHERE username = '${username}'`  // UNSAFE
);

// Prisma (Node.js) — SAFE: always parameterized
const user = await prisma.user.findFirst({
  where: { username: username }
});

// Prisma raw query — SAFE with tagged template literal
const user = await prisma.$queryRaw`SELECT * FROM users WHERE username = ${username}`;

-- SAFE stored procedure — parameter is bound as data
DELIMITER //
CREATE PROCEDURE GetUser(IN p_username VARCHAR(100))
BEGIN
  SELECT id, username, email
  FROM users
  WHERE username = p_username;  -- p_username is data, not SQL
END//
DELIMITER ;

-- DANGEROUS stored procedure — uses CONCAT + PREPARE (dynamic SQL)
DELIMITER //
CREATE PROCEDURE SortUsers(IN p_col VARCHAR(50))
BEGIN
  SET @sql = CONCAT('SELECT * FROM users ORDER BY ', p_col);  -- injectable!
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

-- Create a restricted application user — only the minimum needed
CREATE USER 'webapp'@'10.0.0.%' IDENTIFIED BY 'V3ry$tr0ng!Pass';

-- Grant only DML on the app schema
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'webapp'@'10.0.0.%';

-- Explicitly ensure dangerous privileges are NOT granted:
-- FILE: allows LOAD DATA INFILE to read OS files
-- SUPER: bypasses read-only restrictions
-- DROP, CREATE, ALTER: allows schema destruction
-- PROCESS: lets user see all queries in SHOW PROCESSLIST
-- GRANT OPTION: lets user grant their own privileges to others

-- Verify what the app user can do
SHOW GRANTS FOR 'webapp'@'10.0.0.%';

-- If injection occurs with minimal privileges:
-- SELECT — attacker can only read your own app data (serious but limited)
-- Without FILE — cannot read /etc/passwd via LOAD DATA
-- Without DROP — cannot destroy tables

// Complete secure login example (Node.js + mysql2 + bcrypt)
const bcrypt = require('bcrypt');
const mysql  = require('mysql2/promise');

async function login(username, plainPassword) {
  // 1. Validate input types and lengths before touching the DB
  if (typeof username !== 'string' || username.length > 100) {
    throw new Error('Invalid username');
  }

  // 2. Parameterized query — username cannot alter SQL structure
  const [rows] = await db.execute(
    'SELECT id, username, password_hash FROM users WHERE username = ? AND active = 1',
    [username]
  );

  // 3. Always perform the hash comparison even on not-found (prevents timing attacks)
  const dummyHash = '$2b$12$invalidhashfortimingprotection000000000000000000000';
  const storedHash = rows[0]?.password_hash ?? dummyHash;
  const isValid    = await bcrypt.compare(plainPassword, storedHash);

  if (!rows[0] || !isValid) {
    throw new Error('Invalid credentials');  // same error for both cases
  }

  return rows[0];
}

# Basic sqlmap scan of a URL parameter
sqlmap -u "https://example.com/products?id=1" --dbs

# Test a POST login form
sqlmap -u "https://example.com/login"   --data "username=admin&password=test"   --dbs

# If sqlmap finds injection: fix it with prepared statements, not by tweaking sqlmap rules

// Allowlist approach for dynamic ORDER BY columns (Node.js)
const ALLOWED_COLUMNS = ['name', 'price', 'created_at', 'rating'];
const ALLOWED_DIRS    = ['ASC', 'DESC'];

function buildOrderQuery(column, direction) {
  if (!ALLOWED_COLUMNS.includes(column)) {
    throw new Error('Invalid sort column');
  }
  const dir = ALLOWED_DIRS.includes(direction?.toUpperCase()) ? direction : 'ASC';
  // Safe because column came from an allowlist, not raw user input
  return `SELECT * FROM products ORDER BY ${column} ${dir}`;
}

// Bad: exposes MySQL error details to API consumer
app.get('/products', async (req, res) => {
  const result = await db.query(`SELECT * FROM products WHERE id = ${req.query.id}`);
  res.json(result);
  // On error: { message: "Unknown column 'injection' in 'where clause'" }
  // This tells the attacker the column names and query structure!
});

// Good: log internally, return generic error to client
app.get('/products', async (req, res) => {
  try {
    const [rows] = await db.execute('SELECT * FROM products WHERE id = ?', [req.query.id]);
    res.json(rows);
  } catch (err) {
    console.error('DB error:', err);  // log full error server-side
    res.status(500).json({ error: 'Internal server error' });  // generic to client
  }
});