Sql Injection in Feathersjs with Cockroachdb

SQL injection in a FeathersJS application using CockroachDB typically occurs when dynamic values are interpolated directly into SQL strings or passed without proper parameterization. FeathersJS is a framework-agnostic real-time application framework. When you use a SQL transport or a custom Feathers service that builds SQL queries with string concatenation, user-controlled input can modify query structure.

CockroachDB is PostgreSQL-wire compatible, so standard PostgreSQL injection patterns apply. Consider a Feathers service that constructs a query like SELECT * FROM users WHERE id = '${req.query.id}'. An attacker can supply an id such as 1; DROP TABLE users; --, altering the statement’s intent. Because CockroachDB supports multiple statements in some drivers when not parameterized, a malicious payload can execute additional commands if the driver permits it.

Another common vector is unsafe use of knex (a query builder often used with CockroachDB) where column or table names are interpolated directly, since parameterized queries only protect values, not identifiers. For example, knex('users').whereRaw('${columnName} = ?', [input]) can allow injection if columnName is user-controlled. Feathers services that expose query parameters without validation may inadvertently expose these vectors.

Unauthenticated attack surface exposure is heightened when a Feathers service is reachable without requiring an access token and uses SQL transport without strict input validation. The 12 security checks in middleBrick, including Input Validation and Authentication, are designed to detect these classes of issues by testing unauthenticated endpoints for abnormal data handling and authorization bypass risks.

In a real-world scenario, an attacker might probe an endpoint like /users?login=admin%27%20OR%201=1-- to bypass authentication logic. Because CockroachDB adheres to SQL standards, the injection mechanics align with PostgreSQL techniques, such as exploiting string concatenation and comment sequences to terminate intended clauses.

middleBrick scans this combination—FeathersJS routing, service implementations, and CockroachDB interaction—without requiring credentials, reporting findings such as unsafe query construction, missing input sanitization, and authorization flaws. The scanner cross-references the OpenAPI spec with runtime behavior to ensure declared parameters are handled safely, highlighting high-severity items that could lead to data exposure or integrity compromise.

Remediation focuses on avoiding string interpolation for SQL, using parameterized queries, and validating identifiers. Below are concrete code examples for a Feathers service that interacts with CockroachDB via knex.

Safe query with parameterized values

Always use placeholders for values. With CockroachDB and knex, use ? or named bindings depending on the driver. Example using knex with parameterized inputs:

// src/services/users/users.service.js
const { Application } = require('feathersjs');
const knex = require('knex')({
  client: 'postgresql',
  connection: {
    host : 'localhost',
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'mydb',
    port: 26257,
    ssl: {
      rejectUnauthorized: false // adjust as required for your CockroachDB setup
    }
  }
});

const app = new Application();
app.configure(require('feathers-knex')({
  knex,
  id: 'id',
  name: 'users'
}));

// Ensure the service uses safe query methods
app.service('users').hooks({
  before: {
    create: [context => {
      const { email, role } = context.data;
      // Example of safe parameterized usage
      return knex('users')
        .where('email', '=', email)
        .limit(1)
        .then(rows => {
          if (rows.length) {
            throw new Error('Email already exists');
          }
          return context;
        });
    }]
  }
});

// Using knex with raw parameterized queries safely
app.service('users').find({
  query: {
    id: 123
  }
}).then(result => {
  // Safe: id is passed as a parameter, not interpolated
  console.log(result);
});

Safe identifier handling (column/table names)

Never interpolate identifiers. Use a denylist/allowlist or map user input to known values. Example with a controlled mapping:

const allowedColumns = new Set(['id', 'email', 'createdAt']);

app.service('users').find({ // or patch
  query: {
    sort: req.query.sort // e.g., 'email'
  }
});

// In a custom hook or service method:
function sanitizeColumn(input) {
  if (!allowedColumns.has(input)) {
    throw new Error('Invalid column');
  }
  return input;
}

// Usage with knex:
const column = sanitizeColumn(req.query.column);
return knex('users').orderBy(column, req.query.order || 'asc');

Parameterized DDL and migrations

When creating tables via migrations, avoid dynamic SQL with user input. Define schemas explicitly:

// migrations/20250101000000_create_users.js
exports.up = function(knex) {
  return knex.schema.createTable('users', table => {
    table.increments('id').primary();
    table.string('email').notNullable().unique();
    table.string('role').notNullable();
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

These practices reduce the risk of SQL injection when using FeathersJS with CockroachDB. middleBrick’s checks for Input Validation and Property Authorization can validate that such mitigations are in place by analyzing the spec and runtime behavior.