SQL injection prevention
Reinforce protection against SQL injection with prepared statements.
What is SQL injection?
If you put user input straight into a SQL string, an attacker can change the query. For example ' OR '1'='1 can bypass a login form or drop a table. This is SQL injection.
Defense: separate SQL code from data. prepare() builds the query as a template; execute() binds parameters safely. PDO placeholders (? or :name) are never interpreted as SQL.
The prepared statement pattern
A safe query is written in three steps. First prepare the template with $stmt = $pdo->prepare("SELECT ... WHERE id = ?"). Then bind values with $stmt->execute([$id]); the array order matches the ? placeholders.
For numeric input you can validate first with filter_var($input, FILTER_VALIDATE_INT). It returns false on invalid input, so you catch bad data early. Then fetch the row with fetch(PDO::FETCH_ASSOC).
Exercise Validate and query safely
Validate the $input variable with filter_var and FILTER_VALIDATE_INT; assign the result to $id. Then use prepare + execute([$id]) to fetch the name column from the users table; get the row with fetch(PDO::FETCH_ASSOC) and print the name value.
Show example solution
:FETCH_ASSOC); and print echo $row['name'] . "\n";.
Example solution:
<?php
$pdo = new PDO("sqlite::memory:");
$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')");
$input = "1";
$id = filter_var($input, FILTER_VALIDATE_INT);
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = ?");
$stmt->execute([$id]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['name'] . "\n";Test yourself
-
Match the PDO security concepts
- prepare
- Prepares the query
- execute
- Binds parameters
- ?
- Positional placeholder
- PDO::FETCH_ASSOC
- Returns associative array
-
Which is safe against SQL injection?
- "SELECT * FROM u WHERE id = " . $input
- Removing quotes with str_replace
- prepare + execute([$id])
- Encrypting with md5($input)
Show answer
prepare + execute([$id]) — Correct. prepare + execute keeps data separate from SQL; injection is blocked.
-
Complete the prepare call
$stmt = $pdo->____("SELECT name FROM users WHERE id = ?");Show answer
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = ?");