PHP MySQL Prepared
Understanding Prepared Statements
A prepared statement, also known as a parameterized statement, is a SQL query template with placeholders instead of actual parameter values. These placeholders are filled with real values when the statement is executed.
MySQLi supports anonymous positional placeholders (?), like this:
INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?);
PDO supports both anonymous positional placeholders (?) and named placeholders starting with a colon (:), such as:
INSERT INTO persons (first_name, last_name, email)
VALUES (:first_name, :last_name, :email);
The execution of a prepared statement involves two stages: prepare and execute.
- Prepare — The SQL statement template is prepared and sent to the database server. The server parses, checks syntax, optimizes the query, and stores it for execution.
- Execute — Parameter values are sent to the server. The server combines the statement template with these values and executes it.
Prepared statements are highly efficient for executing the same statement multiple times with different values, such as a series of INSERT statements. They reduce bandwidth usage by transmitting only parameter values instead of the entire SQL statement on each execution.
Moreover, prepared statements offer robust protection against SQL injection attacks because they separate parameter values from the SQL query string. Values are sent separately to the database server, preventing them from altering the query structure. This approach enhances database security and reduces errors.
The following example demonstrates the use of prepared statements:
Example
Procedural Object Oriented PDO
Download
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
// Prepare an insert 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 the parameters values and execute
the statement again to insert another row */
$first_name = "Hermione";
$last_name = "Granger";
$email = "hermionegranger@mail.com";
mysqli_stmt_execute($stmt);
/* Set the parameters values and execute
the statement to insert a row */
$first_name = "Ron";
$last_name = "Weasley";
$email = "ronweasley@mail.com";
mysqli_stmt_execute($stmt);
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link);
}
// Close statement
mysqli_stmt_close($stmt);
// Close connection
mysqli_close($link);
?>
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("sss", $first_name, $last_name, $email);
/* Set the parameters values and execute
the statement again to insert another row */
$first_name = "Hermione";
$last_name = "Granger";
$email = "hermionegranger@mail.com";
$stmt->execute();
/* Set the parameters values and execute
the statement to insert a row */
$first_name = "Ron";
$last_name = "Weasley";
$email = "ronweasley@mail.com";
$stmt->execute();
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not prepare query: $sql. " . $mysqli->error;
}
// Close statement
$stmt->close();
// Close connection
$mysqli->close();
?>
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt insert query execution
try{
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
$stmt = $pdo->prepare($sql);
// Bind parameters to 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 the parameters values and execute
the statement again to insert another row */
$first_name = "Hermione";
$last_name = "Granger";
$email = "hermionegranger@mail.com";
$stmt->execute();
/* Set the parameters values and execute
the statement to insert a row */
$first_name = "Ron";
$last_name = "Weasley";
$email = "ronweasley@mail.com";
$stmt->execute();
echo "Records inserted successfully.";
} catch(PDOException $e){
die("ERROR: Could not prepare/execute query: $sql. " . $e->getMessage());
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
?>
As shown in the example above, we prepared the INSERT statement once and executed it multiple times with different sets of parameters.
Explanation of Code (Procedural style)
In the SQL INSERT statement (line 12), placeholders (?) are used for the first_name, last_name, and email fields.
The mysqli_stmt_bind_param() function (line 16) binds variables to these placeholders (?) in the SQL statement template. The placeholders are replaced by actual values held in the variables during execution. The type definition string ("sss") specifies that each bind variable is a string.
The type definition string specifies data types for each bind variable using characters:
- b — binary data (e.g., image, PDF)
- d — double (floating point number)
- i — integer
- s — string (text)
The number of bind variables and characters in the type definition string must match the number of placeholders in the SQL statement template.
Using Inputs from a Web Form
In the previous chapter, we created an HTML form to insert data into a database. Here, we extend that example by implementing prepared statements. You can use the same HTML form with the correct file name in the action attribute of the form to test the following insert script example.
Below is the updated PHP code for inserting data. Notice that we do not use mysqli_real_escape_string() to escape user inputs as we did in the previous example. Prepared statements handle user inputs securely by not directly substituting them into the query string.
Example
Procedural Object Oriented PDO
Download
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
// Prepare an insert 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 the prepared statement
if(mysqli_stmt_execute($stmt)){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not execute query: $sql. " . mysqli_error($link);
}
} else{
echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link);
}
// Close statement
mysqli_stmt_close($stmt);
// Close connection
mysqli_close($link);
?>
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$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 the prepared statement
if($stmt->execute()){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not execute query: $sql. " . $mysqli->error;
}
} else{
echo "ERROR: Could not prepare query: $sql. " . $mysqli->error;
}
// Close statement
$stmt->close();
// Close connection
$mysqli->close();
?>
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt insert query execution
try{
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
$stmt = $pdo->prepare($sql);
// Bind parameters to 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 the prepared statement
$stmt->execute();
echo "Records inserted successfully.";
} catch(PDOException $e){
die("ERROR: Could not prepare/execute query: $sql. " . $e->getMessage());
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
?>
Note: While prepared statements eliminate the need for escaping user inputs, it's crucial to validate data types, sizes, and enforce limits on data received from external sources. This ensures protection against potential system resource exploits.
