SQL Injection: How It Works and How to Prevent It

What Is SQL Injection?

SQL injection is a code injection attack where an attacker inserts SQL commands into input fields that are then executed by the database. It's been the #1 web application vulnerability for over two decades and still appears regularly in breach reports.

The core problem: applications that build SQL queries by concatenating user input allow attackers to modify the query's logic.

A Classic Example

Consider a login form with this backend query:

// ❌ VULNERABLE — never do this
const query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

With normal input username = "alice", the query becomes:

SELECT * FROM users WHERE username = 'alice' AND password = 'secret'

Now an attacker enters username = "' OR '1'='1' --":

SELECT * FROM users WHERE username = '' OR '1'='1' --' AND password = '...'

The -- comments out the rest of the query. '1'='1' is always true. The attacker logs in as the first user in the database — often an admin — without knowing any password.

More Dangerous Injection Patterns

Data Extraction (UNION attack)

' UNION SELECT username, password, null FROM users --

If the app displays query results, the attacker can extract any data from any table.

Blind SQL Injection

When the app doesn't display query results, attackers use timing or boolean responses to extract data one bit at a time:

' AND SLEEP(5) --          -- MySQL: delays 5 seconds if vulnerable
' AND 1=(SELECT 1 FROM users WHERE username='admin' AND SUBSTRING(password,1,1)='a') --

Database Destruction

'; DROP TABLE users; --
'; DELETE FROM orders WHERE 1=1; --

Prevention: Parameterized Queries (Prepared Statements)

The fundamental fix: never concatenate user input into SQL. Use parameterized queries where the SQL structure is fixed and data is passed separately.

// ✅ Node.js with pg (PostgreSQL)
const { rows } = await pool.query(
  'SELECT * FROM users WHERE username = $1 AND password_hash = $2',
  [username, passwordHash]
);

// ✅ Node.js with mysql2
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE username = ? AND password_hash = ?',
  [username, passwordHash]
);

// ✅ Node.js with better-sqlite3
const stmt = db.prepare('SELECT * FROM users WHERE username = ? AND password_hash = ?');
const user = stmt.get(username, passwordHash);

With parameterized queries, the database receives the SQL structure and the parameters separately. Even if a parameter contains SQL syntax, it's treated as a literal string — never as SQL commands.

# ✅ Python with psycopg2 (PostgreSQL)
cur.execute(
    "SELECT * FROM users WHERE username = %s AND password_hash = %s",
    (username, password_hash)
)

# ✅ Python with SQLite
cur.execute(
    "SELECT * FROM users WHERE username = ? AND password_hash = ?",
    (username, password_hash)
)

# ✅ Python with SQLAlchemy (ORM)
from sqlalchemy import text
result = db.execute(
    text("SELECT * FROM users WHERE username = :username"),
    {"username": username}
)

// ✅ Go with database/sql
row := db.QueryRow(
    "SELECT id, username FROM users WHERE username = $1",
    username,
)

ORMs: Parameterized by Default

Object-Relational Mappers generate parameterized queries automatically, making the safe path the easy path:

// ✅ Prisma — safe by default
const user = await prisma.user.findFirst({
  where: {
    username: username,  // automatically parameterized
    passwordHash: passwordHash,
  },
});

// ✅ Sequelize
const user = await User.findOne({
  where: { username, passwordHash },
});

// ✅ TypeORM
const user = await userRepo.findOne({
  where: { username, passwordHash },
});

Warning: ORMs can still be vulnerable if you use raw query methods with string interpolation:

// ❌ Still vulnerable — using raw SQL in ORM
const user = await prisma.$queryRaw`SELECT * FROM users WHERE username = ${username}`;
// Wait — this is actually safe! Prisma uses tagged template literals with automatic parameterization

// ❌ This IS vulnerable:
const user = await prisma.$queryRawUnsafe(
  `SELECT * FROM users WHERE username = '${username}'`  // never do this
);

Input Validation: Defense in Depth

Parameterized queries are the primary defense. Input validation adds a second layer:

// Allowlist validation — only accept expected characters
function validateUsername(username) {
  if (!/^[a-zA-Z0-9_]{3,50}$/.test(username)) {
    throw new Error('Invalid username format');
  }
  return username;
}

// Type checking
function validateUserId(id) {
  const parsed = parseInt(id, 10);
  if (isNaN(parsed) || parsed <= 0) {
    throw new Error('Invalid user ID');
  }
  return parsed;
}

Validation reduces the attack surface but never replaces parameterized queries. Attackers find creative ways around validation; parameterized queries are structurally safe.

Dynamic Queries: Handling Column Names and Table Names

Parameters work for values, but not for column names, table names, or SQL keywords. If you must build these dynamically, use an allowlist:

// ✅ Safe: allowlist approach for dynamic column names
const ALLOWED_SORT_COLUMNS = new Set(['name', 'email', 'created_at', 'updated_at']);

function buildSortQuery(column, direction) {
  if (!ALLOWED_SORT_COLUMNS.has(column)) {
    throw new Error(`Invalid sort column: ${column}`);
  }
  const dir = direction === 'DESC' ? 'DESC' : 'ASC';  // force valid value
  return `ORDER BY ${column} ${dir}`;  // safe: both values are from allowlists
}

const sql = `SELECT * FROM users ${buildSortQuery(req.query.sort, req.query.dir)}`;

Never use blocklists (trying to filter out bad characters). Attackers know hundreds of encoding techniques to bypass them.

Least Privilege: Limit Damage if Injection Occurs

Even with parameterized queries, defense in depth is valuable:

-- Create a restricted database user for the application
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant only what the application needs
GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'app_user'@'localhost';
GRANT SELECT, INSERT ON myapp.orders TO 'app_user'@'localhost';

-- Don't grant DELETE, DROP, or access to system tables
REVOKE ALL ON *.* FROM 'app_user'@'localhost';

If an injection attack somehow succeeds, a restricted database user limits what the attacker can do.

Detecting SQL Injection Vulnerabilities

Manual testing: Try these in input fields:

'
''
'--
' OR '1'='1
' OR 1=1 --

If the application throws a database error or behaves differently, it may be vulnerable.

Automated scanning: OWASP ZAP, sqlmap (for authorized testing only), Burp Suite Pro.

Code review: Search for string concatenation with SQL:

grep -r "SELECT.*+.*req." src/
grep -r "WHERE.*`${" src/

Summary

→ Use the SQL Prettify tool to inspect and format SQL queries during development.