PHP : MySQL Prepared Statements

Using prepared statements in PHP with MySQL is a good practice to prevent SQL injection attacks and improve performance, especially when executing the same query with different parameters multiple times. Below are examples of using prepared statements with both mysqli and PDO to interact with a users table.

Using mysqli :

Here's an example of using prepared statements with mysqli :


// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

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

// Data to be inserted
$name = "John Doe";
$email = "john@example.com";

// Prepare the SQL statement
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";
$stmt = $conn->prepare($sql);

// Bind parameters
$stmt->bind_param("ss", $name, $email);

// Execute the statement
if ($stmt->execute()) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

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

Using PDO :

Here's the same example using PDO :


// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

try {
    // Create connection
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Data to be inserted
    $name = "John Doe";
    $email = "john@example.com";

    // Prepare the SQL statement
    $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
    $stmt = $conn->prepare($sql);

    // Bind parameters
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':email', $email);

    // Execute the statement
    if ($stmt->execute()) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $stmt->errorInfo()[2];
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
} finally {
    // Close connection
    $conn = null;
}
 

Explanation:

  • In both examples, the SQL query is prepared with placeholders ( ? for mysqli and :name, :email for PDO ) for the parameters.
  • For mysqli, bind_param is used to bind parameters to the prepared statement.
  • For PDO, bindParam is used to bind parameters to the prepared statement.
  • After binding parameters, the statement is executed with execute().

Important Notes:

  • Prepared statements help prevent SQL injection attacks by automatically escaping user input.
  • They are also more efficient when executing the same query with different parameters.
  • Always verify that the connection is successful before executing queries.
  • Close the statement and connection after use to free up resources.

Running these scripts will insert a new record with the provided name and email into the users table in your MySQL database. Adjust the $name and $email variables with the desired values to be inserted.