In our last session, you successfully performed a full UNION injection attack to extract credentials. However, that attack relied on a crucial assumption: we had to guess the names of the table (users) and its columns (username, password). In a real-world scenario, such guesses can be inefficient and unreliable.
Today, we will elevate your technique from educated guessing to systematic enumeration. You will learn how to interrogate the database itself to reveal its type, version, and internal structure. This process, known as database fingerprinting and schema discovery, is a cornerstone of professional penetration testing. This lesson focuses on extracting the database version and its metadata—the tables and columns it contains—using UNION-based injection.
1. Database Fingerprinting: What and Why?
The first step in methodical data exfiltration is to identify the database management system (DBMS) and its version. Different systems like Oracle, MySQL, and PostgreSQL have unique function names, query syntax, and metadata storage mechanisms. Knowing the version helps you:
- Craft the correct queries to extract schema information.
- Potentially identify known vulnerabilities associated with that specific software version.
You can achieve this by injecting database-specific queries and seeing which one executes successfully. A UNION attack is the perfect vehicle for this, as it allows you to return the output of the version query directly in the application's response.
The following resource provides a concise reference for the commands used across major database systems.
Examining the database in SQL injection attacks - PortSwigger
This article from PortSwigger's Web Security Academy is a valuable reference for querying database type and version.
Please read the section Querying the database. Pay close attention to the table that lists the specific version queries for Microsoft/MySQL, Oracle, and PostgreSQL. Also, note the example UNION payload which demonstrates how to embed this query.
For quick reference, here is a summary of common version-retrieval queries:
| Database | Query to Get Version |
|---|---|
| MySQL / Microsoft SQL Server | SELECT @@version |
| PostgreSQL | SELECT version() |
| Oracle | SELECT * FROM v$version |
| SQLite | SELECT sqlite_version() |
If you determined in a previous step that a vulnerable query returns two columns, you could try the following payload to fingerprint a MySQL or Microsoft SQL Server database:
' UNION SELECT @@version, NULL--
If the server responds with a version string, your identification is confirmed. If it errors out, you can move on to try the query for another database type.
2. Unveiling the Schema: Listing Tables and Columns
Once the database type is known, you can proceed to map out its structure. Most SQL databases (including MySQL, PostgreSQL, and Microsoft SQL Server) adhere to a standard called the information_schema. This is a built-in schema that contains a set of views providing information about all of the tables, columns, constraints, and other objects in the database.
The two most important views for our purposes are:
information_schema.tables: Contains information about all tables in the database.information_schema.columns: Contains information about all columns in all tables.
The following video provides a clear explanation of how to use the information_schema.
SQL injection Tutorial - Union Based Attack
This video from Infosec Mastery explains how to use the information_schema to discover table and column names.
Watch the segment from retrieving table and column names. Focus on the two main queries presented: one for listing all tables from information_schema.tables and the other for listing columns from information_schema.columns.
The Oracle Exception
It's critical to know that Oracle does not use the information_schema. Instead, it provides a set of data dictionary views. The most common ones for reconnaissance are:
all_tables: Lists all tables accessible to the current user.all_tab_columns: Lists columns in all tables accessible to the current user.
To list tables in Oracle, your payload would look something like this:' UNION SELECT table_name, NULL FROM all_tables--
This distinction is precisely why fingerprinting the database type first is so important.
3. Practical Application: An Oracle Case Study
Now, let's apply these concepts in a practical lab walkthrough. We will follow a demonstration that targets an Oracle database, which is an excellent case study due to its unique syntax requirements.
Step 1: Identifying the Database via Query Behavior
The initial steps in the video—confirming the vulnerability and finding the column count—will be a quick review. Pay close attention to how the presenter deduces that the backend database is Oracle based on how the server responds to a standard UNION query.
SQL Injection - Lab #7 SQL injection attack, querying the database type and version on Oracle
In this video, security researcher Rana Khalil walks through a PortSwigger lab targeting an Oracle database. You'll see how a standard UNION query fails and how that failure provides a crucial clue to the database type.
First, watch from the beginning to determine the column count. This will be a brief refresher on using ORDER BY. Next, and most importantly for this lesson, watch the segment from identifying the database. Notice how the initial ' UNION SELECT NULL,NULL-- payload fails. The key insight here is the subsequent payload: ' UNION SELECT NULL,NULL FROM dual--. The requirement of a FROM clause for every SELECT statement, and the use of the built-in dual table for this purpose, is a classic fingerprint for an Oracle database.
Step 2: Extracting the Version String
Having confirmed the database is Oracle, the next logical step is to retrieve its exact version string. The presenter uses the specific Oracle query for this, injecting it into one of the available columns in the UNION statement.
SQL Injection - Lab #7 SQL injection attack, querying the database type and version on Oracle
This part of the video demonstrates the final step: extracting the database version.
Watch the final segment from extracting the version. The payload used is ' UNION SELECT banner, NULL FROM v$version--. Here, banner is the column within the v$version view that contains the version information. Observe how the full version string is returned in the application's output, successfully achieving our goal.
Conclusion
You have now moved beyond guesswork and learned the systematic, professional approach to database enumeration via SQL injection. This methodical process is far more reliable and effective in a real-world engagement.
Key Takeaways:
- Fingerprint First: Always begin by identifying the database type and version. This is done by injecting version-specific queries (e.g.,
SELECT @@version,SELECT * FROM v$version) within aUNIONstatement. - Know Your Metadata Source: For most databases (MySQL, PostgreSQL, etc.), you will query the
information_schemato find tables and columns. - Remember the Exceptions: For Oracle, you must query its data dictionary views, such as
all_tablesandall_tab_columns. - Oracle's
dualTable: ASELECTstatement that doesn't logically need a table (like selecting static values) must still includeFROM dualin Oracle, a behavior that can be used to identify it.
In all our UNION attacks so far, we have relied on the application displaying our injected query's results directly on the page. But what if it doesn't? What if the injection point is in a query that doesn't return data, or the results are never shown? In our next lesson, we will tackle this challenge by exploring blind SQL injection, a set of techniques used to exfiltrate data when you can't see the output directly.