Summary
This module is focused on advanced SQL injection techniques, specifically when working with PostgreSQL. The target for this module is a Java/Spring application which we will decompile, analyze and eventually exploit.
This module is split up into the following sections:
-
Introduction: These sections serve as a very brief introduction to interacting with PostgreSQL. -
Identifying Vulnerabilities: In these sections we cover various white-box techniques for identifying/debugging SQL injection vulnerabilities in Java applications. -
Advanced SQL Injection Techniques: Throughout these sections we cover the following advanced SQL injection techniques:- Common character bypasses
- Error-based SQL injection
- Second-order SQL injection
-
PostgreSQL-Specific Techniques: These sections teach you the following attacks specific to PostgreSQL:- Reading and writing files
- Command execution
-
Defending Against SQL Injection: In this section we discuss how to fix/prevent SQL injection vulnerabilities from the point of view of a developer. -
Skills Assessment: Here we put your skills to the test! Identify and exploit multiple SQL injections in a Java application on your own.
After completing this module, you should be comfortable identifying and exploiting advanced SQL injection vulnerabilities, especially against Java applications and PostgreSQL databases.
CREST CPSA/CRT-related Sections:
- All sections
CREST CCT APP-related Sections:
- All sections
CREST CCT INF-related Sections:
- All sections
This module is broken into sections with accompanying hands-on exercises to practice each of the tactics and techniques we cover. The module ends with a practical hands-on skills assessment to gauge your understanding of the various topic areas.
You can start and stop the module at any time and pick up where you left off. There is no time limit or "grading," but you must complete all of the exercises and the skills assessment to receive the maximum number of cubes and have this module marked as complete in any paths you have chosen.
As you work through the module, you will see example commands and command output for the various topics introduced. It is worth reproducing as many of these examples as possible to reinforce further the concepts presented in each section. You can do this in the PwnBox provided in the interactive sections or your virtual machine.
The module is classified as "hard" and assumes an intermediate knowledge of how web applications function and common attack principles. It is highly recommended to also complete the following modules before starting this one:
- SQL Injection Fundamentals
- Blind SQL Injection
Introduction to PostgreSQL
Introduction
In this module we will be exploring a few advanced SQL injection techniques as well as some PostgreSQL-specific attacks from a white-box approach. As this is an advanced module, an understanding of SQL syntax, SQL injections and Python3 is expected to fully grasp the concepts explained. Although this module will focus on PostgreSQL, the same techniques can be adapted to work with other SQL variants, as it is a standardized language.
Interacting with PostgreSQL
Before we get into injection vulnerablities, let's take a moment to familiarize ourselves with two of the most common tools for interacting with PostgreSQL databases: psql and pgAdmin4.
psql (PostgreSQL Interactive Terminal)
psql is a command-line tool for interacting with PostgreSQL databases that comes pre-packaged with the PostgreSQL server and works on Linux or Windows.
You can install psql on a Linux distribution with this single command:
[!bash!]$ sudo apt install postgresql-client-15
Note: It's possible that the distribution of Linux you are running does not have version 15. In that case, you can install version 13 and everything will work fine with minimally adapted steps.
To install psql on Windows, you should first download the PostgreSQL installer from postgresql.org and then during the installation process unselect everything except for Command Line Tools.

Once it's done installing, you may use psql.exe from the installation directory (C:\Program Files\PostgreSQL\15\bin by default) or you can add the directory to the system PATH variable to be able to use it from anywhere:

Once you've installed psql on your operating system of choice, you can connect to a PostgreSQL database with the following command:
[!bash!]$ psql -h 127.0.0.1 [-p PORT] -U acdbuser acmecorp
Password for user acdbuser:
psql (15.1 (Debian 15.1-1+b1), server 13.9 (Debian 13.9-0+deb11u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
acmecorp=>
Once connected, you can list databases with the \l command or \l+ for extended details.
acmecorp=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
acmecorp | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
To switch to a database you can use the \c <DATABASE> command. In this case we are already in the acmecorp database.
To list the tables in a database (after you've switched to it), you can use the \dt command or \dt+ for extended information.
acmecorp=> \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------+-------+----------+-------------+---------------+------------+-------------
public | departments | table | postgres | permanent | heap | 8192 bytes |
public | dept_emp | table | postgres | permanent | heap | 72 kB |
public | employees | table | postgres | permanent | heap | 176 kB |
public | salaries | table | postgres | permanent | heap | 72 kB |
public | titles | table | postgres | permanent | heap | 80 kB |
(5 rows)
Last, but not least, you can query the database simply by entering the query and making sure it's terminated with a semicolon. Multi-line queries work as well.
acmecorp=> SELECT first_name, last_name, email FROM employees LIMIT 5;
first_name | last_name | email
------------+-------------+---------------------------
Kathleen | Flint | [email protected]
Henry | Watson | [email protected]
Ruth | Perez | [email protected]
Leon | Tappin | [email protected]
Donita | Fairweather | [email protected]
(5 rows)
pgAdmin4
pgAdmin4 is a GUI application for interacting with PostgreSQL databases that works on Linux and Windows.
To install pgAdmin4 on Linux, run the following commands:
[!bash!]$ curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
[!bash!]$ sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
[!bash!]$ sudo apt install pgadmin4
Note: If you are using Kali or ParrotOS (like the Pwnbox), you will want to replace $(lsb_release -cs) in the second command with bullseye, otherwise the installation will fail.
To install pgAdmin4 on Windows you can download the installer from pgadmin.org and follow the installation steps, or you can reuse the installer we used to install psql.exe, just make sure the pgAdmin4 option is checked this time in the installation process.
The first time you launch pgAdmin4 you will have to set a master password. This is simply to protect the credentials you will later use to connect to databases.

To connect to a PostgreSQL server, go to Dashboard > Add New Server, fill out the details and press Save.

Once you've done that, you can access the server on the left-hand side under whatever name you chose. Viewing databases and tables is very intuitive with this graphic interface, and running queries is as simple as right-clicking on a database and selecting the Query Tool.

Practice
To finish off this section install psql or pgAdmin4, spawn and connect to the target database (acmecorp) with the credentials acdbuser:AcmeCorp2023!, and then answer the questions below.