PHP Prepared Statements

A **Prepared Statement** is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency. Most importantly, it is the standard defense against **SQL Injection** attacks.

Why Use Prepared Statements?

• Security: They separate the SQL query logic from the data. The database treats the data as "literal values," meaning hackers cannot inject malicious commands.
• Performance: The database compiles and optimizes the query plan only once. You can then execute it many times with different data.
• Code Cleanliness: No more messy string concatenations or worrying about manual escaping.

How It Works (The 3 Steps)

1. Prepare: An SQL statement template is created and sent to the database. Parameters are replaced by question marks (?).
2. Bind: You "bind" your PHP variables to the question marks in the template.
3. Execute: The database executes the statement using the provided values.

1. MySQLi (Object-Oriented)

In MySQLi, you use prepare() and then bind_param() to link your variables.

<?php
    $stmt = $conn->prepare("INSERT INTO Users (firstname, lastname, email) VALUES (?, ?, ?)");
    
    // "sss" means the three parameters are all strings
    $stmt->bind_param("sss", $firstname, $lastname, $email);

    // Set parameters and execute
    $firstname = "John";
    $lastname = "Doe";
    $email = "john@example.com";
    $stmt->execute();

    echo "New records created successfully";

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

2. PDO (PHP Data Objects)

PDO is even more powerful because it supports **Named Parameters**, which make your code much more readable.

<?php
    try {
        $stmt = $conn->prepare("INSERT INTO Users (firstname, lastname, email) 
                               VALUES (:firstname, :lastname, :email)");
        
        $stmt->bindParam(':firstname', $firstname);
        $stmt->bindParam(':lastname', $lastname);
        $stmt->bindParam(':email', $email);

        // Insert one row
        $firstname = "Jane";
        $lastname = "Smith";
        $email = "jane@example.com";
        $stmt->execute();

        echo "New record created successfully";
    } catch(PDOException $e) {
        echo "Error: " . $e->getMessage();
    }
    $conn = null;
?>

Summary

• Prepared statements are mandatory for any production web application using user data.
• They stop SQL Injection by design.
• MySQLi uses question mark placeholders (?).
• PDO supports both question marks and named parameters (:name).

What's Next?

We've covered the core PHP & MySQL operations! Now let's dive into **Advanced Topics**, starting with how PHP handles **JSON** data.