How to Prevent SQL Injection in PHP

SQL injection is a technique, used to attack data-driven applications. Using this method, hackers will try to execute their SQL statements within your application and access your database data.

Here is an example SQL injection. Let's consider you have a login form with two fields - email (text field) and password (password field). Upon login, you will build and execute a similar query:

<?php
"SELECT * FROM `users` WHERE `email` = '".$_POST["email"]."' AND `password` = '".$_POST["password"]."'";
?>

You can see that the query is searching for a user in `users`table, which matches the email and password posted via the login form. However, since both email and password are not properly handled, an attacker can modify the query. Assume that they enter:

Email: email@domain.com
Password: password

The constructed query will be:

SELECT * FROM `users` WHERE `email` = 'email@domain.com' AND `password` = 'password';

Which seems to be correct. However, if the attacker uses:

Email: email@domain.com
Password: password'; DROP TABLE 'users

the query will become:

SELECT * FROM `users` WHERE `email` = 'email@domain.com' AND `password` = 'password'; DROP TABLE 'users';

And of course, you do not want people to execute such queries over your database.

To protect your PHP application from being abused via such SQL injections, you should correctly set all SQL queries that are being run. With older versions of PHP (>= 4.3.0, 5) you would do that with mysql_real_escape_string(). So above query would look like this:

<?php
"SELECT * FROM `users` WHERE `email` = '".mysql_real_escape_string($_POST["email"])."' AND `password` = '".mysql_real_escape_string($_POST["password"])."'";
?>

This is how the SQL injection protected query looks like now:

SELECT * FROM `users` WHERE `email` = 'email@domain.com' AND `password` = 'password\'; DROP TABLE \'users'

You can see that the data being passed via $_POST is now escaped and DROP TABLE query will not be executed separately, but will be considered as a part of the password string.

With the latest versions of PHP you can now use PDO and prepared queries. Here is an example:

$stmt = $conn->prepare("SELECT * FROM `users` WHERE `email`=:email AND `password` = :password");
$stmt->bindValue(':email', $_POST["email"]);
$stmt->bindValue(':password', $_POST["password"]);
$stmt->execute();

The key function here is prepare(). It secures the SQL query and protects it from SQL injections.

There are other ways to verify that data passed via SQL queries is valid and not abused. For example, if you expect an integer to be passed, you may use interval() to convert the inputted data into an integer.

"SELECT * FROM `users` WHERE `age` = '".intval($_POST["age"])."'";

SQL injection is one of the top website vulnerabilities, so you should be very careful when using user inputted data to construct SQL queries.