Zend PHP 7 Certification – Databases – Prepared Statements

22nd January 2018

This post covers the Prepared Statements section of the Databases chapter when studying for the Zend PHP 7 Certification.

Many databases support the concept of prepared statements. They can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customised using variable parameters. Some of the major benefits using prepared statements include the following points.

  • The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters.
  • Related parameters do not need to be quoted, so there will no possibility of SQL injection.

To start writing prepared statements, configure a connection to the database and check the connection is working.

$dbh = new MySQLi($host, $user, $password, $database);

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

You can then use the prepare() method of $dbh.

$stmt = $dbh->prepare("INSERT INTO table1 (name, description) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $description);

// Add the values and execute the statement
$name = "John";
$description = "His name is John";
$stmt->execute();

// Close resources
$stmt->close();
$dbh->close();

So within the SQL statement certain values are left unspecified, called parameters (labeled “?”). The question marks are inserted where we want to substitute in an integer, string, double or blob value.

The bind_param() method binds the parameters to the SQL query and tells the database what the parameters are. The ss argument in our example represents that our two parameters are both strings. The parameters can be one of four types.

  • i – integer
  • d – double
  • s – string
  • b – BLOB

Note that you can also execute multiple statements without having to write out the query again. So for the example above, we could add multiple records by writing the below code.

<?php
$stmt = $dbh->prepare("INSERT INTO table1 (name, description) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $description);

$name = "Bob";
$description = "His name is Bob";
$stmt->execute();

$name = "Jim";
$description = "His name is Jim";
$stmt->execute();

$stmt->close();
$dbh->close();

PDO prepared statements’ syntax is slightly different. It uses the same prepare() method however the ? placeholders are changed to named placeholders. So instead of using ?, in our example we can use :name and :description, as seen below.

<?php
$conn = new PDO("mysql:host=localhost;dbname=testdb", 'user', 'password');

$stmt = $conn->prepare("INSERT INTO table1 (name, description) 
    VALUES (:name, :description)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':description', $description);

// Insert a row
$name = "Test";
$description = "Test Description";
$stmt->execute();

$conn = null;

In additional, multiple bindParam() methods are used here each passing in the name of the placeholder rather than a single bind_param() method with characters representing the type of data.

So what types of prepared statements should you use? Ultimately it’s up to you, however PDO can support different database drivers. So if you’re looking to make your system cross-database compatible, use PDO. Otherwise Mysqli is just fine.

Note: This article is based on PHP version 7.1.