Preventing injection with prepared statements in PHP

Introduction

Prepared statements help keep your database safe by stopping bad code from sneaking in. They make sure user input is treated as data, not code.

When you get user input like names or emails to save in a database.

When you want to search or filter data based on what a user types.

When you build login forms that check usernames and passwords.

When you update or delete database records using user input.

Whenever you want to avoid security problems like SQL injection attacks.

Syntax

PHP

<?php
$stmt = $pdo->prepare('SQL query with ? or :named placeholders');
$stmt->execute([$value1, $value2]);
// or
$stmt->execute(['name' => $value1, 'age' => $value2]);

Use prepare() to create a safe SQL template.

Use execute() to fill in the placeholders with actual values.

Examples

Using question mark ? as a placeholder for one value.

PHP

<?php
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$email]);

Using named placeholder :user for clarity.

PHP

<?php
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :user');
$stmt->execute(['user' => $username]);

Inserting data safely with named placeholders.

PHP

<?php
$stmt = $pdo->prepare('INSERT INTO products (name, price) VALUES (:name, :price)');
$stmt->execute(['name' => $productName, 'price' => $productPrice]);

Sample Program

This program shows how to safely add and get a user from a database using prepared statements. It stops any harmful input from changing the database commands.

PHP

<?php
// Connect to database using PDO
$pdo = new PDO('sqlite::memory:');
// Create a simple table
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, email TEXT)');

// User input simulation
$username = "alice";
$email = "[e-mail]";

// Prepare statement to insert data safely
$stmt = $pdo->prepare('INSERT INTO users (username, email) VALUES (:username, :email)');
$stmt->execute(['username' => $username, 'email' => $email]);

// Prepare statement to select data safely
$stmt = $pdo->prepare('SELECT id, username, email FROM users WHERE username = :username');
$stmt->execute(['username' => $username]);

// Fetch and print result
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if ($user) {
    echo "User found: " . $user['username'] . " with email " . $user['email'] . "\n";
} else {
    echo "User not found.\n";
}

Important Notes

Always use prepared statements when working with user input to avoid SQL injection.

Placeholders can be question marks ? or named like :name. Named placeholders are easier to read.

Prepared statements also help your code run faster if you repeat the same query many times with different values.

Summary

Prepared statements keep your database safe by separating code from data.

Use prepare() and execute() to run safe queries.

Always use them when you include user input in SQL commands.