MySQLi Prepared Statements

I'll start off with stating that the main advantage of prepared statements is that they provide the easiest solution for the ultimate defense against SQL injection attacks (stronger than just using escaping).

Here is an excerpt from the PHP docs on prepared statements:

"Bound variables are sent to the server separately from the query and thus cannot interfere with it. The server uses these values directly at the point of execution, after the statement template is parsed. Bound parameters do not need to be escaped as they are never substituted into the query string directly. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. ... Such a separation sometimes considered as the only security feature to prevent SQL injection, but the same degree of security can be achieved with non-prepared statements, if all the values are formatted correctly. It should be noted that correct formatting is not the same as escaping and involves more logic than simple escaping. Thus, prepared statements are simply a more convenient and less error-prone approach to this element of database security."

Indeed one of their main advantages is that you will no longer need to perform manual escaping of values, and the values can be used "raw". If you only use prepared statements in your codebase, you don't have to worry about an values accidentally slipping through your stack to your query without getting escaped (I'm imaging a custom library where the developer(s) aren't writing the queries directly, but passing the values down to functions in model(s) which handle the interfacing with the database).

Traditional Escaping

For those who don't know what I mean by "escaping", here is an example where we escape a user's provided email address before using it in a "traditional" non-prepared MySQLi query, in order to make it "safe":

# get posted form inputs
$rawEmail = $_POST['email'];

# escape the user provided email address
# in case they put a malicious bit of code in there.
$escapedEmail = $db->real_escape_string($rawEmail);

# Run the query
$selectQuery = "SELECT * FROM `user` WHERE `email`='{$escapedEmail}'";
$result = $db->query($selectQuery);

# Loop over the results
while (($user = $result->fetch_assoc()) != null)
{
    print "User: " . print_r($user, true) . PHP_EOL;
}

I have heard incorrectly from more than one PDO based user that I shouldn't use mysqli because it's deprecated. They must have got confused somewhere along the way with the older mysql_ based functions becoming deprecated and being replaced by mysqli_.

If you are using non-prepared statements or you just aren't sure, please go check your codebase now! SQL injection attacks, whilst being easy to protect against, still cause issues today, even with "big name" companies such as Talk Talk and Sony (Playstation).

Prepared Statements Example

Here is another example that does the same thing as the previous example, but we are going to use a prepared statement.

# get posted form inputs
$rawEmail = $_POST['email'];

# Prepare the statement - notice the ? and no variables yet.
$selectQuery = "SELECT * FROM `user` WHERE `email`= ?";
$stmt = $db->prepare($selectQuery);

# Bind the parameters, this is where the variable "enter" the query safely.
$stmt->bind_param("s", $rawEmail);

# Execute the statement
$stmt->execute();

# Get the result
$result = $stmt->get_result();

# loop over the rows returned in result (I am assuming didn't return FALSE for error)
while (($user = $result->fetch_assoc()) ! == null)
{
    print "User: " . print_r($user, true) . PHP_EOL;
}

$stmt->close();

As you can see, we didn't need to perform any escaping, but we had to use ? placeholders where the variables should go, and then bind the variable's data to the statement later.

In this statement of the code $stmt->bind_param("s", $rawEmail);, the "s" part is telling the database that $rawEmail is a string type. The letters you need for the different types are listed below:

• i - integer
• d - double (decimal value)
• s - string
• b - blob and will be sent in packets

Prepare Once, Execute Multiple Times

You only have to prepare the statement once, and can then use it (bind and execute), any number of times. You may wish to use this for executing multiple insert statements, but you may wish to use a traditional non-prepared batch insert query, for efficiency/performance. We will cover this more, later.

Preserved Types By Default

Because prepared statements communicate in binary format, unlike non-prepared statements, which uses strings, your types will be returned more appropriately by default. For example, integer values in the database will be returned as integer values in PHP rather than as a string e.g. 3 instead of "3". However, you can get the correct types out of non-prepared statements to with little a bit of effort. You can either set the MYSQLI_OPT_INT_AND_FLOAT_NATIVE connection option or use fetch_field_direct for getting the field information and then perform the casting yourself.

Result Buffering

You will notice that in my code example, I used $stmt->get_result();, whereas other sources may only show you output result binding like below

...
# Execute the statement
$stmt->execute();

$out_id    = NULL;
$out_label = NULL;
if (!$stmt->bind_result($out_id, $out_label)) {
    echo "Binding output parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

while ($stmt->fetch()) {
    printf("id = %s (%s), label = %s (%s)\n", $out_id, gettype($out_id), $out_label, gettype($out_label));
}

If you use this binding method instead of using get_result() then you will be getting one result from the database at a time (multiple round trips instead of just the one for all rows). If you want to increase performance by cutting out these round trips, then be sure to use get_result().

Downsides

Input Binding - Specify Type

Unfortunately, with prepared statements, you have to take the extra step of explicitly binding the values in the order of the appropriate ? in the statement, and specify the types, which you don't have to do with the traditional mysqli statements. This can be a bit of a burden, but a good trade-off for never having to worry about escaping.

Additional Round Trips

The prepared statement is sent immediately and separately to the input variables. Thus this uses far more round trips than traditional unprepared statements. Also, as mentioned before, additional round trips are also incurred if you use binding for getting the results rather than using the buffered $stmt->get_result(); way.

Conclusion

Prepared statements provide the easiest way to provide ultimate security against SQL injection attacks, but have differences that need to be known about to prevent the developer incurring performance drawbacks. These differences are best indicated by the table at the bottom of the PHP docs on prepared statements, shown below:

References

• PHP Docs - Prepared Statements
• PHP Docs - mysqli::prepare
• Websitebeaver.com - PHP MySQLi Prepared Statements to Prevent SQL Injection