A common mistake with new PHP developers is that they write code that’s vulnerable to SQL injection attacks. SQL injection attacks can be very dangerous to your website! It’s an easy mistake to make if you don’t know what to look out for, but one which is easy to avoid if you do.
This article describes what an SQL Injection Attack is, and what you can do to protect your website from it.
This article uses the MySQLi library as an example, but the same vulnerabilities apply to PDO as well.
What is an SQL Injection Attack
Suppose, you’ve written a simple piece of code that lets visitors subscribe to your newsletter. People can enter their email address, which is then added to the database so you can contact them later. The code looks like this:
<?php
$email = $_POST['email'];
$sql = "INSERT INTO subscribers (email) VALUES ('$email')";
$result = mysqli_query($connection, $sql);
If someone enters a valid email, this works fine. Suppose someone enters the value [email protected], then the generated SQL query becomes this:
INSERT INTO subscribers (email) VALUES ('[email protected]');
This query will work as intended and add the user’s email address to the list.
But what if someone wanted to do something nefarious? If so, they could enter the email address ´; DROP TABLE subscribers; ---.
The PHP code will turn this input into the following SQL query:
INSERT INTO subscribers (email) VALUES (''); DROP TABLE subscribers; --- ');
The --- is a comment, so that part is ignored. So what will happen is that MySQL will first create an empty row in the table… and then delete the entire table!
But that’s just one example of a query. Using this method, a hacker can execute basically any query in your database to read or write whatever data they want, including things like creating their own admin users or reading sensitive data in your database.
How to protect your code against SQL Injection Attacks
There are two ways to protect your code against SQL injection attacks: prepared statements and escaping.
Of these two, prepared statements are the best solution, so let’s begin with those.
Using Prepared Statements
By using prepared statements, it’s possible to completely separate the query logic and the input data from each other, making it impossible to do SQL injection.
Considering the newsletter subscription form from above, we can rewrite the code to use prepared statements like this:
<?php
$email = $_POST['email'];
// Create the query text. Any part where we want user data, we place a question mark.
$sql = "INSERT INTO subscribers (email) VALUES (?)";
$statement = mysqli_prepare($connection, $sql);
// "Bind" the data to the query. This will replace the question mark with the string containing the email field.
// You can repeat this function multiple times to replace multiple question marks for multiple parameters.
mysqli_stmt_bind_param($statement, 's', $email);
$result = mysqli_stmt_execute($statement);
As you can see, the query text is always the same, no POST data is put into the query text itself. Because of that, there is no possibility of the query doing anything other than inserting a single row into the table.
Using escape functions
Another approach to prevent SQL injection is to “escape” the input data. To “escape” data means you add backslashes to special characters or represent them in a different form so the database server doesn’t interpret them as query text.
With MySQLi, you can use the function mysqli_real_escape_string to sanitize the input data to make it safe. Considering the newsletter example from above, the resulting code could look like this:
<?php
$email = mysqli_real_escape_string($connection, $_POST['email']);
$sql = "INSERT INTO subscribers (email) VALUES ('$email')";
$result = mysqli_query($connection, $sql);
Note that PDO does not have any built-in escape functions, so you must use prepared statements if you use PDO instead of MySQLi.
Common pitfalls
So you’ve (re)written your code to use prepared statements or escape all input data. Now you’re good, right? Well, let’s take a look at a few common errors people make after implementing either of these.
Putting data in prepared statements
Prepared statements are a great way to prevent SQL injection, but you have to make sure that any user submitted data is ALWAYS passed as a bound parameter, not in the prepared statement.
For example, let’s take a look at the code below:
<?php
$email = $_POST['email'];
$sql = "INSERT INTO subscribers (email) VALUES ('$email')";
$statement = mysqli_prepare($connection, $sql);
$result = mysqli_stmt_execute($statement);
This code does use prepared statements, but the input data is being passed to the statement, making this code just as vulnerable as not using prepared statements at all.
How to fix: always bind user submitted data to parameters. Avoid using string interpolation in query text at all.
Using the wrong escape function
In the example above, we used mysqli_real_escape_string to escape the database parameters. This is good.
But PHP offers many other functions to escape data as well, such as addslashes, filter_var and htmlspecialchars. Do not use these functions to escape query parameters. Every escape function is designed to escape data in a certain way for a certain use case. Using the wrong escape function for the situation can result in data being escaped too much (resulting in garbled data in the database) or too little (leaving your code open to SQL Injection anyways).
How to fix: when using MySQLi, always use mysqli_real_escape_string to escape data. When using PDO, always use prepared statements, because it doesn’t have database escape functions.
Escaping data multiple times or not at all
In our simple example above, it’s very easy to see when and where our data is escaped. But when code becomes more complex, and a given feature is separated across multiple files and functions, it becomes harder to keep track where data is escaped.
Because of this, it’s possible that you may end up escaping the same data twice (resulting in garbage data in the database), or not at all (leaving your code open to SQL Injection).
How to fix: be consistent in where you escape data to make sure that all data is escaped exactly once. Or use prepared statements instead.