How to protect a PHP website from SQL injection attacks

by Ahmed Khan  September 18, 2015

SQL Injection is a common problem that arises due to loopholes in the backend programming. There are many methods that can be used to avoid SQL injections in a PHP website.

sql injection

In this article, we will be discussing one of the scenarios and its remedy for protecting your PHP website.

But first, it is important to know what actually SQL Injection is and what harm it causes:

SQL Injection

SQL is a technique used by hackers through executed SQL commands that change the SQL statement running at the backend. Such kind of injections are usually done through input fields of the form causing a bad effect on database. This results in loss of sensitive information from the database.

Through such tactics, attackers input vulnerable data to SQL interpreter that executes unintended commands. By such injections attackers may insert, update and even delete data from database.

The following example will help you better understand the concept of SQL injections:

Example

Suppose we have a form containing 2 text fields’ username and password, along with a login button. The backend PHP code will be as follows:

<?php
$userName=$_POST[‘userName’];
$password=$_POST[‘password’];
$sqlQuery=”SELECT * FROM users WHERE user_name='”.$username.”‘ AND user_password='”.$password”‘;”;
?>

The above code contains a loophole, if a user enters ‘ or ‘a’=’a ‘or’ then the variable $password will have the value ‘ or ‘a’=’a ‘or’

In this way, above query will be updated as:

<?php
$sqlQuery=”SELECT * FROM users WHERE user_name='”.$username.”‘ AND user_password=” or ‘a’=’a’;”;
?>

In the above example, the statement a=a is always true. So the statement is executed without the matching of actual password.

Solutions to SQL injection vulnerabilities

In this post, I am telling the two methods through which you can solve this issue.

Method 1

Now you need to make a few changes in the previous code. Make a function like:

<?php
function BlockSQLInjection($str)
{
return str_replace(array(“‘”,”””,”‘”,'”‘),array(“‘”,”&quot;”‘”,”&quot;”,$str));
}
?>

Through above statement, str_replace() function will replace all characters in the string. Now you will use the function as follows:

<?php
$userName=BlockSQLInjection($_POST[‘userName’]);
$password=BlockSQLInjection($_POST[‘password’]);
?>

These functions will help you avoid SQL injection vulnerabilities.

Method 2

Another approach for avoiding SQL injections is using PHP Prepared Statements. Prepared statement is a feature in PHP which enables users to execute similar SQL queries efficiently and repeatedly.

Through prepared statements, SQL query is sent to the database with few unspecified values called parameters denoted by ‘?’. The database then compiles it and stores the result without executing. Afterwards, the application binds values to the parameters before finally executing the statement. This enables execution of the statement repeatedly with different set of values.

Advantages of Executing Prepare Statements

  • It reduces parsing time as the query is executed once but can be executed multiple times with the same parameters.
  • Bound parameters reduce the bandwidth to the server because whole query is not sent every time but the parameters are sending.
  • Bound Parameters reduces the bandwidth as whole query is not sent every time but parameters are sent.

Example

Consider the following example:

<?php
$stmt=$conn->prepare(INSERT INTO MyGuests(firstname,lastname,email)VALUES(?,?,?)”);
$stmt->bind_param(“sss”,$firstname,$lastname,$email);
//set paramters and execute
$firstname=”John”;
$lastname=”Doe”;
$email=”john@example.com”;
$stmt->execute();
$firstname=”Mary”;
$lastname=”Moe”;
$email=”mary@example.com”;
$stmt->execute();
?>

In the above example, you can see in line 14 that the insert statement contains values (?,?,?). It indicates that we can substitute integer, double, string or blob value. Now consider line 15 containing bind_param. This function basically binds different parameters to the query and conveys parameters to the database. ‘sss’ is an argument which basically lists the type of data. Argument may be integer(i), double(d), string(s), BLOB(b). By telling the database what type of data to expect, we basically minimize risk of SQL injection.

Tips For Avoiding SQL Injection Vulnerabilities

Prevention is better than cure. You must take the following precautions:

  • To avoid SQL injections, user input should be authenticated for a definite set of rules for syntax, type and length.
  • While giving administrative rights to database to particular users, try to give the least rights in order to avoid any future attacks to sensitive data.
  • If a user is given rights for a specific application, make sure that he does not access the application unnecessarily.
  • Removing unused stored procedures may also help in the prevention of SQL injects.
  • Be careful when using stored procedures as they are easily exploited.

 

Create PHP websites without the worry of server management.

Deploy your PHP app on optimized PHP hosting servers.

About Ahmed Khan

Ahmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works. You can email him at ahmed.khan@cloudways.com

Stay Connected:

You Might Also Like...