Mastering the Art of SQL Injection: A Comprehensive Guide

13 min readSep 14, 2023

In today’s world, data is the new currency. Every organization, whether big or small, deals with data in one way or another. But with the increasing use of technology, the risk of data breaches has also increased. One of the most common and dangerous types of attacks is SQL injection. SQL injection is a type of cyber attack where an attacker uses malicious SQL code to manipulate a database and extract sensitive information. In this blog post, we will discuss everything you need to know about SQL injection, including its types, prevention techniques, case studies, and much more. So, let’s dive into the world of SQL injection and learn how to protect ourselves from this dangerous cyber attack.

SQL (Structured Query Language) ?

SQL, or Structured Query Language, is a programming language that is used to manage and manipulate relational databases. It allows users to interact with databases by performing various tasks such as creating, updating, and deleting data. An example of SQL in a database would be creating a table for customer information. The SQL query would look something like this:

CREATE TABLE Customers ( 
CustomerID INT NOT NULL, 
FirstName VARCHAR(50), 
LastName VARCHAR(50), 
Address VARCHAR(100), 
Phone VARCHAR(20), 
Email VARCHAR(50), 
PRIMARY KEY (CustomerID) 
);

This SQL code creates a table named “Customers” with columns for CustomerID, FirstName, LastName, Address, Phone, and Email. The PRIMARY KEY statement specifies that the CustomerID column will be used as the primary key for the table.

In summary, SQL is a powerful language that enables users to effectively manage and manipulate data in a database. By using SQL, users can easily create, update, and delete data within a database, ultimately making it easier to access and analyze important information.

What is SQL Injection?

SQL Injection (SQLi) is a type of web application security vulnerability that allows an attacker to execute malicious SQL statements within a web application’s database. It occurs when an attacker inserts malicious code into a web application’s input fields, such as search forms, login pages, or contact forms. SQL injection is a serious threat to web applications as it can result in the theft of sensitive information, unauthorized access to data, and even complete system compromise. It is a prevalent attack vector, and according to OWASP’s Top 10 Application Security Risks, SQL injection is still the second most critical vulnerability.

In simple terms, SQL injection happens when a user input is not correctly sanitized, and the attacker can inject their malicious SQL code into the database query. The injected code can then execute any SQL commands that the attacker desires, including retrieving, modifying, or deleting data. It’s important to note that SQL injection can affect any web application that uses a database and not just websites that store sensitive data like banks or healthcare providers. Therefore, developers and website owners should take adequate measures to prevent SQL injection attacks. We will discuss the best practices for preventing SQL injection in a later point.

Understand SQL Injection with an example

Example 1:

Let’s say there is a web application that has a search function that allows users to search for products based on their names. The search function works by taking the user’s input and then searching the product database for the matching results. The SQL query that runs the search function might look something like this:

SELECT * FROM products WHERE product_name LIKE '%user_input%';

The ‘%user_input%‘ is a variable that represents the user’s search query. The ‘%’ symbols are wildcards that allow any character to match the user’s search input.

Now, suppose an attacker enters the following input into the search field:

' OR 1=1--

The modified SQL query would now look like this:

SELECT * FROM products WHERE product_name LIKE '%' OR 1=1--%';

The SQL engine would interpret this query as “Select all data from the product table where the product name contains any character, or where 1 is equal to 1 (which is always true).” This means that the attacker would be able to bypass the search function’s intended behavior and get access to the entire product database.

Example 2:

Suppose a web application has a login page where users enter their username and password. The login form code is programmed to check the entered credentials with the user database to validate the user’s identity. The query would look something like this:

SELECT * FROM users WHERE username = 'entered_username' AND password = 'entered_password';

The entered_username and entered_password are values entered by the user. Now, suppose an attacker tries to login to the same application with the username “admin” and the password ” ‘OR 1=1– ” (without quotes).

The modified SQL query would look something like this:

SELECT * FROM users WHERE username = 'admin' AND password = ''OR 1=1--';

Here, the “– –” denotes a comment in SQL, and everything after it is ignored by the SQL engine. Therefore, the query now reads as “select all data from the user table where the username is ‘admin,’ and the password is empty or where 1 is equal to 1 (which is always true).” As a result, the attacker would bypass the authentication check and gain access to the system as an administrator. This is how SQL injection works.

In both of these examples, attackers use SQL injection to exploit vulnerabilities in web applications to gain access to sensitive data or perform unauthorized actions. It’s crucial to understand how SQL injection works to prevent such attacks from happening.

Types of SQL Injection?

Following is a list of SQL injection attacks.

Union-based: In union-based injection, attackers use the “union” statement to combine the results of multiple queries to extract data.
Error-based: This type of injection relies on error messages generated by the database to extract information.
Boolean-based: Attackers use boolean logic in this type of injection to determine whether a specific condition is true or false.
Time-based: In time-based injection, attackers use time delays to extract information from the database.
Out-of-band: This type of injection involves using a separate channel to extract information from the database, such as DNS queries.
Second-Order: Second-order SQL injection occurs when user input is stored in a database and used later to build an SQL query, creating a vulnerability that attackers can exploit.

Let us now delve into a brief discussion of the various types of SQL injection.

Union-Based SQL Injection:

Union-based SQL Injection is another type of SQL injection attack that takes advantage of the UNION operator in SQL. The attacker crafts a malicious SQL query that includes a UNION statement, which allows the attacker to combine the results of two or more SELECT statements into a single result set. This technique can be used to extract sensitive data from the database, including usernames, passwords, and other confidential information.

Error-Based SQL Injection:

Error-based SQL Injection is a type of SQL injection attack that exploits the error messages returned by the web application’s database server. The attacker intentionally inputs malicious code into the web application’s input fields, causing the database server to generate error messages. These error messages can provide valuable information about the database structure, which the attacker can use to extract sensitive data.

Boolean-Based SQL Injection:

Boolean-Based SQL Injection is a technique used to exploit vulnerabilities in the web application’s input fields. The attacker sends a series of True or False questions to the database server, using the web application’s input fields. Based on the response, the attacker can determine if the SQL query executed successfully or not. This technique is often used to extract information from the database, including user credentials, sensitive data, and other confidential information.

Time-Based SQL Injection:

Time-Based SQL Injection is a type of SQL injection attack that exploits time delays in the web application’s database server. The attacker sends a series of SQL queries to the server, each with a specific time delay. Based on the response time of each query, the attacker can determine if the query was successful or not. This technique can be used to extract sensitive data from the database, including user credentials, payment information, and other confidential information.

Out of Band SQL Injection:

Out of Band SQL Injection is a type of SQL injection attack that uses a separate channel to retrieve data from the database. The attacker sends a malicious SQL query to the server, which then sends the results to a separate server that the attacker controls. This technique is often used to bypass firewalls and other security measures that might block direct connections to the database server.

Second Order SQL Injection:

Second-order SQL injection occurs when an application stores user input in a database, but the input is not immediately processed. Instead, it is stored and then used later in an SQL query. This delayed processing can create a vulnerability that attackers can exploit. The danger with second-order SQL injection is that the attacker’s code may not execute immediately. Instead, it may be stored in the database and executed at a later time, when the injected data is used to build another SQL query. This can make it harder to detect and fix the vulnerability.

Impact of SQL Injection

SQL injection can have a range of impacts that can be leveraged by an attacker, including:

Disclose sensitive information (e.g., passwords, credit card data).
Modify or delete data, compromising the integrity of the database.
Take over or gain unauthorized access to a database or other systems.
Hijack user sessions or bypass authentication mechanisms to perform unauthorized actions.
Overload a database server with requests, causing a denial of service.
Steal or manipulate financial data, resulting in financial loss.
Damage an organization’s reputation and customer trust by stealing and publishing sensitive information.

Prevention of SQL Injection

SQL Injection is a type of attack that can compromise the security of your database. In this attack, an attacker inserts malicious SQL code into your database to extract sensitive data. To prevent this, you need to take proactive measures to secure your database. Here are some effective ways to prevent SQL Injection:

Use Parameterized Queries: Parameterized queries help prevent SQL Injection attacks by separating the SQL code and user input. In this method, the SQL code and user input are passed as separate parameters, making it difficult for attackers to inject malicious code into the database.
Input Validation: Input validation is a process of validating user input before processing it. By doing this, you can ensure that the user input is safe to use and does not contain any malicious code. This can prevent SQL Injection attacks and other security threats.
Escape User Input: Escaping user input is a method of encoding user input to prevent SQL Injection attacks. In this method, special characters are encoded, making it difficult for attackers to insert malicious code into the database.
Limit Database Privileges: Limiting database privileges is an effective way to prevent SQL Injection attacks. By limiting the privileges of the database users, you can ensure that they only have access to the data they need. This can prevent unauthorized access to the database and minimize the risk of SQL Injection attacks.
Do Not Use Shared Database Accounts: It is important to avoid using shared database accounts between different websites or applications. Each website or application should have its own unique database account with limited privileges. This can prevent SQL Injection attacks and other security threats.
Regularly Update Your Software: Regularly updating your software is crucial to prevent SQL Injection attacks. This is because software updates often contain security patches that fix vulnerabilities in the software. By regularly updating your software, you can ensure that your database is secure and protected against SQL Injection attacks.
Web Application Firewall (WAF): Web Application Firewall (WAF) is another important measure to prevent SQL Injection attacks. A WAF is a security solution that helps to filter and block malicious traffic from reaching your website. It works by monitoring the traffic to your website and filtering out any malicious requests that could cause a SQL Injection attack. WAFs can also help to detect and block SQL Injection attacks in real-time, making them an effective measure to prevent such attacks. Thank you again for bringing up this important point.

Tools to check SQL Injection

If you want to ensure the security of your web application, it is essential to be aware of the possible vulnerabilities that may exist, including SQL injection attacks. Detecting these attacks can be challenging, but there are several tools available that can help you identify and prevent them.

Here are some of the most popular tools for detecting SQL injection in a web application:

SQLMap (Open-Source): This open-source tool is designed to automate the process of detecting and exploiting SQL injection flaws. It supports a wide range of database management systems and can also be used for penetration testing.
OpenVAS (Open-Source): This is an open-source vulnerability scanner that can detect SQL injection vulnerabilities in web applications. It can also identify a range of other security issues, including weak passwords and outdated software.
OWASP ZAP (Open-Source): This free and open-source tool is a popular web application scanner that includes a SQL injection scanner. It also includes several other security features such as XSS and CSRF testing.
Skipfish (Open-Source): This open-source web application scanner can detect various vulnerabilities in web applications, including SQL injection. It can also identify other issues like directory traversal and remote file inclusion.
Arachni (Open-Source): This is another open-source web application scanner that includes a SQL injection scanner. It can also detect other vulnerabilities like XSS and code injection.
Wapiti (Open-Source): This open-source web vulnerability scanner can identify SQL injection vulnerabilities in web applications. It can also identify other security issues like file disclosure and command injection.
Acunetix (Paid): This web vulnerability scanner includes a SQL injection tool that can detect and exploit SQL injection vulnerabilities in web applications. It also offers a range of other security features to ensure the protection of your website.
Burp Suite Professional (Paid): This suite of web application security tools includes a scanner that can detect SQL injection vulnerabilities. It also provides an extensive range of other security testing features, including cross-site scripting and cross-site request forgery.
Netsparker (Paid): This automated web application scanner includes a SQL injection tool that can detect and exploit SQL injection vulnerabilities. It is also capable of detecting a range of other web application vulnerabilities.

By using one or more of these tools, you can help to ensure that your web application is protected against SQL injection attacks, keeping your data and your users’ data secure.

Vulnerable and secure code of SQL Injection

The following example illustrates the contrast between a vulnerable and a secure code for SQL Injection. Through this demonstration, one can gain a deeper understanding of how SQL Injection vulnerabilities can occur within a code and the measures that can be taken to mitigate such risks and ensure the code’s security.

Vulnerable Code:

<?php
$user_id = $_POST['user_id'];
$sql = "SELECT * FROM users WHERE user_id = '$user_id'";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "Username: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
?>

This code is vulnerable to SQL injection attacks because it concatenates user input directly into the SQL query, without properly sanitizing or escaping the input. An attacker could use this vulnerability to inject malicious SQL code into the query, potentially allowing them to access or manipulate sensitive data.

Secure code:

<?php
// Get user input from form
$user_id = mysqli_real_escape_string($conn, $_POST['user_id']);
// Prepare SQL query with parameterized statement
$stmt = mysqli_prepare($conn, "SELECT * FROM users WHERE user_id = ?");
// Bind parameters to statement
mysqli_stmt_bind_param($stmt, "s", $user_id);
// Execute SQL query
mysqli_stmt_execute($stmt);
// Process query result
$result = mysqli_stmt_get_result($stmt);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "Username: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
?>

This code is secure against SQL injection attacks because it uses parameterized statements and properly sanitizes user input with “mysqli_real_escape_string()“. The use of parameterized statements ensures that user input is treated as data rather than code, preventing any injected code from being executed.

Case studies of SQL Injection:

SQL injection attacks have been a common form of cyber attack for over two decades. The following case studies demonstrate how SQL injection attacks can result in serious consequences for individuals and organizations.

Heartland Payment Systems: In 2008, Heartland Payment Systems, a payment processing company, suffered a massive data breach that resulted in the theft of over 130 million credit card numbers. The breach was caused by a SQL injection attack that exploited a vulnerability in the company’s payment processing software.
Yahoo!: In 2012, Yahoo! suffered a data breach that resulted in the theft of over 400,000 usernames and passwords. The breach was caused by a SQL injection attack that exploited a vulnerability in the company’s login page.
Sony Pictures: In 2014, Sony Pictures suffered a devastating cyber attack that resulted in the theft of confidential data, including employee records, email archives, and unreleased movies. The breach was caused by a SQL injection attack that exploited a vulnerability in the company’s content management system.

These case studies demonstrate the serious consequences that can result from SQL injection attacks. It is important for individuals and organizations to take proactive steps to prevent these types of attacks from occurring, such as using secure coding practices, regularly updating software, and conducting regular security audits.

Some of the HackerOne reports

SQL injection on contactws.contact-sys.com in TRateObject.AddForOffice in the USER_ID parameter leads to remote code execution: https://hackerone.com/reports/816560
SQL injection on admin.acronis.host development web service: https://hackerone.com/reports/923020
SQL injection in https://www.acronis.cz/ via the log parameter: https://hackerone.com/reports/1109311
SQL Injection on https://www.olx.co.id: https://hackerone.com/reports/639876
[intensedebate.com] SQL Injection Time Based on /changeReplaceOpt.php: https://hackerone.com/reports/1042746
SQL Injection in ████: https://hackerone.com/reports/419017
SQL injection in https://labs.data.gov/dashboard/datagov/csv_to_json via User-agent: https://hackerone.com/reports/297478
SQL injection on contactws.contact-sys.com in TScenObject action ScenObjects leads to remote code execution: https://hackerone.com/reports/816254

Our Telegram channel shares daily bug bounty writeups that can help you learn more about security and stay in the loop with the latest trends.

Conclusion

In conclusion, SQL Injection is a serious security threat that can cause significant damage to organizations. It can compromise the confidentiality, integrity, and availability of data. Understanding the different types of SQL Injection and their impact is crucial in developing effective prevention strategies. Implementing secure coding practices, regular vulnerability assessments, and using tools to check for SQL Injection can help mitigate the risks. It is also essential to stay up to date with the latest hacking trends and be aware of common misconceptions about SQL Injection. By doing so, organizations can safeguard themselves against SQL Injection attacks and protect their valuable data assets.

Twitter: https://twitter.com/piyush_supiy

Linkedin: https://linkedin.com/in/piyush-kumawat

Website: https://securitycipher.com/services