Prepared Statements
When working with SQL statements, it is important to be aware of SQL injection attacks. With SQL injections malicious users attempt to insert bad code into a web site’s SQL queries. The aim of the attack is to create an invalid query that is capable of exposing, altering, or destroying stored data. Fortunately, SQL injections can be prevented by using prepared statements. When executing normal SQL scripts, PHP sends the entire query including data to MySQL as one long string. With a prepared statement, the SQL is sent in two parts – first, the SQL syntax is sent to MySQL where it is parsed and checked for errors, and second, the data values are sent. MySQL then assembles the SQL syntax and data and executes the complete query. Prepared statements offer greater security and potentially greater performance.
Prepared statements can be created out of any SQL SELECT, INSERT, UPDATE, or DELETE query. The first step is to re-create the query, replacing the values with a special placeholder, the question mark (?). In the following example, the first SELECT statement passes the variable $lastname. The second example, shows a prepared statement in which the $lastname variable is replaced with the "?" placeholder.
<?php
$sql = "SELECT first_name, last_name FROM Customers WHERE last_name = $lastname";
$sql = "SELECT first_name, last_name FROM Customers WHERE last_name = ?"
?>
Next, we initialize the prepared statement using the mysqli_stmt_init() function and then the SQL query is sent to MySQL using the mysqli_prepare() function. The SQL query is parsed, validated, and prepared for execution. The results are stored in a PHP variable:
<?php
$sql = "SELECT first_name, last_name FROM Customers WHERE last_name = ?"
$stmt = mysqli_stmt_init($conn);
mysqli_prepare($stmt,$sql);
?>
After the SQL query is validated and prepared, the PHP variables are bound to the query’s placeholders using the mysqli_stmt_bind_param() function. The function requires three parameters, a reference to the prepared SQL query, the data types of the values to be bound, and the variables containing the data to be bound. The table below shows the bound value types.
| Bound Value Types |
| Letter |
Represents |
| d |
Decimal |
| i |
Integer |
| b |
Blob (binary data) |
| s |
All other types |
In the example, only one place holder is associated with the SQL query. Therefore, we will bind only one value, the last name variable. Since the value is string data we will use the "s" bound value type. Unlike standard SQL statements, string variables are not placed in quotes.
<?php
$sql = "SELECT first_name, last_name FROM Customers WHERE last_name = ?"
$stmt = mysqli_stmt_init($conn);
if(mysqli_prepare($stmt,$sql))
{
mysqli_stmt_bind_param($stmt,'s',$lastname);
}
?>
Finally, we execute the prepared statement using the mysqli_stmt_execute() function and close the prepared statement.
<?php
$sql = "SELECT first_name, last_name FROM Customers WHERE last_name = ?"
$stmt = mysqli_stmt_init($conn);
if(mysqli_prepare($stmt,$sql))
{
mysqli_stmt_bind_param($stmt,'s',$lastname);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
}
?>
The example below shows an INSERT prepared statement. The mysqli_stmt_affected_rows() function is used to return the number of rows inserted.
<?php
$sqlInsert = "INSERT INTO accounts (first_name,last_name,username,password,email) VALUES (?,?,?,?,?)";
$stmt = mysqli_stmt_init($conn);
if(mysqli_prepare($stmt,$sqlInsert))
{
mysqli_stmt_bind_param($stmt,'sssss',$fname,$lname,$uname,$pword,$email);
mysqli_stmt_execute($stmt);
echo mysqli_stmt_affected_rows($stmt) . " records inserted";
mysqli_stmt_close($stmt);
}
?>
Prepared Statements and Transactions
It is also possible to use prepared statements. Each of the prepared statements are prepared and executed as shown in the previous section within a transaction block. The mysqli_stmt_affected_rows() function is used to obtain a count of the rows affected by the prepared statement. A PHP variable, $count, is used to keep track of the total number of rows affected. In this case, if the two prepared statements are executed correctly, a total of two rows will be inserted – one into the Customers table, and one into the Addresses table. The mysqli_stmt_insert_id() function is used here to retrieve the value of the auto increment primary key field created in the Customers table. It assigned to a variable $id and later used to insert the id value into the Address table. You will recall in the previous section on transactions, we used the MySQL LAST_INSERT_ID() function to obtain the auto increment value. When working with prepared statements, this function does not work. Instead the PHP equivalent function, mysqli_stmt_insert_id(), is used.
<?php
if (isset($_POST['submitb']))
{
$firstName = $_POST['FirstName'];
$lastName = $_POST['LastName'];
$address = $_POST['Address'];
$city = $_POST['City'];
$state = $_POST['State'];
//Establish a connection to the Database
//Reference the mysqli_connection.php file created above
include('mysqli_connection.php');
//Begin Transaction
mysqli_begin_transaction($conn);
$sqlInsertCustomer = "INSERT INTO Customer
(first_name,last_name) VALUES
(?,?)";
$sqlInsertAddress = "INSERT INTO Address (address,city,state,cid) VALUES (?,?,?,?)";
$count=0;
$id=0;
$stmt=mysqli_stmt_init($conn);
if(mysqli_stmt_prepare($stmt,$sqlInsertCustomer))
{
mysqli_stmt_bind_param($stmt,'ss',$firstName,$lastName);
mysqli_stmt_execute($stmt);
$count = mysqli_stmt_affected_rows($stmt);
$id= mysqli_stmt_insert_id($stmt);
}
if(mysqli_stmt_prepare($stmt,$sqlInsertAddress))
{
mysqli_stmt_bind_param($stmt,'sssi',$address,$city,$state,$id);
mysqli_stmt_execute($stmt);
$count = $count + mysqli_stmt_affected_rows($stmt);
}
if ($count == 2)
{
mysqli_commit($conn);
}
else
{
mysqli_rollback($conn);
}
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>