PHP
SQL injection and prevention
Learn the SQL injection attack and how to prevent it with prepared statements.
What is SQL injection?
SQL injection is a security vulnerability where an attacker hides malicious SQL fragments inside user input to change the query's structure. The goal is to reach unauthorized data or modify it.
For example, entering ' OR '1'='1 in a login form can break the query so it returns all rows. The attacker can then log in without knowing any password.
Such attacks cause data leaks, privilege escalation and data loss; user input must never be trusted blindly.
How does prepare() block injection?
In a prepared statement the SQL template and the data are separate. PDO compiles the template first; then execute() passes the values as parameters. Values are never merged into the SQL text.
So an input like ' OR '1'='1 is treated as a plain string value, not as SQL. The query structure stays intact; only rows equal to that value are searched.
As a result, the injection attempt is neutralized; security is provided automatically.
Exercise Block an injection attempt
Assign "' OR '1'='1" to $userInput (this is a malicious input). Build the template 'SELECT name FROM users WHERE name = ?' with prepare(). Run it with execute([$userInput]), fetch rows with fetchAll(PDO::FETCH_ASSOC) and print count($rows).
Show example solution
:FETCH_ASSOC). Print echo count($rows) . "\n".
Example solution:
<?php
// Block a SQL injection attack with prepare
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$pdo->exec("INSERT INTO users (name) VALUES ('Sam')");
$userInput = "' OR '1'='1";
$stmt = $pdo->prepare("SELECT name FROM users WHERE name = ?");
$stmt->execute([$userInput]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo count($rows) . "\n";
Test yourself
-
What is the safest defense against SQL injection?
- Use a prepared statement with prepare()
- Wrap user input in single quotes
- Concatenate input directly with query()
- Filter input with htmlspecialchars
Show answer
Use a prepared statement with prepare() — Correct! prepare() separates SQL from data; an injection attempt is treated as data.
-
Match the SQL injection concepts
- SQL injection
- Security vulnerability
- prepare()
- Prevention method
- ' OR '1'='1
- Malicious input
- fetchAll()
- Returns all rows
-
Complete the safe query
$stmt = $pdo->____("SELECT * FROM users WHERE id = ?");
$stmt->execute([$input]);
Show answer
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$input]);
Next lesson: Named placeholders
