Prepared Statements

Prepared Statements are like compiled templates for SQL that two major benefits over plain SQL queries. The first is that prepared statement only need to be prepared once, but can be executed multiple times. This can optimize database procedures when working with complex queries. The second major benefit is the build-in security. Prepared statements automatically handle quoting, which will help prevent SQL injections. Therefore, prepared statement is crucial any time a query is dependent on values coming from the user.

SQL Injection

The following example shows how SQL injection could happen.

<?php
  require_once "db.php";
  $movie = $_GET['movie'] // ';DROP TABLE movies;'
  $sql = "SELECT * FROM movies WHERE movie_title = '$movie'";
  $result = $db->query($sql);

If the above code what allowed to execute, it could the cause the movies table to be deleted. This is why it is always important to protect against SQL injection and prepared statements are on way to do that.

Creating a Prepared Statement

PDO supports two forms for implementing prepared statements: named parameters and anonymous placeholders. These will be placed inside of the query statement as placeholders for data, which will be added later. We will only cover named parameters in this course.

To use a named parameter inside of a prepared statement, we first start with a colon followed by the parameter name and place it inside of the query statement.

<?php
  require_once "db.php";
  $sql = "SELECT * FROM movies WHERE movie_title = :movie_title";

Then the prepare() method is used to prepare the prepared statement. The method will also return the statement object.

NOTE

The prepare() method does not execute the statement. It is merely creates a template that can be used later with the desired data.

<?php
  require_once "db.php";
  $sql = "SELECT * FROM movies WHERE movie_title = :movie_title";
  $stmt = $db->prepare($sql);

Binding Data

Once a prepared statement has been created, it is ready for the data. This can be down in two ways: directly using the methods bindParam() and bindValue() or by passing an array the execute() method. We will cover the latter method on the next page, for now we will cover bindParam() and bindValue().

The bindParam() and the bindValue() are very similar but do have very crucial difference. The bindParam() method only works with a variable and will not evaluate until the statement is executed.

The example below binds the $_GET['movie'] to the prepared statement using the bindValue() method. The data type is explicitly set using the PDO::PARAM_STR constant.

NOTE

There are many data_type constants can be applied to the both of these methods. You can see all of the available constants on the PDO Constants page.

<?php
  require_once "db.php";
  $sql = "SELECT * FROM movies WHERE movie_title = :movie_title";
  $stmt = $db->prepare($sql);
  $stmt->bindValue(':movie_title', $_GET['movie'], PDO::PARAM_STR);

Executing a Prepared Statement

The final step of using a prepared statement is the execute the statement with the database. This is accomplished using the execute() method.

<?php
  require_once "db.php";
  $sql = "SELECT * FROM movies WHERE movie_title = :movie_title";
  $stmt = $db->prepare($sql);
  $stmt->bindValue(':movie_title', $_GET['movie'], PDO::PARAM_STR);
  $stmt->execute();