PHP Basics

What is the simplest approach to prevent SQL Injection in PHP Scripting?

Hackers follow sql injection to inject virus, delete data, collect data, change data etc.

SQL Injection

Hackers keep looking for the loop holes present in a website. Best target points are input fields. Such as login forms which has input fields. With the help of computer program, they find out the vulnerable points. Once they find, they try to inject sql commands through these points.

In simple language, hackers pass sql commands through the input field. These sql commands do the rest destruction / damage what ever you say.

SQL Injection Prevention

Fortunately there are simple solutions which can prevent sql injection in PHP. We have to remember this simple point, sql injection can only be performed through input fields, text area and address bar of the browser.

Solution 1

Keep weburl neat. With the help .htaccess file we can make web url absolutely clean.

(Webpages whose parameters are visible, such webpages are high vulnerable to sql injection. Example – https://www.gyanol.com/demo?table=demo-table&category=demo-category)

Example

https://www.gyanol.com/demo?table=demo-table&category=demo-category

This url clear says it takes two parameters, table name and category. Ofcourse at the backend, these values are going to be compared with the table in the database. This hints enough the hacker what should he do to inject sql command in to the website.

We should always keep url clean. It can be done with the help of .htaccess

https://www.gyanol.com/demo/demo-table/demo-category

The above url is neat. It hardly hints what is going to happen at the backend. It increases the difficulty level for hackers. But wait, still there is chance for sql injection.

Solution 2

The best solution is to sanitise every data that is collected through input fields or address bar before sending it to database.

Sanitise?

That is escaping data values.

Mysqli real escape string

PHP has mysqli_real_escape_string function which helps to escape data value and sanitizes it.

Example

<?php

$employeename =$_POST[“employee_name”];

$employeename=mysqli_real_escape_string($connection,$employeename);

$sql=”select * from employee_table where employe_name=’$employeename'”;

?>

 

In the above example, employee name is collected through a form. In the second line, we sanitised the collected value with the php function mysqli_real_escape_string function and prevents any kind of sql command execution in the third line.

It reduces down 99% chance of successful SQL Injection.

Wait, there is still 1% chance of sql injection and hackers live for this 1% chance.

Solution 3

This is the best solution and most company, organisation follow this approach. The approach is straight and simple. We are going to pass the data to the sql statement if it fulfills the type of expecting data the sql command should receive.

Restrict data length wise

Suppose we are aware, the input field data length should not be greater than 4 then if the input data value length is less than 5 then only we pass to the sql command other wise we will show a rejection message to the user.

Restrict data type wise

Suppose we are expecting number only and that is four digit number. Then we can check if the input field data is a number and a four digit number. If it fulfills this condition then we will allow the value to the sql command.

Remove unwanted characters

If we are sure, the input field should contain only alphabets and single space, then with the help of preg_replace function, we can remove unwanted characters

Example

<?php

$employee_name=$_POST[“employee_name”];

$employee_name=preg_replace(‘/[^a-z\s]/’,”,$employee_name);

?>

 

In similar fashion, if we are sure, the input field data should be number only, then also using preg_replace function we can remove unwanted characters from the string.

<?php

$age=$_POST[“age”];

$age=preg_replace(‘/[^0-9]/’,”,$age);

?>

Solution 4

Following the above logic, there is prepared statement in php, that forces the values to behave as data only.

Note: There is no single solution which can prevent sql injection absolutely.

Depending upon data, and  backend architecture, we adopt an approach that prevents sql injection in PHP.