Auth Bypass in Adonisjs with Postgresql

Auth bypass in an AdonisJS application backed by PostgreSQL often stems from incorrect query construction or missing row-level checks, rather than a flaw in AdonisJS or PostgreSQL themselves. When authentication logic relies on raw or poorly parameterized queries, attackers can manipulate inputs to change the intended WHERE clause behavior.

Consider a typical login flow that builds a SQL string using concatenation or unsanitized request inputs. If the developer interpolates a user-provided email or username directly into the query, an attacker can supply a payload such as ' OR '1'='1 to alter the logical condition. In PostgreSQL, the single quote must be escaped correctly; mishandling this allows the attacker to create a tautology that bypasses credential checks.

AdonisJS relies on its Lucid ORM, which encourages query builder usage, but developers sometimes fall back to raw queries for complex cases. A vulnerable pattern looks like this:

const email = request.input('email');
const user = await Database.from('users').whereRaw(`email = '${email}' AND password = '${password}'`).first();

If email is set to ' OR 1=1 --, the resulting SQL becomes:

SELECT * FROM users WHERE email = '' OR 1=1 -- ' AND password = '...';

The comment sequence (--) truncates the password check, and the condition always evaluates to true for at least one row, effectively bypassing authentication. Even when using the query builder, omitting scope checks (e.g., tenant ID or account status) can lead to horizontal privilege escalation, where one user accesses another user’s data through predictable identifiers.

PostgreSQL-specific behaviors can exacerbate this. For example, case-insensitive collation and certain casting rules may cause unexpected matches. If an application compares email with a case-insensitive operator without canonicalizing input, variations in casing might match unintended rows. Additionally, schema design choices—such as storing disabled flags in a separate column without enforcing them in every query—mean that an authenticated query might still return records the application should reject. Attackers probe these gaps by sending crafted requests that test boundary conditions in WHERE clauses and JOIN logic.

Another vector involves inadequate handling of nullable fields or missing rows. If the code assumes a query always returns a row and skips further verification (e.g., checking password hash matches), an attacker can exploit a non-matching condition that still passes the initial filter due to incomplete constraints. This is common when developers omit explicit status checks like is_active = true or tenant identifiers in multi-tenant schemas, leading to auth bypass across accounts.

Securing authentication against bypass in AdonisJS with PostgreSQL requires disciplined query construction, strict input validation, and explicit constraints in every database interaction. The primary defense is to avoid string interpolation entirely and rely on parameterized queries or the query builder’s binding mechanisms.

Use the AdonisJS query builder with bound parameters instead of raw concatenation:

const user = await Database.from('users')
  .where('email', email)
  .andWhere('password_hash', passwordHash)
  .andWhere('is_active', true)
  .andWhere('tenant_id', tenantId)
  .first();

This ensures that user input is treated strictly as values, not executable SQL. For cases where raw conditions are unavoidable, use bound placeholders:

const user = await Database.raw('SELECT * FROM users WHERE email = $1 AND password_hash = $2 AND is_active = $3 AND tenant_id = $4', [email, passwordHash, true, tenantId]).first();

PostgreSQL enforces strict type matching; ensure that column types align with bound values to avoid implicit casting vulnerabilities. For passwords, always compare against a stored hash using a constant-time routine; never include the plaintext password in SQL logic. Store schema elements like disabled flags or tenant IDs directly in the table and enforce them in every query, avoiding optional scopes that can be accidentally omitted.

Schema-level constraints in PostgreSQL further reduce risk. Define NOT NULL, UNIQUE, and CHECK constraints to prevent ambiguous matches. For example, enforce a unique constraint on normalized email and add a check that critical flags are boolean true:

-- PostgreSQL DDL snippet
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  is_active BOOLEAN NOT NULL CHECK (is_active IS TRUE),
  tenant_id UUID NOT NULL
);

In application code, normalize inputs before comparison to mitigate case-sensitivity issues:

const normalizedEmail = email.trim().toLowerCase();
const user = await Database.from('users')
  .where('email', normalizedEmail)
  .andWhere('password_hash', passwordHash)
  .first();

Regularly audit query outputs for row counts and ensure that authentication flows validate the exact expected record. Combine these technical controls with runtime scanning using tools that exercise authentication endpoints to detect regressions. middleBrick can help by scanning your API endpoints for authentication misconfigurations and providing prioritized remediation guidance tied to frameworks such as OWASP API Top 10.