PHP Prepared Statements

Introduction

When working with databases in PHP, security should be one of your top priorities. One of the most common security vulnerabilities in web applications is SQL injection, where malicious users can manipulate your database queries by inserting harmful SQL code. Prepared statements are one of the most effective ways to prevent these attacks while also improving code readability and performance.

In this tutorial, you'll learn:

• What prepared statements are and why they're important
• How to use prepared statements with both PDO and MySQLi
• Best practices for implementing prepared statements in your PHP applications
• Real-world examples demonstrating practical usage

What Are Prepared Statements?

Prepared statements are a feature provided by database systems that allow you to:

1. Create SQL templates with placeholders for parameters
2. Send these templates to the database separately from the actual data
3. Bind the parameters to the prepared statement later

This separation between SQL code and data is what makes prepared statements secure against SQL injection attacks.

Why Use Prepared Statements?

1. Security

The primary benefit of prepared statements is protection against SQL injection. Since the SQL query and the data are sent to the database separately, malicious data cannot change the intent of your query.

2. Performance

For queries that are executed multiple times with different parameters, prepared statements can improve performance. The database only needs to parse and optimize the query once, then can reuse that plan for subsequent executions.

3. Cleaner Code

Prepared statements often lead to more readable and maintainable code, especially for complex queries with multiple parameters.

Using Prepared Statements with PDO

PHP Data Objects (PDO) is an interface for accessing databases in PHP that provides a consistent API across different database types.

Basic PDO Prepared Statement Example

php

<?php
// Connection setup
try {
    $pdo = new PDO('mysql:host=localhost;dbname=my_database', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Prepare statement
    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
    
    // Bind and execute
    $stmt->execute(['john_doe']);
    
    // Fetch results
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($user) {
        echo "Username: " . $user['username'] . "<br>";
        echo "Email: " . $user['email'];
    } else {
        echo "User not found";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Named Parameters with PDO

PDO also supports named parameters which can make your code more readable, especially with multiple parameters:

php

<?php
try {
    $pdo = new PDO('mysql:host=localhost;dbname=my_database', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Prepare with named parameters
    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND status = :status");
    
    // Bind and execute
    $stmt->execute([
        ':username' => 'john_doe',
        ':status' => 'active'
    ]);
    
    // Fetch results
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($user) {
        echo "Found active user: " . $user['username'];
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Using Prepared Statements with MySQLi

MySQLi (MySQL Improved) is another option for working with MySQL databases in PHP.

Basic MySQLi Prepared Statement Example

php

<?php
// Connection setup
$mysqli = new mysqli('localhost', 'username', 'password', 'my_database');

// Check connection
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// Prepare statement
$stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ?");

// Bind parameters
$email = '[email protected]';
$stmt->bind_param('s', $email); // 's' indicates a string parameter

// Execute statement
$stmt->execute();

// Get results
$result = $stmt->get_result();
$user = $result->fetch_assoc();

if ($user) {
    echo "User found: " . $user['username'];
} else {
    echo "No user with that email";
}

// Close statement and connection
$stmt->close();
$mysqli->close();
?>

MySQLi with Multiple Parameters

Here's how to use prepared statements with multiple parameters in MySQLi:

php

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'my_database');

if ($mysqli->connect_error) {
    die('Connect Error: ' . $mysqli->connect_error);
}

// Prepare statement with multiple parameters
$stmt = $mysqli->prepare("INSERT INTO users (username, email, created_at) VALUES (?, ?, ?)");

// Bind parameters
$username = 'new_user';
$email = '[email protected]';
$created_at = date('Y-m-d H:i:s');

// 'sss' means three string parameters
$stmt->bind_param('sss', $username, $email, $created_at);

// Execute and check
if ($stmt->execute()) {
    echo "New user created with ID: " . $mysqli->insert_id;
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$mysqli->close();
?>

Parameter Type Binding

With MySQLi, you need to specify the types of parameters when binding:

Character	Description
i	Integer
d	Double (floating-point number)
s	String
b	Blob (for binary data)

For example, to bind an integer and two strings:

php

$stmt->bind_param('iss', $user_id, $username, $email);

Real-World Example: User Registration Form

Here's a more comprehensive example showing a user registration system with PDO prepared statements:

php

<?php
// Registration form processing
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    try {
        // Connect to database
        $pdo = new PDO('mysql:host=localhost;dbname=my_app', 'username', 'password');
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        // Validate inputs (simplified for example)
        $username = trim($_POST['username']);
        $email = trim($_POST['email']);
        $password = $_POST['password'];
        
        if (empty($username) || empty($email) || empty($password)) {
            throw new Exception("All fields are required");
        }
        
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            throw new Exception("Invalid email format");
        }
        
        // Check if username already exists
        $checkStmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE username = ?");
        $checkStmt->execute([$username]);
        if ($checkStmt->fetchColumn() > 0) {
            throw new Exception("Username already taken");
        }
        
        // Hash password
        $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
        
        // Insert new user
        $insertStmt = $pdo->prepare("
            INSERT INTO users (username, email, password, created_at) 
            VALUES (?, ?, ?, ?)
        ");
        
        $created_at = date('Y-m-d H:i:s');
        $insertStmt->execute([$username, $email, $hashedPassword, $created_at]);
        
        $message = "Registration successful! User ID: " . $pdo->lastInsertId();
    } catch (Exception $e) {
        $error = $e->getMessage();
    }
}
?>

<!-- Registration Form HTML -->
<!DOCTYPE html>
<html>
<head>
    <title>User Registration</title>
</head>
<body>
    <h1>Create an Account</h1>
    
    <?php if (isset($message)): ?>
        <div style="color: green;"><?php echo $message; ?></div>
    <?php endif; ?>
    
    <?php if (isset($error)): ?>
        <div style="color: red;"><?php echo $error; ?></div>
    <?php endif; ?>
    
    <form method="post">
        <div>
            <label for="username">Username:</label>
            <input type="text" id="username" name="username" required>
        </div>
        <div>
            <label for="email">Email:</label>
            <input type="email" id="email" name="email" required>
        </div>
        <div>
            <label for="password">Password:</label>
            <input type="password" id="password" name="password" required>
        </div>
        <div>
            <button type="submit">Register</button>
        </div>
    </form>
</body>
</html>

Common Pitfalls and Best Practices

1. Not Escaping Like Parameters

A common misconception is that you need prepared statements only for exact matches. However, you should use them for LIKE queries too:

php

// INCORRECT way - still vulnerable to SQL injection
$search = "%{$_GET['search']}%";
$stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE '$search'");

// CORRECT way
$stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE ?");
$search = "%" . $_GET['search'] . "%";
$stmt->execute([$search]);

2. Forgetting to Bind All Parameters

Make sure to bind all the parameters in your query:

php

// Missing parameter binding for $status
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND status = ?");
$stmt->execute([$username]); // Wrong! Missing the status parameter

// Correct
$stmt->execute([$username, $status]);

3. Using String Concatenation with Prepared Statements

Avoid mixing string concatenation with prepared statements, as it defeats their purpose:

php

// INCORRECT - SQL Injection vulnerability still exists
$sort = $_GET['sort'];
$stmt = $pdo->prepare("SELECT * FROM products ORDER BY " . $sort);

// For dynamic table or column names, use whitelisting instead
$allowedColumns = ['name', 'price', 'date_added'];
$sort = in_array($_GET['sort'], $allowedColumns) ? $_GET['sort'] : 'name';
$stmt = $pdo->prepare("SELECT * FROM products ORDER BY " . $sort);

4. Reusing Prepared Statements

Take advantage of prepared statements' reusability for batch operations:

php

try {
    $pdo = new PDO('mysql:host=localhost;dbname=my_database', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Prepare once
    $stmt = $pdo->prepare("INSERT INTO logs (user_id, action, timestamp) VALUES (?, ?, ?)");
    
    $timestamp = date('Y-m-d H:i:s');
    
    // Execute multiple times with different parameters
    $stmt->execute([1, 'login', $timestamp]);
    $stmt->execute([2, 'update_profile', $timestamp]);
    $stmt->execute([3, 'logout', $timestamp]);
    
    echo "All logs inserted successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

PDO vs. MySQLi: Which Should You Choose?

Both PDO and MySQLi offer prepared statements, but they have different advantages:

PDO Advantages:

• Works with 12+ different database drivers (MySQL, PostgreSQL, SQLite, etc.)
• Consistent API across different databases
• Named parameters support
• More object-oriented interface

MySQLi Advantages:

• Specifically optimized for MySQL
• Supports both procedural and object-oriented styles
• Access to MySQL-specific features

For most new projects, PDO is recommended due to its flexibility and consistent API.

Summary

Prepared statements are an essential technique for secure PHP database programming. They provide:

1. Protection against SQL injection by separating SQL code from data
2. Performance benefits for repeatedly executed queries
3. Cleaner, more maintainable code

Both PDO and MySQLi provide robust support for prepared statements, though PDO is generally recommended for its database-agnostic approach and named parameters support.

Always use prepared statements whenever you're working with user input or any dynamic data in your database queries. It's one of the most important security practices in PHP development.

Exercises

1. Convert a basic SQL query to use prepared statements with PDO.
2. Create a simple CRUD application for a "products" table using MySQLi prepared statements.
3. Build a search form that uses prepared statements with LIKE clauses.
4. Implement a secure login system using prepared statements and password hashing.

Additional Resources

• PHP PDO Documentation
• PHP MySQLi Documentation
• OWASP SQL Injection Prevention Cheat Sheet
• PHP Security Best Practices