Pii Leakage in Express with Cockroachdb

When an Express application interacts with a CockroachDB cluster, PII leakage commonly arises from a mismatch between application-layer data handling and the database’s exposure of sensitive columns. CockroachDB, like other relational databases, stores data in tables with defined schemas; if columns such as email, phone, or national identifier are present and application routes fail to restrict which fields are returned, the API can unintentionally expose PII in responses.

In a typical Express route, a developer might query the database and serialize the row directly to JSON. For example, a request to fetch user details could return every column, including sensitive ones:

app.get('/users/:id', async (req, res) => {
  const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [req.params.id]);
  res.json(rows[0]);
});

If the users table contains columns like ssn, email, or password_hash, this pattern leaks PII. The risk is compounded when the API is unauthenticated or when access controls are applied at the route level but not at the column level, allowing an attacker to enumerate or harvest sensitive data through endpoints intended for limited information retrieval.

Another common vector is logging or error handling. CockroachDB error messages may include query context or constraint details that reference PII if developers inadvertently log full queries or results. For instance:

try {
  const { rows } = await pool.query('SELECT email FROM accounts WHERE username = $1', [username]);
  res.json({ email: rows[0].email });
} catch (err) {
  console.error(err); // May expose query structure or data
  res.status(500).send('Internal error');
}

In this scenario, a misconfigured logging system or verbose error page could disclose email addresses or other PII. MiddleBrick’s Data Exposure and Property Authorization checks highlight these patterns by correlating OpenAPI specifications with runtime behavior, identifying endpoints that return or process sensitive fields without appropriate constraints.

Additionally, if the Express app uses an ORM or query builder that reflects all columns dynamically, the surface area for leakage grows. Without explicit field selection, any schema change that adds a PII column can immediately expose new data unless the API layer is updated to filter it out. This is where continuous monitoring and spec-driven analysis, as provided by the Pro plan for ongoing scanning and alerting, can detect regressions before they impact users.

Remediation focuses on strict column selection, parameterized queries, and disciplined error handling. Instead of selecting all columns, explicitly request only the fields required by the client. This reduces the attack surface and prevents accidental PII exposure:

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

For operations that need to handle PII internally but not return it, use server-side variables or temporary structures to isolate sensitive values:

app.post('/users', async (req, res) => {
  const { email, phone, ssn } = req.body;
  // Store sensitive fields separately if needed for internal processing
  const { rows } = await pool.query(
    'INSERT INTO users (username, email, phone) VALUES ($1, $2, $3) RETURNING id, username',
    [req.body.username, email, phone]
  );
  // Do not include ssn in the response
  res.status(201).json(rows[0]);
});

When constructing dynamic queries, always use parameterized statements to prevent injection and avoid embedding sensitive values in logs:

const username = req.body.username;
const { rows } = await pool.query(
  'SELECT email FROM accounts WHERE username = $1 LIMIT 1',
  [username]
);
if (rows.length) {
  // Process email securely
}

Configure logging to exclude sensitive fields. For example, sanitize output before writing to console or external systems:

const safeLog = (data) => {
  const { ssn, ...publicData } = data;
  return publicData;
};
app.get('/users/:id', async (req, res) => {
  const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [req.params.id]);
  console.log(safeLog(rows[0]));
  res.json({ id: rows[0].id, username: rows[0].username });
});

These patterns align with Property Authorization and Data Exposure checks in MiddleBrick, ensuring that only intended data flows through the API. For teams using the CLI, running middlebrick scan <url> can validate that endpoints adhere to these principles, while the Dashboard provides historical tracking to confirm that remediation reduces risk over time.