Type Confusion in Express with Cockroachdb

Type confusion in an Express application using CockroachDB typically arises when data of one type (e.g., a numeric identifier, a JSON field, or a timestamp) is interpreted as another type during request handling or query construction. Because CockroachDB speaks PostgreSQL wire protocol, drivers often accept a wide range of parameter types, and an Express route that does not enforce strict type checks before building queries can lead to ambiguous or incorrect type interpretation at the database layer.

Consider an endpoint that accepts an id parameter and uses it in a SQL condition without validation or parameterized casting. If the driver receives a string where a UUID or integer is expected, CockroachDB may perform implicit type coercion or treat the value as a string literal. This can bypass intended access controls or cause the query to match unintended rows, enabling IDOR or privilege escalation. For example, a route written as SELECT * FROM accounts WHERE id = $1 with req.params.id passed directly can behave differently depending on whether the value is a numeric string, a UUID string, or a JSON-like string, especially when the ORM or query builder does not enforce schema-level types.

Another common scenario involves JSON columns. If an Express route merges user-provided objects into a CockroachDB JSONB column without schema validation, an attacker can supply values that change the interpreted structure of the JSON (e.g., replacing a boolean with a string or an array), leading to type confusion in downstream application logic or in queries that rely on specific JSON paths. Because CockroachDB supports powerful JSON operators, an attacker may be able to inject expressions or exploit type-dependent behavior in indexes and constraints.

Middleware that normalizes inputs (e.g., converting strings to numbers or dates) can inadvertently introduce confusion when the conversion is lossy or context-dependent. For instance, a route that casts req.query.offset to an integer using Number() may produce NaN for malformed input, and if that value is forwarded to CockroachDB, the query may behave unexpectedly or expose different execution paths. Similarly, date strings that fail to parse consistently can lead to comparisons that mix types in the database engine, effectively creating a confusion between string, timestamp, and numeric representations.

The combination of Express’s flexible parameter handling, dynamic JavaScript types, and CockroachDB’s PostgreSQL compatibility means that type confusion often surfaces at the boundary between untrusted request data and typed SQL execution. Because the scan testing performed by tools like middleBrick evaluates unauthenticated attack surfaces and includes checks for Input Validation and Property Authorization, such misconfigurations can be detected as high-severity findings that require explicit type enforcement and strict schema validation.

To prevent type confusion when using CockroachDB in Express, enforce strict input validation, explicit casting, and parameterized queries. Never concatenate user input into SQL strings, and avoid relying on implicit type conversions provided by the driver or ORM.

1. Use parameterized queries with explicit types

Always use prepared statements or query builders that support typed parameters. For CockroachDB, the pg driver works well with Express when you explicitly bind parameters and avoid dynamic SQL.

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

app.get('/account/:id', async (req, res) => {
  const id = req.params.id;
  // Validate id format before using it
  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(id)) {
    return res.status(400).json({ error: 'Invalid UUID format' });
  }
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT id, name, metadata FROM accounts WHERE id = $1::uuid', [id]);
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Not found' });
    }
    res.json(result.rows[0]);
  } finally {
    client.release();
  }
});

2. Validate and cast numeric and JSON inputs explicitly

For numeric IDs or JSON payloads, validate and cast on the server side, and use CockroachDB’s type-specific operators to ensure consistent interpretation.

app.post('/users/:userId/preferences', express.json(), async (req, res) => {
  const userId = parseInt(req.params.userId, 10);
  if (Number.isNaN(userId)) {
    return res.status(400).json({ error: 'Invalid user ID' });
  }
  const preferences = req.body;
  // Ensure preferences is a plain object before sending to JSONB
  if (preferences === null || Array.isArray(preferences) || typeof preferences !== 'object') {
    return res.status(400).json({ error: 'Invalid preferences object' });
  }
  const client = await pool.connect();
  try {
    const result = await client.query(
      'UPDATE user_prefs SET data = $1::jsonb WHERE user_id = $2::int RETURNING id',
      [preferences, userId]
    );
    if (result.rowCount === 0) {
      return res.status(404).json({ error: 'Not found' });
    }
    res.json({ updated: true });
  } finally {
    client.release();
  }
});

3. Enforce schema checks for JSON columns

When inserting or updating JSONB columns, validate the shape and types to avoid downstream confusion. Use runtime checks or a validation library, and rely on CockroachDB’s jsonb_typeof when querying to ensure type-aware conditions.

const Ajv = require('ajv');
const ajv = new Ajv();
const profileSchema = {
  type: 'object',
  required: ['theme', 'notifications'],
  properties: {
    theme: { type: 'string' },
    notifications: { type: 'boolean' },
  },
};
const validateProfile = ajv.compile(profileSchema);

app.put('/profile/:id', express.json(), async (req, res) => {
  const id = req.params.id;
  const profile = req.body;
  if (!validateProfile(profile)) {
    return res.status(400).json({ errors: validateProfile.errors });
  }
  const client = await pool.connect();
  try {
    const result = await client.query(
      'UPDATE profiles SET settings = $1::jsonb WHERE id = $2 AND jsonb_typeof($1) = \"object\" RETURNING id',
      [profile, id]
    );
    if (result.rowCount === 0) {
      return res.status(404).json({ error: 'Not found' });
    }
    res.json({ updated: true });
  } finally {
    client.release();
  }
});

4. Use middleware to normalize and reject malformed types

Add validation middleware that rejects requests where numeric or UUID parameters are not of the expected type or format. This prevents ambiguous values from reaching the database layer.

function validateId(req, res, next) {
  const id = req.params.id;
  if (typeof id !== 'string' || !/^[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(id)) {
    return res.status(400).json({ error: 'Invalid ID type or format' });
  }
  next();
}
app.get('/resource/:id', validateId, async (req, res) => {
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT * FROM resources WHERE id = $1::uuid', [req.params.id]);
    res.json(result.rows);
  } finally {
    client.release();
  }
});

By combining strict schema validation, explicit casts in SQL, and type-aware checks for JSON columns, you reduce the risk of type confusion between Express and CockroachDB. These practices align with findings commonly surfaced by middleBrick scans, which highlight Input Validation and Property Authorization issues in unauthenticated attack scenarios.