SQL Injection in WHERE Clause: How to Retrieve Hidden Data (PortSwigger Lab Walkthrough)

Difficulty: Apprentice
Category: SQL Injection
Lab: SQL injection vulnerability allowing retrieval of hidden data

A beginner-friendly, step-by-step writeup on exploiting a classic SQL injection flaw in a product category filter to expose unreleased products.

Introduction

SQL injection remains one of the most common and dangerous web application vulnerabilities, even years after it was first documented. In this writeup, we'll walk through solving PortSwigger's "SQL injection vulnerability in WHERE clause allowing retrieval of hidden data" lab — a great starting point for anyone learning web application security.

The goal of this lab is simple: find a way to make an e-commerce application display products that haven't been released to the public yet, using nothing but a manipulated URL parameter.

Understanding the Vulnerability

When a user browses the shop and selects a product category, the application runs a backend SQL query similar to this:

SELECT * FROM products WHERE category = 'Gifts' AND released = 1

Here's what's happening:

• category = 'Gifts' filters products belonging to the selected category.
• AND released = 1 ensures that only products marked as released are shown to the public.

If user input (the category name) is inserted directly into this query without proper sanitization, an attacker can manipulate the logic of the entire SQL statement — not just the category filter.

Step-by-Step Solution

Step 1: Explore the Application

On the lab's homepage, you'll notice a "Refine your search" section listing various product categories such as Pets, Gifts, and more.

Clicking any category — for example, Pets — updates the URL to something like:

https://ID.web-security-academy.net/filter?category=Pets

Interestingly, you can replace Pets with almost any string, even a category that doesn't exist, and the application won't throw an error. This is a strong early indicator that the category parameter is being used directly inside a SQL query.

Step 2: Confirm the Query Structure

Based on the app's behavior, the backend is likely running:

SELECT * FROM products WHERE category = 'Pets' AND released = 1

This query returns only released products within the selected category — exactly what we want to bypass.

Step 3: A Naive (Failed) Attempt

A common first instinct is to simply comment out the rest of the query:

category=Pets' --

Which transforms the query into:

SELECT * FROM products WHERE category = 'Pets' -- ' AND released = 1

While this does successfully comment out the AND released = 1 check, it only returns products from the Pets category — and specifically, it still only returns released ones from that category by default, since we haven't actually broken the released condition in a useful way. This step is useful for understanding how the query is built, but it doesn't solve the lab on its own.

Step 4: Crafting the Working Payload

To truly bypass the filter and expose all products — released or not — we need a condition that's always evaluated as true, regardless of the category:

category=Pets' OR 1=1--

This produces the following query:

SELECT * FROM products WHERE category = 'Pets' OR 1=1 -- ' AND released = 1

Here's why this works:

• OR 1=1 is always true, no matter what.
• Since it's joined with OR, the entire WHERE clause evaluates to true for every row in the table.
• The -- comments out the remainder of the original query (AND released = 1), so the released-status check never even runs.

Result: The database returns every single product in the table, including unreleased ones — solving the lab.

Step 5: The Final URL

Putting it all together, the exploit URL looks like this (URL-encoded):

https://ID.web-security-academy.net/filter?category=Pets%27%20OR%201=1--

Why This Works: A Quick Analogy

Think of the WHERE clause as a gatekeeper checking two conditions joined by AND — both must be true to let data through. By injecting OR 1=1, we're telling the gatekeeper: "Let it through if the category matches, OR if this is always true (which it is)." Since 1=1 is unconditionally true, the gatekeeper lets everything through, and the trailing comment (--) erases the final check entirely.

You can experiment with this logic yourself using a safe SQL sandbox. Try running:

SELECT * FROM Customers WHERE City = 'Berlin' OR 1=1;

on W3Schools' Try SQL editor. Even if you change 'Berlin' to a city that doesn't exist, the query still returns every row in the table — proving how powerful (and dangerous) this kind of logic flaw can be.

Key Takeaways

• Never trust user input. Any value that reaches a SQL query — including dropdown or filter values — must be treated as untrusted.
• Parameterized queries (prepared statements) are the fix. They separate SQL logic from user data, making injection like this impossible.
• Error-free behavior isn't safe behavior. The fact that invalid categories didn't throw errors was itself a clue that input wasn't being validated or escaped.
• OR 1=1 is a classic SQLi fingerprint. It's one of the first payloads every security tester learns, precisely because it's so effective at revealing broken WHERE clause logic.

Conclusion

This lab is a great introduction to how SQL injection can be used to bypass application-level business logic — not just to dump a database, but to simply unlock data that was never meant to be public. Understanding the underlying query structure, testing input boundaries, and recognizing logical operators like OR 1=1 are foundational skills for anyone getting into web application penetration testing.

Resources