SQL Injection Explained: How Attackers Manipulate Your Database

// User enters: admin' --
const query = `SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`;

// The query becomes:
SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'

// Everything after -- is a comment, so password check is skipped!
// Attacker logs in as admin without knowing the password

// VULNERABLE: String concatenation
const query = `SELECT * FROM products WHERE id = ${productId}`;
const query = "SELECT * FROM users WHERE email = '" + email + "'";

// VULNERABLE: Template literals with user input
const query = `DELETE FROM orders WHERE id = ${req.params.id}`;

// Search field input: ' UNION SELECT username, password, email FROM users --

// Original query:
SELECT name, description FROM products WHERE name LIKE '%search%'

// Becomes:
SELECT name, description FROM products WHERE name LIKE '%'
UNION SELECT username, password, email FROM users --%'

// Returns all usernames, passwords, and emails!

// Login form:
Username: admin' --
Password: (anything)

// Query becomes:
SELECT * FROM users WHERE username = 'admin' --' AND password = 'x'

// The password check is commented out

// PostgreSQL with node-postgres
const query = 'SELECT * FROM users WHERE email = $1 AND password = $2';
const result = await pool.query(query, [email, password]);

// MySQL with mysql2
const query = 'SELECT * FROM users WHERE email = ? AND password = ?';
const [rows] = await connection.execute(query, [email, password]);

// SQLite with better-sqlite3
const stmt = db.prepare('SELECT * FROM users WHERE id = ?');
const user = stmt.get(userId);

// Prisma (safe by default)
const user = await prisma.user.findUnique({
  where: { email: userInput }
});

// Drizzle (safe by default)
const users = await db.select().from(users).where(eq(users.email, userInput));

// Sequelize (safe by default)
const user = await User.findOne({ where: { email: userInput } });

import { z } from 'zod';

// Define expected input format
const userIdSchema = z.string().uuid();
const emailSchema = z.string().email();

// Validate before using in queries
const validatedId = userIdSchema.parse(req.params.id);
const validatedEmail = emailSchema.parse(req.body.email);

// Now safe to use (though still use parameterized queries!)

-- Create a limited user for the application
CREATE USER app_user WITH PASSWORD 'secure_password';

-- Only grant necessary permissions
GRANT SELECT, INSERT, UPDATE ON products TO app_user;
GRANT SELECT ON categories TO app_user;

-- Don't grant DELETE or DROP permissions unless needed
-- NEVER use the database superuser for your app

// AI often produces string concatenation
app.get('/user/:id', async (req, res) => {
  const query = `SELECT * FROM users WHERE id = ${req.params.id}`;
  const result = await db.query(query);
  res.json(result);
});

// What you should ask for:
app.get('/user/:id', async (req, res) => {
  const query = 'SELECT * FROM users WHERE id = $1';
  const result = await db.query(query, [req.params.id]);
  res.json(result);
});