Simple tricks to prevent SQL Injection using PHP

SQL Injection is one of the biggest threat most website developer are worried for. There are various solutions available. But there are no 100% sure shot solution to prevent SQL Injection.

What is SQL Injection?

A way to inject SQL queries in to the server. This injection can be done through input fields such as email, name field, and some times in url. If a website is not clean, then there are chances of SQL injection easily.

Example:

http://www.example.com/employees.php?id=3899

The above url can be an easy target for SQL Injection. It clearly says that there is table inside the database which contains employees information and it has one column which contains employee id.

(Note: Not necessarily, table name should be employees and employee id column name  should be id)

Solution:

Make such url clean.

Example: http://www.example.com/employees/3899

The above url is hiding the logic. But still it is vulnerable to hackers. It is the duty of developer, to handle the url queries with parameter values.

Following are few solutions:

Solution 01:

If you are pretty sure about the length of the parameter value in the url, then restrict these values accordingly.

In above example, assume, the employee id is number value only and there are only 5000 employees in the company.

Therefore, upon loading the url with parameter value, we can check first whether the id value is number and the maximum length of the id value is 4. If this condition does not meet, then the url is invalid.

Solution 02:

In general we prepare sitemap for our website. So we are aware of all the web page url. So we can check whether the visited url exist in the sitemap. If exist, then load otherwise exist.

Solution 03:

In general, no one uses special character in email, first name, last name and in url. So using regex method, (Preg_Replace), we can remove all the unwanted characters present in the fetched string.

Example:

http://www.example.com/employees/3899;’select * from

$val=$_GET[“val”];

$val = preg_replace(‘/[^a-zA-Z0-9]+/’,”,$val);

There it will replace every character from the val variable except, a-z, A-Z, 0-9.

Solution 04:

We can sanitise the values retrieved from input fields or urls.

And that is using

mysql_real_escape_string function.

or if, using mysqli, then

mysqli_real_escape_string();

Example:

http://www.example.com/employees/3899;’select * from

$conn=mysql_connect(“host”,”user”,”pwd”,”dbname”);

$val=mysql_real_escape_string($conn, $_GET[“val”]);

or

$val=mysqli_real_escape_string($conn, $_GET[“val”]);

Solution 05:

If ultimate goal is to fetch data from database then we could use prepared statement in SQL.

Using PDO :

$stmt = $pdo->prepare(‘SELECT * FROM employees WHERE name = :name’);

$stmt->execute(array(‘name’ => $name));

foreach ($stmt as $row) {
// do the required task here
}

Using MySQLi:

$stmt = $dbConnection->prepare(‘SELECT * FROM employees WHERE name = ?’);
$stmt->bind_param(‘s’, $name); // ‘s’ specifies the variable type => ‘string’

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
// Do required task here
}