What are prepared statements?

This is a method to enhance security and performance when working with databases, where the SQL query template is sent to the database server separately from the data itself.

Advantages:

• Protection against SQL injection: Data is processed by the database server as literals, not as part of the SQL code.
• Performance: The database server caches the execution plan of the query, saving time when executing the same query multiple times with different parameters.
• Convenience: Allows structuring code and simplifies debugging.

Stages of executing a prepared statement:

1. Preparation (Prepare): The query template is sent to the database server for analysis, syntax checking, and execution plan creation. Placeholders are used in the template to denote where data will be inserted.
2. Binding (Bind): Variable values are bound to the placeholders. The server does not interpret them as SQL code.
3. Execution (Execute): The query is executed with the bound values.

Example of using a prepared statement in PHP with PDO:

<?php
// Connecting to the database (example)
$dsn = 'mysql:host=localhost;dbname=mydatabase';
$user = 'myuser';
$password = 'mypassword';

try {
    $pdo = new PDO($dsn, $user, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die('Connection error: ' . $e->getMessage());
}

// Preparing a query with placeholders
$stmt = $pdo->prepare("SELECT name, email FROM users WHERE id = :id");

// Binding a value to the placeholder
$userId = 1;
$stmt->bindParam(':id', $userId, PDO::PARAM_INT);

// Executing the query
$stmt->execute();

// Fetching results
$user = $stmt->fetch(PDO::FETCH_ASSOC);

if ($user) {
    echo "User name: " . htmlspecialchars($user['name']) . "<br>";
    echo "User email: " . htmlspecialchars($user['email']) . "<br>";
} else {
    echo "User with ID " . htmlspecialchars($userId) . " not found.";
}

?>

In this example, :id is a named placeholder. Positional placeholders (?) can also be used.

<?php
// Preparing a query with a positional placeholder
$stmt = $pdo->prepare("SELECT name, email FROM users WHERE id = ?");

// Executing the query with an array of values for the placeholder
$userId = 2;
$stmt->execute([$userId]);

// Fetching results
$user = $stmt->fetch(PDO::FETCH_ASSOC);

if ($user) {
    echo "User name: " . htmlspecialchars($user['name']) . "<br>";
    echo "User email: " . htmlspecialchars($user['email']) . "<br>";
} else {
    echo "User with ID " . htmlspecialchars($userId) . " not found.";
}
?>