How To Control SQL Injection In PHP

Introduction

This is an interview question - When a person showcases himself as a Full Stack developer, it is very important that the developer should have the basic knowledge of the security aspects of the applications.

When it comes to SQL injection, it is the main place where the data can be very easily hacked by external people. SQL injection can be prevented in several ways using the .Net framework and other high-level framework oriented programming.

When we do the development using PHP, it is a nightmare to prevent the SQL injection and make your application secure.

Solution:

In PHP, we have the concept of “prepared statements “ and using these prepared statements along with the parameterized queries will help you prevent the SQL injection in PHP.

There are SQL statements that are sent to any application and they are parsed by the database server. Here, the database server will parse separately from any other parameters supplied. In this method, the parsing of the data is isolated from the actual values, so it is impossible for an attacker to inject any of the malicious SQL queries into the actual values.

How to Implement this?

We have two types to implement this.

• PHP Data Objects - [PDO]
• MySQL Improved Extension - [MySQLi]

What is PHP Data Object [PDO]?

The PHP Data Object (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server.

Reference 

http://php.net/manual/en/intro.pdo.php

What is MySQL Improved Extension [MySQLi]?

The MySQLi extension allows you to access the functionality provided by MySQL 4.1 and above.

The MySQL Enterprise Monitor and the MySQL Query Analyzer continuously monitor your databases and alerts you to potential problems before they impact your system. It's like having a "Virtual DBA Assistant" at your side to recommend best practices to eliminate security vulnerabilities, improve replication, and optimize performance and more. As a result, the productivity of your developers, DBAs, and System Administrators is improved significantly.

Reference

http://php.net/manual/en/intro.mysqli.php

Implementation using PHP Data Objects - [PDO]

Here, the following statement of code implementation is shown, where the student table is accessed and the name parameter is supplied, as the following.

Code

$stmt = $pdo - > prepare('SELECT * FROM STUDENTS WHERE name = :name');
$stmt - > execute(array('name' => $name));
foreach($stmt as $row)
{
    // TODO: Manipulate something with $row
}

Implementation using MySQL Improved Extension - [MySQLi]

Here, the following statement of the code implementation is shown, where the student table is accessed and the name parameter is supplied, the executed data is assigned to the results and then the result variable is used to do further manipulation of the data.

Code

$stmt = $dbConnection - > prepare('SELECT * FROM students WHERE name = ?');
$stmt - > bind_param('s', $name);
$stmt - > execute();
$result = $stmt - > get_result();
while ($row = $result - > fetch_assoc())
{
    // TODO: Manipulate something with $row
}

Setting up the connection

Real prepared statements are not used by default when we are trying to use PDO to access a MySQL database. We have to disable the emulation of prepared statements as the first step and the example of creating a connection using PDO is explained below.

Code

$dbConnection = new PDO('mysql:dbname=dbSecuritytest;host=127.0.0.1;charset=utf8', 'username', 'password');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Explanation

It is advised to have the error mode enabled, which will not stop with a Fatal Error when the exception occurs. This also provide developers the chance to catch any exception that is thrown during execution.

setAttribute()  in the Line number #3 is a mandate and this tells PDO to disable emulated prepared statements [Default is it enabled], setAttribute()  take care of making the real prepared statements.

Conclusion

The statement which was implemented is now free from SQL injection and the code is well secured by following the above set of methods.