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 = ?");

Next lesson: XSS prevention

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies