Prepared Statements With PHP PDO and MySQLi: Differences and Examples

One of the ways to execute tasks quicker is to use PHP prepared statements. They are templates for sending queries or uploading data to SQL databases. We'll discuss these prepared statements together with PHP data objects (PHP PDO) that offer a lightweight interface, designed for accessing databases.

Using PDO or MySQLi, prepared statements provide an additional layer of security as the values are being sent to the server separately from the query. As a result, both parts of the combination can't be affected at once.

PDO PHP and MySQLi: Main Tips

• PHP prepared statements are very useful against SQL injections and reduce parsing time.
• Preset parameters reduce the required bandwidth by only sending the specific values instead of the whole query.
• Prepared statements are supported by both PHP PDO and MySQLi.

How Prepared Parameters Work

PHP prepared statements work in two clear stages:

• Preparing: creating a SQL statement template and sending it to the database server to be parsed. After the server finishes this process, it checks the syntax, performs query optimization, and stores the statement.
• Executing: at some point later, you send the parameter values to the server, which takes the template it was storing. Then, the server creates a statement with the values it received. After that, the statement is executed.

PHP PDO vs MySQLi: Usage Explained

When using PHP prepared statements, it won't make a huge difference whether you choose to connect to the database via MySQLi extension or PHP PDO. Both of those connection types support prepared statements, though there are some differences in how you write your script.

MySQLi

Look at the example below. Notice that it uses bound parameters and PHP prepared statements to send a query to MySQLi:

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db =  'db';
  // Create connection
  $conn = new mysqli($host, $user, $pass, $db);
  // Check connection
  if ($conn->connect_error) {
    die("Failed to connect: " . $conn->connect_error); 
  }
  // bind and prepare
  $stmt = $conn->prepare("INSERT INTO users (name, surname, email) VALUES (?, ?, ?)");
  $stmt->bind_param("sss", $name, $surname, $email);
  // set parameters and execute
  $name = "John";
  $surname = "Doe";
  $email = "john@example.com";
  $stmt->execute();

  $name = "Mary";
  $surname = "Moe";
  $email = "mary@example.com";
  $stmt->execute();

  $name = "Julie";
  $surname = "Dooley";
  $email = "julie@example.com";
  $stmt->execute();
  
  echo "Uploaded.";
  $stmt->close();  
  $conn->close();
?>

Let's take a closer look at this particular line from the example above:

"INSERT INTO users (name, surname, email) VALUES (?, ?, ?)"

A question mark (?) in this statement represents the variable we want to upload.

Next, let's look into the function bind_param():

$stmt->bind_param("sss", $name, $surname, $email);

• The function above binds the parameters we want to use to the query we're sending and informs the database about them.
• The sss part specifies what types of data can be assigned to the parameters.
• s is meant to define that a particular parameter is nothing other than a string.

There are four possible argument types, and each of them can be represented by a specific letter:

• integer (i)
• double (d)
• string (s)
• BLOB (b)

Specify arguments for each parameter, as determining the type of data our database should expect minimizes the risk of SQL injection.

Note: if we want to insert any data from external sources (like user input), it's crucial that the data is sanitized and validated.

PHP PDO

The example below shows how you would write a script for PHP PDO prepared statements:

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';
  try {
    $conn = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
    // set error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //bind parameters and prepare sql
    $stmt = $conn->prepare("INSERT INTO users (name, surname, email) VALUES (:name, :surname, :email)");
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':surname', $surname);
    $stmt->bindParam(':email', $email); 
    // insert a row
    $name = "John";
    $surname = "Doe";
    $email = "john@example.com";
    $stmt->execute();
    // insert another row
    $name = "Mary";
    $surname = "Moe";
    $email = "mary@example.com";
    $stmt->execute();
    // insert another row
    $name = "Julie";
    $surname = "Dooley"; 	
    $email = "julie@example.com";
    $stmt->execute();
    echo "New records created successfully";
  } catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
  }
  $conn = null;  
?>

PHP PDO and MySQLi: Summary

• Both MySQLi extension and PDO support prepared statements.
• This functionality makes your application less sensitive to SQL injections and quickens parsing.
• When parameters are set in advance, less bandwidth is used: you don't need to send the whole query (all that's left is to specify values).