Sql Injection in Adonisjs with Cockroachdb

SQL Injection remains a critical risk in web frameworks when developer-written query strings are concatenated with external input. In AdonisJS, which supports multiple SQL clients including CockroachDB, the danger arises when raw queries or dynamic query-building bypass the ORM’s safeguards. CockroachDB, while wire-protocol compatible with PostgreSQL, introduces subtle behavioral differences that can affect how parameterized queries are handled when misused.

Consider the following unsafe pattern in an AdonisJS controller:

const { request } = use('request')
const userQuery = `SELECT * FROM users WHERE email = '${request.input('email')}'`
const results = await Database.rawQuery(userQuery)

Here, user input is directly interpolated into the SQL string. Even though CockroachDB supports prepared statements, the raw query bypasses them. An attacker can supply ' OR '1'='1 as the email, potentially returning all user rows. AdonisJS also allows query bindings with knex, but if developers use string templates with Database.query() or raw SQL without bindings, the protection is lost.

Another common vector involves dynamic column or table names. For example:

const sortColumn = request.input('sort')
const rows = await Database.from('users').orderBy(`${sortColumn}`, 'asc')

Identifier interpolation like this cannot be parameterized by CockroachDB or any SQL engine. If sortColumn is supplied as email; DROP TABLE users --, the resulting statement may execute unintended operations even though CockroachDB uses a PostgreSQL-wire compatible protocol and does not support some multi-statement behaviors over the wire in the same way.

The LLM/AI Security checks in middleBrick specifically test for such injection risks by probing endpoints that accept dynamic query fragments and inspecting outputs for data leakage or error messages that indicate successful injection. This is important because AdonisJS applications often expose REST endpoints that directly map to database operations, and CockroachDB’s strict SQL compliance does not prevent logic flaws introduced by unsafe string assembly.

To eliminate SQL Injection risks in AdonisJS with CockroachDB, always use parameterized queries or the query builder’s binding mechanisms. Never concatenate user input into SQL strings, even if the database is tolerant of certain syntax.

Use Database.rawQuery with bindings

The safest way to run dynamic SQL is to supply parameters separately:

const email = request.input('email')
const results = await Database.rawQuery('SELECT * FROM users WHERE email = $1', [email])

CockroachDB uses $1, $2 placeholders (PostgreSQL-style). This ensures the input is treated strictly as a value, not executable SQL.

Use AdonisJS Lucid ORM with parameterized conditions

When using Lucid models, avoid merge with raw strings and rely on object filters:

const users = await User.query().where('email', email).limit(1).first()

For complex conditions, use parameterized where objects:

const users = await User.query()
  .whereRaw('created_at > NOW() - INTERVAL \'7 days\'') // safe because interval is fixed
  .andWhere(function () {
    this.where('status', 'active').orWhereIn('role', ['admin', 'moderator'])
  })

Safely handle dynamic identifiers with whitelisting

For column or table names, validate against a strict allowlist:

const allowedColumns = ['id', 'email', 'created_at', 'updated_at']
const sortColumn = request.input('sort')
if (!allowedColumns.includes(sortColumn)) {
  throw new Error('Invalid sort column')
}
const rows = await Database.from('users').orderBy(sortColumn, 'asc')

This prevents injection through identifiers while still allowing flexible sorting. Note that CockroachDB does not support parameterizing identifiers, so validation is mandatory.

Leverage middleware and validation schemas

Use AdonisJS schema validation to enforce types and patterns before data reaches the database layer:

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

const emailSchema = schema.create({
  email: schema.string({ trim: true, normalize: true }, [rules.email()])
})

export const validator = Validator.createCompiler(emailSchema)

With this, invalid email formats are rejected early, reducing the chance of malformed queries being constructed. middleBrick’s Input Validation checks complement this by testing for edge cases that may bypass client-side rules.

Finally, middleBrick’s OpenAPI/Swagger spec analysis helps by cross-referencing spec definitions with runtime findings. If your API spec defines strict parameter types but runtime behavior deviates, middleBrick highlights the mismatch, prompting safer implementation aligned with CockroachDB’s expectations.