SQL Injection Prevention
Overview
SQL Injection (SQLi) is the #1 most catastrophic security vulnerability in web development. It occurs when a backend developer takes raw user input (like a username from a login form) and concatenates it directly into a raw SQL string. A hacker can type malicious SQL syntax into the form (e.g., '; DROP TABLE users; --), tricking the database into executing it. Parameterized Queries (Prepared Statements) mathematically guarantee prevention.
Syntax
/* --- THE DEADLY MISTAKE (String Concatenation) --- */
-- Node.js Example
const email = req.body.email; // Hacker sends: "x' OR '1'='1"
// Resulting Query: SELECT * FROM users WHERE email = 'x' OR '1'='1'
// '1=1' is ALWAYS true, so the database logs the hacker in as the first user!
const badQuery = "SELECT * FROM users WHERE email = '" + email + "'";
/* --- THE SOLUTION: Parameterized Queries --- */
// The database strictly separates the SQL logic from the Data Payload.
const goodQuery = "SELECT * FROM users WHERE email = $1";
// The driver ensures the payload is treated strictly as a String, NEVER as executable code!
const result = await db.execute(goodQuery, [req.body.email]);Common Pitfalls
- Using Regex or String Escaping functions (like
replace(''', '\'')) to 'sanitize' inputs manually. Hackers are smarter than your regex, utilizing HEX encoding or Unicode bypasses to break through. ALWAYS use native Parameterized Queries provided by your database driver (pg, mysql2, etc.). - Using ORMs blindly. While modern ORMs (Prisma, Sequelize, SQLAlchemy) automatically parameterize standard queries, they often provide raw query escape hatches (e.g.,
db.queryRaw()). If you use these raw functions and concatenate strings inside them, you are instantly vulnerable again.
Interview Questions
They split the request into two steps. Step 1: The backend sends the raw SQL Template (with $1 placeholders) to the DB. The DB parses, compiles, and optimizes the logic structure. Step 2: The backend sends the Data Payload. Because the logic structure is already locked in memory, the DB treats the payload strictly as literal scalar values, completely ignoring any malicious syntax within them.
Real-World Example
How Python's psycopg2 driver handles secure parameterized inserts natively.
query = "INSERT INTO users (username, password) VALUES (%s, %s);"
# The tuple safely isolates the data from the query execution
data = (user_input_name, user_input_pass)
cursor.execute(query, data)Check Your Knowledge
Test your understanding of SQL Injection Prevention with these quick questions.