Most web applications use one or multiple databases to store and process information in real time.
Indeed, when a user sends requests, the web application queries the database in order to build the response. However, when the information provided by the user is used to forge the query to the database, an attacker can alter the database by using it for purposes other than those intended by the original developer. This allows an attacker to query the database via SQL injection, or SQLi.
SQL injection refers to attacks against relational databases such as MySQL, Oracle Database or Microsoft SQL Server. By contrast, injections against non-relational databases, such as MongoDB or CouchDB, are NoSQL injections.
Principles, impacts, exploitations, in this article we present an overview of SQL injections, as well as best security practices and measures to implement to counter the risks of attack.
What is an SQL injection (SQLi)?
There are many types of injection vulnerabilities, such as XSS vulnerabilities, HTTP header injection, code injection and command injection. However, the best known, one of the most feared and the favourite of attackers is certainly SQL injection.
An SQL injection occurs when a malicious user communicates an entry that modifies the SQL query sent by the web application to the database. This then allows the user to execute other unwanted SQL queries directly on the database.
To do this, the attacker must inject code outside the bounds of the expected user input, so that it is not executed as standard input. In the simplest case, injecting a single or double quote is enough to escape the bounds of user input and insert data directly into the SQL query.
Indeed, if there are injection possibilities, the attacker will look for a way to execute a different SQL query. In most cases, they will use SQL code to create a query that executes both the intended SQL query and the new SQL query.
Use cases and impacts of an SQL injection
An SQL injection can have a huge impact, especially if the privileges on the server and on the database are too permissive.
Firstly, an attacker can retrieve sensitive information, such as user IDs and passwords or credit card information. Indeed, SQL injections are the cause of many password and data compromises of websites and applications.
Another use case for SQL injection is to hijack the intended logic of the web application. The most common example is bypassing an authentication page. Attackers may also be able to read and write files directly on the server, which can lead to backdoors being placed on the server and then the application being taken over.
How to hijack the logic of an application via an SQL injection attack?
Before we start executing full SQL queries, we will first look at how to hijack the logic of the original query.
Search for a parameter vulnerable to SQL injections
Before we can achieve our goal of hijacking the logic of the web application and bypassing authentication, we first need to test the authentication form to see if it is vulnerable to SQL injection.
To do this, we can add one of the payloads below after our username and see if this causes any errors or changes the behaviour of the page:
| Payload | URL Encoded |
| ‘ | %27 |
| “ | %22 |
| # | %23 |
| ; | %3B |
| ) | %29 |
When adding a single quote, an SQL error is displayed.
The SQL query sent to the database is as follows:
The quote we entered resulted in an odd number of quotes, which caused a syntax error. One option would be to comment out and write the rest of the query as part of our injection to forge a functional query. Another option is to use an even number of quotes in our injected query, so that the final query still works.
Authentication bypass via SQL injection attack
On this authentication page, we can log in with the administrator’s credentials:
Username: admin
Password: Vaada7aPa55w0rd!
The page displays the SQL query being executed in order to better understand how to hijack the logic of the query.
The page takes the credentials and then uses the AND operator to select the records that match the username and password entered. If the MySQL database returns matching records, the credentials are valid, and the PHP code evaluates the login attempt condition as true. If the condition is true, the administrator record is returned, and our connection is validated.
On the contrary, when the wrong connection information is entered, the connection fails and the database returns “False”.
SQL injection attack with the OR operator
To bypass authentication, the query would have to return true, regardless of the username and password entered. To do this, we can abuse the OR operator in our SQL injection.
The MySQL documentation states that the AND operator is evaluated before the OR operator. This means that if there is at least one true condition in the query with an OR operator, the query will be evaluated as true since the OR operator returns true if one of its operands is true.
An example of a condition that always returns TRUE is 1=1. However, to keep the SQL query working and the number of quotes even, instead of using (‘1’=’1’), we will delete the last quote and use (‘1’=’1), so that the remaining single quote from the original query is in its place.
The AND operator will be evaluated first and will return “false”. Then the OR operator will be evaluated, and if either statement is true, it will return “true”. Since 1=1 always returns true, this query will return true and give us access.
Note: The payload we used above is one of many authentication bypass payloads we can use to bypass authentication logic.
If we have an invalid username, the connection will fail because it does not exist in the table and has resulted in a false global query.
Bypassing authentication with comments
Like any other language, SQL also allows the use of comments. Comments are used to document queries or to ignore a certain part of the query. We can use two types of comments with MySQL: — and #.
As we can see, the rest of the request is now ignored and the password is no longer checked. In this way we can ensure that there are no syntax problems with the request.
Focus on SQL injection attacks with UNION (Union Based SQLi)
Another type of SQL injection is to inject entire SQL queries that are executed at the same time as the original query.
The UNION clause is used to combine the results of multiple SELECT statements. This means that with a UNION injection we will be able to select and retrieve data from the entire database.
UNION combined the output of the two SELECT statements into one, so the table entries were combined into one output.
A UNION statement can only work on SELECT statements with an equal number of columns. UNIONing two queries that have results with a different number of columns will return an error.
If there are more columns in the original query table, more numbers must be added to create the remaining columns required.
As we can see, the desired result of the query is in the first column of the second row, while the numbers fill the other columns.
Identification of the number of columns
In order to exploit queries based on the UNION clause, the number of columns selected by the server must be found. There are two methods to identify this number:
- Using ORDER BY
- Using UNION
Using ORDER BY
The first way to identify the number of columns is the ORDER BY clause. The injected query will sort the results by the number of columns we specified until we get an error indicating that the specified column does not exist. The last column we successfully sorted by gives us the total number of columns.
Using UNION
The other method is to use the UNION clause with a different number of columns until we get the results successfully. Unlike the previous method, this one always gives an error until we get the right number of columns.
Locating the injection
While a query may return several columns, the web application can only display some of them. Thus, if we inject our query into a column that is not displayed on the page, we will not get its result. Therefore we need to determine which columns are present on the page, in order to determine where to place our injection.
Enumeration of the database following an SQLi
Before listing the database, we need to identify the type of Database Management System (DBMS) so that we know which queries to use.
If the web server we see in the HTTP responses is Apache or Nginx, it is likely that the web server is Linux, and therefore the DBMS is MySQL. The same goes for the Microsoft DBMS if the web server is IIS, so it is probably MSSQL. So there are different queries we can test to determine the database type.
Now, to retrieve data from tables using UNION SELECT, we need to properly form our SELECT queries. To do this, we need to have:
- The list of databases
- The list of tables in each database
- The list of columns in each table
With the above information, we can formulate our SELECT statement to retrieve all the data.
The INFORMATION_SCHEMA database contains metadata about the databases and tables on the server. This database plays a crucial role in the exploitation of SQL injection vulnerabilities.
Schema
To find out which databases are available on the DBMS, we can use INFORMATION_SCHEMA.SCHEMATA, which contains information about all databases on the server.
Tables
To find all the tables in a database, we can use INFORMATION_SCHEMA.TABLES. This can be done in the same way as finding the database names.
Columns
To find the names of the columns in the table, we can use the COLUMNS table in the INFORMATION_SCHEMA database. It contains information about all the columns present in all the databases.
Data
Now that all the information is gathered, we can form our UNION query to extract the data from the database.
Reading files following an SQLi
An SQL injection can also be used to perform many other operations, such as reading and writing files on the server and even remotely executing code on the server.
Reading data is much more common than writing data, which is strictly reserved for privileged users in modern DBMSs, as it can lead to system exploitation. In MySQL, the database user must have the FILE privilege to load the contents of a file into a table.
Several SQL queries can be used to determine which user is executing the queries.
We can now list the privileges of the users. We see that the FILE privilege is listed for our user, which allows us to read files and potentially even write to them.
With this FILE privilege, a user is able to read files from the server.
Writing in files following an SQLi
Writing files to the server can be used to write a webshell to the remote server, which will allow the user to execute code and take control of the server.
As with reading files, if the user has the following privileges, they will be able to write to the server:
- FILE privilege enabled
- MySQL global variable secure_file_priv not being set.
- Write access to the location where he wants to write to the server.
The SELECT INTO OUTFILE statement can be used to write data to files from selection queries. It is typically used to export data from tables.
An attacker can upload a webshell and gain access to the server.
How to counter SQL injection attacks?
In this section, we will learn how to avoid these types of vulnerabilities in our code and how to fix them when they are discovered.
Ensure proper management of user privileges
DBMS software allows users to be created with very fine-grained permissions. We must ensure that the user querying the database has only the minimum permissions.
Use prepared statements
The use of prepared statements is another way of ensuring that input is secure. Indeed, prepared statements contain placeholders for input data, which is then escaped and passed by the drivers. Instead of passing the data directly in the SQL query, we use placeholders and then fill them with PHP functions.
The query is modified to contain two placeholders, marked with “?” where the username and password will be placed. We then bind the username and password to the query using the mysqli_stmt_bind_param() function. This function allows us to escape the quotes and place the values in the query.
Other posts on common attacks and vulnerabilities of applications: