Injection Flaws in Adonisjs with Cockroachdb

AdonisJS is a Node.js web framework that encourages an ORM-based approach to database interaction. When working with CockroachDB, a PostgreSQL-compatible distributed database, developers typically use an ORM or query builder that relies on parameterized queries. Injection flaws arise when dynamic SQL is constructed by concatenating user input directly into queries or raw query fragments, bypassing parameterization. In AdonisJS, this can occur in controller actions that build queries using string interpolation or by passing untrusted input to RawQuery or dialect-specific helpers without sanitization.

Because CockroachDB speaks PostgreSQL wire protocol, many standard PostgreSQL driver behaviors apply, including how prepared statements and placeholders are handled. If AdonisJS code constructs SQL like SELECT * FROM users WHERE email = '${email}', the database receives a literal string that may include injected SQL fragments. An attacker can supply input such as ' OR 1=1 --, altering query logic and potentially exposing authentication bypass paths or enabling data exfiltration. This pattern is relevant to the BOLA/IDOR and Authentication checks in middleBrick’s 12 security checks, where unauthenticated attack surface testing can detect whether endpoints reflect or execute injected payloads.

Another common vector is unsafe usage of the query builder’s where() with raw condition fragments. For example, passing unsanitized column names or table names into dynamic queries can lead to second-order injection if those identifiers are later used in raw SQL. middleBrick’s Input Validation and Property Authorization checks are designed to surface these risks by analyzing the OpenAPI spec and runtime behavior for overly permissive parameter handling. Since CockroachDB is wire-compatible with PostgreSQL, driver-level nuances such as placeholder syntax and escaping rules must be respected consistently across all query-building layers in AdonisJS to avoid bypasses.

SSRF and related infrastructure probing can also intersect with injection when endpoints accept URLs or hostnames that later reach database drivers. Although CockroachDB does not directly interpret HTTP requests, a compromised application layer may use injected SQL to read configuration tables that contain internal hostnames, leading to further internal reconnaissance. middleBrick’s SSRF and Inventory Management checks help identify whether endpoints expose behaviors that could facilitate such chains in a real environment.

Finally, insecure default configurations or missing validation in AdonisJS apps using CockroachDB can allow injection payloads to bypass expected type checks, especially when numeric IDs are cast improperly or when JSON inputs are merged into raw queries. By combining static spec analysis with active testing, middleBrick’s LLM/AI Security and Input Validation modules detect patterns where user-controlled data reaches the database layer without sufficient sanitization, providing prioritized findings and remediation guidance rather than attempting to fix the code automatically.

To prevent injection flaws when using CockroachDB with AdonisJS, always prefer the ORM or query builder’s parameterized methods instead of string concatenation. Use named or indexed placeholders so that user input is never interpreted as SQL code. Below are concrete, working examples that demonstrate safe patterns.

Safe query using Lucid ORM with parameterized where conditions

import User from 'App/Models/User'

// Safe: using parameterized where with user input
const user = await User.query()
  .where('email', request.input('email'))
  .preload('roles')
  .first()

Safe raw query with named placeholders via knex-style bindings

import { RawQuery } from '@ioc:AdonisJS/Lucid/Database'

const email = request.input('email')
const result = await RawQuery.query(
  'SELECT * FROM users WHERE email = $1',
  [email]
)

Dynamic column/table names: validate against an allowlist

const allowedColumns = ['id', 'email', 'created_at']
const column = request.input('sortBy')
const sortOrder = request.input('sortOrder') === 'desc' ? 'DESC' : 'ASC'

if (!allowedColumns.includes(column)) {
  throw new Error('Invalid column')
}

const users = await User.query()
  .orderBy(column, sortOrder)
  .exec()

Using the query builder with complex conditions safely

const username = request.input('username')
const status = request.input('status')

const query = User.query()
  .where((builder) => {
    builder.where('username', username)
    if (status) {
      builder.andWhere('status', status)
    }
  })

const results = await query.exec()

Avoiding injection in raw SQL fragments

Never interpolate values into raw SQL strings. Instead, use bindings:

// Unsafe (do not do this):
// `SELECT * FROM users WHERE id = ${id}`

// Safe:
const id = request.param('id')
const { rows } = await Database.rawQuery('SELECT * FROM users WHERE id = $1', [id])

Validation and sanitization for identifiers

When constructing dynamic queries that reference schemas or tables, validate identifiers against a strict allowlist or regex pattern that matches valid names for CockroachDB. Do not rely on escaping alone.

middleBrick’s CLI tool can be used to scan an endpoint and surface these patterns in reports; the GitHub Action can enforce thresholds so that builds fail if risky query construction is detected in source code reviews. The MCP Server allows AI coding assistants to highlight unsafe queries in real time within your IDE when working on AdonisJS projects targeting CockroachDB.