Best 2024 Guide To Protect Against PHP SQL Injection Attacks

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 PHP SQL Injection attacks in a website.

Web developers use different tactics and logic to find out vulnerabilities and their possible solutions. Nowadays you might have heard the term TDD and BDD. These both are development processes used in programming to assure the maximum security with testing while developing in different languages.

Read More About: Ultimate Guide to PHP Security Best Practices

In this article, we will be discussing a few scenarios of SQL Injection and how to prevent them in web apps deployed on different PHP web hosting servers.

Back in 2017, around 50% malicious attacks on the web apps were based on SQL Injections. Source: Akamai

But first, it is important to know what actually SQL Injection is and what harm it causes. But before let’s understand a few concepts.

What is TDD?

Test Driven Development is a practice in which you write a test first and code later to pass the test. This approach makes sure that the number of bugs must be solved while development. The developer tightly writes the test cases first and then the code, as this practice enables him to quickly find out the potential bugs.

The process is as follows:

1. Write a test.
2. Run the test (can fail).
3. Write the code.
4. Run the test again and see it pass.
5. Refactor

Read More About: PHP Unit Testing With PHPUnit

You can clearly see in the above process that we are writing the tests first and then passing the code from it. If the test passes then the cycle will go in a loop. See the visual representation below:

Read More About: Test and Deploy With TravisCI

What Is Exactly SQL Injection?

SQL Injection is a technique used by the hackers to change SQL statements running at the backend from forged executed SQL commands. Such kind of injections is 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 PHP MySQL injections, attackers may insert, update or even delete data from database.

Cause Of SQL Injection

While coding we should follow best practices to avoid SQL injection in PHP. Some of the causes which can affect these attacks are:

1. Incorrectly filtered space characters
2. Incorrect Type handling
3. Passing unsanitized data to DB
4. Not using full Unicode encoding
5. Mixing of the code and data.
6. Use of quotation marks to delimit strings

So these are some causes, you might watch out while coding to avoid SQL injections.

Let’s See The Examples

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

Example # 1

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, the 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 the actual password.

Example # 2

The SQL query is a legitimate program. And we are creating such a program dynamically, by adding some data on the fly. This data can interfere within the program code and can even alter it, as every SQL injection example shows it (all examples in PHP/Mysql):

$expected_data = 1;

$query = "SELECT * FROM users where id=$expected_data";

will produce a regular query

SELECT * FROM users where id=1

while this code can surprise you.

$spoiled_data = "1; DROP TABLE users;"

$query   = "SELECT * FROM users where id=$spoiled_data";

will produce a malicious sequence

SELECT * FROM users where id=1; DROP TABLE users;

It works because we are adding the data directly to the program body and it becomes a part of the program, so the data may alter the program and depend on the data passed, we will have either a regular output or a table user deleted.

Solutions to SQL injection vulnerabilities

The SQL injection protection in PHP is quite a complexed topic. In this post, I am demonstrating 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 the 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. A prepared statement is a feature in PHP which enables users to execute similar SQL queries efficiently and repeatedly.

Read More About: Learn How to Use Prepared Statements

Through prepared statements, SQL query is sent to the database with a few unspecified values called parameters denoted by ‘?’. The database then compiles it and stores the result without executing.

Afterward, the application binds values to the parameters before finally executing the statement. This enables execution of the statement repeatedly with a 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 the whole query is not sent every time but the parameters are sending.
• Bound Parameters reduces the bandwidth as the 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="[email protected]";

$stmt->execute();

$firstname="Mary";

$lastname="Moe";

$email="[email protected]";

$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.

Supercharged Managed PHP Hosting – Improve Your PHP App Speed by 300%

Tips For Avoiding PHP SQL Injection Vulnerabilities

Prevention is better than cure. You must take the following precautions as these are the best ways to prevent SQL injection in php:

• 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 the 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.

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.

Get Connected on: Twitter Community Forum