Formula Injection in Adonisjs with Cockroachdb

Formula Injection occurs when untrusted data is concatenated into executable formulas or query expressions without validation or escaping. In AdonisJS, this commonly arises when building SQL queries dynamically using raw query building methods or when interpolating user input into ORM query scopes. CockroachDB, while wire-compatible with PostgreSQL, introduces nuances in how it parses and executes SQL that can affect injection surface areas. Because AdonisJS applications often use the @adonisjs/lucid ORM, dynamic query construction patterns—such as Book.query().whereRaw() or Model.query().withSchema()—can inadvertently incorporate unsanitized inputs into WHERE clauses, ORDER BY, or LIMIT/OFFSET expressions.

When user-controlled data is embedded directly into SQL fragments, an attacker can manipulate the logical structure of the query. For example, a search filter like filter[title]=*" OR "1"="1 could alter predicate logic if passed into a whereRaw call without parameterization. CockroachDB’s strict SQL grammar and support for advanced features such as array and JSON operators do not inherently prevent injection; they merely define the syntax an attacker might exploit. If AdonisJS code builds queries by string concatenation rather than using bound parameters or schema-based query builders, an attacker can inject additional SQL fragments, including subqueries or malicious comment terminators like /* to truncate intended logic.

Another dimension specific to the AdonisJS + CockroachDB combination is the use of schema-qualified identifiers and case-sensitive object names. If user input influences schema or table names in raw queries, and those names are not validated against an allowlist, an attacker can pivot across schemas or access unintended tables. CockroachDB’s distributed SQL architecture does not change this risk; it only means that injection attempts will be processed across distributed nodes consistently. Without proper input validation or strict identifier quoting, an application might expose metadata or sensitive rows by dynamically switching contexts based on injected identifiers.

Furthermore, AdonisJS middleware and route handlers may construct dynamic queries for reporting or multi-tenant scenarios where tenant identifiers are embedded in SQL. If these identifiers originate from request parameters and are inserted via string interpolation into CockroachDB queries, an attacker can perform privilege escalation or data exfiltration across tenant boundaries. Even when using parameterized queries for values, failing to parameterize identifiers or table names leaves a path for Formula Injection. The key takeaway is that the ORM’s convenience methods must be used consistently, and raw SQL should be limited to parameterized forms to mitigate this class of vulnerability in the AdonisJS and CockroachDB stack.

Remediation centers on strict separation of SQL logic and data, using AdonisJS’s built-in query parameterization and schema validation. Always prefer the query builder’s parameterized methods over raw string concatenation. For CockroachDB, ensure that identifiers are validated against an allowlist and, when necessary, quoted using Knex’s identifier formatting utilities, since AdonisJS uses Knex under the hood for SQL generation.

Parameterized queries with whereRaw

// Unsafe
const results = await Book.query()
  .whereRaw(`title = '${userInput}'`)
  .exec()

// Safe: use bindings
const results = await Book.query()
  .whereRaw('title = ?', [userInput])
  .exec()

Dynamic schema/table validation

Never directly interpolate user input into schema or table names. Instead, validate against a predefined set of allowed values:

const allowedSchemas = ['public', 'tenant_a', 'tenant_b']
function buildQuery(userSchema, filter) {
  if (!allowedSchemas.includes(userSchema)) {
    throw new Error('Invalid schema')
  }
  // Knex identifier quoting via raw query with bindings is not applicable for identifiers,
  // so we use a whitelist approach.
  return Book.query().from(`${userSchema}.books`).where('status', filter.status)
}

Using Lucid ORM with parameterized scopes

Define reusable query scopes that avoid raw SQL entirely:

// In a model file
static scopeByStatus(query, status) {
  return query.where('status', status)
}

// In a controller
const books = await Book.query()
  .scope('scopeByStatus', 'published')
  .exec()

Handling JSON and array operators safely

CockroachDB supports JSON and array operators that can be misused if user input is embedded directly. Use parameterized conditions and avoid building JSON path expressions from raw input:

// Unsafe
const data = await Book.query()
  .whereRaw("metadata @> '{\"category\": \"' + userCategory + '\"}'")
  .exec()

// Safe: use parameterized JSON construction via Knex or validate/sanitize input
const safeCategory = sanitizeCategory(userInput)
const data = await Book.query()
  .where('metadata', '@>', { category: safeCategory })
  .exec()

Leveraging middleware for input normalization

Apply schema validation and trimming in AdonisJS middleware to ensure only expected formats reach the query layer:

// start/hooks.ts or middleware
import { schema } from '@ioc:Adonis/Core/Validator'

const filterSchema = schema.create({
  title: schema.string.optional(),
  status: schema.enum(['published', 'draft', 'archived'])
})

export const sanitizeFilter = async (ctx) => {
  const validated = await validator.validate({ schema: filterSchema, data: ctx.request.qs() })
  ctx.request.merge({ filter: validated })
}