SQL injections are classified into three types: In-band SQLi (Classic), Inferential SQLi (Blind), and Out-of-band SQLi are the three types of SQLi.
SQL injection is a typical issue that emerges as a result of vulnerabilities in backend programming. There are numerous strategies for preventing PHP SQL Injection attacks on a website.
PHP SQL injection is a technique malicious actors use to exploit vulnerabilities in PHP applications and gain unauthorized access to databases.
In this article, we will explore the concept of PHP SQL injection, its impact on web applications, and effective techniques to prevent such attacks.
But first, let’s look at some shocking stats on SQL injections worldwide.
SQL Injection Incidents: Attacks Statistics and Trends
- SQL Injection is the leading source of web application critical vulnerabilities found globally in 2022, accounting for 33%, while cross-site scripting (stored) attacks account for 26.7%.
- In 2014, Russian hackers stole approximately 1.2 billion IDs and passwords from over 420,000 websites worldwide thanks to SQL injection.
- According to an Akamai analysis, SQL injection (SQLi) accounted for over two-thirds (65.1%) of all Web application assaults between November 2017 and March 2019.
- SQL Injection is the third most prevalent web application vulnerability, with 94% of applications assessed for some sort of injection, a maximum incidence rate of 19%, an average incidence rate of 3%, and 274k occurrences.
- Heartland handled more than 100 million credit card transactions every month for 175,000 retailers. When they alerted Heartland about dubious transactions in January 2009, Visa and MasterCard found the hack. The attackers used a well-known vulnerability to launch SQL injection attacks. The company paid an estimated $145 million in settlement for illegal payments.
- According to Akamai publisher SQL Injection (SQLi) attacks remained the most common across all company types in 2020, accounting for 68% of all web application attacks, with Local File Inclusion (LFI) assaults ranking second at 22%. However, in the financial services industry, LFI assaults accounted for 52% of web application attacks in 2020, followed by SQLi at 33% and Cross-Site Scripting at 9%.
All these stats are more terrifying than ever, and that gives you more reason than ever to prevent these attacks from happening. Before I go into prevention, it’s important to understand what SQL Injection is and how it causes harm. Let’us define a few terms.
What is SQL Injection?
SQL injection is a form of code injection attack that occurs when unvalidated user input is used to create SQL statements dynamically.
These attacks enable unauthorized individuals to execute arbitrary SQL commands on the database, resulting in potential data theft, data manipulation, and various security issues.
To comprehend SQL injection, it is crucial to understand the functioning of SQL. SQL is a language employed to interact with databases. When a user submits data to a web application, this data is frequently utilized to generate an SQL query.
For instance, when a user submits their username and password through a login form, the web application may employ this data to create an SQL query similar to the following:
SELECT * FROM users WHERE username = ‘username’ AND password = ‘password’;
Subsequently, the SQL query is executed against the database. If the username and password the user provides are accurate, the user is granted access.
However, suppose an attacker manages to inject malevolent code into the user input. In that case, they can modify the intended meaning of the SQL query. For instance, if the attacker injects the following code:
‘ OR 1 = 1 –
The resulting SQL query would appear as follows:
SELECT * FROM users WHERE username = ‘username’ OR 1 = 1 — ‘;
The OR 1 = 1 clause always evaluates to true, thereby making the WHERE clause also true. Consequently, the attacker can access any data within the database.
Examples of PHP SQL injection vulnerabilities
These SQL injection examples may help in your understanding of injections.
- Example 1
Assume we have a form with a login button, username, and password text fields. Assisting with the login procedure is the following PHP code:
<?php
$userName = $_POST[‘userName’];
$password = $_POST[‘password’];
$sqlQuery = “SELECT * FROM users WHERE user_name='”.$username.”‘ AND user_password='”.$password.”‘;”;
?>
The code above contains a vulnerability. If a user enters ‘ or ‘a’=’a ‘or’ in the password field, the $password variable will have the value ‘ or ‘a’=’a ‘or’. Consequently, the query will be updated as follows:
<?php
$sqlQuery = “SELECT * FROM users WHERE user_name='”.$username.”‘ AND user_password=” or ‘a’=’a’;”;
?>
In this example, the statement ‘a’=’a’ is always true, so the query will be executed without matching the actual password.
- Example 2
In this example, the SQL query is a legitimate program, and we dynamically create the program by adding data on the fly. This data can hamper with the program code and even alter it, as indicated in PHP/MySQL SQL injection
$expected_data = 1;
$query = “SELECT * FROM users WHERE id=$expected_data”;
The above code produces a regular query:
SELECT * FROM users WHERE id=1
However, the following code can have unintended consequences:
$spoiled_data = “1; DROP TABLE users;”;
$query = “SELECT * FROM users WHERE id=$spoiled_data”;
The code above produces a malicious sequence:
SELECT * FROM users WHERE id=1; DROP TABLE users;
This works naturally because we are directly adding the data to the program body, and it becomes part of the program. As an outcome, the data can alter the program, and depending on the data passed, we may get either a regular output or the deletion of the user’s table.
Solutions to SQL Injection Vulnerabilities
SQL injection prevention in PHP is a complicated issue. In the example below, I will offer two fantastic solutions to this problem.
- Method 1: Sanitizing User Input
To modify and enhance the previous code’s security, you can create a function that sanitizes the input. Consider the following code snippet:
<?php
function blockSQLInjection($str) {
return str_replace(array(“‘”, “\””), array(“‘”, “\””), $str);
}
?>
The blockSQLInjection() function replaces all occurrences of single quotes (‘) and double quotes (“) within the provided string. You can utilize this function as demonstrated below:
<?php
$userName = blockSQLInjection($_POST[‘userName’]);
$password = blockSQLInjection($_POST[‘password’]);
?>
By employing this function, you can mitigate SQL injection vulnerabilities by sanitizing the input and guaranteeing its security.
- Method 2: Prepared Statements
Another brilliant approach to preventing SQL injections is by using PHP-prepared statements. Prepared statements allow for the efficient and repeated execution of similar SQL queries. The query is sent to the database with unspecified values denoted by ?. The database compiles and stores the result without executing it. Later, the application can bind values to the parameters and execute the statement.
Here’s an example:
<?php
$stmt = $conn->prepare(“INSERT INTO MyGuests(firstname, lastname, email) VALUES (?, ?, ?)”);
$stmt->bind_param(“sss”, $firstname, $lastname, $email);
// Set parameters and execute
$firstname = “John”;
$lastname = “Doe”;
$email = “[email protected]”;
$stmt->execute();
$firstname = “Mary”;
$lastname = “Moe”;
$email = “[email protected]”;
$stmt->execute();
?>
The insert statement in the above example comprises (?,?,? ), suggesting that we can substitute integer, double, string, or blob values. The bind_param() method attaches various parameters to the query and sends them to the database. The “sss” input defines the data type: integer (i), double (d), string (s), or blob (b). We reduce the danger of SQL injection by providing the intended data types.
By using these techniques, you may dramatically limit the likelihood of SQL injection vulnerabilities in your PHP code.
Safeguard Your Application
Protect your application from SQL injection attacks by following best practices, such as input validation and sanitization, using parameterized queries or prepared statements, and keeping your software and libraries up to date.
End-users can also ensure their safety by practicing good password hygiene, using unique and complex passwords, and being cautious about sharing personal information online.
Remember, prevention is better than cure when it comes to security. Stay vigilant and adopt secure coding practices to safeguard your applications and databases.
FAQ on SQL injection
How can SQL injection be prevented in PHP?
To prevent SQL injection in PHP, you should avoid directly concatenating user input into SQL queries. Instead, use prepared statements or parameterized queries, which separate the SQL code from the user input. Additionally, validate and sanitize user input to ensure it meets the expected format and does not contain malicious code.
Are prepared statements the only solution for preventing SQL injection?
Prepared statements are one effective solution for preventing SQL injection. They provide a way to parameterize queries and separate data from code. However, applying input validation and sanitization in conjunction with prepared statements for comprehensive security is essential.
Can’t I just use addslashes() to prevent SQL injection?
The addslashes() function can sometimes help, but there are better solutions for preventing SQL injection. It escapes special characters, but different databases and contexts may have different requirements. Prepared statements offer a more robust and reliable approach to prevent SQL injection.
Is SQL injection the only security vulnerability to consider in PHP?
No, SQL injection is just one of many security vulnerabilities that can affect PHP applications. Other vulnerabilities to be aware of include cross-site scripting (XSS), cross-site request forgery (CSRF), and insecure direct object references (IDOR). It’s crucial to follow secure coding practices and stay updated on the latest security threats to protect your application effectively.
