Data Exposure in Express with Cockroachdb

The combination of Express and CockroachDB can inadvertently expose sensitive data when application logic does not enforce proper field-level authorization and query scoping. Data Exposure in this context means that API responses return more data than the requester is permitted to see, including fields such as internal identifiers, email addresses, role flags, or payment information.

With CockroachDB, a distributed SQL database, schema design commonly includes columns intended for internal use—such as created_by, deleted_at, or tenant identifiers—alongside user-facing fields. If route handlers in Express construct queries by selecting all columns (e.g., SELECT *) or by failing to scope rows by tenant or user ownership, these internal columns can be returned to the client. Because CockroachDB preserves column ordering and nullability from the schema, an Express route that maps rows directly to JSON using row[0] or an ORM without explicit field filtering will expose every selected column.

Another vector specific to this stack arises from inconsistent use of parameterized queries and ad-hoc string concatenation. For example, building a WHERE clause by interpolating request parameters can bypass intended filters, causing broader result sets—and consequently more data—to be returned. Even when authentication is enforced, improper authorization checks can allow one user to request another user’s record by manipulating an ID, and CockroachDB will return the full row, including sensitive columns, unless the query explicitly restricts the selected fields.

Middleware that adds user context to requests must ensure that the query builder includes only necessary columns and applies row-level filters. A missing or misconfigured middleware layer can result in an effectively un-scoped SELECT that returns complete table rows. Because CockroachDB supports secondary indexes and distributed joins, an Express route that performs joins across tables may unintentionally surface columns from related tables that were not intended for exposure.

To illustrate, consider an endpoint that lists user profiles. If the handler runs SELECT * FROM profiles and sends the result as JSON, fields such as ssn or internal_notes are included. With CockroachDB, the table schema may also contain system columns or computed columns that are not immediately obvious but are still queryable. Without explicit column lists and strict ownership checks, the API becomes a data exposure channel.

Regular scanning with middleBrick helps detect these patterns by correlating OpenAPI specifications with runtime behavior. The tool identifies whether responses include fields that are not documented or whether query logic lacks proper scoping. By mapping findings to frameworks such as OWASP API Top 10 and SOC2, teams can prioritize remediation for endpoints that expose sensitive columns or fail to enforce field-level authorization.

Remediation focuses on explicit column selection, strict tenant and user scoping, and consistent use of parameterized queries. The following examples demonstrate secure patterns for Express routes interacting with CockroachDB.

1. Explicit column selection and field filtering

Instead of SELECT *, list only the fields required by the response schema. This prevents sensitive columns from being returned inadvertently.

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

app.get('/api/profiles/:userId', async (req, res) => {
  const client = await pool.connect();
  try {
    const result = await client.query(
      'SELECT id, display_name, email, avatar_url, updated_at FROM profiles WHERE id = $1 AND tenant_id = $2',
      [req.params.userId, req.tenant.id]
    );
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Not found' });
    }
    res.json(result.rows[0]);
  } finally {
    client.release();
  }
});

2. Row-level ownership and tenant scoping

Always include tenant and ownership filters in the WHERE clause. Avoid relying on middleware alone to filter results after the query has returned data.

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

3. Parameterized queries to prevent filter bypass

Never concatenate user input into SQL strings. Use parameterized queries to ensure filters are applied correctly and to prevent injection that could widen the result set.

app.get('/api/search', async (req, res) => {
  const client = await pool.connect();
  try {
    const { rows } = await client.query(
      'SELECT id, name, public_description FROM items WHERE category = $1 AND tenant_id = $2 LIMIT $3',
      [req.query.category, req.tenant.id, req.query.limit || 50]
    );
    res.json(rows);
  } finally {
    client.release();
  }
});

4. Avoiding ORM-generated broad selects

If using an ORM, configure it to select only necessary fields or define view models that exclude sensitive columns.

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize(process.env.DATABASE_URL);

const Profile = sequelize.define('Profile', {
  id: { type: DataTypes.INTEGER, primaryKey: true },
  display_name: DataTypes.STRING,
  email: DataTypes.STRING,
  // Intentionally omitting: ssn, internal_notes, role_flags
}, {
  timestamps: true,
  paranoid: true // for soft deletes, maps to deleted_at in CockroachDB
});

app.get('/api/profiles/me', async (req, res) => {
  const profile = await Profile.findOne({
    where: { id: req.user.id },
    attributes: ['id', 'display_name', 'email']
  });
  res.json(profile);
});

5. Consistent middleware for tenant and user context

Ensure that tenant and user identifiers are attached early and used in every query. Do not rely on default database settings or implicit row filtering.

app.use((req, res, next) => {
  // Example: derive tenant from subdomain or auth token
  req.tenant = { id: resolveTenant(req) };
  req.user = authenticate(req);
  next();
});

// Then in routes, always include tenant_id
app.get('/api/messages', async (req, res) => {
  const client = await pool.connect();
  try {
    const messages = await client.query(
      'SELECT id, content, created_at FROM messages WHERE tenant_id = $1 ORDER BY created_at DESC',
      [req.tenant.id]
    );
    res.json(messages.rows);
  } finally {
    client.release();
  }
});

By adopting these patterns—explicit column lists, parameterized queries, and strict row-level scoping—you reduce the risk of data exposure when using Express with CockroachDB. middleBrick can validate that your endpoints adhere to these principles by checking whether documented fields align with returned data and whether query logic reflects required authorization constraints.