In in-band SQL injection, attackers use the same communication channel to perform the attack and retrieve the results. Depending on the technique used, there are various types of in-band SQL injection attacks. The most commonly used in-band SQL injection attacks are error-based SQL injection and UNION SQL injection.
The different types of in-band SQL injection are as follows:
Error-based SQL Injection
An attacker intentionally inserts bad inputs into an application, causing it to return database errors. The attacker reads the resulting database-level error messages to find an SQL injection vulnerability in the application. Accordingly, the attacker then injects SQL queries that are specifically designed to compromise the data security of the application. This approach is very useful to build a vulnerability-exploiting request.
System Stored Procedure
The risk of executing a malicious SQL query in a stored procedure increases if the web application does not sanitize the user inputs used to dynamically construct SQL statements for that stored procedure. An attacker may use malicious inputs to execute the malicious SQL statements in the stored procedure. Attackers exploit databases’ stored procedures to perpetrate their attacks.
For example,
Create procedure Login @user_name varchar(20), @password varchar(20) As Declare @query varchar(250) Set @query = ‘ Select 1 from usertable Where username = ‘ + @user_name + ‘ and password = ‘ + @password exec(@query) Go
If the attacker enters the following inputs in the application input fields using the above stored procedure running in the backend, he/she will be able to login with any password.
User input: anyusername or 1=1' anypassword
Illegal/Logically Incorrect Query
An attacker may gain knowledge by injecting illegal/logically incorrect requests such as injectable parameters, data types, names of tables, and so on. In this SQL injection attack, an attacker intentionally sends an incorrect query to the database to generate an error message that may be useful for performing further attacks. This technique may help an attacker to extract the structure of the underlying database.
For example, to find the column name, an attacker may give the following malicious input:
Username: 'Bob"
The resultant query will be
SELECT * FROM Users WHERE UserName = 'Bob"' AND password =
After executing the above query, the database may return the following error message:
"Incorrect Syntax near 'Bob'. Unclosed quotation mark after the character string '' AND Password='xxx''."
UNION SQL Injection
The “UNION SELECT” statement returns the union of the intended dataset and the target dataset. In a UNION SQL injection, an attacker uses a UNION clause to append a malicious query to the requested query, as shown in the following example:
SELECT Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable
The attacker checks for the UNION SQL injection vulnerability by adding a single quote character (‘) to the end of a ".php? id=" command. The type of error message received will tell the attacker if the database is vulnerable to a UNION SQL injection.
Tautology
In a tautology-based SQL injection attack, an attacker uses a conditional OR clause such that the condition of the WHERE clause will always be true. Such an attack can be used to bypass user authentication.
For example,
SELECT * FROM users WHERE name = ‘’ OR ‘1’=‘1’;
This query will always be true, as the second part of the OR clause is always true.
End-of-Line Comment
In this type of SQL injection, an attacker uses line comments in specific SQL injection inputs. Comments in a line of code are often denoted by (--), and they are ignored by the query. An attacker takes advantage of this commenting feature by writing a line of code that ends in a comment. The database will execute the code until it reaches the commented portion, after which it will ignore the rest of the query.
For example,
SELECT * FROM members WHERE username = 'admin'--' AND password = 'password'
With this query, an attacker can login to an admin account without the password, as the database application will ignore the comments that begin immediately after username = ‘admin’.
In-line Comments
Attackers simplify an SQL injection attack by integrating multiple vulnerable inputs into a single query using in-line comments. This type of injections allows an attacker to bypass blacklisting, remove spaces, obfuscate, and determine database versions.
For example,
INSERT INTO Users (UserName, isAdmin, Password) VALUES ('".$username."', 0, '".$password."')"
is a dynamic query that prompts a new user to enter a username and password.
The attacker may provide malicious inputs as follows.
UserName = Attacker', 1, /*
Password = */'mypwd
After these malicious inputs are injected, the generated query gives the attacker administrator privileges.
INSERT INTO Users (UserName, isAdmin, Password) VALUES(‘Attacker', 1, /*’, 0, ‘*/’mypwd’)
Piggybacked Query
In a piggybacked SQL injection attack, an attacker injects an additional malicious query into the original query. This type of injection is generally performed on batched SQL queries. The original query remains unmodified, and the attacker’s query is piggybacked on the original query. Owing to piggybacking, the DBMS receives multiple SQL queries. Attackers use a semicolon (;) as a query delimiter to separate the queries. After executing the original query, the DBMS recognizes the delimiter and then executes the piggybacked query. This type of attack is also known as a stacked queries attack. The intention of the attacker is to extract, add, modify, or delete data, execute remote commands, or perform a DoS attack.
For example, the original SQL query is as follows:
SELECT * FROM EMP WHERE EMP.EID = 1001 AND EMP.ENAME = ’Bob’
Now, the attacker concatenates the delimiter (;) and the malicious query to the original query as follows:
SELECT * FROM EMP WHERE EMP.EID = 1001 AND EMP.ENAME = ’Bob’; DROP TABLE DEPT;
After executing the first query and returning the resultant database rows, the DBMS recognizes the delimiter and executes the injected malicious query. Consequently, the DBMS drops the table DEPT from the database.
Let us understand the details of error-based SQL injection. As discussed earlier, in error-based SQL injection, the attacker forces the database to return error messages in response to his/her inputs. Later, the attacker may analyze the error messages obtained from the underlying database to gather information that can be used for constructing the malicious query. The attacker uses this type of SQL injection technique when he/she is unable to exploit any other SQL injection techniques directly. The primary goal of this technique is to generate the error message from the database, which can be used to perform a successful SQL injection attack. Such exploitation may differ from one DBMS to another.
Consider the following SQL query:
SELECT * FROM products WHERE id_product=$id_product
Consider the request to a script that executes the query above:
http://www.example.com/product.php?id=10
The malicious request would be (e.g., Oracle 10g):
http://www.example.com/product.php?
id=10||UTL_INADDR.GET_HOST_NAME( (SELECT user FROM DUAL) )—
In the aforementioned example, the tester concatenates the value 10 with the result of the function UTL_INADDR.GET_HOST_NAME. This Oracle function will try to return the hostname of the parameter passed to it, which is another query, i.e., the name of the user. When the database looks for a hostname with the user database name, it will fail and return an error message such as
ORA-292257: host SCOTT unknown
Then, the tester can manipulate the parameter passed to the GET_HOST_NAME() function and the result will be shown in the error message.
In a UNION SQL injection, an attacker combines a forged query with a query requested by the user using a UNION clause. The result of the forged query will be appended the result of the original query, which makes it possible to obtain the values of fields from other tables. Before running the UNION SQL injection, the attacker ensures that there is an equal number of columns taking part in the UNION query. To find the right number of columns, the attacker first launches a query using an ORDER BY clause followed by a number to indicate the number of database columns selected:
ORDER BY 10--
If the query is executed successfully and no error message appears, then the attacker will assume that 10 or more columns exist in the target database table. However, if the application displays an error message such as “Unknown column '10' in 'order clause”, then the attacker will assume that there are less than 10 columns in the target database table. Through trial and error, an attacker can learn the exact number of columns in the target database table.
Once the attacker learns the number of columns, the next step is to find the type of columns using a query such as
UNION SELECT 1,null,null—
If the query is executed successfully, then the attacker knows that the first column is of integer type and he/she can move on to learning the types of the other columns.
Once the attacker finds the right number columns, the next step is to perform UNION SQL injection.
For example,
SELECT Name, Phone, Address FROM Users WHERE Id=$id
Now, set the following Id value:
$id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable
The attacker now launches a UNION SQL injection query as follows:
SELECT Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable
The above query joins the result of the original query with all the credit card users.
Blind SQL Injection is used when a web application is vulnerable to an SQL injection but the results of the injection are not visible to the attacker. Blind SQL injection is identical to a normal SQL Injection except that when an attacker attempts to exploit an application, he/she sees a generic custom page instead of a useful error message. In blind SQL injection, an attacker poses a true or false question to the database to determine whether the application is vulnerable to SQL injection.
A normal SQL injection attack is often possible when the developer uses generic error messages whenever an error has occurred in the database. Such generic messages may reveal sensitive information or give a path to the attacker to perform an SQL injection attack on the application. However, when developers turn off the generic error message for the application, it is difficult for the attacker to perform an SQL injection attack. Nevertheless, it is not impossible to exploit such an application with an SQL injection attack. Blind injection differs from normal SQL injection in the manner of retrieving data from the database. Attackers use blind SQL injection either to access sensitive data or to destroy data. Attackers can steal data by asking a series of true or false questions through SQL statements. The results of the injection are not visible to the attacker. This type of attack can become time-intensive because the database should generate a new statement for each newly recovered bit.
When an attacker tries to perform an SQL injection with the query “certifiedhacker'; drop table Orders --”, two kinds of error messages may be returned. A generic error message may help the attacker to perform SQL injection attacks on the application. However, if the developer turns off the generic error messages, the application will return a custom error message, which is not useful to the attacker. In this case, the attacker will attempt a blind SQL injection attack instead.
If generic error messaging is in use, the server returns an error message with a detailed explanation of the error, with database drivers and ODBC SQL server details. This information can be used to further perform the SQL injection attack. When custom messaging is in use, the browser simply displays an error message saying that there is an error and the request was unsuccessful, without providing any details. Thus, the attacker has no choice but to attempt a blind SQL injection attack.
Time delay SQL injection (sometimes called time-based SQL injection) evaluates the time delay that occurs in response to true or false queries sent to the database. A waitfor statement stops the SQL server for a specific amount of time. Based on the response, an attacker will extract information such as connection time to the database as the system administrator or as another user and launch further attacks.
Figure 7.41: Example of Time Delay SQL Injection
Step 1: IF EXISTS(SELECT * FROM creditcard) WAITFOR DELAY '0:0:10'—
Step 2: Check if database “creditcard” exists or not
Step 3: If No, it displays “We are unable to process your request. Please try back later”.
Step 4: If Yes, sleep for 10 seconds. After 10 seconds, it displays “We are unable to process your request. Please try back later.”
Since no error message will be returned, use the “waitfor delay” command to check the SQL execution status.
WAIT FOR DELAY 'time' (seconds)
This is just like sleep; wait for a specified time. The CPU is a safe way to make a database wait.
WAITFOR DELAY '0:0:10'--
BENCHMARK() (Minutes)
This command runs on MySQL Server.
BENCHMARK(howmanytimes, do this)
Boolean-based blind SQL injection (sometimes called inferential SQL Injection) is performed by asking the right questions to the application database. Multiple valid statements evaluated as true or false are supplied in the affected parameter in the HTTP request. By comparing the response page between both conditions, the attackers can infer if the injection was successful. If the attacker constructs and executes the right request, the database will reveal everything that the attacker wants to know, which facilitates further attacks. In this technique, the attacker uses a set of Boolean operations to extract information about database tables. The attacker often uses this technique if it appears that the application is exploitable using a blind SQL injection attack. If the application does not return any default error message, the attacker tries to use Boolean operations against the application.
For example, the following URL displays the details of an item with id = 67
http://www.myshop.com/item.aspx?id=67
The SQL query for the above request is
SELECT Name, Price, Description FROM ITEM_DATA WHERE ITEM_ID = 67
An attacker may manipulate the above request to
http://www.myshop.com/item.aspx?id=67 and 1=2
Subsequently, the SQL query changes to
SELECT Name, Price, Description FROM ITEM_DATA WHERE ITEM_ID = 67 AND 1 = 2
If the result of the above query is FALSE, no items will be displayed on the web page. Then, the attacker changes the above request to
http://www.myshop.com/item.aspx?id=67 and 1=1
The corresponding SQL query is
SELECT Name, Price, Description FROM ITEM_DATA WHERE ITEM_ID = 67 AND 1 = 1
If the above query returns TRUE, then the details of the item with id = 67 are displayed. Hence, from the above result, the attacker concludes that the page is vulnerable to an SQL injection attack.
In some circumstances, it is impossible to use time delay functions in SQL queries, as the database administrator may disable the use of such functions. In such cases, an attacker can use heavy queries to perform a time delay SQL injection attack without using time delay functions. A heavy query retrieves a massive amount of data, and it will take a long time to execute on the database engine. Attackers generate heavy queries using multiple joins on system tables because queries on system tables take more time to execute.
For example, the following is a heavy query in Oracle that takes a long time to execute:
SELECT count(*) FROM all_users A, all_users B, all_users C
If an attacker injects a malicious parameter into the above query to perform time-based SQL injection without using functions, then it takes the following form:
1 AND 1 < SELECT count(*) FROM all_users A, all_users B, all_users C
The final resultant query takes the form
SELECT * FROM products WHERE id=1 AND 1 < SELECT count(*) FROM all_users A, all_users B, all_users C
A heavy query attack is a new type of SQL injection attack that has a severe impact on the performance of the server.
Out-of-band SQL injection attacks are difficult to perform because the attacker needs to communicate with the server and determine the features of the database server used by the web application. In this attack, the attacker uses different communication channels (such as database email functionality or file writing and loading functions) to perform the attack and obtain the results. Attackers use this technique instead of in-band or blind SQL injection if they are unable to use the same channel through which the requests are being made to launch the attack and gather the results.
Attackers use DNS and HTTP requests to retrieve data from the database server. For example, in Microsoft SQL Server, an attacker exploits the xp_dirtree command to send DNS requests to a server controlled by the attacker. Similarly, in Oracle Database, an attacker may use the UTL_HTTP package to send HTTP requests from SQL or PL/SQL to a server controlled by the attacker.