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

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.