Prepared Statements

Introduction

Prepared Statements are a secure and efficient way to execute SQL queries in PHP. They help protect web applications from SQL Injection attacks and improve database performance when executing repeated queries.

Prepared Statements separate SQL code from user input, making database operations safer and more reliable.

Objectives

By the end of this training, you will be able to:

• Understand what Prepared Statements are
• Learn why Prepared Statements are important
• Create secure database queries in PHP
• Insert, update, delete, and retrieve data safely
• Prevent SQL Injection attacks
• Use MySQLi Prepared Statements in PHP

What are Prepared Statements

A Prepared Statement is a precompiled SQL query where placeholders are used instead of direct user input.

The database prepares the SQL query first, and then the values are added separately.

This process improves:

• Security
• Performance
• Code readability

Why Use Prepared Statements

Prevent SQL Injection

Prepared Statements protect databases from malicious SQL code entered by users.

Improve Performance

Queries executed multiple times are processed faster because the SQL statement is compiled once.

Better Data Handling

Prepared Statements automatically handle special characters and data types correctly.

Database Connection in PHP

<?php
$conn = mysqli_connect("localhost", "root", "", "training_db");

if (!$conn) {
    die("Connection failed");
}
?>

Inserting Data Using Prepared Statements

<?php
$name = "Ali";
$email = "ali@example.com";

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");

$stmt->bind_param("ss", $name, $email);

$stmt->execute();

echo "Data inserted successfully";
?>

Understanding the Code

prepare()

The prepare() method creates a prepared SQL statement.

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");

Question marks act as placeholders for values.

bind_param()

The bind_param() method binds variables to the placeholders.

$stmt->bind_param("ss", $name, $email);

The first parameter defines data types.

• s = string
• i = integer
• d = double
• b = blob

execute()

The execute() method runs the SQL query.

$stmt->execute();

Selecting Data Using Prepared Statements

<?php
$id = 1;

$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");

$stmt->bind_param("i", $id);

$stmt->execute();

$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['name'];
}
?>

Updating Data Using Prepared Statements

<?php
$name = "Ahmed";
$id = 1;

$stmt = $conn->prepare("UPDATE users SET name = ? WHERE id = ?");

$stmt->bind_param("si", $name, $id);

$stmt->execute();

echo "Record updated successfully";
?>

Deleting Data Using Prepared Statements

<?php
$id = 2;

$stmt = $conn->prepare("DELETE FROM users WHERE id = ?");

$stmt->bind_param("i", $id);

$stmt->execute();

echo "Record deleted successfully";
?>

Benefits of Prepared Statements

• Increased database security
• Protection against SQL Injection
• Faster repeated query execution
• Cleaner and more organized code
• Better handling of user input

Common Mistakes to Avoid

• Forgetting to bind parameters
• Using incorrect data types
• Not checking database connection errors
• Executing queries without validation

Best Practices

• Always use Prepared Statements for user input
• Validate and sanitize data before processing
• Close statements after execution
• Use error handling for database operations

Closing Prepared Statements

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

Real World Applications

Prepared Statements are commonly used in:

• Login systems
• Registration forms
• E-commerce websites
• Student management systems
• Banking applications
• Online booking systems

Conclusion

Prepared Statements are an essential part of secure PHP development. They improve security, performance, and reliability when working with databases. Every modern PHP application should use Prepared Statements to safely manage user data and database interactions.