SQL INJECTION ATTACK PREVENTION IN PHP | CODE EXAMPLES

After several years of penetration testing and working with development teams to secure their systems, I have noticed that there was no complete blog post that gives a detailed explanation of how to prevent SQL injection attacks in all the most popular web application development languages and frameworks. Therefore, I decided to write a series of multiple posts to make it the most complete guide to prevent SQL injection.

This guide is an open series of posts and this is the first post. If you want me to add any technology, please just comment below and I will be very happy to explain SQL injection prevention for that technology. This guide will analyze the following technologies:

• PHP
  • Laravel
  • Symfony
  • CodeIgniter
  • CakePHP
  • FuelPHP
  • Zend
• JEE
  • JDBC
  • JPA
• ASP.NET
  • ADO.NET
  • ADO.NET Dopper
  • NHibernate

This first post will focus on the PHP language and all its frameworks. More articles will follow to deal with the other listed technologies.

For those who do not know what is SQL injection attack, let me do a small introduction to explain this to them. SQL injection attacks happen when a bad user tries to inject a malicious SQL request into a legitimate request. The impact of SQL injection attack differs from a situation to another depending on multiple elements related to the app environment, and it can go from as “simple” as information leak to a full server control. I will not go deeper into the way an attacker could exploit this vulnerability or how we can discover it in blackbox tests as this is not the objective of this post. What we will see is how to discover it in source code and how to fix it.

How to prevent SQL injection attack in a Pure PHP source code ?

Let us start our journey with one of the oldest and popular development technology PHP without any frameworks. Now the first thing to do is to know how a vulnerable source code line looks like to be able to identify the vulnerability.

Here is an example of an authentication request:

$request = “SELECT * FROM users WHERE username=”. $_POST[‘username’] .” AND password = ”. $_POST[‘password’];
$result = $mysqli->query($request);

Now if you take a close look at the request you will notice that the user parameters $_POST[‘username’] and $_POST[‘password’] are directly injected in the SQL request without any filtering. Doing so make this code line vulnerable to SQL injection.

To fix this vulnerability you will need to filter the data before using them in the sql request. To do this the best solution is to use the prepared statements. Here is an example of how you can use the prepared statements to fix this vulnerability:

$conn = new mysqli($servername, $username, $password, $dbname);
$stmt = $conn->prepare("SELECT * FROM users WHERE username=? AND password=?");
$stmt->bind_param($username, $password);
$stmt->execute();

Good practice

You have the possibility to not use the bind_param() function here is an example of how to do this :

$conn = new mysqli($servername, $username, $password, $dbname);
$stmt = $conn->prepare("SELECT * FROM users WHERE username=:username AND password=:password");
$stmt-> execute(array(‘username’=>$_POST[‘username’],‘password’=>$_POST[‘password’]));

After years of analyzing source codes … I have discovered an interesting error that many developers do while fixing this vulnerability. Take a look at the following example:

$stmt = $conn->prepare("SELECT * FROM users WHERE username=”.$_POST[‘username’].” AND password=”.$_POST[‘password’]);

If you look at this example, you will see that we are using the prepared statements. However, the parameters are directly injected in the SQL request. This is a very common mistake that developers do, and make the code vulnerable to SQL injection even if we use the prepared statements.

The idea behind the prepared statements is to filter the parameters before injecting them in the SQL request … keep this in your mind.

Very Important note :

PHP offers some other functions to filter SQL requests and prevent SQL injection, but some of them are not efficient. The mysql_real_escape_string() function, for example, is one of the most known PHP functions to prevent SQL injection. Unfortunately, this function is really efficient. By using this function, the MySQL library will add a backslash to the following characters: NULL, \ x00, \ n, \ r, \, ‘, “and \ x1a. However, for a situation like this one :

$id = mysql_real_escape_string("1 OR 1=1");   
$request = "SELECT * FROM users WHERE id = $id";

In red color is what a bad user will send you

The mysql_real_escape_string() will not protect you against such attack.

This is why I always recommend using the prepared statements instead of this function.

How to prevent SQL injection attack in Laravel framework ?

Now that we have seen the SQL injection attack against a Pure PHP code, now it is time to see how we can fix the vulnerability in a framework based web application. Before we start talking about this, let me first give you a small description of the Laravel framework.

Laravel is an open-source web framework written in PHP respecting the model-view-controller principle and entirely developed in object-oriented programming.

Example 1

Here is an example of a vulnerable SQL request in a laravel :

$user = DB::select('select * from users where username='. $request->post('username').' AND password='. $request->post('password'));

The Laravel framework offers some awesome functions to help developers to secure the SQL request against malicious injections. Unfortunately, most of the SQL injection vulnerabilities that I discover in such apps are related to misuse of Laravel functions.

Let’s analyze the previous example, according to Laravel documentation the DB::select function could receive two parameters. The first one is the SQL request and the second is the parameters. So to filter the parameters for SQL injection you will need to insert the parameters in the request using the second parameter just like this example :

$user = DB::select(‘select * from users where username=? AND password=?‘, [$request->post(‘username’), $request->post(‘password’)]);

Example 2

Laravel has multiple ways to communicate with the database in this example you will see that laravel force the developer to use some functions that automatically filter the user data, like the following example:

Users::where('username', $request->get('username'))->orderBy($request->get('orderby'))->get();

This function is supposed to be secured against SQL injection and they are, the only problem is at the orderBy() function level. According to Laravel documentation, this function does not filter the user data so an SQL injection attack is still possible through this function.

The best thing to do is not giving the user the possibility to control the table name, but If this is something inevitable, then you will need to use a white list to validate the user data before inserting it in that function.

How to prevent SQL injection attack in Symfony ?

Let’s see another well-known PHP framework that offer a set of reusable PHP components to accelerate PHP apps development. Symfony is also used by some of the most known web CMS like Drupal and Magento. 

Symfony is one of the most secure frameworks that you can work with, it offers a wide number of functions to write a secure code. However, if those functions are not well used then you get a vulnerable code. So here is an example of a vulnerable code:

$entityManager = $this->getEntityManager();
$query = $entityManager->createQuery('SELECT p
            FROM App\Entity\Product p
WHERE p.price > '. $request->query->get('price')
);

Now let’s analyze this vulnerable code. According to Symfony documentation, createQuery() is a function that uses by default the prepared statements. So, the object resulting from such a function gives you access to the setParameter() function. This one filters all the user data to avoid an SQL injection. Here is an example of how to use it :

$entityManager = $this->getEntityManager();
$query = $entityManager->createQuery('SELECT p
            FROM App\Entity\Product p
            WHERE p.price > :price
            ORDER BY p.price ASC'
        )->setParameter('price', $price);

How to prevent SQL injection attack in Codeigniter ?

CodeIgniter one of the most powerful, lightweight, and popular PHP frameworks with a very small footprint. It was built for developers who need a simple and elegant toolkit to create full-featured web applications. Like Symfony and Laravel, Codeigniter also comes with some security systems to help developers to create safer apps.

However, also with Codeigniter some developers make the same mistake and inject the user data without any filtering. Here is an example of such errors that lead to an SQL injection attack:

$query = 'SELECT * FROM users WHERE username = '. $this-> input->post('username'). ' AND password= '. $this-> input->post('password');
$this->db->query($query);

Now to fix this issue, you will need to take a look at Codeigniter documentation. According to it, the query() function take two parameters. The first one is the sql query, and the second one is the parameters that you want to bind.

Example 1

By default, this function filter all the binded parameters to prevent SQL injections. Here is an example of the correct way to use this function:

$query = 'SELECT * FROM users WHERE username = ? AND password = ? ';
$this->db->query($query, array($this->input->post('username'), $this-> input-> post('password')));

Example 2:

Codeigniter provide another way to perform SQL request will preventing SQL injections. Here is an example of using the Active Record Class to prevent SQL injections:

$this->db->get_where('users',array('username'=>$this->input->post('username') ,'password' => $this->input->post('password')));

How to prevent SQL injection attack in CakePHP ?

CakePHP is a rapid development framework for PHP which uses commonly known design patterns like Associative Data Mapping, Front Controller, and MVC. CakePHP offer a bunch of built in component to facilitate the development of web applications and will making them more secure.

However, CakePHP apps are also vulnerable to SQL injections if it is not well used. Here is an example of a vulnerable code to SQL injection attack in this framework:

$results = $connection-> execute('SELECT * FROM users WHERE username = '. $this->request->getParam('username').' AND password='. $this->request->getParam('password'))->fetchAll('assoc');

The execute() function by default use the prepared statements. However, the previous example still vulnerable to SQL injection attack, as the user data are inserted directly in a legitimate sql request. Here is an example of the right way to use this function:

$results = $connection->execute('SELECT * FROM users WHERE username = :username AND password=:password', [
'username' => $this->request->getParam('username'),
'password' => $this->request->getParam('password')
])->fetchAll('assoc');

The CakePHP framework offer also a system called Query Builder, which forces user data filtering to prevent SQL injection attacks. According to CakePHP documentation, Underneath the covers, the Query Builder use the prepared statements.

Here is an example of how you can use such system in the right way:

use Cake\ORM\Locator\LocatorAwareTrait;
$users = $this->getTableLocator()->get('users');
// Start a new query.
$query = $users->find();
$query->where(['username' => $this->request->getParam('username'),
'password' => $this->request->getParam('password')
]);

How to prevent SQL injection attack in FuelPHP ?

FuelPHP is one of the most recent PHP framework that was born based on the best ideas of each framework in the market. It was developed with PHP 5 and is fully object-oriented. In FuelPHP, security was the front and center of concern, which pushed its contributors to implement many security mechanisms to filter user data. SQL injection attacks one of the reasons to implement such mechanisms in FuelPHP.

However, even with such a powerful framework, a simple misuse of those mechanisms put the whole code in danger for an SQL injection attack. Here is an example of such code error:

$query = "SELECT * FROM article WHERE id = ". Input::get('id');
$result = DB::query($query)->execute();

To fix this, there are also two techniques like the other frameworks. The first one is to actually correctly using the query() function, by binding the parameters like the following example :

$query = "SELECT * FROM article WHERE id = :id"; // our query
$result = DB::query($query)->bind('id', Input::get('id'))->execute();

The second solution is using the ORM mechanism implemented in the FuelPHP framework to communicate with the database. Here is an example of how to use it:

$user = DB::select()->from('article')->where('id', Input::get('id'))->execute();

How to prevent SQL injection attack in zend framework ?

Zend framework (changed to Laminas Project) is one of the most known framework in the world of PHP. It was developed with performance tuning in mind, which explains why each new version is much faster than the old one. Zend was also developed with the best security practices, which pushed his developers to implement additional security mechanisms to deal with known cyber threats.

Some of those mechanisms were implemented to deal with the SQL injection attacks. But as always a misuse of those mechanisms leads to a vulnerable code. In this part of the article I am going to show an example of such an error:

$adapter->query('SELECT * FROM `article` WHERE `id` = '. $this->getRequest()->getPost(‘id’));

Here is how to fix this vulnerability:

$adapter->query('SELECT * FROM `article` WHERE `id` = ?', [$this->getRequest()->getPost(‘id’)]);