PHP and MySQL: Prepared Statements and SQL Injection (Security)

Using variables directly in your SQL queries, as we did in the previous chapter, exposes your application to a critical vulnerability called SQL Injection. Prepared Statements are the industry-standard method used to prevent this.

1. What is SQL Injection?

SQL Injection is a technique where an attacker inserts malicious SQL code into a form input field (like a username or password). If your PHP code doesn’t filter this input, the malicious code can be executed by the database, leading to:

• Deletion of entire database tables.
• Theft of sensitive user information.
• Bypassing login screens.

Vulnerable Code Example:

If the user enters the following malicious input into the user_id field: 10 OR 1=1

The original query:

$sql = “SELECT * FROM Users WHERE id = $user_id”;

Becomes the malicious query:

SELECT * FROM Users WHERE id = 10 OR 1=1;

Since 1=1 is always TRUE, the database will return all users, granting unauthorized access.

2. The Solution: Prepared Statements

A Prepared Statement works by sending the SQL structure (the template) to the database server first, and the data (the input) separately later. The database treats the input as pure data, never as executable SQL code.

We use special placeholder characters (?) in the SQL structure where the data will eventually go.

Using Prepared Statements with MySQLi

This process requires four primary steps using the MySQLi Object-Oriented approach:

Step 1: Prepare the Statement

You use the $conn->prepare() method, replacing all user inputs with question mark (?) placeholders.

<?php
    // The connection ($conn) is assumed to be open

    // The question marks are placeholders for the data
    $sql = "INSERT INTO Students (firstname, email) VALUES (?, ?)";
    
    // Prepare the statement object
    $stmt = $conn->prepare($sql); 
?>

Step 2: Bind Parameters

You use the $stmt->bind_param() method to tell the database what type of data is going into each placeholder.

<?php
    $first = "David";
    $email = "david@example.com";

    // 'ss' indicates that both placeholders expect a string
    $stmt->bind_param("ss", $first, $email);
?>

Step 3: Execute the Statement

You run the prepared and bound statement.

<?php
    $stmt->execute();
    echo "New record created securely!";
?>

Step 4: Close the Statement

Always close the statement object when you are done.

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

Full Prepared Statement Example (Secure INSERT)

<?php
    // 1. Setup connection ($conn)
    $servername = "localhost";
    // ... (rest of connection details) ...
    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); }

    // 2. Prepare the statement with placeholders
    $sql = "INSERT INTO Students (firstname, email) VALUES (?, ?)";
    $stmt = $conn->prepare($sql);

    // 3. Bind the parameters ('ss' for two strings)
    $stmt->bind_param("ss", $firstName, $emailAddress);

    // 4. Set variables and execute
    $firstName = "Elara";
    $emailAddress = "elara@example.com";
    $stmt->execute();

    $firstName = "Zoe";
    $emailAddress = "zoe@example.com";
    $stmt->execute(); // Reuse the same prepared statement for multiple inserts!

    echo "Two new records added securely.";
    
    // 5. Close the statement and connection
    $stmt->close();
    $conn->close();
?>

Next Steps

Security is now covered! You have mastered the entire lifecycle of data: user input, storage, retrieval, and protection. The next set of chapters should cover the missing foundational topics like Strings and Dates to complete your core PHP toolkit.