Auth Bypass in Adonisjs with Mssql

AdonisJS is a Node.js web framework that often pairs with Microsoft SQL Server (Mssql) via packages like @adonisjs/lucid for ORM and authentication utilities. When authentication is implemented using Mssql as the backing store, specific implementation patterns can lead to Auth Bypass vulnerabilities, particularly when developer assumptions about query behavior, parameterization, and session validation diverge from runtime behavior.

One common pattern is constructing SQL queries using string concatenation or improperly parameterized inputs when validating credentials. For example, a login route that builds a WHERE clause by interpolating user input directly into the SQL string can bypass authentication if an attacker supplies a payload that always evaluates to true. With Mssql, this can occur when user-controlled values are not bound as parameters, allowing an attacker to inject logic such as ' OR '1'='1 into a username or password field. If the query returns any row, the attacker is authenticated without valid credentials.

Another vector specific to AdonisJS with Mssql involves how the framework’s authentication guards and Lucid models resolve user existence and password verification. If the developer overrides or bypasses the standard auth provider methods and writes custom Mssql queries without consistent hashing and timing-safe comparisons, an attacker can exploit subtle differences in SQL execution to infer valid usernames or evade checks. For instance, an improperly constructed conditional like WHERE username = @username AND deleted_at IS NULL may still return a row for a soft-deleted account if the parameter binding is inconsistent, leading to unexpected authentication success.

Mssql-specific behaviors such as case-insensitive collation by default can also contribute to bypasses. An attacker might use mixed-case payloads that match usernames in ways the developer did not anticipate, especially when string comparisons in the application logic do not enforce explicit case sensitivity. If AdonisJS middleware assumes case-sensitive checks but the underlying Mssql query does not enforce it, an attacker can log in as another user by leveraging collation differences.

Additionally, when using stored procedures or dynamic SQL on the Mssql side, AdonisJS applications may inadvertently trust EXEC results without validating the output shape. A procedure that returns a token or user row under certain conditions can be abused if the application trusts the presence of any row. This trust can be exploited by feeding crafted input that causes the procedure to return success for an unauthorized context, effectively bypassing the intended gate.

These issues are detectable by middleBrick’s 12 security checks, which include Authentication, BOLA/IDOR, and Input Validation scans. The scanner cross-references the OpenAPI/Swagger spec (including $ref resolution) with runtime behavior to highlight discrepancies such as unparameterized queries, missing row-level constraints, and inconsistent guard implementations. Findings include severity, a description of the vulnerable pattern, and remediation guidance mapped to frameworks like OWASP API Top 10.

To secure authentication when using AdonisJS with Mssql, adopt parameterized queries, explicit collation handling, and strict validation of ORM results. Below are concrete code examples that demonstrate secure patterns.

1. Parameterized login query

Always use Lucid’s query builder with bound parameters instead of raw string concatenation. This prevents injection and ensures Mssql treats values as data, not executable SQL.

import { schema } from '@ioc:Adonis/Core/Validator'
import User from 'App/Models/User'
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'

export default class SessionsController {
  public async store({ request, auth, response }: HttpContextContract) {
    const loginSchema = schema.create({
      username: schema.string(),
      password: schema.string(),
    })

    const validated = await request.validate({ schema: loginSchema })

    // Secure: parameterized query via Lucid
    const user = await User.query()
      .where('username', validated.username)
      .where('deleted_at', null)
      .preload('roles')
      .first()

    if (!user || !(await user.verifyPassword(validated.password))) {
      return response.unauthorized()
    }

    await auth.use('api').login(user)
    return response.ok({ token: await user.generateToken() })
  }
}

2. Enforce case-sensitive comparison when needed

If your security model requires case-sensitive username checks, specify collation explicitly in the query to avoid reliance on server defaults.

const user = await User.query()
  .where('username', 'collate', 'Latin1_General_CS_AS', request.input('username'))
  .whereRaw('deleted_at IS NULL OR deleted_at = ?', [null])
  .first()

3. Validate output shape and use consistent guards

Do not trust implicit row counts from stored procedures. Map returned fields explicitly and validate before authentication.

const result = await Database.fromRaw(
  'exec dbo.LoginUser @username, @password',
  { username: validated.username, password: validated.password }
)

if (result.length !== 1 || !result[0].id) {
  throw new Error('Invalid credentials')
}

const user = await User.find(result[0].id)
if (!user) {
  throw new Error('User not found')
}

4. Use framework auth providers instead of custom SQL

Leverage AdonisJS’s built-in auth provider so the framework handles hashing, token generation, and guard consistency. Custom SQL should only supplement, not replace, the provider when necessary.

5. Apply principle of least privilege to Mssql logins

Ensure the Mssql user configured in database.ts has only the permissions needed for authentication operations, reducing impact if a bypass is discovered elsewhere.

// config/database.ts
export const db = {
  connection: {
    client: 'mssql',
    version: '15',
    host: 'localhost',
    port: 1433,
    user: 'app_user',
    password: process.env.DB_PASSWORD,
    database: 'app_db',
  },
  debug: false,
}