Node.js Auth Query SQL Injection via Group Code Interpolation

Introduction

The authorization check that runs during sign-in validates whether a user's account belongs to one of a set of permitted groups. To build that check, the code took an authorizedGroups array and turned it into a SQL fragment with:

authorizedGroups.map((group) => `'${group}'`).join(", ")

That fragment was then spliced straight into a template-literal SQL string passed to _db.queryFirst(). Any group code that reaches this array without being escaped becomes part of the SQL text itself — not a bound parameter. This matters because the query in question isn't a reporting endpoint or an admin dashboard filter; it's the gate that decides whether a signed-in user is allowed to proceed, which makes it one of the highest-value targets in the whole authentication flow for an injection bug.

Affected Versions

This is first-party application code with no package version to track. The fix is scoped to the single authorization query inside the sign-in handler.

The Vulnerability Explained

Here's the relevant part of the original query construction:

WHERE 1 = 1
    AND people_id = ${dbPeople.getInt("id")}
    AND user_group_id IN (
        SELECT id FROM user_group WHERE code IN (
            ${authorizedGroups.map((group) => `'${group}'`).join(", ")}
        )
    )
    AND active = true

Two problems stack on top of each other here:

1. dbPeople.getInt("id") is interpolated directly into the template literal rather than bound as a parameter.
2. Each entry in authorizedGroups is wrapped in single quotes and joined with commas by hand — a manual, unescaped string-building step feeding straight into the IN (...) clause.

If any value that ends up in authorizedGroups can be influenced — for example through a group-management flow, a claim derived from an upstream token, or any code path that assembles this array from external input — a crafted group code containing a single quote can break out of the intended string literal. From there, an attacker can comment out the rest of the clause, inject an OR 1=1-style condition, or otherwise manipulate the truth value of the WHERE clause. Because this specific query is the authorization decision for sign-in, a successful injection doesn't just leak data — it can make isAuthorized resolve truthy for a user who shouldn't pass, defeating the group-based access control outright.

The Fix

The fix removes every hand-built string from the query and replaces it with positional ? placeholders, binding the actual values through a value-builder:

WHERE 1 = 1
    AND people_id = ?
    AND user_group_id IN (
        SELECT id FROM user_group WHERE code IN (?, ?)
    )
    AND active = true
`, _val.init()
  .add(dbPeople.getInt("id"))
  .addAll(authorizedGroups));

Two changes work together