Last updated
Common SQL Injection Patterns (for awareness)
Authentication bypass:
' OR '1'='1
' OR 1=1--
admin'--
UNION-based data extraction:
' UNION SELECT username, password FROM users--
Comment-based injection:
'; DROP TABLE users--
'; UPDATE users SET password='hacked' WHERE '1'='1
Defense Summary
- Always use parameterized queries / prepared statements — this is the primary defense
- Never concatenate user input directly into SQL strings
- Use manual escaping only when parameterized queries are not possible (legacy code)
- Escape LIKE wildcards (% and _) separately from regular string escaping
- Use identifier quoting when table/column names come from user input or contain reserved words
- Apply input validation as an additional layer — reject input that doesn't match expected patterns
Examples
Example 1: Basic String Escaping
Input: O'Brien
MySQL: O\'Brien
PostgreSQL: O''Brien (double the single quote)
SQL Server: O''Brien
Input: It's a "test" value
MySQL: It\'s a \"test\" value
PostgreSQL: It''s a "test" value (only single quotes need escaping)
Input: C:\Users\Alice\file.txt
MySQL: C:\\Users\\Alice\\file.txt (backslashes doubled)
PostgreSQL: C:\Users\Alice\file.txt (no backslash escaping needed)
Example 2: Vulnerable vs Safe Code
VULNERABLE — string concatenation (never do this):
// JavaScript — VULNERABLE
const query = "SELECT * FROM users WHERE username = '" + username + "'";
// If username = "' OR '1'='1", the query becomes:
// SELECT * FROM users WHERE username = '' OR '1'='1'
// This returns ALL users — authentication bypass!
SAFE — parameterized query (always use this):
// Node.js + mysql2 — SAFE
const [rows] = await db.execute(
'SELECT * FROM users WHERE username = ?',
[username] // user input is always treated as data, never as SQL
);
// Node.js + pg (PostgreSQL) — SAFE
const { rows } = await db.query(
'SELECT * FROM users WHERE username = $1',
[username]
);
// Python + psycopg2 — SAFE
cursor.execute(
'SELECT * FROM users WHERE username = %s',
(username,)
)
// Java + JDBC — SAFE
PreparedStatement stmt = conn.prepareStatement(
"SELECT * FROM users WHERE username = ?"
);
stmt.setString(1, username);
Example 3: LIKE Clause Escaping
The % and _ characters are wildcards in LIKE and must be escaped if literal:
-- User searches for "50% off" — the % must be escaped
-- VULNERABLE:
SELECT * FROM products WHERE name LIKE '%' + userInput + '%'
-- SAFE (PostgreSQL):
SELECT * FROM products WHERE name LIKE '%' || $1 || '%'
-- And escape wildcards in the input:
-- userInput = '50\% off' (backslash escapes the %)
-- MySQL LIKE escape:
SELECT * FROM products WHERE name LIKE CONCAT('%', ?, '%') ESCAPE '\'
-- Input: '50\% off' matches literal "50% off"