Safely Preparing SQL Queries with prepare()

The prepare() method prepares an SQL statement for execution and returns a statement object. Its primary job is to separate the SQL logic from the actual data. Instead of injecting direct values into the command, we use named parameters (e.g., :id). This allows the database to pre-analyze and optimize the query while completely blocking SQL injection attacks, as data is treated separately from the executable command.

EXAMPLE

<?php
class Database {
    private $server = "localhost";
    private $username = "root";
    private $password = "";
    private $db_name = "pdo_db";
    public $connection;

    public function __construct() {
        try {
            // Added charset=utf8mb4 for character support
            $dsn = "mysql:host=" . $this->server . ";dbname=" . $this->db_name . ";charset=utf8mb4";
            $this->connection = new PDO($dsn, $this->username, $this->password);
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            die("Connection failed: " . $e->getMessage());
        }
    }

    public function findUser($id) {
        // Prepare the statement with a named parameter :id
        $sql = "SELECT * FROM users WHERE id = :id";
        $statement = $this->connection->prepare($sql);
        
        // Execute the statement and safely pass the actual value
        $statement->execute(['id' => $id]);
        
        return $statement->fetch(PDO::FETCH_ASSOC);
    }
}

// PART 3: Usage
$db = new Database();
$user = $db->findUser(2);

if ($user) {
    echo "User found: " . $user['first_name'] . " " . $user['last_name'];
}
?>

RESULT

The result will be displayed here...

Practical Application: We always use the prepare method whenever an SQL statement contains data entered by a user (e.g., search, login, or profile editing). Check the process at home by following these steps:

Start the Apache and MySQL modules in the XAMPP control panel.
Ensure via phpMyAdmin that the users table exists within the pdo_db database.
The method returns a PDOStatement object, which you then execute using the execute() method.
Insert the example below into a PHP file and see how we safely find a user by their ID.