Bola Idor in Express with Cockroachdb

Broken Object Level Authorization (BOLA) occurs when an API exposes object references (IDs) in URLs or parameters and fails to enforce that the requesting user is authorized to access that specific resource. In Express applications using CockroachDB, BOLA typically arises from routes like /users/:userId or /accounts/:accountId where the server directly uses the user-supplied ID in a SQL query without verifying ownership or permissions.

When CockroachDB is the backend, developers often write queries that filter by a primary key or tenant ID extracted from the request. If the route parameter (e.g., req.params.accountId) is used to build a query without confirming that the authenticated subject has access to that account, an attacker can change the ID to enumerate or manipulate other users’ data. For example, an authenticated user modifying /api/accounts/123 to /api/accounts/124 might be able to read or update account 124 if the server does not check that account 124 belongs to the authenticated user or that the user has explicit permission.

Because CockroachDB supports distributed SQL and secondary indexes, queries that filter on tenant or ownership columns remain efficient, but this does not remove the need for explicit authorization checks. A typical vulnerable Express route might look like:

app.get('/api/accounts/:accountId', async (req, res) => {
  const { accountId } = req.params;
  const user = req.user; // authenticated user from session/JWT
  const result = await pool.query('SELECT * FROM accounts WHERE id = $1', [accountId]);
  res.json(result.rows);
});

Here, the route trusts accountId without verifying that user has access to that account. An attacker authenticated with their own account can iterate numeric or UUID identifiers to access other accounts, leading to mass assignment or data exfiltration. This pattern is common across CRUD APIs and is not specific to CockroachDB, but CockroachDB’s strong consistency and SQL interface can make unauthorized reads or writes appear successful if authorization is omitted.

In more complex scenarios, BOLA can intersect with business logic that involves joins across tables (e.g., users, organizations, permissions). If the server constructs queries using multiple IDs (organizationId, resourceId) and only validates one, the attack surface expands. CockroachDB’s support for foreign keys and referential integrity does not prevent BOLA; it only ensures that referenced rows exist. Authorization must be enforced at the application layer, typically by including the user’s tenant or ownership context in every query.

Logging and monitoring practices can inadvertently aid attackers in BOLA scenarios if error messages reveal whether an ID exists. In CockroachDB, errors like ‘pq: row not found’ versus ‘permission denied’ can give clues. Consistent error handling and rate limiting help reduce information leakage, but the core mitigation is to ensure every data access validates the subject against the requested resource.

To prevent BOLA in Express with CockroachDB, always include the authenticated subject’s context in SQL filters and avoid relying solely on route parameters for authorization. Below are concrete patterns and code examples that you can adopt.

1. Include user context in WHERE clauses

Ensure every query that accesses a user- or tenant-specific resource includes the user identifier (or a derived scope) as a condition. For example, if each account row has an owner_id, use both the route parameter and the authenticated user ID:

app.get('/api/accounts/:accountId', async (req, res) => {
  const { accountId } = req.params;
  const user = req.user;
  const result = await pool.query(
    'SELECT * FROM accounts WHERE id = $1 AND owner_id = $2',
    [accountId, user.id]
  );
  if (result.rows.length === 0) {
    return res.status(404).json({ error: 'Not found' });
  }
  res.json(result.rows[0]);
});

This guarantees that even if an attacker changes accountId, they cannot access accounts where owner_id does not match their user ID.

2. Map routes to scoped identifiers, not raw database keys

Instead of exposing CockroachDB primary keys directly, use scoped identifiers (e.g., organizationId_resourceId) and validate mapping in the database. For example:

app.get('/api/orgs/:orgId/projects/:projectId', async (req, res) => {
  const { orgId, projectId } = req.params;
  const user = req.user;
  const result = await pool.query(
    `SELECT p.* FROM projects p
     JOIN organizations o ON p.org_id = o.id
     WHERE o.id = $1 AND p.id = $2 AND o.owner_id = $3`,
    [orgId, projectId, user.id]
  );
  if (result.rows.length === 0) {
    return res.status(403).json({ error: 'Forbidden' });
  }
  res.json(result.rows[0]);
});

This approach uses joins to enforce that the project belongs to the organization and that the user is allowed to access the organization.

3. Use parameterized queries and avoid string concatenation

Always use parameterized queries with CockroachDB to prevent SQL injection, which can compound BOLA. Never build SQL strings by interpolating req.params.

// Safe
const result = await pool.query('SELECT * FROM documents WHERE id = $1 AND user_id = $2', [
  documentId,
  userId
]);

// Avoid
const result = await pool.query(`SELECT * FROM documents WHERE id = ${documentId}`);

4. Enforce ownership checks in mutations

For POST, PUT, and DELETE, re-validate ownership before performing the operation. For instance, when updating an account balance:

app.put('/api/accounts/:accountId', async (req, res) => {
  const { accountId } = req.params;
  const user = req.user;
  const { amount } = req.body;

  // Verify ownership first
  const check = await pool.query('SELECT balance FROM accounts WHERE id = $1 AND owner_id = $2', [
    accountId,
    user.id
  ]);
  if (check.rows.length === 0) {
    return res.status(403).json({ error: 'Forbidden' });
  }

  // Proceed with update within a transaction if needed
  const result = await pool.query(
    'UPDATE accounts SET balance = balance + $1 WHERE id = $2 RETURNING *',
    [amount, accountId]
  );
  res.json(result.rows[0]);
});

5. Consistent error handling

Return uniform error messages (e.g., ‘Not found’ or ‘Forbidden’) regardless of whether the record exists, to reduce information leakage that could assist attackers in mapping valid IDs.

6. Leverage middleware for scope validation

Use middleware to attach user-scoped data to the request and centralize checks. For example:

function scopeByAccount(req, res, next) {
  const { accountId } = req.params;
  const user = req.user;
  // Attach scoped account if user has access
  pool.query('SELECT * FROM accounts WHERE id = $1 AND owner_id = $2', [accountId, user.id])
    .then(result => {
      if (result.rows.length === 0) {
        return res.status(403).json({ error: 'Forbidden' });
      }
      req.account = result.rows[0];
      next();
    })
    .catch(err => next(err));
}

app.get('/api/accounts/:accountId', scopeByAccount, (req, res) => {
  res.json(req.account);
});

These practices ensure that BOLA risks are mitigated specifically for Express services backed by CockroachDB, combining SQL safety with robust authorization logic.