SQL injection attack, listing the database contents on non-Oracle databases

    A typical SQL injection lab focused on “listing database contents” is designed to simulate how attackers move from a simple injection point to full database enumeration. In non-Oracle database systems such as MySQL, PostgreSQL, and Microsoft SQL Server, this process heavily relies on UNION-based SQL injection combined with metadata exploration.

The key idea behind this type of lab is not simply to extract data, but to understand how relational databases expose internal structure through system catalogs. Once an attacker can manipulate a query using UNION, they can gradually reconstruct the database schema, identify tables, discover columns, and finally extract sensitive application data.

In real-world applications, this becomes possible when user input is directly concatenated into SQL queries. Instead of being treated as data, the input becomes part of the executable query structure, allowing the attacker to influence how the database executes logic.

UNION Injection and Structural Query Control

The first stage in the lab begins with a simple structural test:

' UNION SELECT NULL --

This represents the foundational concept of UNION-based SQL injection. The UNION operator merges results from two SELECT statements into a single result set. However, for this to work, both queries must have:

• the same number of columns
• compatible data types

At this stage, the attacker is not extracting any meaningful data. The use of NULL is intentional because it avoids type conflicts. This step is purely about determining whether the backend query structure can be extended.

The -- comment syntax ensures that any remaining part of the original query is ignored. This is essential because most SQL injection points are embedded inside larger queries, and without commenting out the rest, syntax errors would break execution.

Research on SQL injection UNION attacks confirms that this step is always required before any data extraction is possible, because structural alignment is mandatory for UNION queries to execute successfully

Output Reflection and Understanding Visible Columns

The next stage introduces string-based testing:

Gifts' UNION SELECT 'text', 'text' --

This step shifts the focus from structural validation to output reflection mapping. Instead of NULL values, fixed strings are injected into the query results. The purpose is to determine which columns are actually displayed in the application response.

This is important because SQL queries often return multiple columns, but web applications usually display only a subset of them. Some columns might be processed internally or ignored entirely.

By injecting recognizable values like 'text', the attacker observes where these values appear in the response. This process effectively maps:

• database output columns → application UI fields

Without this step, later data extraction could succeed at the database level but fail to appear in the browser.

Schema Discovery Using information_schema

Once structural control and reflection mapping are confirmed, the next stage is schema discovery:

'UNION SELECT NULL FROM information_schema.columns --

This introduces one of the most important concepts in relational databases: system metadata catalogs.

In MySQL, PostgreSQL, and MSSQL, information_schema is a built-in virtual database that stores metadata about all database objects. It includes:

• tables
• columns
• data types
• schemas
• constraints

The columns table specifically contains a full list of every column in every table in the database.

According to SQL injection research, information_schema is a standard feature across multiple database engines and is commonly targeted in enumeration attacks because it provides structured access to schema metadata .

At this stage, the attacker is no longer interacting with application data. Instead, they are querying the database’s internal description of itself. 

Targeted Column Enumeration for Specific Tables

The next payload refines schema extraction:

'UNION SELECT column_name, NULL FROM information_schema.columns WHERE table_name ='users_boqnzp' --

This step demonstrates targeted metadata filtering. Instead of retrieving all columns from the database, the query narrows results to a specific table.

The column_name field returns the names of columns inside the selected table. This allows the attacker to reconstruct the structure of the users_boqnzp table without any prior knowledge of its schema.

This is a critical step in SQL injection because modern databases often contain hundreds of tables. Without schema enumeration, guessing table structure would be impractical.

In real-world exploitation, attackers typically use this phase to identify:

• username fields
• password fields
• email or session token columns

Once the structure is known, data extraction becomes straightforward.

PostgreSQL System Catalogs and Privilege Exposure

The sequence also includes references to PostgreSQL internal tables:

Gifts'UNION SELECT # pg_user, NULL ...
Gifts'UNION SELECT member, admin_option FROM pg_auth_members --

These queries interact with PostgreSQL’s internal system catalogs, which are fundamentally different from application tables.

PostgreSQL uses system tables such as:

• pg_user → stores database users
• pg_roles → defines roles and permissions
• pg_auth_members → maps role memberships

These structures form the database’s authentication and authorization system.

E

xtracting data from these tables exposes sensitive security information such as:

• user accounts
• role assignments
• administrative privileges
• inheritance relationships between roles

This type of exposure is particularly dangerous because it does not just reveal application data—it reveals how the database itself is secured.

In security research, this is considered a shift from data extraction to privilege system enumeration, which can enable privilege escalation in real environments.

Direct Application Data Extraction

The final payload represents full exploitation:

Gifts'UNION SELECT username_yupsye, password_lfffee FROM users_boqnzp --

At this stage, the attacker has already:

• confirmed injection
• matched query structure
• mapped output columns
• discovered schema using metadata

Now they directly extract application data from a user table.

This demonstrates the highest impact of SQL injection: loss of confidentiality of sensitive data. If the database stores credentials insecurely (for example, weak hashing or plaintext passwords), this can lead to full account compromise.

Security literature and SQL injection cheat sheets consistently show this as the final stage of UNION-based attacks, where attackers directly target application tables after schema discovery.

PostgreSQL Role Membership Enumeration

The final query:

Gifts'UNION SELECT member, admin_option FROM pg_auth_members --

focuses specifically on PostgreSQL privilege relationships.

The pg_auth_members table defines how roles are assigned to users and whether they inherit administrative privileges. Extracting this data provides insight into:

• who has elevated permissions
• how roles are structured
• whether privilege escalation paths exist

This is not application data but security infrastructure data, which can be just as sensitive or even more sensitive than user credentials in certain environments.

Understanding privilege structure is essential in assessing the impact of a compromised database, as it determines how far an attacker could escalate access.

This lab demonstrates a structured progression of SQL injection exploitation. It begins with structural validation, moves into output reflection mapping, advances into schema discovery using information_schema, expands into PostgreSQL system catalog exploration, and ends with full application data extraction.

Each stage builds logically on the previous one. Without structural alignment, UNION cannot execute. Without reflection mapping, results cannot be seen. Without metadata discovery, table structure remains unknown. Without schema knowledge, data extraction is guesswork.

This progression reflects a key principle of relational database systems: they

are inherently introspective. They are designed to describe their own structure through system catalogs like information_schema (MySQL/PostgreSQL) or sys.objects and sys.columns (MSSQL). SQL injection exploits this introspective capability by forcing the database to reveal its internal structure through legitimate query mechanisms.

From a defensive perspective, this entire attack chain exists because of one fundamental flaw: mixing user input with executable SQL logic. Once that boundary is broken, the database becomes fully queryable beyond intended constraints.