Insecure Direct Object Reference in Express with Cockroachdb

Insecure Direct Object Reference (IDOR) occurs when an API exposes internal object references (such as database primary keys) without sufficient authorization checks, allowing one user to access or modify data belonging to another. When Express routes use CockroachDB as the backend without validating that the authenticated subject has permission to access the targeted resource, the API becomes vulnerable.

Consider an endpoint /api/users/:userId/profile in Express that reads a user profile from CockroachDB using the userId from the URL. If the route only checks that a user is authenticated and directly interpolates req.params.userId into a SQL query, there is no ownership or tenant check. An attacker who is authenticated as User A can change userId to User B’s known ID and retrieve or manipulate another user’s data. Because CockroachDB is strongly consistent and exposes row-level information in query patterns, missing authorization at the route layer translates directly into data exposure.

In a typical Express + CockroachDB setup, developers might use an ORM or a query builder and write code such as:

app.get('/api/users/:userId/profile', async (req, res) => {
  const { userId } = req.params;
  const result = await db.query('SELECT id, email, display_name FROM users WHERE id = $1', [userId]);
  res.json(result.rows[0]);
});

This code trusts userId from the client and does not verify that the authenticated user is allowed to view that specific row. In a multi-tenant scenario, an attacker can enumerate IDs sequentially or use known IDs to perform BOLA/IDOR attacks. CockroachDB’s SQL interface does not enforce application-level permissions; therefore, authorization must be implemented in Express. Without it, the database simply returns data when the row exists, and the API returns it with a 200 status, completing the IDOR chain.

Moreover, IDOR can combine with other checks in middleBrick’s scan, such as Authentication and Property Authorization, to surface broader authorization gaps. The scanner tests unauthenticated and authenticated contexts to confirm whether access controls consistently enforce permissions across endpoints that reference database objects by ID.

Remediation requires enforcing ownership or tenant checks in Express before querying CockroachDB. Always resolve the authenticated subject (e.g., from session or JWT) and compare it with the resource owner or apply row-level security policies. Below are concrete, safe patterns.

1) Enforce ownership with a parameterized query

Ensure the authenticated user’s ID is compared with the resource’s owner ID in the SQL statement.

app.get('/api/users/:userId/profile', async (req, res) => {
  const { userId } = req.params;
  const authenticatedUserId = req.user.id; // resolved from auth token/session

  if (userId !== authenticatedUserId) {
    return res.status(403).json({ error: 'Forbidden' });
  }

  const result = await db.query(
    'SELECT id, email, display_name FROM users WHERE id = $1 AND owner_id = $2',
    [userId, authenticatedUserId]
  );

  if (result.rows.length === 0) {
    return res.status(404).json({ error: 'Not found' });
  }
  res.json(result.rows[0]);
});

2) Use tenant scoping for multi-tenant apps

In a tenant-aware schema, include the tenant identifier in the WHERE clause.

app.get('/api/tenant/:tenantId/resources/:resourceId', async (req, res) => {
  const { tenantId, resourceId } = req.params;
  const authenticatedTenantId = req.user.tenantId;

  if (tenantId !== authenticatedTenantId || authenticatedTenantId !== 'expected-uuid') {
    return res.status(403).json({ error: 'Forbidden' });
  }

  const result = await db.query(
    'SELECT id, name, data FROM resources WHERE id = $1 AND tenant_id = $2',
    [resourceId, tenantId]
  );

  if (result.rows.length === 0) {
    return res.status(404).json({ error: 'Not found' });
  }
  res.json(result.rows[0]);
});

3) Apply row-level security (RLS) in CockroachDB and still enforce in Express

RLS in CockroachDB can complement application checks. Define a policy that allows users to see or modify only their own rows, and keep the Express ownership check for defense in depth.

-- CockroachDB DDL example (run separately)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_policy ON users FOR ALL TO authenticated_user
  USING (id = auth.uid());

Even with RLS, keep authorization in Express to ensure correct error handling and to avoid exposing whether rows exist. Combine with parameterized queries to prevent SQL injection:

app.get('/api/users/:userId', async (req, res) => {
  const { userId } = req.params;
  const result = await db.query('SELECT id, email FROM users WHERE id = $1', [userId]);
  if (result.rows.length === 0) {
    return res.status(404).json({ error: 'Not found' });
  }
  res.json(result.rows[0]);
});

4) Validate and sanitize input

Ensure userId conforms to expected format to avoid injection or malformed queries.

const { userId } = req.params;
if (!/^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[1-5][0-9a-fA-F]{3}-[89abAB][0-9a-fA-F]{3}-[0-9a-fA-F]{12}$/.test(userId)) {
  return res.status(400).json({ error: 'Invalid user ID format' });
}

5) Use middleware for centralized checks

Extract ownership logic into reusable middleware to avoid mistakes across routes.

function ensureResourceOwner(req, res, next) {
  const resourceUserId = req.params.userId;
  if (resourceUserId !== req.user.id) {
    return res.status(403).json({ error: 'Forbidden' });
  }
  next();
}
app.get('/api/users/:userId/profile', ensureResourceOwner, async (req, res) => {
  const result = await db.query('SELECT id, email FROM users WHERE id = $1', [req.params.userId]);
  res.json(result.rows[0]);
});