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

⚠️ Disclaimer

This guide is strictly for educational, defensive, and authorized-lab use (PortSwigger Web Security Academy, local testbeds, or authorized engagements). Do not apply these techniques to systems you do not own or have explicit permission to test. The payloads and examples are lab-safe and use placeholders such as <LAB_HOST>.

TL;DR

This lab shows how to move from a simple UNION probe to full database enumeration on non-Oracle systems by using the information_schema tables. The steps are:

1. Confirm the number of returned columns and which columns accept text.
2. Use UNION SELECT table_name, NULL FROM information_schema.tables to list database tables.
3. Target the discovered user-table name and enumerate its columns using information_schema.columns.
4. Craft a UNION SELECT to extract username and password fields (or concatenate them if necessary).
5. Use the retrieved admin credentials to log in and solve the lab.

Key successful payloads used in this lab (sanitized):

/filter?category=Accessories'+UNION+SELECT+table_name,+NULL+FROM+information_schema.tables--
/filter?category=Accessories'+UNION+SELECT+column_name,+NULL+FROM+information_schema.columns+WHERE+table_name='users_xksdey'--
/filter?category=Accessories'+UNION+SELECT+NULL,username_ntguxh||'-'||password_vrmldn+FROM+users_xksdey--

This write-up explains every step in detail, why it works, common pitfalls, robust troubleshooting, detection recommendations, and hands-on remediation steps for developers and ops.

1 - Lab objective & why it matters

Lab goal (PortSwigger): Using the vulnerable product category filter, list database tables, inspect columns in the user table, extract credentials, and log in as the administrator.

Why this lab is pedagogically rich: it demonstrates the full reconnaissance → enumeration → extraction flow for SQL injection on non-Oracle RDBMS (MySQL, PostgreSQL, etc.). In real engagements, this flow is often how an initial injection escalates to full data exposure. Understanding each phase and how defenders can stop it is crucial.

For a deeper theory primer, see our pillar post: The Ultimate Guide to SQL Injection (SQLi).

2 - Lab setup & initial reconnaissance

I started by opening the lab and browsing categories until I found the page that accepted a category parameter. I proxied my browser through Burp Suite and captured a representative request. The sanitized request pattern is:

GET /filter?category=Accessories HTTP/1.1
Host: <LAB_HOST>
Accept: text/html
...

This confirms the category parameter is reflected in the query results, making it a candidate for UNION-style injection because returned rows are included in the HTML response.

3 - Step 1 - Confirm column count & text-capable columns

Before listing tables, you must know how many columns the application query returns and which of those columns render text in the HTML. If you skip this, UNION payloads will error.

A quick test (replace Accessories with the category you captured) is:

/filter?category=Accessories'+UNION+SELECT+'abc','def'--

If the page shows abc and def, the query returns two columns and both accept text. In this lab the check showed two columns, both text-capable - that simplifies enumeration because we can return textual table/column names directly.

If you had seen an error, you would increment NULL placeholders until the UNION matches the column count (see earlier labs for that process).

4 - Step 2 - List database tables (information_schema)

With the column count known, you can list tables using the standard SQL metadata table information_schema.tables. The typical payload pattern is:

/filter?category=Accessories'+UNION+SELECT+table_name,+NULL+FROM+information_schema.tables--

Why this works: information_schema.tables holds all table names accessible to the DB user. Selecting table_name in the first column aligns with the application's two-column result set; NULL fills the second column placeholder. The app will then render table names where product data usually appears.

In my lab session the response contained a list of table names - among them a table like users_xksdey. That is our target for credential extraction.

Tip: Paginated or large schemas may require filtering the information_schema query (e.g., WHERE table_schema='public' or LIMIT), but in labs the set is small and visible.

5 - Step 3 - List columns for the user table

Having found users_xksdey, we need to know which column names hold username and password values. Use information_schema.columns:

/filter?category=Accessories'+UNION+SELECT+column_name,+NULL+FROM+information_schema.columns+WHERE+table_name='users_xksdey'--

This returns rows of column_name values. In the lab I discovered columns named username_ntguxh and password_vrmldn. Note the randomized suffixes - PortSwigger often uses randomized column/table names to ensure students don't rely on static names.

Practical note: Some DBs require table names in lowercase inside information_schema queries or require quoting-if your query returns nothing, try LOWER(table_name)='users_xksdey' or inspect the column naming exactly as returned.

6 - Step 4 - Extract credentials from the user table

Armed with column names, extract data by selecting those columns in the right order to match the application’s result columns. Example extraction payload used in the lab:

/filter?category=Accessories'+UNION+SELECT+NULL,username_ntguxh||'-'||password_vrmldn+FROM+users_xksdey--

Notes on the payload:

• NULL fills the first column (the non-text or placeholder column).
• username_ntguxh||'-'||password_vrmldn concatenates username and password into a single text value using || (string concatenation operator). Many RDBMS accept || (PostgreSQL, SQLite); MySQL may require CONCAT(username,'-',password) instead - adapt to the DB you fingerprinted.
• -- comments out the rest of the original SQL to avoid syntax issues.

After sending the payload in Burp Repeater, the HTML response contained concatenated username-password entries. From there I located the administrator credentials and used them in a standard login to solve the lab.

Example flow to authenticate (lab-only):

POST /login HTTP/1.1
Host: <LAB_HOST>
Content-Type: application/x-www-form-urlencoded

username=administrator&password=admin_password_from_payload

On success the lab shows a "Congratulations" banner or admin dashboard.

7 - Why this approach works (technical reasoning)

The sequence leverages two principles:

1. UNION combines result sets - If you control a second SELECT that matches the original column count and compatible types, you can append rows containing arbitrary values or metadata to the application's output.
2. information_schema is a metadata source - It lists tables and columns visible to the connected DB user, enabling schema discovery without access to application code or privileged credentials.

Combining these allows an attacker to discover table names, enumerate columns inside a target table, and extract the desired data.

8 - Troubleshooting & common pitfalls

This enumeration flow is reliable in labs but can fail in real-world conditions for a few reasons. I documented the common problems and fixes below.

Practical tip: always test a safe, visible string (e.g., 'TEST123') in different column positions before running a broad metadata query. That reduces noise and confirms which column renders output.

9 - Defensive perspective - immediate fixes & long-term strategy

This section is critical for developers, SecOps, and product owners. The attack chain is simple but high-impact - preventing any step will stop the chain.

9.1 Code-level fixes (most important)

• Use parameterized queries everywhere. This eliminates the root cause by separating code from data.
• Map inputs to safe IDs (e.g., { "Pets": 3 }) instead of interpolating raw strings into SQL.
• Use ORM query builders if appropriate - avoid raw SQL concatenation.

9.2 Database privileges

• Least privilege: The web application account should only have access to required tables - ideally only product-related tables for a public listing page. It should not have SELECT access to admin/user tables.
• Separate roles: Use different DB users/credentials for public-facing queries and administrative tasks.

9.3 Error handling & information hiding

• Hide DB error messages from users. Do not render stack traces or SQL errors on public pages.
• Generic error pages make enumeration much harder.

9.4 Runtime protections

• WAF rules: detect and block UNION SELECT, information_schema, or repeated NULL probes in parameters.
• Rate-limiting: throttle requests to filter endpoints to slow automated enumeration.
• Anomaly detection: alert on repeated parameter modifications that escalate from NULL probes to information_schema queries.

9.5 Testing & CI/CD

• Security tests in staging that run UNION SELECT NULL probes and fail builds or raise tickets if vulnerable traces are found.
• Code reviews that flag dynamic SQL and require parameterization before merge.

10 - Detection and logging recipes (practical)

Use these sample detection rules and logging ideas:

• WAF signature: Block or log query parameters that match (UNION\s+SELECT|information_schema|CONCAT\(|\|\|) (tune for false positives).
• SIEM correlation: Raise an alert if a single IP performs repeated NULL-increasing probes and then requests information_schema pages.
• App logging: Log suspicious parameter values and attach request IDs to logs for rapid incident triage.
• User telemetry: Alert when an admin login occurs from an IP that previously issued enumeration probes.

These detection layers should complement code fixes - they slow attackers and give defenders time to respond.

11 - Real-world examples & impact (detailed)

Adding real-world context helps explain why this simple lab technique is critical.

Example A - SaaS account compromise via user-table enumeration

An attacker used a filter parameter on a SaaS public dashboard to enumerate information_schema and then extracted admin emails and hashed passwords. They targeted weak hashes and performed offline cracking, later using the cracked admin password to access tenant settings. Root cause: a single public filter used a dynamic SQL fragment and a shared DB account with broad SELECT permissions.

Example B - Credential exfiltration through concatenation

A retail site had a vulnerable product search. Attackers concatenated email || ':' || password_hash and pulled hundreds of credentials. Those hashes were reused or cracked, enabling account takeover and subsequent fraudulent orders. Mitigation included rotating hashes and enforcing least-privilege DB accounts.

Example C - Chain to internal APIs

Enumeration revealed a table storing API tokens. Attackers used those tokens to call internal APIs, leading to lateral movement. A relatively small SQLi allowed a full chain: data discovery → credential/secret extraction → API abuse → privilege escalation.

These cases show the multi-stage impact: enumeration is rarely the end - it's the enabler.

12 - Ethical reporting & remediation workflow

If you find such an issue on an engagement:

1. Document steps reproducibly but avoid sharing raw credentials in public reports.
2. Provide a minimal PoC (e.g., screenshot showing redacted username: (redacted)), reproduction steps, and specific remediation suggestions (parameterize, restrict DB user).
3. Coordinate with ops teams for patching and rotation of any revealed secrets, if applicable.
4. Retest after fixes and provide validation evidence.

For bug-bounty programs, follow the program's disclosure policy and provide only the minimum data needed to prove the finding.

13 - Practice suggestions & next steps

To solidify the skillset:

• Re-run this lab and time yourself through enumeration steps until they become second nature.
• Build a local vulnerable app with a simple product filter that concatenates SQL, then fix it and demonstrate the patched behavior.
• Practice writing WAF signatures and SIEM correlation rules on logs generated by lab probes.
• Join a peer review: have another engineer attempt to find the SQLi with only the report you created - that validates your remediation instructions.

14 - Final thoughts

This lab is a complete demonstration of SQLi enumeration: a small injection combined with powerful DB metadata tables becomes a full schema discovery and data-extraction vector. The mitigation steps are clear and practical: parameterize queries, reduce DB privileges, and monitor for suspicious probes. As both a practitioner and defender, focus on preventing the enumeration steps - that neuters the attack early.

References & further reading

• PortSwigger Web Security Academy - SQL Injection labs (UNION, information_schema).
• OWASP - SQL Injection Prevention Cheat Sheet.
• MySQL / PostgreSQL documentation on information_schema and string functions.