PHP MySQL Prepared Statements

In this tutorial, you will learn how to use prepared statements in MySQL with PHP.

What is a prepared statement

A prepared statement (also known as a parameterized statement) is just a SQL query template that contains placeholders instead of actual parameter values. When executing the statement, these placeholders are replaced with actual values.

MySQLi supports the use of anonymous placeholder (?), as shown below:

INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?);

PDO supports anonymous placeholder (?), and named placeholders. Named placeholders start with a colon (:), followed by an identifier, as shown below:

INSERT INTO persons (first_name, last_name, email)
VALUES (:first_name, :last_name, :email);

The execution of prepared statements includes two phases: preparation and execution.

• Preparation - In the preparation phase, a SQL statement template is created and sent to the database server. The server parses the statement template, performs syntax checking and query optimization, and stores it for later use.

• Execution - During execution, parameter values will be sent to the server. The server creates a statement from the statement template and these values to execute it.

Prepared statements are very useful, especially when you need to execute a specific INSERT statement multiple times with different values (for example, a series of statements). The following section describes some of the main advantages of using it.

Advantages of using prepared statements

A prepared statement can be executed efficiently for the same statement multiple times because it is parsed only once and can be executed multiple times. Since only placeholder values need to be transmitted to the database server each time it is executed, rather than the complete SQL statement, it can also minimize bandwidth usage to the maximum extent.

Prepared statements also provide strong protection againstSQL injectionThis is because parameter values are not directly embedded in the SQL query string. Parameter values are sent to the database server separately from the query using different protocols, so they do not interfere with it. After parsing the statement template, the server uses these values directly when executing. This is why prepared statements are less prone to errors and are considered one of the most critical elements of database security.

The following example will show you how prepared statements actually work:

<?php
/*　Attempt to connect to MySQL server.  Assume you are running MySQL.
Server with default settings (user 'root' without a password)　*/
$link = mysqli_connect("localhost", "root", "", "demo");
　
//Check connection
if($link === false){
　　　　die("Error: Unable to connect. " . mysqli_connect_error());
}
　
//Use prepared statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
　
if($stmt = mysqli_prepare($link, $sql)){
　　　　//Bind variables as parameters to the prepared statement
　　　　mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
　　　　
　　　　/*　Set parameter values and execute, this statement inserts another row again.　*/
　　　　$first_name = "Hermione";
　　　　$last_name = "Granger";
　　　　$email = "[email protected]";
　　　　mysqli_stmt_execute($stmt);
　　　　
　　　　/*　Set parameter values and execute the statement to insert a row.　*/
　　　　$first_name = "Ron";
　　　　$last_name = "Weasley";
　　　　$email = "[email protected]";
　　　　mysqli_stmt_execute($stmt);
　　　　
　　　　echo "Record insertion successful.";
} else{
　　　　echo "Error: Unable to prepare query: $sql. " . mysqli_error($link);
}
　
//Close statement
mysqli_stmt_close($stmt);
　
//Close connection
mysqli_close($link);
?>

<?php
/*　Attempt to connect to MySQL server.  Assume you are running MySQL.
Server with default settings (user 'root' without a password)　*/
$mysqli = new mysqli("localhost", "root", "", "demo");
　
//Check connection
if($mysqli === false){
　　　　die("Error: Unable to connect. " . $mysqli->connect_error);
}
　
//　Use prepared statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
　
if($stmt = $mysqli->prepare($sql)){
　　　　//　Bind variables as parameters to the prepared statement
　　　　$stmt->bind_param("sss", $first_name, $last_name, $email);
　　　　
　　　　/*　Set parameter values and execute.
　　　　Execute the statement again to insert another row　*/
　　　　$first_name = "Hermione";
　　　　$last_name = "Granger";
　　　　$email = "[email protected]";
　　　　$stmt->execute();
　　　　
　　　　/*　Set parameter values and execute
　　　　　　　　The statement to insert the row　*/
　　　　$first_name = "Ron";
　　　　$last_name = "Weasley";
　　　　$email = "[email protected]";
　　　　$stmt->execute();
　　　　
　　　　echo "Successfully inserted record.";
} else{
　　　　echo "Error: Unable to prepare query: $sql. " . $mysqli->error;
}
　
//Close statement
$stmt->close();
　
//Close connection
$mysqli->close();
?>

<?php
/*　Attempt to connect to MySQL server.  Assume you are running MySQL.
Server with default settings (user 'root' without a password)　*/
try{
　　　　$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
　　　　//　Set PDO error mode to exception
　　　　$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
　　　　die("Error: Unable to connect.  " . $e->getMessage());
}
　
//Attempt to execute insertion query
try{
　　　　//Use prepared statement
　　　　$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
　　　　$stmt = $pdo->prepare($sql);
　　　　
　　　　//Bind parameters to the statement
　　　　$stmt-bindParam(':first_name', $first_name, PDO::PARAM_STR);
　　　　$stmt-bindParam(':last_name', $last_name, PDO::PARAM_STR);
　　　　$stmt-bindParam(':email', $email, PDO::PARAM_STR);
　　　　
　　　　/*　Set parameter values and execute,
　　　　　　Execute the statement again to insert another row　*/
　　　　$first_name = "Hermione";
　　　　$last_name = "Granger";
　　　　$email = "[email protected]";
　　　　$stmt->execute();
　　　　
　　　　/*　Set parameter values and execute
　　　　　　　　The statement to insert the row　*/
　　　　$first_name = "Ron";
　　　　$last_name = "Weasley";
　　　　$email = "[email protected]";
　　　　$stmt->execute();
　　　　
　　　　echo "Record insertion successful.";
} catch(PDOException $e){
　　　　die("Error: Unable to prepare/Execute query: $sql. " . $e->getMessage());
}
　
//　Close statement
unset($stmt);
　
//Close connection
unset($pdo);
?>

As you can see in the example above, we prepared the INSERT statement only once, but executed the statement multiple times by passing different sets of parameters.

Code usage (programming style)

In the SQL INSERT statement example above, the question mark is used asfirst_name,last_nameandemailPlaceholders for field values.

The mysqli_stmt_bind_param() function binds variables to placeholders (?) in the SQL statement template. The placeholders (?) are replaced by the actual values stored in the variables at execution time. The type definition string provided as the second parameter, i.e., 'sss' for strings, specifies the data type of each binding variable as string (string).

The type definition string specifies the data type of the corresponding binding variable, with the following four types of parameters:

• i - integer (integer)

• d - double (double precision floating point type)

• s - string (string)

• b - BLOB (binary large object: binary large object)

The number of bind variables and the number of characters in the string type definition must match the number of placeholders in the SQL statement template.

Use input received through a web form

If you remember the previous chapter, we have created an HTML form toInsert data into the databaseHere, we will expand the example by executing a preprocessing statement. You can use the same HTML form to test the following insert script example, but make sure to use the correct filename in the form's action attribute.

This is the updated PHP code for inserting data. If you look closely at the example, you will notice that we do not use mysqli_real_escape_string() like the example in the previous chapter to escape user input. Since user input is never directly substituted into the query string in prepared statements, there is no need to properly escape them.

<?php
/*　Attempt to connect to MySQL server.  Assume you are running MySQL.
Server with default settings (user 'root' without a password)　*/
$link = mysqli_connect("localhost", "root", "", "demo");
　
//Check connection
if($link === false){
　　　　die("Error: Unable to connect. " . mysqli_connect_error());
}
　
//Use prepared statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
　
if($stmt = mysqli_prepare($link, $sql)){
　　　　//Bind variables to the prepared statement as parameters
　　　　mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
　　　　
　　　　//Set parameters
　　　　$first_name = $_REQUEST['first_name'];
　　　　$last_name = $_REQUEST['last_name'];
　　　　$email = $_REQUEST['email'];
　　　　
　　　　//Attempt to execute prepared statement
　　　　if(mysqli_stmt_execute($stmt)){
　　　　　　　　echo "Record insertion successful.";
　　　　} else{
　　　　　　　　echo "Error: Unable to execute query: $sql " . mysqli_error($link);
　　　　}
} else{
　　　　echo "Error: Unable to execute query: $sql " . mysqli_error($link);
}
　
//　Close statement
mysqli_stmt_close($stmt);
　
//Close connection
mysqli_close($link);
?>

<?php
/*　Attempt to connect to MySQL server.  Assume you are running MySQL.
Server with default settings (user 'root' without a password)　*/
$mysqli = new mysqli("localhost", "root", "", "demo");
　
//Check connection
if($mysqli === false){
　　　　die("Error: Unable to connect. " . $mysqli->connect_error);
}
　
//Use prepared statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
　
if($stmt = $mysqli->prepare($sql)){
　　　　//Bind variables as parameters to the prepared statement
　　　　$stmt->bind_param("sss", $first_name, $last_name, $email);
　　　　
　　　　//Set parameters
　　　　$first_name = $_REQUEST['first_name'];
　　　　$last_name = $_REQUEST['last_name'];
　　　　$email = $_REQUEST['email'];
　　　　
　　　　//Attempt to execute prepared statement
　　　　if($stmt->execute());
　　　　　　　　echo "Record insertion successful.";
　　　　} else{
　　　　　　　　echo "Error: Unable to execute query: $sql.  " . $mysqli->error;
　　　　}
} else{
　　　　echo "Error: Unable to execute query: $sql.  " . $mysqli->error;
}
　
//Close statement
$stmt->close();
　
//Close connection
$mysqli->close();
?>

<?php
/*　Attempt to connect to MySQL server.  Assume you are running MySQL.
Server with default settings (user 'root' without a password)　*/
try{
　　　　$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
　　　　//Set PDO error mode to exception
　　　　$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
　　　　die("Error: Unable to connect.  " . $e->getMessage());
}
　
//Attempt to execute insertion query
try{
　　　　//Use prepared statement
　　　　$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
　　　　$stmt = $pdo->prepare($sql);
　　　　
　　　　//　Bind parameters to the statement
　　　　$stmt->bindParam(':first_name', $_REQUEST['first_name'], PDO::PARAM_STR);
　　　　$stmt->bindParam(':last_name', $_REQUEST['last_name'], PDO::PARAM_STR);
　　　　$stmt->bindParam(':email', $_REQUEST['email'], PDO::PARAM_STR);
　　　　
　　　　//　Execute prepared statement
　　　　$stmt->execute();
　　　　echo "Record insertion successful.";
} catch(PDOException $e){
　　　　die("Error: Unable to prepare/Execute query $sql.  "  .  $e->getMessage());
}
　
//Close statement
unset($stmt);
　
//Close connection
unset($pdo);
?>