Sql Injection in Adonisjs with Jwt Tokens

SQL injection in AdonisJS when JWT tokens are involved typically occurs not from the JWT parsing itself, but from how developer code uses claims or payload data to construct dynamic database queries. JWTs are often used for authentication and to carry user identity (e.g., sub, role, tenant_id). If a developer directly interpolates these values into raw SQL or into query-building helpers without validation and parameterization, the application remains vulnerable even though the token mechanism itself is sound.

For example, an attacker who obtains or guesses a valid JWT for one user might attempt to escalate by manipulating a query built from token claims. Consider an endpoint that reads user_id from the JWT and uses it in a raw SELECT to fetch records, but also accepts a query parameter like ?team_id= that is concatenated into the SQL string. The JWT confirms identity, but the unchecked team_id input enables classic injection. Similarly, dynamic table or column names based on roles extracted from the token (e.g., switching between user tables or audit schemas) can lead to injection if not strictly enumerated and sanitized.

AdonisJS encourages the use of Lucid ORM with parameterized queries and schema-based validation, which mitigates injection by design. However, when developers bypass Lucid in favor of raw DB queries or use string concatenation for performance tuning, the risk reappears. The JWT context can amplify impact because tokens often carry role-based claims that grant broader data access; an injection flaw in a role-privileged path may expose more data than in an unauthenticated context. Common patterns include constructing WHERE clauses with stringified IDs from the token or using token-supplied sorting/ordering fields directly in orderBy clauses without allowlisting.

Real-world attack patterns mirror general SQL injection (OWASP API Top 10 A03:2023), but the JWT surface introduces subtlety: attackers may probe endpoints with modified tokens containing unexpected or malicious claims, or exploit token parsing differences across libraries. The vulnerability is not in JWT verification, but in subsequent usage of token-derived data. Therefore, even with robust JWT validation, unchecked use of token claims in SQL strings remains a high-severity risk that can lead to data exfiltration, bypass of authorization, or schema manipulation.

Remediation focuses on strict separation of authentication context and query construction, using parameterized queries, allowlists, and input validation on any data derived from the JWT. Below are concrete, syntactically correct examples for AdonisJS.

1. Safe Lucid ORM usage with JWT claims

Use Lucid models and scope queries by the user identity from the token without string interpolation. This ensures parameterization and avoids injection.

import Database from '@ioc:Adonis/Lucid/Database'
import User from 'App/Models/User'

export default class ReportsController {
  public async index({ request, auth }) {
    const user = auth.user! // authenticated by JWT middleware
    // Safe: Lucid uses parameterized queries under the hood
    const reports = await user.related('reports').query()
      .where('status', 'published')
      .exec()
    return reports
  }
}

2. Parameterized raw queries with token-derived IDs

If you must use raw queries, always use named or positional bindings and never concatenate values from the token.

import Database from '@ioc:Adonis/Lucid/Database'
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'

export default class DataController {
  public async show({ request, auth }: HttpContextContract) {
    const user = auth.user!
    const teamId = request.qs().team_id
    // Safe: use bindings instead of string concatenation
    const rows = await Database.from('records')
      .where('user_id', user.id)
      .andWhere('team_id', teamId)
      .select('id', 'name')
    return rows
  }
}

3. Allowlist for dynamic identifiers from JWT claims

When JWT claims influence table or column names (rare), strictly allowlist values and map them before use.

import Database from '@ioc:Adonis/Lucid/Database'
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'

export default class DataController {
  public async export({ request, auth }: HttpContextContract) {
    const user = auth.user!
    const requestedTable = request.qs().table // e.g., 'users' or 'audit_log'
    const allowedTables: string[] = ['users', 'audit_log']
    if (!allowedTables.includes(requestedTable)) {
      throw new Error('Invalid table name')
    }
    // Safe: table name is from allowlist, not directly from token or request
    const rows = await Database.from(requestedTable as 'users' | 'audit_log')
      .where('owner_id', user.id)
      .select('id', 'email')
    return rows
  }
}

4. Validate and sanitize JWT claims before use

Do not trust shape or types from decoded tokens; validate with a schema (e.g., Joi) and coerce types before using in queries.

5. Enforce parameterized sorting and filtering
Do not allow raw column or direction values from JWT claims; map them to safe values.
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'

type SortField = 'created_at' | 'updated_at' | 'name'
const sortMapping: Record = {
  created_at: 'created_at',
  updated_at: 'updated_at',
  name: 'name',
}

export default class SearchController {
  public async list({ request, auth }: HttpContextContract) {
    const user = auth.user!
    const raw = request.qs().sort_by
    const sortBy = sortMapping[raw] || 'created_at'
    return Database.from('items')
      .where('owner_id', user.id)
      .orderBy(sortBy, request.qs().order || 'desc')
      .select('id', 'title')
  }
}