How to Prevent SQL Injections with PHP and MySQLi

Protecting private data stored within your database has never been more important. User data, passwords, credit card information, and any other types of personal data need to be protected at all costs.

In this article, we'll cover the importance of protecting your database and private data by preventing SQL injections with PHP and MySQLi. There's a lot to cover, but it's a basic concept overall.

What is SQL Injection?

SQL injection is a malicious code technique used to attack data-driven applications. It's one of the most common web hacking techniques used to retrieve, update, or even delete data from a database.

For example, we could query a user table that theoretically accepts a single user ID to return a record associated with that user:

SELECT	*
FROM	users
WHERE	id = 2187;

This SQL statement would return all data associated with user id 2187.

Now, assuming the script written to return a single user isn't protected, we could query the whole user table, returning all records and getting everyone's personal data:

SELECT	*
FROM	users
WHERE	id = 2187 OR 1 = 1;

Now, by passing in the id 2187 OR 1 = 1, we've got everything in the table.

It's terrible practice to not protect your data, yet very simple to setup. Let's get going!

How to Prevent SQL Injection with PHP

There are a few PHP methods readily available for us to use within our script to prevent SQL injection, such as the prepare() and bind_param() methods.

Here's a simple code snippet that puts them into action:

$id = 2187;

$stmt = $db->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();

$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
	echo $row["id"] . ". " . $row["name"] . "<br/>";
}

To sum it up, we first create a SQL statement that selects a single user ID with PHP's prepare() method.

Next, we use PHP's bind_param() method to assign a datatype of i, or integer, to the id value, and pass in $id as the value. The ? in the query is the replaced by the value of the variable passed into the bind_param() method. In this case, the value of the $id variable.

Then we execute the SQL statement and assign the resultset to the $result variable and loop over the data.

Passing Multiple Variables in a Single Query

Let's say you wanted to pull a user record by its ID and its name. Unconventional, but let's do it for the sake of this example:

$id = 2187;
$name = "Orangeable";

$stmt = $db->prepare("SELECT * FROM users WHERE id = ? AND name = ?");
$stmt->bind_param("is", $id, $name);
$stmt->execute();

Here, we've updated our SQL statement to include a second condition, looking for the record by ID and name. Both must be a match to return a matching result from the table successfully.

Notice we've now added two ?. The bind_param() method will step through each of its arguments and match/replace these symbols in order. So you must always have the same number of ? and variable arguments.

We've also added a second datatype to the first argument of bind_param(), s, or string. The full value now becomes is, which means the first variable is an integer and the second variable is a string.

You can add as many datatypes as you'd like, as long as they match the datatypes of the variables you're passing into the method and as long as they are in the same order to prevent a thrown exception.

Datatypes for PHP's bind_param() Method

Here is a list of the datatypes available for PHP's bind_param() method:

• i: integer datatype
• d: double datatype
• s: string datatype
• b: blob datatype that will be sent in packets

If we were to pass in a datatype of s, meaning string for example, an exception will be thrown since the value of $id is an integer, not a string. So make sure you are passing in the correct datatype when using bind_param().

Conclusion

I can't stress enough how important it is to use this practice to prevent SQL injections within your PHP scripts using MySQLi. It's extremely easy to set up and will prevent many headaches in the long run for both you and your users.

Trust is everything, so make sure you're using best practices to set up your applications correctly.