Nosql Injection in Express with Cockroachdb

NoSQL injection in an Express application using CockroachDB typically arises when user-controlled input is directly interpolated into queries instead of being handled through parameterized operations. CockroachDB uses a PostgreSQL-wire compatible protocol, and many Node.js drivers rely on pg-style parameterization. However, misuse of query building—such as dynamic key names, unescaped identifiers, or concatenating JSON payloads into SQL strings—can leave injection surfaces.

Consider an endpoint that builds a filter object by merging user input with a base query object. If keys or values are not properly validated, an attacker can inject operators or JSON structures that change the query intent. For example, a login route might accept { email: '[email protected]', password: 'anything' } and build a WHERE clause by iterating keys. If the key is used directly as an identifier via string concatenation, an input like { "email": "[email protected]", "password[operator]=$ne": "" } can bypass authentication by turning the logical condition into something unexpected.

In a typical Express route, this looks like:

app.post('/login', (req, res) => {
  const { email, password } = req.body;
  const query = `SELECT * FROM users WHERE email = '${email}' AND password = '${password}'`;
  client.query(query).then(…);
});

Even if you use an ORM or query builder, injecting raw JSON into a dynamic query can be risky. For instance, with a filter builder that spreads user input into an object and then converts it to a WHERE clause, an attacker might supply:

{ email: '[email protected]', 'password[$ne]': '' }

If the code does not sanitize keys, the resulting SQL might resemble WHERE email = '[email protected]' AND password <> '', which is valid but bypasses the intended check. CockroachDB’s JSONB capabilities and the way drivers serialize objects can amplify this if field names are treated as identifiers rather than placeholders.

Other patterns increase exposure: accepting dynamic sort fields without allowlisting, embedding user input into JSON paths used in WHERE clauses, or constructing LIMIT/OFFSET via string interpolation. Since NoSQL-style logic often lives in JavaScript objects before being translated to SQL, the boundary between data and code blurs. This makes it crucial to treat all user input as untrusted, validate against strict schemas, and use parameterized queries or proper identifier quoting for any dynamic element.

Remediation centers on strict separation of data and code, using parameterization for values and safe handling for identifiers. Never concatenate user input into SQL strings or dynamic query fragments. Instead, rely on placeholders for values and explicit quoting for identifiers.

Safe value parameterization example:

app.post('/login', async (req, res) => {
  const { email, password } = req.body;
  const query = 'SELECT * FROM users WHERE email = $1 AND password = $2';
  const values = [email, password];
  try {
    const result = await client.query(query, values);
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: 'server_error' });
  }
});

When dynamic identifiers are unavoidable (e.g., sorting or selecting columns), use an allowlist and a strict validation function:

const ALLOWED_SORT_FIELDS = new Set(['id', 'email', 'created_at']);

app.get('/users', async (req, res) => {
  const sortField = req.query.sortBy;
  if (!ALLOWED_SORT_FIELDS.has(sortField)) {
    return res.status(400).json({ error: 'invalid_sort_field' });
  }
  // Safe identifier quoting via the driver's built-in identifier quoting
  const query = `SELECT id, email, created_at FROM users ORDER BY ${client.escapeIdentifier(sortField)}`;
  const result = await client.query(query);
  res.json(result.rows);
});

For JSON-based filtering, validate and transform user input into a parameterized query instead of passing raw objects into the SQL string. For example, convert a filter object into a parameterized WHERE clause:

app.post('/search', async (req, res) => {
  const { email, status } = req.body;
  const conditions = [];
  const values = [];
  let idx = 1;
  if (email !== undefined) {
    conditions.push(`email = $${idx}`);
    values.push(email);
    idx += 1;
  }
  if (status !== undefined) {
    conditions.push(`status = $${idx}`);
    values.push(status);
    idx += 1;
  }
  const whereClause = conditions.length ? 'WHERE ' + conditions.join(' AND ') : '';
  const query = `SELECT id, email, status FROM users ${whereClause}`;
  const result = await client.query(query, values);
  res.json(result.rows);
});

Additionally, enforce schema validation on incoming JSON bodies using a library like ajv to reject unexpected keys that could be used for injection:

const Ajv = require('ajv');
const ajv = new Ajv();
const validate = ajv.compile({
  type: 'object',
  required: ['email'],
  properties: {
    email: { type: 'string', format: 'email' },
    password: { type: 'string' }
  },
  additionalProperties: false
});

app.post('/login', (req, res) => {
  if (!validate(req.body)) {
    return res.status(400).json({ error: 'invalid_payload' });
  }
  // proceed with parameterized query as above
});

Finally, review your ORM or query builder’s documentation to ensure it uses parameterization under the hood and avoid methods that allow raw string interpolation. Combine these practices—parameterized values, allowlisted identifiers, strict input validation, and schema enforcement—to mitigate NoSQL injection risks effectively in Express with CockroachDB.