Data Exposure in Hapi with Cockroachdb

The Data Exposure check in middleBrick examines whether sensitive information is returned to clients when API endpoints are exercised without authentication. When an API built with Hapi uses CockroachDB as its backend, specific patterns in query construction and response formatting can unintentionally expose data. This occurs because dynamic query building may omit row-level conditions, or because developer-facing error messages reveal internal table structures or column names.

In a Hapi route that queries CockroachDB, a common vulnerable pattern is constructing SQL by string concatenation using user-supplied parameters without strict validation. For example, an endpoint intended to fetch a user’s profile might concatenate an id parameter directly into the query. If the developer does not enforce strict type checks or sanitize inputs, an attacker can manipulate the parameter to extract data for other user IDs or even probe schema details through crafted payloads.

CockroachDB’s PostgreSQL wire protocol compatibility means that typical SQL injection behaviors and error messages resemble those seen in PostgreSQL. If a Hapi route does not handle database errors gracefully, stack traces or error objects might be returned to the client. These messages can disclose table names such as users or api_keys, column names like password_hash, or internal details about indexes and constraints, aiding further reconnaissance.

Additionally, pagination and filtering logic can contribute to exposure. If a Hapi route uses CockroachDB LIMIT and OFFSET without validating that the requesting user is authorized to view the entire dataset, an attacker may iterate through offsets to harvest records belonging to other users. This is especially risky when combined with missing or weak authentication controls, allowing enumeration of sensitive records such as email addresses or internal identifiers.

middleBrick’s Data Exposure checks simulate unauthenticated requests to endpoints that interact with CockroachDB, looking for responses that include data not intended for public consumption. It inspects whether responses contain PII such as email addresses, phone numbers, or internal IDs, and whether server-side errors expose stack traces or schema information. The goal is to identify routes where improper query scoping or error handling leads to leakage of sensitive data in production-like scenarios.

To align with compliance frameworks referenced by middleBrick, Data Exposure findings often map to OWASP API Top 10 A01:2023 broken object level authorization and parts of A05:2023 security misconfiguration. In regulated contexts, exposure of personal data through API endpoints may also implicate GDPR or HIPAA considerations, depending on the nature of the data. middleBrick provides prioritized findings with severity ratings and remediation guidance to help developers address these risks before deployment.

Remediation focuses on strict parameter handling, safe query construction, and defensive error handling in Hapi routes that interact with CockroachDB. Use parameterized queries or an ORM/query builder that supports placeholders to avoid injecting uncontrolled values into SQL strings. Validate and sanitize all inputs, and ensure that authorization checks confirm the requesting user is allowed to access the specific resource.

Below are concrete, working examples for Hapi routes using CockroachDB with the @cockroachdb/client driver. These examples demonstrate secure patterns that reduce the risk of data exposure.

1. Parameterized query with strict input validation

Use UUID validation for user identifiers and parameterized SQL to prevent injection and ensure queries only target the intended row.

const Hapi = require('@hapi/hapi');
const { Client } = require('@cockroachdb/client');
const { object, string } = require('@hapi/joi');

const validateUserId = (id) => {
  const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;
  return uuidRegex.test(id);
};

const init = async () => {
  const server = Hapi.server({ port: 4000, host: 'localhost' });

  server.route({
    method: 'GET',
    path: '/users/{{id}}',
    options: {
      validate: {
        params: object({
          id: string().guid()
        })
      },
      handler: async (request, h) => {
        const client = new Client({
          connectionString: 'postgresql://user:password@localhost:26257/defaultdb?sslmode=require'
        });
        await client.connect();

        const userId = request.params.id;
        if (!validateUserId(userId)) {
          throw Boom.badRequest('Invalid user ID');
        }

        const query = 'SELECT id, email, name FROM users WHERE id = $1';
        const values = [userId];
        const result = await client.query(query, values);

        await client.end();

        if (result.rows.length === 0) {
          return h.response({ error: 'Not found' }).code(404);
        }
        // Return only intended public fields; avoid exposing password_hash or internal columns
        return { id: result.rows[0].id, email: result.rows[0].email, name: result.rows[0].name };
      }
    }
  });

  await server.start();
  console.log('Server running on %s', server.info.uri);
};

init().catch(console.error);

2. Server-side pagination with ownership check

When listing resources, enforce ownership or tenant scoping and avoid exposing total counts that can aid enumeration.

server.route({
  method: 'GET',
  path: '/me/items',
  options: {
    handler: async (request, h) => {
      const client = new Client({
        connectionString: 'postgresql://user:password@localhost:26257/defaultdb?sslmode=require'
      });
      await client.connect();

      const userId = request.auth.credentials.userId; // injected by an authentication strategy
      const limit = Math.min(parseInt(request.query.limit) || 10, 100);
      const cursor = request.query.cursor || null;

      let query = 'SELECT id, name, created_at FROM user_items WHERE user_id = $1';
      const values = [userId];

      if (cursor) {
        query += ' AND created_at < $2';
        values.push(new Date(cursor));
      }
      query += ' ORDER BY created_at DESC LIMIT $' + (values.length + 1);
      values.push(limit);

      const result = await client.query(query, values);
      await client.end();

      return {
        data: result.rows,
        nextCursor: result.rows.length === limit ? result.rows[result.rows.length - 1].created_at.toISOString() : null
      };
    }
  }
});

3. Safe error handling to avoid schema leakage

Ensure database errors are logged internally but return generic messages to the caller.

server.ext('onPreResponse', (request, h) => {
  const response = request.response;
  if (response.isBoom && response.output.statusCode >= 500) {
    // Log full error details server-side
    request.logger.error(response.originalError);
    // Return a generic error to avoid exposing stack traces or SQL details
    return h.response({ error: 'Internal server error' }).code(500);
  }
  return h.continue;
});

These patterns reduce the likelihood that responses or errors will expose sensitive data or schema details when Hapi routes interact with CockroachDB. By combining input validation, parameterized queries, ownership checks, and safe error handling, you address the common causes of data exposure identified by middleBrick’s scans.