In the previous database articles, we have learned how to create a relational database using the SQLite database management system. We have covered how to create tables, insert data into tables, modify tables, update databases, delete records, or drop tables from the database. In this tutorial, we shall discuss different types of database attacks, how hackers can exploit the database vulnerabilities through SQL injections, and possible remedies to keep the database safe from various kinds of SQL injections.
WHAT IS SQL INJECTION
When a user submits a query (input) to an application linked with a database, the application communicates with the database to fetch results or take actions according to the submitted query. In a relational database, the application communicates with the database through SQL commands, also known as SQL statements. In some cases, the user input becomes part of the SQL statements executed by the application. When a rogue user submits unexpected or deliberately wrong input data for framing the SQL statement, the action is termed as the SQL injection. A successful SQL injection can lead to a number of security problems, such as authentication bypass; exfiltration, modification, deletion of data; privilege escalation; and in some cases the execution of mal-activities like Denial of Service (DoS) attacks.
TYPES of SQL INJECTION
Although there are different types of SQL injection, we can classify them into the following three main categories.
1) In-Band SQL Injection
2) Out-of-Band SQL Injection
3) Blind SQL Injection
In-Band SQL Injection
In the In-Band SQL injection (SQLi), the same channel is used to attack and retrieve results. The In-Band SQLi is sub-categorized into the following types.
(i) Error-Based SQLi: Error-based is the most common type of SQLi that relies on the errors thrown by the database. There are techniques to force vulnerable databases to generate errors and display them on the screen. Consider the following test website that interacts directly with the database.
If we append the quote (‘) to the target URL, we get the database error message as shown below. The error suggests that the application is vulnerable to SQL injection.
(ii) Union-based SQLi: In Union-based SQLi, the hackers combine the results of two or more SELECT statements into a single output. Consider a vulnerable database with products and customers tables. The products table has two columns named as item and price. Similarly, the customers table has the username and password attributes. The hacker can make use of the UNION operator in the following manner to fetch results from both tables.
SELECT item, price FROM products UNION SELECT username, password FROM customers
However, there are two main requirements for the UNION attack to work. There must be the same number of columns generated by the individual SQL queries. The type of data in the columns must be compatible with the individual SQL queries.
OUT-of-BAND SQL INJECTION
Out-of-Band SQL injection is the opposite of In-Band SQL injection. The attacker is not able to use the same communication channel to attack the database and gather the desired results. In the Out-of-Band SQLi, the data is extracted via outbound channels using the DNS and HTTP protocols. There are certain requirements to initiate the Out-of-Band SQL injection. The application should lack the input validation; the attacker must have enough privileges to launch the Out-of-Band SQLi; the database enabled features must support the Out-of-Band ex-filtration protocols like DNS and HTTP.
BLIND SQL INJECTION
The Blind SQLi, also known as the inferential SQLi, is the second most common category of SQL injections. The Blind SQLi is applied to the web applications that are vulnerable to SQLi but they either don’t throw any error message or share a very generic message with no conclusive information. The basic technique behind the Blind SQLi is to ask the database True/False queries and record the response of the web application. There are two main types of Blind SQli.
(i) Time-Based Blind SQLi: In Time-Based Blind SQli, the attacker sends specially crafted SQL queries to the web application that forces the database to wait for a specified time period before responding to the query. The attacker deduces the True/False nature of the queries based on the response time of the database.
(ii) Boolean-Based Blind SQLi: The Boolean-Based SQLi is similar to the Time-Based SQLi, except the True/False of the payload queries is determined by different responses (instead of time delay) of the database.
SQLi DEMONSTRATION EXAMPLE
Bypassing authentication through manipulated SQL queries is the most common type of SQL attack. Hackers try to pass specially crafted SQL queries to the input fields of the applications to attempt access without any valid credentials. To understand this type of attack, let’s consider the following login form that accepts values from the users.
<html> <h2> This is a Test Form</h2> <form action=‘index.php’ method="post"> <input type="email" name="email" placeholder = "your Email" required="required"/> <input type="password" name="password" placeholder = "Type Password"/> <pre> <input type="checkbox" name="remember_me" value="Remember me"/> <label>Remember Me</label> <input type="submit" value="Submit"/> </pre> </form> </html>
The form takes the following inputs through the post method.
The checkbox in the form indicates that it can store the values of the current session through cookies. The front view of the form can be seen in the following screenshot.
Let’s assume the application running the above form has the following customers table at the back end.
CREATE TABLE customers ( email VARCHAR(40) NULL, password VARCHAR(40) NULL );
The table has three email addresses and passwords records stored in the database.
INSERT INTO customers (email,password) VALUES ('firstname.lastname@example.org',md5('hack')); INSERT INTO customers (email,password) VALUES ('email@example.com',md5('rack')); INSERT INTO customers (email,password) VALUES ('firstname.lastname@example.org',md5('back'));
[Note: The passwords are stored in the MD5 hash format. The output of the above table can be seen in the following screenshot.]
LEGITIMATE ACCESS: When a user submits an email and a password through the login form, the following query is generated at the backend to validate the user (login) request.
SELECT * FROM users WHERE email = '<user provided email>' AND password = md5('<user provided password>');
SELECT * FROM users WHERE email = 'email@example.com' AND password = md5('hack');
If the supplied credentials (email and password) match with the table record the user is granted access; otherwise rejected by the application.
ROGUE ACCESS: A rogue user can exploit the vulnerable login form through different input techniques. For example, the attacker can make use of additional clauses in the SQL query with the help of OR operator. The additional clause can be a conditional statement followed by the comment operator. The conditional statements can be inserted after a quote in the input field. Let’s assume the password field in the aforementioned form is vulnerable to SQL injection. The adversary has the email information; he can insert the following conditional statement in the password field.
‘ OR 1=1–
The (‘) in the above command is the quote. If the login page is not sanitized, the query after the quote is entertained by the database.
The (1= 1) is the conditional statement. The database application checks if the provided password OR the applied condition is true. If any of the two (password or conditional statement) is true, the application validates the fake login request.
The (–) is the comment sign. It tells the database to ignore any further checks if the provided password or the appending query (condition) is true. The (–) symbol is supported by MYSQL and SQL server.
In MYSQL, we can also use (#) symbol as a comment. See the following syntax.
‘ OR 1=1#
The manipulated backend SQL statement takes the following form.
SELECT * FROM customers WHERE email = 'firstname.lastname@example.org' AND password = '' or 1=1--' LIMIT 1
Although the user did not provide any password, the condition appended to the password field (1=1) is true. The database management system validates the request on the basis of this true appended condition.
HOW TO PREVENT SQL INJECTIONS
According to the latest Verizon’s Data Breach Investigation Report (DBIR 2020), the majority of the database breaches were found as a result of the database misconfigurations due to human errors. This fact indicates that we can control SQL injection to a great extent if databases are configured correctly. The following are a few rules that are considered helpful in preventing SQLi.
A prepared statement is an approach of keeping the query and the data separate from each other. In SQL, the problem starts when the query and data are mixed to frame (produce) a malicious statement. The prepared statement prevents such mixing by using the placeholders for user input. When a user submits a data, it is not directly embedded into the query. Rather the temporary placeholders (?) are used to compile the queries. The place holders (?) are later replaced with the user-supplied data. In this way, the user input data and the queries are kept separate through prepared statements to avoid SQL injections.
User input sanitization can greatly reduce the SQLi attempts by hackers. The normal approach is to block special characters in the input query (string) by the user. However, there is a better sanitization approach. Instead of blocking special characters, consider allowing only special characters and blocking anything else. For instance and email, the field can have the following characters only.
a-z (alphabets), 0-9 (digits), (.), (-), (_), (@), (+)
The email sanitization must allow only these characters and block anything else.
Escape Character Approach
The aforementioned sanitization approach may work best for email and phone numbers except for the name field. The name may contain a valid quote (‘). The same quote is used to construct malicious SQL commands. However, we can use escape characters like (\) to allow legitimate quotes and block malicious ones.
SQL injection is one of the most sophisticated attacks in Cybersecurity. The variety of SQLi makes it hard for security experts to completely secure database management systems. However, with the right security measures and correct programming techniques, one can greatly mitigate the risk of becoming a victim of SQL attacks.