Safe queries with PDO

PDO (PHP Data Objects) is a PHP extension that helps you work with databases safely and efficiently. One of the biggest benefits is protection against a serious security risk called SQL injection.

When you build SQL queries by inserting user input directly, like this:

<?php
$username = $_POST['username'] ?? '';
$password = $_POST['password'] ?? '';

// VULNERABLE to SQL injection
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $pdo->query($sql)->fetch();

A malicious user could enter something like admin' OR 1=1 # as the username on your login form, which tricks the query into always returning a result, even if the user doesn't exist. This is called a login bypass attack.

Why it works: The # symbol starts a comment in MySQL, so everything after it is ignored. That means the actual SQL query becomes something like:

SELECT * FROM users WHERE username = 'admin' OR 1=1 # ' AND password = 'anything'

This matches all users because 1=1 is always true and the password check is skipped entirely.

PDO prevents SQL injection by separating the SQL query from user input. Instead of building a single string, you send a fixed template first. Any input sent afterward is treated strictly as plain text, so it can never alter the query logic.

How prepared statements work

• 1. Prepare the query: You send the SQL statement with placeholders like :username. The database processes the structure first.
• 2. Pass the data: Your input fills the placeholders. If an attacker inputs admin' OR 1=1 #, the database searches for a user with that exact string as their name and doesn't execute the raw SQL injection.

<?php
$pdo = include 'database.php';

// Grab the form input directly from the request
$username = $_POST['username'] ?? '';
$email = $_POST['email'] ?? '';

// Define the query structure with named placeholders
$stmt = $pdo->prepare('INSERT INTO users (username, email) VALUES (:username, :email)');

// Pass the POST data safely into the prepared statement
$stmt->execute([
    ':username' => $username,
    ':email' => $email,
]);

Because the database already knows the exact layout of the query from the prepare() step, the placeholders act as safe containers. Whatever someone types into the form fields is treated strictly as literal text data, preventing submitted inputs from altering the query structure.

• Always use prepared statements with placeholders for user input.
• Use htmlspecialchars() when outputting data to prevent HTML injection.
• Keep your database connection code separate and reusable.

Next: Useful PDO snippets for practical examples how to safely handle data with PDO.