Formula Injection in Express with Cockroachdb

Formula Injection (a subset of Injection) occurs when untrusted data is concatenated into a query, formula, or command interpreted by the database. With Express and CockroachDB, this typically manifests through dynamic SQL built via string concatenation or improper use of query builders. CockroachDB, while PostgreSQL-wire compatible, still interprets SQL the same way: if user input is not parameterized, an attacker can alter query logic.

Express applications often construct SQL strings for CockroachDB using template literals or string concatenation. For example, concatenating an id parameter directly into a SQL string allows an attacker to change the intended operation or extract data:

const userId = req.query.id; // attacker-controlled
const sql = `SELECT * FROM accounts WHERE id = ${userId}`;
client.query(sql, (err, res) => { ... });

An input like 1 OR 1=1 returns all rows. More advanced formula injection can manipulate computed columns, CASE expressions, or even execute administrative commands if the SQL string includes semicolons and additional statements. Because CockroachDB supports standard PostgreSQL syntax, attackers can use UNION-based extraction, tautologies, or nested subqueries to bypass intended filters.

Additionally, dynamic ORDER BY or dynamic table/column names built via concatenation expose schema information and enable second-order injection if values are stored and later executed. Even when using an ORM or query builder, if raw strings are interpolated, the protection is lost. The risk is elevated when combined with weak input validation and missing output encoding, allowing exfiltration of session tokens or PII stored in CockroachDB tables.

Formula Injection maps directly to OWASP API Top 10 A03:2023 — Injection, and can violate compliance requirements such as PCI-DSS and GDPR when sensitive data is exposed. In a black-box scan, middleBrick tests for these patterns by submitting payloads designed to change query semantics and observing deviations in response size, status codes, and timing, then maps findings to the relevant framework.

Remediation centers on strict parameterization and avoiding dynamic SQL construction. Always use prepared statements or query bindings supported by the CockroachDB driver for Node.js. Do not concatenate user input into SQL strings, even for dynamic identifiers; if dynamic identifiers are unavoidable, use an allowlist and strict validation.

Below are concrete, safe examples for Express with CockroachDB.

Parameterized query with placeholders

Use $1, $2 style placeholders (positional) with the query method:

const express = require('express');
const { Client } = require('pg'); // CockroachDB wire compatible
const app = express();
const client = new Client({ connectionString: process.env.DATABASE_URL });

app.get('/account', async (req, res) => {
  const userId = req.query.id;
  try {
    const result = await client.query('SELECT * FROM accounts WHERE id = $1', [userId]);
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: 'Database error' });
  }
});
app.listen(3000);

Using a query builder with parameterization

Libraries like `pg` enforce safe parameterization. Avoid building raw strings in the builder; rely on its binding methods:

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

app.get('/search', async (req, res) => {
  const { name, minBalance } = req.query;
  const sql = 'SELECT * FROM profiles WHERE name = $1 AND balance >= $2';
  const values = [name, parseFloat(minBalance)];
  const result = await pool.query(sql, values);
  res.json(result.rows);
});

Dynamic identifiers: use allowlist validation

If you must order by a user-provided field, validate against a strict allowlist instead of concatenation:

const allowedColumns = new Set(['created_at', 'updated_at', 'email']);
app.get('/users', async (req, res) => {
  const sortColumn = req.query.sort;
  if (!allowedColumns.has(sortColumn)) {
    return res.status(400).json({ error: 'Invalid sort column' });
  }
  const sql = `SELECT id, email FROM users ORDER BY ${sortColumn}`; // safe: validated
  const result = await client.query(sql);
  res.json(result.rows);
});

Prepared statements for repeated queries

Prepare statements once and reuse them for efficiency and safety:

await client.connect();
const selectById = await client.prepare('select_by_id', 'SELECT * FROM accounts WHERE id = $1');
app.get('/account/prepared', async (req, res) => {
  const result = await client.query(selectById, [req.query.id]);
  res.json(result.rows);
});

Additional guidance: enforce strict input validation (e.g., using an allowlist for IDs, numeric checks), employ least-privilege database roles, and avoid exposing raw errors to clients. middleBrick can detect Formula Injection patterns by submitting crafted payloads and analyzing response deviations; findings include severity, affected parameters, and remediation guidance mapped to OWASP API Top 10.