Sql Injection in Adonisjs

Sql Injection in Adonisjs applications typically occurs when user input is concatenated directly into SQL queries without proper sanitization. Adonisjs, built on Node.js and using the Lucid ORM by default, provides several query-building mechanisms that can be vulnerable if misused.

The most common Adonisjs-specific vulnerability arises from raw query execution. When developers use Database.raw() or Database.query() methods with string interpolation, they bypass the ORM's built-in protections. For example:

const { username, limit } = request.all()
const users = await Database
  .raw(`SELECT * FROM users WHERE username = '${username}' LIMIT ${limit}`)
  .then((response) => response.rows)

This code is vulnerable because an attacker could supply username as admin' OR '1'='1 and limit as 1; DROP TABLE users; --, resulting in destructive SQL execution.

Another Adonisjs-specific pattern involves Lucid model queries where developers manually construct where clauses. While Lucid provides query bindings, developers sometimes concatenate values directly:

const { id } = request.all()
const user = await User
  .query() 
  .whereRaw(`id = ${id}`)
  .first()

The whereRaw method accepts unescaped input, making it dangerous when values come from user input. This is particularly problematic in Adonisjs because the framework's convention-over-configuration approach encourages rapid development, sometimes at the expense of security review.

Stored procedure calls in Adonisjs can also be vulnerable. When using Database.raw() to call procedures with user input:

const { userId } = request.all()
const result = await Database
  .raw(`CALL get_user_data(${userId})`)

Without proper parameter binding, this becomes a SQL injection vector specific to Adonisjs applications that rely on database procedures.

Detecting SQL injection in Adonisjs applications requires both static analysis and runtime scanning. Static analysis tools can identify dangerous patterns like whereRaw, raw, and string interpolation in database queries. However, runtime detection is crucial for identifying actual vulnerabilities.

middleBrick's API security scanner specifically detects SQL injection vulnerabilities in Adonisjs applications through black-box scanning. The scanner tests for injection points by sending specially crafted payloads to API endpoints and analyzing responses for SQL error patterns, unexpected data exposure, or timing differences.

For Adonisjs applications, middleBrick examines:

• Query parameter injection attempts on routes that accept database identifiers
• POST body injection through JSON payloads targeting ORM methods
• Header injection where database queries might use request headers
• Authentication bypass attempts through SQL manipulation
• Time-based blind SQL injection detection through response timing analysis

The scanner's SQL injection module tests 15 different payload patterns specifically designed to trigger database errors or information disclosure. For Adonisjs applications using PostgreSQL, MySQL, or SQLite, middleBrick adapts its payloads to match the underlying database syntax.

middleBrick also analyzes OpenAPI specifications for Adonisjs applications. If your Adonisjs app exposes a Swagger/OpenAPI spec, middleBrick cross-references the documented parameters with its runtime findings, identifying discrepancies between documented and actual behavior that might indicate security issues.

The scanner provides Adonisjs-specific remediation guidance, including code examples showing how to refactor vulnerable queries using Adonisjs's safe query-building features. For instance, it might suggest replacing:

// Vulnerable
const users = await Database
  .raw(`SELECT * FROM users WHERE email = '${email}'`)

With the secure equivalent:

// Secure
const users = await Database
  .raw('SELECT * FROM users WHERE email = ?', [email])

Remediating SQL injection in Adonisjs requires understanding the framework's query-building mechanisms and consistently using parameterized queries. The most effective approach is to leverage Lucid ORM's built-in protections rather than raw SQL execution.

For basic queries, use Lucid's query builder with parameter binding:

const { username } = request.all()
const user = await User
  .query() 
  .where('username', username) // Safe - uses parameter binding
  .first()

This approach automatically escapes input and prevents SQL injection. The same pattern works for whereIn, whereBetween, and other query builder methods.

When you must use raw queries, always use parameter binding instead of string concatenation:

const { status, limit } = request.all()
const orders = await Database
  .raw('SELECT * FROM orders WHERE status = ? LIMIT ?', [status, limit])
  .then((response) => response.rows)

The ? placeholders are replaced with properly escaped values, eliminating injection risks.

For Adonisjs applications using complex queries, the QueryBuilder provides safe dynamic query construction:

const query = User.query()
if (request.input('active')) {
  query.where('active', true)
}
if (request.input('role')) {
  query.where('role', request.input('role'))
}
const users = await query

This pattern allows conditional query building without ever concatenating user input into SQL strings.

Adonisjs also provides the whereRaw method with parameter support:

const { id } = request.all()
const user = await User
  .query()
  .whereRaw('id = ?', [id]) // Safe with parameter binding
  .first()

Never use whereRaw with string interpolation or without parameter binding.

For stored procedures, use parameter binding or the Database transaction methods:

const { userId } = request.all()
const result = await Database
  .raw('CALL get_user_data(?)', [userId])

Additionally, implement input validation using Adonisjs's schema validation to ensure parameters match expected formats before they reach database queries:

const UserSchema = schema.create({
  id: schema.number(),
  status: schema.enum(['active', 'inactive', 'pending'])
})

const payload = await request.validate({
  schema: UserSchema
})

This validation layer provides defense-in-depth, catching malformed input before it can be used in SQL queries.