Introduction to Prepared Statements in MySQLi

Table of Contents

• What Are Prepared Statements?
• Why Use Prepared Statements?
  • Security: Preventing SQL Injection
  • Performance Benefits
• Understanding the Structure of Prepared Statements
  • Steps Involved in Using Prepared Statements
• How to Use Prepared Statements in PHP with MySQLi
  • Creating a Prepared Statement for Insertion
  • Creating a Prepared Statement for Selection
  • Creating a Prepared Statement for Update
  • Creating a Prepared Statement for Deletion
• Binding Parameters
  • What Are Bound Parameters?
  • Types of Bound Parameters in MySQLi
• Error Handling with Prepared Statements
• Summary

What Are Prepared Statements?

Prepared statements are a feature of MySQLi (and other database drivers) that allow you to safely execute SQL queries by separating the query structure from the data being inserted, updated, or retrieved. This approach helps secure your application against SQL injection attacks, a common vulnerability when user input is directly included in SQL queries.

In a typical SQL query, data and query structure are combined in a single string. This can be dangerous when the data comes from an untrusted source, such as a user’s input. For example, without prepared statements, a query like the following could be easily exploited:

$sql = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'";

If an attacker submits a malicious email address, such as ' OR 1=1 --, it can alter the query’s behavior, potentially exposing sensitive data.

Prepared statements address this issue by allowing you to define placeholders for data, ensuring that user input is treated only as data and never as part of the SQL query structure.

Why Use Prepared Statements?

Security: Preventing SQL Injection

The most critical reason for using prepared statements is security. By separating the query structure from the user input, prepared statements ensure that user data is never directly executed as part of the SQL query. This prevents SQL injection attacks.

For example, if an attacker tries to insert a string like '; DROP TABLE users; --, the database will treat it as data, not as part of the SQL query, keeping your database safe.

Performance Benefits

Prepared statements also offer performance benefits, especially when executing the same query multiple times with different data. The SQL query structure is sent to the database server only once, and subsequent executions of the query only send the data to be inserted or updated. This can reduce the workload on the database server and increase efficiency, particularly in high-traffic applications.

Understanding the Structure of Prepared Statements

A prepared statement involves the following steps:

1. Prepare: The SQL query is sent to the database with placeholders for the values.
2. Bind: The actual values are bound to the placeholders.
3. Execute: The query is executed with the bound values.
4. Close: The prepared statement is closed when it is no longer needed.

Prepared statements typically consist of placeholders that are marked with a question mark (?) where the actual data will be inserted later.

How to Use Prepared Statements in PHP with MySQLi

Let’s dive into the process of using prepared statements in PHP with MySQLi for various operations like inserting, selecting, updating, and deleting data.

Creating a Prepared Statement for Insertion

Here’s how you can safely insert data into the database using a prepared statement:

<?php
// Database credentials
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sample_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare and bind
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);

// Set parameters and execute
$name = "John Doe";
$email = "[email protected]";
$stmt->execute();

echo "New record created successfully";

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

In this example:

• The prepare() method prepares the SQL query with placeholders (?) for the name and email fields.
• The bind_param() method binds the actual data to the placeholders. The first argument, "ss", indicates that both name and email are strings.

Creating a Prepared Statement for Selection

To safely select data from the database, use a prepared statement as follows:

<?php
// Prepare and bind
$stmt = $conn->prepare("SELECT id, name, email FROM users WHERE id = ?");
$stmt->bind_param("i", $id);

// Set parameters and execute
$id = 1;
$stmt->execute();

// Bind result variables
$stmt->bind_result($id, $name, $email);

// Fetch the result
if ($stmt->fetch()) {
    echo "id: " . $id . " - Name: " . $name . " - Email: " . $email;
} else {
    echo "No user found.";
}

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

In this example:

• The bind_param() method binds the id to the placeholder.
• The bind_result() method binds the columns of the result set to variables.
• The fetch() method fetches the result, making it available for display.

Creating a Prepared Statement for Update

Updating records with prepared statements is also simple:

<?php
// Prepare and bind
$stmt = $conn->prepare("UPDATE users SET email = ? WHERE id = ?");
$stmt->bind_param("si", $email, $id);

// Set parameters and execute
$email = "[email protected]";
$id = 1;
$stmt->execute();

echo "Record updated successfully";

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

Here, the bind_param() method binds the email and id values to the placeholders. The first argument "si" indicates that email is a string and id is an integer.

Creating a Prepared Statement for Deletion

To delete records, use the following approach:

<?php
// Prepare and bind
$stmt = $conn->prepare("DELETE FROM users WHERE id = ?");
$stmt->bind_param("i", $id);

// Set parameters and execute
$id = 1;
$stmt->execute();

echo "Record deleted successfully";

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

This example deletes the user with the specified id from the users table.

Binding Parameters

What Are Bound Parameters?

Bound parameters are the actual values that replace the placeholders (?) in the SQL query. Binding parameters ensures that user input is treated as data, not executable code, thereby preventing SQL injection.

Types of Bound Parameters in MySQLi

When binding parameters, the first argument passed to bind_param() specifies the type of the data:

• “i”: Integer
• “d”: Double (for floating-point numbers)
• “s”: String
• “b”: Blob (binary data)

For example, if you’re binding an integer and a string, you would use "is".

Error Handling with Prepared Statements

Error handling is essential to ensure that your application can handle any issues with the database operations gracefully. For instance, you can check if the prepare() or execute() functions return false, indicating an error:

if ($stmt === false) {
    echo "Error in statement preparation: " . $conn->error;
}

if ($stmt->execute() === false) {
    echo "Error in statement execution: " . $stmt->error;
}

You can also configure MySQLi to report errors more explicitly:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

This ensures that MySQLi throws exceptions on errors, which can be caught and handled properly in your application.

Summary

In this module, we introduced prepared statements in PHP with MySQLi, covering their importance in preventing SQL injection and improving performance. We walked through the process of using prepared statements for insertion, selection, updating, and deletion. Additionally, we discussed binding parameters and the types of parameters used in prepared statements, along with error handling to ensure smooth execution.