mysqlphpsecurity

Prepared Statements in PHP MySQLi to Prevent SQL Injection

Table of Contents

Introduction

A hack attempt has recently been discovered, and it appears they are trying to take down the entire database. An impromptu staff meeting has been called at 2am, and everyone in the company is freaking out. Ironically, as the database manager, you remain the calmest. Why? You know that these scrubs are are no match for those prepared statements you coded! In fact, you find this humorous, as these hackers will likely be annoyed that they wasted their time with futile attempts.

Hopefully this scenario will never happen to your website. However, it is undoubtedly a good idea to take proper precautions. If implemented correctly, prepared statements (aka parameterized queries) offer superior protection against SQL injection. You basically just create the query template with placeholder values, and then replace the dummy inputs with the real ones. Escaping is not necessary, since it will treat the values as literals; all attempts to inject sql queries will be interpreted as such.

Prepared statements may seem intimidating at first, but once you get hang of it, it’ll seem like second nature to you. The goal of this tutorial is to transform someone with little to no knowledge of prepared statements, into an expert.

Disclaimer: Don't actually be as laid back as this database manager. When it comes to security, you should never be complacent, no matter how secure you think your system is.

mysqli::real_escape_string

In a normal MySQL call, you would do something like:

$name = $_POST['name'];
$con->query("Select * FROM myTable WHERE name='$name'");

The problem with this, is that if it is based off of user input, like in the example, then a malicious user could do ' OR '1'='1. Now this statement will always evaluate to true, since 1=1. In this case, the malicious user now has access to your entire table. Just imagine what could happen if it were a DELETE query instead. Take a look at what is actually happening to the statement.

Select * FROM myTable WHERE name='' OR '1'='1' 

A hacker could do a lot of damage to your site if your queries are set up like this. An easy fix to this would be to do:

$name = $con->real_escape_string($_POST['name']);
$con->query("Select * FROM myTable WHERE name='$name'");

Notice how similar to the first example, I still added quotes to the column name. Without them, it is still equally susceptible to SQL injection.

This covers strings, as the function name implies, but what about numbers? You could do (int)$con->real_escape_string($_POST['name']), which would certainly work, but that's redundant. If you're casting the variable to an int, you don't need to escape anything. You are already telling it to essentially make sure that the value will be an integer. Doing (int)$_POST['name'] would suffice. Since it is an integer you also obviously do not need to add quotes to the sql column name.

In reality, if you follow these instructions perfectly, it should be enough to use mysqli::real_escape_string for strings and (int)$var for integers; you really could even stop reading here if that's your preference. However, I should mention that once you understand prepared statements, you realize how much more readable it makes the code. Also, apparently there are a few edge cases to break it?

Key Differences

A prepared statement, as its name implies, is a way of preparing the MySQL call, without storing the variables. You tell it that variables will go there eventually — just not yet. The best way to demonstrate it is by example.

$stmt = $con->prepare("SELECT * FROM myTable WHERE name=? AND age=?");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
//fetching result would go here, but will be covered later
$stmt->close();

If you've never seen prepared statements before, this may look a little weird. Basically what's happening is that you are creating a template for what the SQL statement will be. In this case, we are selecting everything from myTable, where name and age equal ?. The question mark is just a placeholder for where the values will go.

The bind_param() method is where you attach variables to the dummy values in the prepared template. Notice how there are two letters in quotes before the variables. This tells the database the variable types. The s specifies that name will be a string value, while the i forces age to be an integer. This is precisely why I didn't add quotation marks around the question mark for name, like I normally would for a string in an SQL call. You probably thought I just forgot to, but the reality is that there is simply no need to (In fact, it actually won't work if you do put quotes around the ?, since it will be treated as a string literal, rather than a dummy placeholder.). You are already telling it that it will be a string literal when you call bind_param(), so even if a malicious user tries to insert SQL into your user inputs, it will still be treated as a string. $stmt->execute then actually runs the code; the last line simply closes the prepared statement. We will cover fetching results in the Select section.

Insert, Update and Delete

Inserting, updating and deleting have an identical syntax, so they will be combined.

Insert

$stmt = $con->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
$stmt->close();

Update

$stmt = $con->prepare("UPDATE myTable SET name=? WHERE id=?");
$stmt->bind_param("si", $_POST['name'], $_SESSION['id']);
$stmt->execute();
$stmt->close();

Delete

$stmt = $con->prepare("DELETE FROM myTable WHERE id=?");
$stmt->bind_param("i", $_SESSION['id']);
$stmt->execute();
$stmt->close();

Select

All select statements in parameterized queries will start off about the same. However, there is a key difference to actually storing and fetching the results. The two methods that exist are get_result() and bind_result().

get_result()

This is the more versatile of the two, as it can be used for any scenario.

$stmt = $con->prepare("SELECT * FROM myTable WHERE name=?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    $id[] = $row['id'];
    $name[] = $row['name'];
    $age[] = $row['age'];
  }
}
$stmt->close();

bind_result()

You might be wondering, why even use bind_result()? This is strictly due to preference, as the syntax is considered to be more readable. However, it should be noted that bind_result() may not be used with the * wildcard selector. It must contain explicit values. This usually won't matter, as you shouldn't be using the wildcard selector in production mode anyway (but you know you are). So the SELECT statement would have to be like this:

$stmt = $con->prepare("SELECT id, name, age FROM myTable WHERE name=?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows > 0) {
  $stmt->bind_result($idRow, $nameRow, $ageRow); 
  while ($stmt->fetch()) {
    $id[] = $idRow;
    $name[] = $nameRow;
    $age[] = $ageRow;
  }
}
$stmt->close();

Selecting Single Row

I personally find it simpler to use bind_result() when I know for fact that I will only be fetching one row, as I can access the variables in a cleaner manner.

$stmt = $con->prepare("SELECT id, name, age FROM myTable WHERE name=?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows === 1) {
  $stmt->bind_result($id, $name, $age);
  $stmt->fetch();
}
$stmt->close();

Now you can use just simply use the variables in bind_result(), since you know they will only contain one value, not an array. It is comparable to $id = $id[0].

Here's the get_result() version:

$stmt = $con->prepare("SELECT id, name, age FROM myTable WHERE name=?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 1)
  $row = $result->fetch_assoc();
$stmt->close();

You would then use the variable as $row['id'] for example. Similar to bind_result(), this variable is equivalent to $row['id'] = $row['id'][0].

Conclusion

bind_param() - best used for fetching single row without * selector; avoids $row['val'] syntax.

get_result() - can be used in any case; best used for multiple rows; must be used if * is involved.

Like

You would probably think that you could do something like:

$stmt = $con->prepare("SELECT id, name, age FROM myTable WHERE Name LIKE %?%"); 

But this is not allowed. The ? placeholder must be the entire string or integer literal value. This is how you would do it correctly.

$search = "%{$_POST['search']}%";
$stmt = $con->prepare("SELECT id, name, age FROM myTable WHERE Name LIKE ?"); 
$stmt->bind_param("s", $search);

Error Handling

Error Handling is fairly easy, as all of the functions you need to check return false. To get a meaningful error message, you can simply use the error and errno functions on the MySQLi connection. Furthermore, you can even get error messages from the prepared statement itself after execute() is called.

if ( !$stmt = $con->prepare("SELECT * FROM myTable WHERE name=?") ) 
 echo "Prepare Error: ($con->errno) $con->error";
if ( !$stmt->bind_param("s", $_POST['name']) )
  echo "Binding Parameter Error: ($con->errno) $con->error";
if ( !$stmt->execute() ) 
 echo "Execute Error: ($stmt->errno)  $stmt->error";
if ( !$stmt->get_result() ) //Only for select with get_result()
 echo "Getting Result Error: ($con->errno) $con->error";
if ( !$stmt->store_result() ) //Only for select with bind_result()
 echo "Storing Result Error: ($con->errno) $con->error";
$stmt->close();

You may also want to check the status of a row you inserted, updated or deleted. Here's how you would it if you're updating a row.

$stmt = $con->prepare("UPDATE myTable SET name=?");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
if($stmt->affected_rows === 0)
 echo 'No rows updated';
$stmt->close();

In this case, we checked to see if any rows got updated. For reference, here's the usage for mysqli::$affected_rows return values.

-1 - query returned an error; redundant if there is already error handling for execute()

0 - no records updated on UPDATE, no rows matched the WHERE clause or no query been executed

Greater than 0 - returns number of rows affected; comparable to mysqli_result::$num_rows for SELECT

Author - Daniel Marcus

Firmly believes that web technologies should take over everything. Enjoys writing tutorials about JavaScript and PHP.