PHP SQL Injection (Causes and Fixes)

In the class of injection attacks, PHP SQL injection attack has come out highly prominent. The majority of websites are vulnerable to it. According to research, more than 50 % of attacks were done on the web using SQL injection. In this article, we would discuss how SQL injection is carried out and how we can prevent the same in PHP applications.

This Blog Includes show

What is SQL Injection (SQLi)?

First, let us understand SQL injection. SQL Injection (SQLi) is an approach where hackers change the SQL statement running at the backend through falsification of executed SQL commands. Such form of injections is carried out through input fields of the form and cause awful aftermath on the database. It eventually leads to the disclosure of highly sensitive information from the database. As the hacker gains control over the database through injecting SQL commands, he/she gets the right to insert, update or even delete data from the database.

SQL injection attack occurs when the code is not written in a secured fashion. Some of them seemingly written in secure coding, but still gives way to SQLi are:

Incorrect filtration of space characters and type handling
Passing unsanitized, improperly encoded data to the database
Mixing of the code and data
Use of quotation marks to delimit strings

In short, when user input is not validated properly, then it leads to user data being interpreted maliciously by the database. If the user input is directly fetched from front-end and inserted into the query, then it would also lead to a compromise.

Another issue that leads to SQLi is that the errors are displayed to the user which may be misused by a malicious user to get details about database type and version and thus deploy exploits that cause SQL injection attack.

What is PHP SQL Injection?

Example of Insecure Codes

Let’s understand the concept of PHP SQL injection attack with the help of the following vulnerable PHP code:

<?php

$username = $_POST[‘uname'];

$pass = $_POST[‘password'];

$sql = "SELECT * FROM user_table WHERE username='".$username."' AND userpwd='".$pass"';";

?>

The above code accepts the user input directly without any validation and inserts it directly into the SQL query that would run over the database. A hacker can also insert in it logical queries that would return a result true i.e. if the variable $password gets value as ” or ‘1’=’1′ then the value becomes as 1=1 which is always true. This would eventually lead to a successful login which would lead to a severe security compromise.

Also, consider another scenario where the error gets displayed on to the user is

<?php

…

elseif($mysqli->error)

{

print($mysqli->error);

}

?>

In this scenario, instead of printing the error logs on to the user screen, it would be a better practice to keep a database where error logs can be lodged in and later used to check the number of times the website was subjected to SQLi.

Example of Secure Codes

An example of secure coding to avoid PHP SQL injection could be validating the username and password for such logical queries in the following manner:

<?php

Function checkUserInput($str)

{

return

str_replace(array("‘",""", "'", ‘"‘),array("‘","""'",""",$str));

}

$username = checkUserInput($_POST[‘uname']);

$pass = checkUserInput($_POST[‘password']);

?>

In this manner, user input can be validated before being appended to an SQL query. Another approach to prevent PHP SQL injection is to use prepared statements (PHP Data Objects (PDO)). This is an efficient approach wherein SQL statements can be executed repeatedly. The PDO provides methods that make parameterized queries easy to use. It also enhances readability and portability of code and works with not only MySQL database but other types of databases as well. PDO defines which part to treat as user inputs and the remaining portion as database commands.

An example code snippet for PDO for MySQL database is:

//DB credentials

define(‘DB_HOST','localhost');

define(‘DB_USER','John_Reaper');

define(‘DB_PASS','Ire@pP@$$w0rd$');

define(‘DB_NAME','users');

if(isset($_GET[‘id']))

{

$id = $_GET[‘id'];

if(is_numeric($id) == true)

{

try

{

$dbh = new PDO(‘DBHOST;DB_NAME','DB_USER','DB_PASS');

$dbh->setAttribute(PDO::AFTER_ERRMODE, PDO::ERRMODE_EXCEPTION);

$q = "SELECT username FROM users WHERE id = :id";

$sth = $dbh->prepare($q);

$sth->bindParam(‘:id',$id);

$sth->execute();

$sth->setFetchMode(PDO::FETCH_ASSOC);

$res = $sth->fetchColumn();

print(htmlentities($res));

$dbh = null;

}

catch(PDOException $e)

{

error_log(‘PDOException - ‘.$e->getMessage(),0);

http_response_code(500);

die(‘Connection to database unsuccessful');

}

else

{

http_response_code(400);

die(‘Error processing malformed request');

}

How to prevent PHP SQL injection

Thus, you can secure your websites from SQL injection by implementing the following best practices:

Validating user input either at front end using JavaScript or at the backend using PHP.
Not displaying the error to the user. Instead, logging the errors into a file and making it inaccessible to an attacker. Also, the error message that is displayed to the user must be very generic and not reveal any information about the database.
Using a parameterized query which specifies the part of SQL query that needs to be treated as user input.
Removing unused stored procedures as they are easily exploitable and thus prevent SQL injection.
Giving appropriate rights to proper users to avoid any compromise in case attackers bypasses the authentication.

Ultimate Protection from PHP SQL Injection

Using a firewall like Astra can heighten your immunity to SQLi. In addition to SQLi, Astra Security Suite shields a website from 100+ more attacks, some of which are bad bots, XSS, CSRF etc.

Our Malware scanner is known to scan a website in less than 10 minutes. And takes even lesser time (<1 minute) for subsequent scans. Under our Security Audit and Pentesting offering, engineers at Astra scan each line of code manually to find and mend vulnerabilities in your website to make the security impenetrable.