In this article, I am going to explain how you can prevent SQL Injection in your PHP website by making it immune to most SQL Injection attacks. We will begin with a simple example followed by a few easy to understand solutions. Read on!
SQL Injection – A Simple Example
SQL Injection is the act of injecting SQL statements through HTML form input fields with the intention of modifying the database without authorization. Let us take the example of a ‘users’ table being used to store customer information. The customers can choose a username and provide their age. Let’s say we write the following PHP snippet:
$username = $_POST["username"]; $age = $_POST["age"]; $sql = "INSERT INTO users(username, age) VALUES('$username', '$age')"; mysqli_query($con, $sql); // $con is our connection variable
Using PDO (PHP Data Objects)
$username = $_POST["username"]; $age = $_POST["age"]; $sql = "INSERT INTO users(username, age) VALUES('$username', '$age')"; $statement = $con->prepare($sql); // $con is our connection variable $statement->execute();
The problem with this approach is simple:
If a user with malicious intent enters a username as follows, along with some random value for age:
';DELETE FROM users;
The SQL statement is going to become:
INSERT INTO users(username, age) VALUES('';DELETE FROM users;', '666')
The SQL statement we wrote is now broken into three parts. The first part does not have any effect:
INSERT INTO users(username, age) VALUES('';
The second part does the damage, by deleting all records from the table:
DELETE FROM users;
The third part is quite insignificant by now.
How to prevent SQL Injection
Preventing such attacks is more straightforward than you think. Whether you prefer MySQLi or PDO, you must make it a point to prepare your SQL statements by binding your dynamic parameters with it. Both MySQLi and PDO support binding parameters.
Parameters are values which we leave unspecified. SQL statements created using unspecified values or parameters are known as dynamic statements.
Have a look at the following snippets, which do the same as above, but uses parameter binding instead:
$username = $_POST["username"]; $age = $_POST["age"]; $sql = "INSERT INTO users(username, age) VALUES(?, ?)"; $statement = $mysqli->prepare($sql); $statement->bind_param('sd', $username, $age); $statement->execute();
bind_param() is a function which takes the data types of the parameters you want to bind as the first argument. Here, ‘sd’ means string and double. You can find more information in the official docs.
$username = $_POST["username"]; $age = $_POST["age"]; $sql = "INSERT INTO users(username, age) VALUES(:username, :age)"; $statement = $con->prepare($sql); $statement->execute(array( 'username' => $username, 'age' => $age ));
What have these question marks and colons got to do with SQL?
Both are doing the same job. In MySQLi, we use question marks to indicate a parameter and in PDO we can use colons to name the parameters.
So How Does it Work?
It works by following three simple steps:
- Prepare: An SQL statement template using the parameters is created and sent to the database.
- The database parses, compiles and performs query optimization on the SQL statement template and stores the result without executing it.
- Execute: PHP binds the values to their parameters and the database executes the statement.
Parameter binding comes with its intended set of advantages, but it also is very powerful in preventing SQL Injection as the parameter values are transmitted using a different protocol.
I tried injecting SQL the same way after binding the parameters and this time, the username itself was set to –
';DELETE FROM users;
MySQLi vs PDO Parameter Binding
As you saw above, both MySQLi and PDO support binding parameters, so which one is more preferable? It really comes down to your personal preference. I prefer to use PDO, since it supports naming parameters.
Using named parameters in your SQL statement can reduce the chances of error caused by confusion, which may occur when the number of parameters are significant. Keep in mind though, PDO also supports unnamed parameters using question marks.
I hope you found this article helpful. In the next article, I am going to walk you through a few examples of Parameter Binding in some real life situations. Stay tuned and subscribe to GeekyMinds for notifications. 😎