Prepared Statements in PHP (MySQLi)

1. What is a Prepared Statement?

• A Prepared Statement is a feature in MySQLi that allows you to execute SQL queries safely.
• Instead of directly putting user input into SQL, we first prepare the query with placeholders (?), and then bind actual values to these placeholders.
• This prevents SQL Injection (a hacking technique where users can insert malicious SQL code).

2. Benefits of Prepared Statements:

1. Security → Prevents SQL Injection.
2. Performance → The query is parsed only once, but can be executed multiple times with different values.
3. Readability → Easy to separate SQL query and values.

3. Syntax (MySQLi Prepared Statement)

Steps:

1. Prepare the query:

$stmt = $conn->prepare("SQL QUERY WITH ?");

2. Bind parameters:

$stmt->bind_param("type", $variable);

3. Execute the query:

$stmt->execute();

4. Get result (for SELECT):

$result = $stmt->get_result();

5. Close the statement:

$stmt->close();

4. Parameter Types

When binding parameters, we must specify data types:

5. Examples

A. Insert Data using Prepared Statement

<?php
$conn = new mysqli("localhost", "root", "", "mydatabase");

// Step 1: Prepare query with placeholders
$stmt = $conn->prepare("INSERT INTO students (name, email) VALUES (?, ?)");

// Step 2: Bind parameters (s = string, s = string)
$stmt->bind_param("ss", $name, $email);

// Step 3: Assign values
$name = "Alice";
$email = "alice@example.com";

// Step 4: Execute
$stmt->execute();

echo "New student inserted successfully";

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

Explanation

• ? → Placeholder for dynamic values.
• $stmt->bind_param("ss", $name, $email) → First s is for name (string), second s is for email (string).
• $stmt->execute() → Runs the query safely.

B. Select Data using Prepared Statement

<?php
$conn = new mysqli("localhost", "root", "", "mydatabase");

// Step 1: Prepare query
$stmt = $conn->prepare("SELECT id, name, email FROM students WHERE email = ?");

// Step 2: Bind parameter
$stmt->bind_param("s", $email);

// Step 3: Assign value
$email = "alice@example.com";

// Step 4: Execute query
$stmt->execute();

// Step 5: Get result
$result = $stmt->get_result();

// Step 6: Fetch data
while($row = $result->fetch_assoc()){
    echo "ID: " . $row["id"] . " Name: " . $row["name"] . " Email: " . $row["email"] . "<br>";
}

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

Explanation

• $stmt->get_result() → Gets result set from SELECT query.
• $result->fetch_assoc() → Fetches each row as an associative array.

C. Update Data using Prepared Statement

<?php
$conn = new mysqli("localhost", "root", "", "mydatabase");

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

// Bind parameters (s = string, i = integer)
$stmt->bind_param("si", $newEmail, $id);

// Assign values
$newEmail = "alice.new@example.com";
$id = 1;

// Execute
$stmt->execute();

echo "Record updated successfully";

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

D. Delete Data using Prepared Statement

<?php
$conn = new mysqli("localhost", "root", "", "mydatabase");

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

// Bind parameter (i = integer)
$stmt->bind_param("i", $id);

// Assign value
$id = 1;

// Execute
$stmt->execute();

echo "Record deleted successfully";

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

6. Explanation of Functions

7. Why Prepared Statements Prevent SQL Injection?

Without prepared statements:

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

If user enters:
' OR '1'='1 → The query becomes valid and may return all users.

With prepared statements:

$stmt = $conn->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $_POST['email']);

The value is treated strictly as data, not SQL. So injection fails.

Summary

• Prepared Statements protect against SQL Injection.
• Always use ? placeholders and bind_param().
• Parameter types: i = integer, s = string, d = double, b = blob.
• Steps: prepare → bind_param → execute → (get_result for SELECT) → close.
• Use Prepared Statements for Insert, Select, Update, Delete queries.