PHP Prepared Statements with PDO

Introduction to Prepared Statements

Prepared statements are a crucial security feature in PHP database programming that help prevent SQL injection attacks while also improving performance for repeated queries. They work by separating SQL code from data, ensuring that user input is treated as data rather than executable code.

The SQL Injection Threat

SQL injection remains one of the most dangerous web application vulnerabilities. When user input is directly concatenated into SQL queries, attackers can inject malicious SQL code that can:

Bypass authentication systems
Extract sensitive data from databases
Modify or delete database records
Execute administrative operations on the database

Prepared statements eliminate this threat by ensuring that user input can never be interpreted as SQL code, regardless of its content.

What are Prepared Statements?

A prepared statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. The statement template is sent to the database server, which parses, compiles, and performs query optimization on it. Later, the application binds parameters and executes the statement.

How Prepared Statements Work

The process involves two distinct phases:

Preparation Phase:

SQL template with placeholders is sent to the database
Database parses and validates the SQL structure
Query execution plan is created and optimized
Statement is stored for later use

Execution Phase:

Application binds actual values to placeholders
Database executes the pre-compiled statement with provided data
Results are returned to the application

This separation ensures that data values can never alter the SQL structure, providing ironclad protection against SQL injection.

Benefits of Prepared Statements:

Security: Protection against SQL injection attacks
- User input is never interpreted as SQL code
- Special characters are automatically escaped
- No need for manual sanitization
Performance: Better performance for repeated queries
- Query parsing and optimization happens only once
- Reduced network traffic for repeated executions
- Database can cache execution plans
Type Safety: Automatic data type handling
- PDO handles type conversions appropriately
- Prevents type-related errors
- Ensures data integrity
Clean Code: Separation of SQL logic from data
- More readable and maintainable code
- Clear distinction between structure and content
- Easier debugging and testing

Basic Prepared Statement Syntax

Here's the basic workflow for using prepared statements with PDO:

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

Code Breakdown:

Connection Setup: The PDO constructor creates a database connection. Setting ERRMODE_EXCEPTION ensures that database errors throw exceptions, enabling proper error handling.

Statement Preparation: The prepare() method sends the SQL template to the database. The ? placeholders indicate where data will be inserted. At this stage, the database validates the SQL syntax and prepares the execution plan.

Parameter Binding and Execution: The execute() method sends the actual data values to fill the placeholders. The array order must match the placeholder positions. PDO ensures these values are properly escaped and quoted.

Result Retrieval: The fetch() method retrieves the query results. FETCH_ASSOC returns an associative array with column names as keys.

Named Parameters vs Positional Parameters

PDO supports two styles of parameter placeholders, each with distinct advantages:

Positional Parameters (?)

<?php
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
$stmt->execute(['John Doe', '[email protected]', 30]);
?>

Positional Parameter Characteristics:

Order Matters: Values must be provided in the exact order of placeholders
Simplicity: Clean syntax for simple queries
Numeric Binding: Can bind by position number (1-indexed)
Best For: Simple queries with few parameters

Named Parameters (:parameter)

<?php
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");
$stmt->execute([
    ':name' => 'John Doe',
    ':email' => '[email protected]',
    ':age' => 30
]);
?>

Named Parameter Advantages:

Self-Documenting: Parameter purpose is clear from the name
Order Independence: Array keys match parameter names, order doesn't matter
Reusability: Same parameter can be used multiple times in the query
Readability: Especially beneficial for complex queries with many parameters
Maintenance: Adding or removing parameters is easier

Choosing Between Styles:

Use positional for simple queries with 1-3 parameters
Use named for complex queries or when parameters might be reordered
Be consistent within your application

Binding Parameters

PDO offers two methods for binding parameters, each with specific use cases:

Using bindParam()

<?php
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > ? AND city = ?");

$minAge = 25;
$city = 'New York';

$stmt->bindParam(1, $minAge, PDO::PARAM_INT);
$stmt->bindParam(2, $city, PDO::PARAM_STR);

$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

bindParam() Characteristics:

Reference Binding: Binds variables by reference, not by value
Dynamic Values: Variable values are read at execution time
Type Specification: Allows explicit data type declaration
Loop Friendly: Useful when executing the same statement with changing values

Important Note: Since bindParam() binds by reference, changing the variable value after binding but before execution will use the new value.

Using bindValue()

<?php
$stmt = $pdo->prepare("UPDATE users SET last_login = :login_time WHERE id = :user_id");

$stmt->bindValue(':login_time', date('Y-m-d H:i:s'));
$stmt->bindValue(':user_id', 123, PDO::PARAM_INT);

$stmt->execute();
?>

bindValue() Characteristics:

Value Binding: Binds the actual value at the time of binding
Immutable: Changing the source variable after binding has no effect
Immediate Evaluation: Expressions are evaluated when bindValue() is called
Memory Efficient: For large loops, prevents reference retention

When to Use Each:

bindParam(): When values might change between bindings or in loops
bindValue(): When binding literals, expressions, or fixed values
execute(): When you have all values ready in an array

Data Types and Parameter Binding

Proper data type specification ensures accurate data handling and optimal performance:

PDO Parameter Types

<?php
// String parameter
$stmt->bindParam(':name', $name, PDO::PARAM_STR);

// Integer parameter
$stmt->bindParam(':age', $age, PDO::PARAM_INT);

// Boolean parameter
$stmt->bindParam(':active', $isActive, PDO::PARAM_BOOL);

// NULL parameter
$stmt->bindParam(':notes', $notes, PDO::PARAM_NULL);

// Large object (LOB) parameter
$stmt->bindParam(':document', $document, PDO::PARAM_LOB);
?>

Data Type Details:

PDO::PARAM_STR (default):

Used for all string data
Adds quotes and escapes special characters
Safe for any text content including HTML and JSON

PDO::PARAM_INT:

Ensures integer values
No quotes added in SQL
Prevents string-to-number conversion issues

PDO::PARAM_BOOL:

Converts PHP booleans to database format
Usually becomes 0/1 or true/false depending on database

PDO::PARAM_NULL:

Explicitly binds NULL value
Useful for nullable columns
Different from empty string

PDO::PARAM_LOB:

For binary large objects (BLOBs)
Used with file uploads or binary data
Requires special handling for streams

SELECT Operations with Prepared Statements

Basic SELECT

<?php
function getUsersByAge($pdo, $minAge) {
    $stmt = $pdo->prepare("SELECT id, name, email FROM users WHERE age >= ?");
    $stmt->execute([$minAge]);
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Usage
$users = getUsersByAge($pdo, 21);
foreach ($users as $user) {
    echo $user['name'] . " - " . $user['email'] . "\n";
}
?>

Function Design Principles:

Single Responsibility: Function does one thing - retrieves users by age
Parameter Safety: Prepared statement prevents SQL injection
Return Type: Returns array for flexibility
Error Propagation: Exceptions bubble up to caller

SELECT with Multiple Conditions

<?php
function searchUsers($pdo, $searchTerm, $city, $minAge) {
    $sql = "SELECT * FROM users 
            WHERE (name LIKE :search OR email LIKE :search) 
            AND city = :city 
            AND age >= :min_age 
            ORDER BY name";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':search' => "%{$searchTerm}%",
        ':city' => $city,
        ':min_age' => $minAge
    ]);
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
?>

Advanced Query Techniques:

LIKE Pattern Matching:

Wildcards (%) must be added to the value, not the SQL
This prevents SQL injection while enabling pattern matching
Same parameter (:search) can be used multiple times

Query Formatting:

Multi-line SQL improves readability
Logical grouping with parentheses clarifies conditions
ORDER BY ensures consistent result ordering

Parameter Reuse:

Named parameters can appear multiple times in the query
Each occurrence uses the same bound value
Reduces parameter count and improves clarity

INSERT Operations

Single INSERT

<?php
function createUser($pdo, $userData) {
    $sql = "INSERT INTO users (name, email, age, city, created_at) 
            VALUES (:name, :email, :age, :city, :created_at)";
    
    $stmt = $pdo->prepare($sql);
    
    return $stmt->execute([
        ':name' => $userData['name'],
        ':email' => $userData['email'],
        ':age' => $userData['age'],
        ':city' => $userData['city'],
        ':created_at' => date('Y-m-d H:i:s')
    ]);
}

// Usage
$newUser = [
    'name' => 'Jane Smith',
    'email' => '[email protected]',
    'age' => 28,
    'city' => 'Boston'
];

if (createUser($pdo, $newUser)) {
    echo "User created successfully. ID: " . $pdo->lastInsertId();
}
?>

INSERT Best Practices:

Data Organization:

Accept data as associative array for flexibility
Add timestamps at the application level for consistency
Return boolean for success/failure indication

Auto-Increment Handling:

lastInsertId() retrieves the auto-generated ID
Only reliable immediately after the INSERT
Thread-safe within the same connection

Error Handling:

Unique constraint violations will throw exceptions
Caller should handle specific database errors appropriately

Batch INSERT

<?php
function insertMultipleUsers($pdo, $users) {
    $sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
    $stmt = $pdo->prepare($sql);
    
    $pdo->beginTransaction();
    
    try {
        foreach ($users as $user) {
            $stmt->execute([$user['name'], $user['email'], $user['age']]);
        }
        $pdo->commit();
        return true;
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}
?>

Batch Processing Strategy:

Transaction Usage:

Groups multiple INSERTs into atomic operation
All succeed or all fail - maintains data consistency
Significantly improves performance for bulk operations

Prepared Statement Reuse:

Statement prepared once, executed multiple times
Reduces parsing overhead
Optimal for repetitive operations

Error Recovery:

Rollback ensures database remains consistent
Re-throws exception for proper error handling
Caller can decide how to handle the failure

UPDATE Operations

<?php
function updateUser($pdo, $userId, $updateData) {
    $sql = "UPDATE users SET name = :name, email = :email, age = :age, updated_at = :updated_at WHERE id = :id";
    
    $stmt = $pdo->prepare($sql);
    
    return $stmt->execute([
        ':id' => $userId,
        ':name' => $updateData['name'],
        ':email' => $updateData['email'],
        ':age' => $updateData['age'],
        ':updated_at' => date('Y-m-d H:i:s')
    ]);
}

// Update user with validation
function updateUserSafely($pdo, $userId, $updateData) {
    // First check if user exists
    $checkStmt = $pdo->prepare("SELECT id FROM users WHERE id = ?");
    $checkStmt->execute([$userId]);
    
    if (!$checkStmt->fetch()) {
        throw new Exception("User not found");
    }
    
    return updateUser($pdo, $userId, $updateData);
}
?>

UPDATE Considerations:

Timestamp Management:

Always update updated_at for audit trails
Use consistent timezone (preferably UTC)
Consider using database CURRENT_TIMESTAMP for accuracy

Existence Checking:

Prevents silent failures when ID doesn't exist
Provides meaningful error messages
Could use row count instead for simpler check

Partial Updates:

Consider allowing partial updates (only changed fields)
Requires dynamic SQL building
Maintains data integrity for unchanged fields

DELETE Operations

<?php
function deleteUser($pdo, $userId) {
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
    return $stmt->execute([$userId]);
}

function deleteUsersByAge($pdo, $maxAge) {
    $stmt = $pdo->prepare("DELETE FROM users WHERE age > ?");
    $stmt->execute([$maxAge]);
    
    return $stmt->rowCount(); // Returns number of affected rows
}
?>

DELETE Safety Measures:

Single Record Deletion:

Use primary key for precise targeting
Consider soft deletes (status flag) instead of hard deletes
Log deletions for audit purposes

Bulk Deletion:

Return affected row count for verification
Use transactions for related deletions
Consider archiving before deletion

Cascading Deletes:

Database foreign key constraints handle related records
Application-level cascading gives more control
Always document deletion impacts

Advanced Prepared Statement Techniques

Reusing Prepared Statements

<?php
class UserRepository {
    private $pdo;
    private $insertStmt;
    private $updateStmt;
    private $deleteStmt;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
        $this->prepareStatements();
    }
    
    private function prepareStatements() {
        $this->insertStmt = $this->pdo->prepare(
            "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
        );
        
        $this->updateStmt = $this->pdo->prepare(
            "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?"
        );
        
        $this->deleteStmt = $this->pdo->prepare(
            "DELETE FROM users WHERE id = ?"
        );
    }
    
    public function create($name, $email, $age) {
        return $this->insertStmt->execute([$name, $email, $age]);
    }
    
    public function update($id, $name, $email, $age) {
        return $this->updateStmt->execute([$name, $email, $age, $id]);
    }
    
    public function delete($id) {
        return $this->deleteStmt->execute([$id]);
    }
}
?>

Repository Pattern Benefits:

Performance Optimization:

Statements prepared once per object lifetime
Significant savings for frequently used queries
Reduced database server load

Code Organization:

Encapsulates database operations
Provides clean API for data access
Easier to mock for testing

Connection Management:

Single PDO instance shared across operations
Ensures consistent transaction boundaries
Simplifies connection configuration

Dynamic WHERE Clauses

<?php
function buildDynamicQuery($pdo, $filters) {
    $sql = "SELECT * FROM users WHERE 1=1";
    $params = [];
    
    if (!empty($filters['name'])) {
        $sql .= " AND name LIKE ?";
        $params[] = "%{$filters['name']}%";
    }
    
    if (!empty($filters['city'])) {
        $sql .= " AND city = ?";
        $params[] = $filters['city'];
    }
    
    if (!empty($filters['min_age'])) {
        $sql .= " AND age >= ?";
        $params[] = $filters['min_age'];
    }
    
    if (!empty($filters['max_age'])) {
        $sql .= " AND age <= ?";
        $params[] = $filters['max_age'];
    }
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
?>

Dynamic Query Building Strategy:

Base Query Technique:

WHERE 1=1 provides consistent starting point
Allows all conditions to use AND
Prevents syntax errors from missing conditions

Progressive Building:

Each filter adds its own condition
Parameters array maintains order
Empty filters are naturally skipped

Security Maintained:

Still uses prepared statements
No string concatenation of user input
Safe from SQL injection

Error Handling with Prepared Statements

<?php
function handleDatabaseOperation($pdo, $sql, $params) {
    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    } catch (PDOException $e) {
        // Log the error
        error_log("Database error: " . $e->getMessage());
        
        // Handle specific error types
        switch ($e->getCode()) {
            case 23000: // Integrity constraint violation
                throw new Exception("Data violates database constraints");
            case 42S02: // Table doesn't exist
                throw new Exception("Database table not found");
            default:
                throw new Exception("Database operation failed");
        }
    }
}
?>

Error Handling Strategy:

PDO Error Codes:

Standardized SQLSTATE codes across databases
Enable specific error handling
Better user experience through meaningful messages

Logging vs Display:

Log detailed technical errors for debugging
Show generic messages to users
Never expose database structure in error messages

Exception Translation:

Convert technical PDOExceptions to application exceptions
Preserve error context for debugging
Provide actionable error messages

Best Practices

1. Always Use Prepared Statements for User Input

<?php
// Bad - vulnerable to SQL injection
$sql = "SELECT * FROM users WHERE email = '{$_POST['email']}'";

// Good - safe from SQL injection
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$_POST['email']]);
?>

Security Principle: Never trust user input. Even if you validate input, always use prepared statements as the last line of defense against SQL injection.

2. Validate Input Before Database Operations

<?php
function createUserSafely($pdo, $userData) {
    // Validate email
    if (!filter_var($userData['email'], FILTER_VALIDATE_EMAIL)) {
        throw new InvalidArgumentException("Invalid email format");
    }
    
    // Validate age
    if ($userData['age'] < 0 || $userData['age'] > 150) {
        throw new InvalidArgumentException("Invalid age");
    }
    
    // Check if email already exists
    $checkStmt = $pdo->prepare("SELECT id FROM users WHERE email = ?");
    $checkStmt->execute([$userData['email']]);
    
    if ($checkStmt->fetch()) {
        throw new Exception("Email already exists");
    }
    
    // Create user
    $stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
    return $stmt->execute([$userData['name'], $userData['email'], $userData['age']]);
}
?>

Validation Layers:

Input Validation:

Check data format and constraints
Provide clear error messages
Fail fast with specific exceptions

Business Rule Validation:

Check uniqueness constraints
Verify business logic requirements
Prevent duplicate data

Defense in Depth:

Multiple validation layers
Database constraints as final safeguard
Clear separation of concerns

<?php
function transferUserData($pdo, $fromUserId, $toUserId, $amount) {
    $pdo->beginTransaction();
    
    try {
        // Deduct from source user
        $deductStmt = $pdo->prepare("UPDATE users SET balance = balance - ? WHERE id = ? AND balance >= ?");
        $deductResult = $deductStmt->execute([$amount, $fromUserId, $amount]);
        
        if ($deductStmt->rowCount() === 0) {
            throw new Exception("Insufficient balance or user not found");
        }
        
        // Add to destination user
        $addStmt = $pdo->prepare("UPDATE users SET balance = balance + ? WHERE id = ?");
        $addStmt->execute([$amount, $toUserId]);
        
        // Log the transaction
        $logStmt = $pdo->prepare("INSERT INTO transactions (from_user, to_user, amount, created_at) VALUES (?, ?, ?, ?)");
        $logStmt->execute([$fromUserId, $toUserId, $amount, date('Y-m-d H:i:s')]);
        
        $pdo->commit();
        return true;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}
?>

Transaction Benefits:

Atomicity: All operations succeed or all fail together. This prevents partial updates that could leave data in an inconsistent state.

Consistency: Database integrity is maintained throughout the operation. Balance is never lost or created.

Isolation: Other connections don't see partial changes. The transfer appears instantaneous to other users.

Durability: Once committed, changes persist even if the system crashes immediately after.

Performance Considerations

1. Prepare Once, Execute Many

<?php
// Efficient for multiple similar operations
$stmt = $pdo->prepare("INSERT INTO logs (user_id, action, timestamp) VALUES (?, ?, ?)");

foreach ($logEntries as $entry) {
    $stmt->execute([$entry['user_id'], $entry['action'], $entry['timestamp']]);
}
?>

Performance Benefits:

Query parsed and optimized only once
Execution plan cached by database
Reduced network overhead
Faster than individual queries

2. Use Appropriate Fetch Methods

<?php
// For single row
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([123]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// For multiple rows
$stmt = $pdo->prepare("SELECT * FROM users WHERE city = ?");
$stmt->execute(['New York']);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// For large result sets, use fetchAll with caution
// Consider using a while loop with fetch() instead

Fetch Method Selection:

fetch():

Retrieves one row at a time
Memory efficient for large result sets
Use in loops for processing individual records

fetchAll():

Loads entire result set into memory
Convenient for small to medium result sets
Can cause memory issues with large data

Fetch Styles:

FETCH_ASSOC: Associative array (column names as keys)
FETCH_NUM: Numeric array (indexed by column number)
FETCH_BOTH: Both associative and numeric (default)
FETCH_OBJ: Anonymous objects
FETCH_CLASS: Instances of specified class

Choose the appropriate method based on your memory constraints and processing needs.