PHP Prepared Statements: Secure and Efficient Database Queries

When working with PHP and MySQL, security and performance are critical. Prepared statements help you write safer and more efficient SQL queries—especially when handling user input such as login forms, search queries, and form submissions.

In this tutorial, you’ll learn:

• What prepared statements are

• Why they are important

• How to use prepared statements with mysqli

• How to use prepared statements with PDO

• Examples for SELECT, INSERT, UPDATE, and DELETE

• Best practices for secure PHP MySQL code

What Are Prepared Statements?

A prepared statement is a feature used to execute the same SQL statement repeatedly with high efficiency and security.

It works in two steps:

1. Prepare – Send the SQL query structure (with placeholders) to the database.

2. Execute – Bind actual values to the placeholders and run the query.

Example structure:

SELECT * FROM users WHERE email = ? AND status = ?

The ? marks are placeholders for values that will be safely bound later.

Why Use Prepared Statements?

1. Protection Against SQL Injection

Prepared statements separate SQL logic from data, preventing attackers from injecting malicious SQL via input fields.

2. Cleaner and More Maintainable Code

No need to manually escape strings or concatenate values into SQL queries.

3. Performance Benefits

The database can reuse the prepared query structure when running similar statements multiple times.

Using Prepared Statements with mysqli

You can use prepared statements with mysqli in two styles:

• Object-oriented

• Procedural

We’ll use object-oriented style here.

Database Connection (db_connect.php)

<?php
$host = "localhost";
$user = "root";
$pass = "";
$dbname = "php_crud_demo";

$conn = new mysqli($host, $user, $pass, $dbname);

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

Example 1: INSERT Using Prepared Statement (mysqli)

<?php
include 'db_connect.php';

$name  = "Shubham";
$email = "[email protected]";
$city  = "Bhopal";

$stmt = $conn->prepare("INSERT INTO users (name, email, city) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $email, $city); // sss = 3 strings

if ($stmt->execute()) {
    echo "User inserted successfully";
} else {
    echo "Error: " . $stmt->error;
}

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

bind_param() Type Definitions

• s → string

• i → integer

• d → double

• b → blob (binary)

Example: "sid" = string, integer, double.

Example 2: SELECT Using Prepared Statement (mysqli)

<?php
include 'db_connect.php';

$email = "[email protected]";

$stmt = $conn->prepare("SELECT id, name, city FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();

$result = $stmt->get_result();

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - City: " . $row['city'] . "<br>";
    }
} else {
    echo "No user found.";
}

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

Example 3: UPDATE Using Prepared Statement (mysqli)

<?php
include 'db_connect.php';

$newCity = "Indore";
$id      = 1;

$stmt = $conn->prepare("UPDATE users SET city = ? WHERE id = ?");
$stmt->bind_param("si", $newCity, $id);

if ($stmt->execute()) {
    echo "User updated successfully";
} else {
    echo "Error: " . $stmt->error;
}

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

Example 4: DELETE Using Prepared Statement (mysqli)

<?php
include 'db_connect.php';

$id = 3;

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

if ($stmt->execute()) {
    echo "User deleted successfully";
} else {
    echo "Error: " . $stmt->error;
}

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

Using Prepared Statements with PDO

When working with databases in PHP, ensuring security and efficiency is crucial. One of the best ways to achieve this is by using Prepared Statements with PDO (PHP Data Objects).

Prepared statements allow you to execute database queries safely by separating SQL logic from user input. This helps prevent common security vulnerabilities like SQL injection attacks, making your application more secure and reliable.

Using PDO prepared statements also improves code readability and reusability, especially when executing the same query multiple times with different values. Overall, they are an essential practice for building secure, scalable, and professional PHP applications.

PDO (PHP Data Objects) is a database abstraction layer that supports multiple databases (MySQL, PostgreSQL, SQLite, etc.). It uses prepared statements by default.

PDO Connection

<?php
$host = "localhost";
$db   = "php_crud_demo";
$user = "root";
$pass = "";

$dsn = "mysql:host=$host;dbname=$db;charset=utf8";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

PDO Example: INSERT

<?php
include 'pdo_connect.php';

$name  = "Rahul";
$email = "[email protected]";
$city  = "Delhi";

$sql = "INSERT INTO users (name, email, city) VALUES (:name, :email, :city)";
$stmt = $pdo->prepare($sql);

$stmt->execute([
    ':name'  => $name,
    ':email' => $email,
    ':city'  => $city
]);

echo "User inserted successfully";
?>

PDO Example: SELECT

<?php
include 'pdo_connect.php';

$email = "[email protected]";

$sql = "SELECT id, name, city FROM users WHERE email = :email";
$stmt = $pdo->prepare($sql);
$stmt->execute([':email' => $email]);

$users = $stmt->fetchAll();

if ($users) {
    foreach ($users as $user) {
        echo $user['id'] . ' - ' . $user['name'] . ' - ' . $user['city'] . '<br>';
    }
} else {
    echo "No user found.";
}
?>

Best Practices with Prepared Statements

• Always use prepared statements for user input in SQL (forms, search boxes, login, etc.).

• Avoid building SQL strings by concatenating variables directly.

• Use appropriate data types (i, s, etc.) in bind_param().

• Validate and sanitize inputs even when using prepared statements.

• Catch and log database errors instead of showing them to users.

Summary

Prepared statements in PHP (using mysqli or PDO) are essential for:

• Securing your application from SQL injection

• Writing clean and maintainable database code

• Optimizing repeated query execution

As you build login systems, search features, admin panels, and APIs, make prepared statements your default way of working with SQL.