MySQL Prepared

PHP Prepared Statements for MySQL

Prepared statements are a critical feature for database security, specifically for preventing SQL injection vulnerabilities, and often improve performance.

Prepared Statements and Bound Parameters

A prepared statement is a pre-compiled SQL template that can be executed multiple times with different parameters. The process generally involves two stages:

Advantages over executing raw SQL strings:

• Security (Primary Benefit): Prepared statements fundamentally prevent SQL injection. Since the SQL code template and the data values are sent separately, the database engine doesn’t interpret the bound data as SQL commands, even if the data contains malicious SQL syntax.
• Efficiency: The database only needs to parse, compile, and optimize the SQL query template once. Subsequent executions with different parameters are faster as they reuse the pre-compiled plan.
• Reduced Bandwidth: For repeated executions, only the (often smaller) parameter values need to be sent to the server each time, not the entire SQL query string.

Rule of Thumb: Always use prepared statements whenever user input or any variable data is incorporated into an SQL query.

Prepared Statements in MySQLi

MySQLi supports prepared statements using positional placeholders (?).

Example:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "myDB"; // Assumes myDB database exists

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

// 1. Prepare the statement with placeholders (?)
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
if ($stmt === false) {
    die("Prepare failed: (" . $conn->errno . ") " . $conn->error);
}

// 2. Bind parameters
// "sss" means three string parameters. Use 'i' for integer, 'd' for double, 'b' for blob.
// The variables are passed by reference.
$stmt->bind_param("sss", $firstname, $lastname, $email);
if ($stmt === false) {
    die("Bind failed: (" . $stmt->errno . ") " . $stmt->error);
}


// 3. Set parameters and execute for the first record
$firstname = "John";
$lastname = "Doe";
$email = "john.doe@example.com";
if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

// 3. Set parameters and execute for the second record
$firstname = "Mary";
$lastname = "Moe";
$email = "mary.moe@example.com";
if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

// 3. Set parameters and execute for the third record
$firstname = "Julie";
$lastname = "Dooley";
$email = "julie.dooley@example.com";
if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

echo "New records created successfully";

// 4. Close statement and connection
$stmt->close();
$conn->close();
?>

Explanation:

• $conn->prepare(...): Creates the prepared statement template with ? placeholders.
• $stmt->bind_param("sss", ...): Binds PHP variables to the placeholders. The first argument ("sss") specifies the data type for each placeholder (s=string, i=integer, d=double, b=blob). The number of type characters must match the number of placeholders.
• $stmt->execute(): Executes the prepared statement using the current values of the bound variables.
• $stmt->close(): Frees the statement resource.

Prepared Statements in PDO

PDO supports both positional (?) and named (:name) placeholders. Named placeholders are often considered more readable, especially for queries with many parameters.

Example using named placeholders:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "myDBPDO"; // Assumes myDBPDO database exists

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // Set the PDO error mode to exception for better error handling
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 1. Prepare the statement with named placeholders (:firstname, etc.)
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email)
                          VALUES (:firstname, :lastname, :email)");

    // 2. Bind parameters (can also be done directly in execute array)
    // $stmt->bindParam(':firstname', $firstname);
    // $stmt->bindParam(':lastname', $lastname);
    // $stmt->bindParam(':email', $email);

    // 3. Set parameters and execute for the first record (using execute array)
    $firstname = "John";
    $lastname = "Doe";
    $email = "john.pdo@example.com";
    $stmt->execute([':firstname' => $firstname, ':lastname' => $lastname, ':email' => $email]);

    // 3. Set parameters and execute for the second record
    $firstname = "Mary";
    $lastname = "Moe";
    $email = "mary.pdo@example.com";
    $stmt->execute([':firstname' => $firstname, ':lastname' => $lastname, ':email' => $email]);

    // 3. Set parameters and execute for the third record
    $firstname = "Julie";
    $lastname = "Dooley";
    $email = "julie.pdo@example.com";
    $stmt->execute([':firstname' => $firstname, ':lastname' => $lastname, ':email' => $email]);

    echo "New records created successfully using PDO";

} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// 4. Close connection (optional in PDO, happens automatically)
$conn = null;
?>

Explanation:

• $conn->prepare(...): Creates the prepared statement template with named placeholders (e.g., :firstname).
• $stmt->bindParam(':name', $variable): Binds a PHP variable to a named placeholder. This binds by reference.
• $stmt->bindValue(':name', $value): Binds a specific value to a named placeholder. This binds by value.
• $stmt->execute([...]): Executes the prepared statement. You can pass an associative array directly to execute() where keys match the named placeholders. This is often the most concise method.
• Error Handling: The try...catch block handles potential PDOException errors gracefully.

Whether using MySQLi or PDO, consistently using prepared statements is fundamental for writing secure and efficient database interaction code in PHP.