Pii Leakage in Adonisjs with Cockroachdb

AdonisJS, a Node.js web framework, encourages structured data access through an ORM layer. When paired with CockroachDB, a distributed SQL database, the risk of PII leakage arises primarily from insecure query construction, missing runtime safeguards, and improper handling of sensitive columns. In a black-box scan, middleBrick tests for unauthenticated endpoints that return user data and checks whether responses inadvertently include fields such as email, phone, government identifiers, or internal metadata.

One common pattern is defining models in AdonisJS that map to CockroachDB tables containing columns like email, ssn, or address. If controller methods return the model instance directly—often via Model.query() or implicit serialization—sensitive fields can be serialized into JSON responses. middleBrick’s checks include verifying whether PII appears in outputs that should be minimal, and whether the API exposes data without appropriate access controls.

CockroachDB’s wire protocol and SQL syntax do not introduce unique leakage by themselves, but the way queries are built in AdonisJS can amplify risk. For example, using raw queries without parameterization may lead to unintentional data exposure if responses include more rows or columns than intended. Additionally, if authentication checks are omitted, an unauthenticated attacker can probe endpoints and harvest emails, phone numbers, or other identifiers. middleBrick’s LLM/AI Security checks look for system prompt leakage and output scanning, ensuring that PII does not appear in responses, even when LLM-related features are not explicitly enabled.

Specific OWASP API Top 10 categories relevant here include Broken Object Level Authorization (BOLA) and Security Misconfiguration. A misconfigured route that maps /users/:id to a controller that fetches all user columns from a CockroachDB table is a classic BOLA scenario when the ID is user-supplied and not properly scoped. Input validation failures can also lead to data exfiltration if an attacker manipulates query parameters to request additional fields or export large datasets.

In scanning such an API, middleBrick runs multiple checks in parallel: Authentication, BOLA/IDOR, Input Validation, Data Exposure, and LLM/AI Security. The scanner tests whether endpoints return sensitive fields without authorization and whether responses contain patterns resembling PII, such as email addresses or credit card-like strings. Findings include severity ratings and remediation guidance, emphasizing the need to limit returned fields, enforce authorization, and validate inputs rigorously.

To mitigate PII leakage, implement strict field selection, row-level security, and input validation within AdonisJS controllers and services that interact with CockroachDB. Always return only the data necessary for the client, and avoid exposing raw model instances.

1. Explicit field selection and response shaping

Instead of returning the entire model, project only required fields. This reduces the attack surface and prevents accidental exposure of sensitive columns.

import User from 'App/Models/User'

export default class UsersController {
  public async show({ params, response }: { params: { id: string }, response: any }) {
    const user = await User.query()
      .where('id', params.id)
      .select('id', 'username', 'created_at')
      .firstOrFail()

    return response.json(user)
  }
}

2. Row-level security via scoped queries

Ensure users can only access their own data by scoping queries with the authenticated user’s identifier. This addresses BOLA/IDOR risks when combined with proper authentication middleware.

import Profile from 'App/Models/Profile'

export default class ProfilesController {
  public async index({ auth, response }: { auth: any, response: any }) {
    const profiles = await Profile.query()
      .where('user_id', auth.user?.id)
      .select('id', 'bio', 'department')
      .execute()

    return response.json(profiles)
  }
}

3. Parameterized raw queries with strict column whitelisting

When using raw SQL against CockroachDB, use parameterized bindings and explicitly list columns. Avoid string interpolation to prevent injection and data leakage.

import { DatabaseQueryBuilder } from '@ioc:Adonis/Lucid/Database'

export async function fetchPublicUserStats(db: DatabaseQueryBuilder, tenantId: string) {
  return await db
    .from('users')
    .where('tenant_id', tenantId)
    .select('id', 'username', 'status')
    .limit(100)
}

4. Input validation and sanitization

Validate and sanitize all incoming parameters. This prevents attackers from manipulating query parameters to request broader data sets or trigger errors that reveal schema details.

import schema from '@ioc:Adonis/Core/Validator'

export const userQuerySchema = schema.create({
  id: schema.string({ trim: true, escape: true }),
})

5. Environment-aware configuration

Ensure that sensitive configurations for CockroachDB connections do not expose credentials in logs or error messages. Use environment variables and avoid committing connection strings to version control.

By combining these practices—explicit column selection, scoped queries, parameterized raw SQL, strict validation, and secure configuration—you reduce the likelihood of PII leakage when using AdonisJS with CockroachDB. middleBrick’s scans can validate these controls by checking for unauthorized data exposure and verifying that responses conform to least-privilege data principles.