SQL Injection: How Attacks Work and How to Prevent Them

Why SQL Injection Still Matters

SQL injection has been in the OWASP Top 10 since the list's inception over a decade ago. Despite being one of the most well-understood vulnerabilities, it continues to cause catastrophic data breaches. In recent years, major companies have exposed millions of user records due to SQL injection flaws — customer data, financial information, and credentials falling into the wrong hands.

The reason it's so dangerous is also why it persists: simplicity. An attacker doesn't need sophisticated equipment or zero-day exploits. A single vulnerable input field is enough to compromise an entire database. Understanding SQL injection isn't optional for developers — it's essential.

How SQL Injection Works

SQL injection occurs when user input is directly concatenated into SQL queries instead of being handled as data. The database interprets user input as part of the SQL command itself, allowing attackers to modify the query's intended behavior.

The Vulnerable Pattern

Consider a typical login form that checks email and password against a database. The vulnerable code might look like this:

// ❌ VULNERABLE — Never concatenate user input into SQL
const query = `SELECT * FROM users
  WHERE email = '${email}' AND password = '${password}'`;

When a legitimate user enters user@example.com and secret123, the query works as intended. But what happens when an attacker enters something unexpected?

The Classic Attack: Bypassing Authentication

An attacker might enter:

• Email: admin@example.com
• Password: ' OR '1'='1

The resulting query becomes:

SELECT * FROM users
WHERE email = 'admin@example.com'
AND password = '' OR '1'='1'

Since '1'='1' is always true, the database returns the admin user — and the attacker gains access without knowing any password.

Extracting Sensitive Data

More sophisticated attacks use UNION to pull data from other tables:

// In a vulnerable search field
' UNION SELECT NULL, username, password, email FROM users--

This transforms the query to return user credentials instead of the intended search results. The -- comments out any remaining SQL, preventing syntax errors.

Types of SQL Injection Attacks

1. In-Band SQL Injection

The most common type, where attackers use the same channel to launch attacks and retrieve results. This includes:

• Union-based: Append additional SELECT statements to combine results from multiple tables
• Error-based: Trigger database errors that reveal structure and data

2. Inferential SQL Injection

No data transfer occurs — attackers reconstruct database structure by analyzing behavior patterns:

• Boolean-based: Inject conditions that return true or false, inferring data character by character
• Time-based: Use SLEEP() or WAITFOR commands to infer responses through response time differences

3. Out-of-Band SQL Injection

Attackers retrieve data through alternative channels when in-band isn't available — DNS requests or HTTP connections that exfiltrate data.

Real Attack Payloads

Here are actual payloads that have been used in SQL injection attacks, categorized by their purpose:

Authentication Bypass

' OR '1'='1
admin'--
admin' OR '1'='1
' OR 1=1--
" OR "1"="1
' OR 'x'='x

Union-Based Data Extraction

' UNION SELECT NULL,username,password,NULL FROM users--
' UNION SELECT NULL,NULL,table_name,NULL FROM information_schema.tables--
' UNION SELECT NULL,column_name,NULL,NULL FROM information_schema.columns WHERE table_name='users'--

Stacked Queries (MSSQL/MySQL)

'; DROP TABLE users;--
'; EXEC xp_cmdshell 'net user hacker password123 /ADD';--

Preventing SQL Injection: Parameterized Queries

The fix is straightforward: never concatenate user input into SQL queries. Instead, use parameterized queries (also called prepared statements) where user input is passed as separate parameters, never interpreted as SQL code.

Node.js with PostgreSQL

// ✅ SECURE — Using parameterized queries
const result = await db.query(
  'SELECT * FROM users WHERE email = $1 AND password = $2',
  [email, password]
);

Python with SQLAlchemy

# ✅ SECURE — Using ORM's parameterized queries
user = db.session.query(User).filter_by(
  email=email,
  password=password
).first()

# Also secure: explicit parameterized queries
db.session.execute(
  text("SELECT * FROM users WHERE email = :email"),
  {"email": email}
)

PHP with PDO

// ✅ SECURE — Using prepared statements
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
$user = $stmt->fetch();

Java with JDBC

// ✅ SECURE — Using PreparedStatement
PreparedStatement stmt = connection.prepareStatement(
  "SELECT * FROM users WHERE email = ? AND password = ?"
);
stmt.setString(1, email);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

Common ORM Vulnerabilities to Avoid

ORMs like SQLAlchemy, Hibernate, and Prisma handle most SQL injection risks automatically — but they're not foolproof. Watch out for these dangerous patterns:

Raw SQL Concatenation

// ❌ VULNERABLE — Don't do this even in an ORM
db.raw('SELECT * FROM users WHERE name = ' + userInput)

Unsafe String Interpolation in ORM Methods

// ❌ VULNERABLE — Some ORM methods allow raw SQL
User.objects.raw('SELECT * FROM users WHERE name = %s' % userInput)

Dynamic Table/Column Names

// ⚠️ RISKY — Whitelist validation required
User.objects.filter(**{column_name: value})  # Safe if column_name is validated

Defense in Depth: Additional Safeguards

Least Privilege Database Access

Your application should only have permissions it needs — nothing more. If the app only reads data, the database user shouldn't have write permissions. If it needs specific tables, don't grant access to everything.

-- App user: read-only access to specific tables
GRANT SELECT ON app.users TO 'app_user'@'localhost';
GRANT SELECT ON app.orders TO 'app_user'@'localhost';
GRANT INSERT, UPDATE ON app.users TO 'app_user'@'localhost';

-- Never grant: DROP, DELETE on users table, access to information_schema

Input Validation as Secondary Defense

While parameterized queries prevent injection, validation adds an extra layer:

# Whitelist validation for expected formats
import re

def validate_email(email):
    pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
    if re.match(pattern, email):
        return email
    raise ValueError('Invalid email format')

Web Application Firewalls (WAF)

WAFs like Cloudflare, AWS WAF, or ModSecurity can detect and block common SQL injection patterns before they reach your application. They're not a replacement for secure code, but provide valuable protection against novel attacks.

Hiding Error Messages

Don't expose database error messages to users. They reveal your database structure and help attackers craft targeted exploits:

// ❌ BAD — Exposes database errors to users
res.send('Database error: ' + error.message);

// ✅ GOOD — Generic error, log details for debugging
res.send('An error occurred. Please try again.');
logger.error('Database error: ' + error.message);

Testing Your Application

Before attackers find vulnerabilities, you should:

Static Analysis

Use tools like Semgrep, SonarQube, or SQL injection detection plugins in your IDE to scan code for vulnerable patterns:

# Semgrep rule example
rules:
  - id: sql-injection
    pattern: query = "SELECT ... " + $USER_INPUT
    message: Potential SQL injection

Dynamic Testing with SQLMap

# Only test your own staging environment!
sqlmap -u "https://staging.example.com/search?q=1" --batch --dbs

# Test specific parameter
sqlmap -u "https://staging.example.com/item?id=1" -p id

Penetration Testing

Regular penetration testing by security professionals can uncover vulnerabilities automated tools miss.

Quick Reference: Prevention Checklist

• Always use parameterized queries — no exceptions
• Never concatenate user input into SQL strings
• Use ORMs correctly — avoid raw SQL unless absolutely necessary
• Apply least privilege — database users should have minimal permissions
• Validate input — reject unexpected patterns early
• Hide errors — never expose database structure to users
• Use a WAF — additional protection against novel attacks
• Scan code — automated tools catch many issues
• Test regularly — penetration testing and SQLMap on staging

SQL injection is preventable. Every vulnerability has been eliminated countless times by developers who made the simple choice to use parameterized queries. Make that choice in your code — every query, every time.