---
# System prepended metadata

title: PortSwigger - Examining the database
tags: [SQL injection, Web]

---

[ToC]
# Examining the database in SQL injection attacks
:::info
:bulb: Some core features of the SQL language are implemented in the same way across popular database platforms, and so many ways of detecting and exploiting SQL injection vulneralilities work identically on different types of database.  

However, there are also many differences between common databases. **There mean that some techniques for detecting and exploiting SQL injection work differently on different platforms**. 
:::

For example:  
- Syntax for string concatenation
- Comments
- Bathed (or stacked) queries
- Platform-specific APIs
- Error messages

:::    success
:point_right: [SQL injection cheat sheet](https://portswigger.net/web-security/sql-injection/cheat-sheet)
:::

## Querying the database type and version

### PRACTITIONER Lab: SQL injection attack, querying the database type and version on Oracle

In this exercise, we have a shopping site display products. We can refine our search, and the browser will send it to the server through parameter `category`

![](https://hackmd.io/_uploads/BJyqRCiK3.png)

I try to confirm the SQL injection vulnerable by using single quote character `'` and boolean condition `OR 1=1`. Of course to make sure the query won't error we should add commnent charater `--` 

![](https://hackmd.io/_uploads/rycYbJnF2.png)

![](https://hackmd.io/_uploads/SJCyM13F3.png)

:::    success
=> Parameter `category` vulnerable to SQL injection
:::

Before making the database retrive the string version, we need to try to figure out how many columns a being return and because we not really sure which server's database is so we need to try syntax of all common database

![](https://hackmd.io/_uploads/BkgBExhYn.png)

It seems that from the query to determine the number of columns we can also guess that this datbase is Oracle. Let's confirm that by querying version of database.

![](https://hackmd.io/_uploads/S16FSx2F3.png)

:::success
**Solved** :thumbsup: 
:::

### PRACTITIONER Lab: SQL injection attack, querying the database type and version on MySQL and Microsoft


Just like previous exercise, there is some step we need to do before trying to enumerate the database

#### 1. Comfirm the SQL injection vulnerable 
A difference in this exercise that the orginal query may have somthing special behind so when I try to comment all the things behind my input, i get an error.

![](https://hackmd.io/_uploads/B16Y91aYn.png)

So I try this

![](https://hackmd.io/_uploads/BJjejyTFh.png)

This will make the query works without comment all the things behind
:::danger
But Why We Can't Comment ?? 
What if the comment mark is wrong syntax :thinking_face:
:::

So I checked the cheat sheet again 

And surprise...

![](https://hackmd.io/_uploads/HJTsXxpF2.png)

The only database that throws an error if we enter the two hyphens as usual is MySQL

Let's confirm that

![](https://hackmd.io/_uploads/HJWELlaKn.png)

Yup that's true :smiley: And if you wondering about `a` letter at the end, its purpose is to send the space character after the two hyphens, without it the browser may automatically remove the space when sending

#### 2. Determine how many columns a being return and what it's type 

![](https://hackmd.io/_uploads/BJuH0x6t3.png)

#### 3. Figure out the database type and version

![](https://hackmd.io/_uploads/HJV1kWaF2.png)

:::success
**Solved** :thumbsup: 
:::

## Listing the contents of the database

:::info
:bulb: Most database types (with the notable exception of Oracle) have a set of views called the information schema which provide information about the database.
:::


### PRACTITIONER Lab: SQL injection attack, listing the database contents on non-Oracle databases


#### 1. Comfirm the SQL injection vulnerable

![](https://hackmd.io/_uploads/H184gWat3.png)

The two hyphens work without the trailing spaces => that's mean the database can't be MySQL

#### 2. Determine how many columns a being return and what it's type

![](https://hackmd.io/_uploads/SkkbM-6Y3.png)

#### 3. Figure out the database type and version

![](https://hackmd.io/_uploads/ryeVGW6tn.png)

#### 4. Listing the contents of the database

In PostgreSQL we can select `table_name` and `table_schema` in `information_schema.tables` like this

![](https://hackmd.io/_uploads/BkH9zZTtn.png)

:::info
:bulb: 
`information_schema.tables` is a system **view** (not a table) that contains metadata about all tables in a database

`table_schema` column refers to the schema that the table belongs to

In PostrgreSQL, the default schema that is created when a new database is created is called `public`, unless a different schema is specified in the CREATE TABLE statement.

It's important to note that the public schema is just like any other schema in PostgreSQL, and can be renamed or deleted if desired.
:::

For more experienced people, they will probably know which are the database tables and which are the created table, but for a newbie like me, I would start with tables where the `table_schema` value is public.

We can easily filter the resutl with WHERE clause

```sql=
SELECT table_name, table_schema FROM information_schema.tables WHERE table_schema = 'pulic' 
```

![](https://hackmd.io/_uploads/Bkc1R-6F3.png)

And we can use this query to list all the column name and it's datatype 

```sql=
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'users' 
```

![](https://hackmd.io/_uploads/rkmRCbaKn.png)

Luckily, the two columns's datatype is charater so we can easily select it.

![](https://hackmd.io/_uploads/Byg6EMpt3.png)

#### 5. Login to administrator account

![](https://hackmd.io/_uploads/SJ_-SMTth.png)

:::success
**Solved** :thumbsup: 
:::


### PRACTITIONER Lab: SQL injection UNION attack, listing the database contents on Oracle


:::info
:bulb: On Oracle, you can obtain the same information with slightly different queries.

You can list tables by querying `all_tables`

And you can list columns by querying `all_tab_columns`
:::

#### 1. Comfirm the SQL injection vulnerable :heavy_check_mark:

#### 2. Determine how many columns a being return and what it's type :heavy_check_mark: 

#### 3. Figure out the database type and version :heavy_check_mark: 

#### 4. Listing the contents of the database

![](https://hackmd.io/_uploads/S1clvM6Fh.png)

![](https://hackmd.io/_uploads/Hk3vPfTt3.png)

![](https://hackmd.io/_uploads/rJH6PfTth.png)

#### 5. Login to administrator account

![](https://hackmd.io/_uploads/rkjedMptn.png)

:::success
**Solved** :thumbsup: 
:::