Prepared Statements

Introduction to Prepared Statements

Prepared statements are a powerful feature of PHP's MySQLi and PDO extensions that help prevent SQL injection attacks and improve performance. They allow you to execute the same SQL statement repeatedly with high efficiency, separating the SQL logic from the data being passed in.

Benefits of Prepared Statements

  • Security: Prepared statements mitigate SQL injection risks by separating SQL code from data.
  • Performance: They can enhance performance when executing the same statement multiple times.
  • Code Readability: They help to make code cleaner and easier to maintain.

Creating a Prepared Statement

To create a prepared statement, you first prepare the SQL statement, and then bind parameters to it. Here's how to do it with MySQLi:

    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";
    
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    
    // Prepare a SQL statement
    $stmt = $conn->prepare("INSERT INTO Users (username, email) VALUES (?, ?)");
    
    // Bind parameters
    $stmt->bind_param("ss", $username, $email);
    
    // Set parameters and execute
    $username = "JohnDoe";
    $email = "john@example.com";
    $stmt->execute();
    
    echo "New record created successfully";
    
    // Close statement
    $stmt->close();
    ?>

Expected Output

New record created successfully

Reading Data with Prepared Statements

Prepared statements can also be used for reading data from the database:

    <?php
    $stmt = $conn->prepare("SELECT id, username, email FROM Users WHERE email = ?");
    $stmt->bind_param("s", $email);
    
    // Set parameter and execute
    $email = "john@example.com";
    $stmt->execute();
    
    // Bind result variables
    $stmt->bind_result($id, $username, $email);
    
    // Fetch the values
    while ($stmt->fetch()) {
        echo "id: $id - Name: $username - Email: $email<br>";
    }
    
    // Close statement
    $stmt->close();
    ?>

Expected Output

id: 1 - Name: JohnDoe - Email: john@example.com

Updating Data with Prepared Statements

To update records using prepared statements:

    <?php
    $stmt = $conn->prepare("UPDATE Users SET email = ? WHERE username = ?");
    $stmt->bind_param("ss", $new_email, $username);
    
    // Set parameters and execute
    $new_email = "john.doe@example.com";
    $username = "JohnDoe";
    $stmt->execute();
    
    echo "Record updated successfully";
    
    // Close statement
    $stmt->close();
    ?>

Expected Output

Record updated successfully

Deleting Data with Prepared Statements

To delete records using prepared statements:

    <?php
    $stmt = $conn->prepare("DELETE FROM Users WHERE username = ?");
    $stmt->bind_param("s", $username);
    
    // Set parameter and execute
    $username = "JohnDoe";
    $stmt->execute();
    
    echo "Record deleted successfully";
    
    // Close statement
    $stmt->close();
    ?>

Expected Output

Record deleted successfully

Closing the Database Connection

As always, remember to close your database connection when you are done:

    <?php
    $conn->close();
    ?>

Summary

This tutorial introduced prepared statements in PHP, highlighting their security and performance advantages. You learned how to create, execute, and manage prepared statements for inserting, selecting, updating, and deleting data in a MySQL database.