Insecure Direct Object Reference in Chi with Cockroachdb

Insecure Direct Object Reference (IDOR) occurs when an API exposes internal object references such as database primary keys in requests without proper authorization checks. The combination of the Chi routing library and CockroachDB as the backend datastore can inadvertently create IDOR risks when route parameters are used directly to construct database queries. For example, a route like /users/{user_id} may extract user_id from the URL and plug it into a CockroachDB query without verifying that the authenticated caller is allowed to access that specific user record.

With Cockroachdb, IDs are often predictable integer or UUID values, and Chi does not enforce any authorization by default. If the handler retrieves a record using a raw SQL query or an ORM without scoping the query to the requesting user, an attacker can modify the ID to access other users' data. This is a classic IDOR, and because Cockroachdb preserves SQL semantics, a simple change of numeric or UUID path segment can return a different row if the query lacks tenant or ownership filtering.

Consider a Chi handler that decodes a user ID from the context and runs a query directly against Cockroachdb:

import (
	"context"
	"database/sql"
	"net/http"

	"github.com/go-chi/chi/v5"
)

func getUserHandler(db *sql.DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		userID := chi.URLParam(r, "user_id")
		var email string
		// Potential IDOR: no ownership check
		row := db.QueryRowContext(r.Context(), "SELECT email FROM users WHERE id = $1", userID)
		if err := row.Scan(&email); err != nil {
			http.Error(w, "not found", http.StatusNotFound)
			return
		}
		w.Write([]byte(email))
	}
}

In this snippet, userID is taken directly from the route and used in a SQL WHERE clause. There is no check to confirm that the requesting user is the same as userID or that they have permission to view it. An attacker can iterate through IDs and enumerate emails or other sensitive fields stored in Cockroachdb, leading to data exposure.

Additionally, if the API exposes sequential or guessable IDs (e.g., auto-incrementing integers in Cockroachdb), the attack surface expands. Even with UUIDs, if the handler does not validate scope, an attacker can substitute one UUID for another. MiddleBrick scans detect such unauthenticated IDOR patterns by correlating OpenAPI path parameters with runtime behavior and flagging endpoints that accept object references without corresponding authorization checks.

IDOR in this stack is not limited to GET requests. PATCH or DELETE handlers that use a raw ID from Chi without ownership validation can enable unauthorized modification or deletion. Because Cockroachdb supports transactional SQL, an attacker may attempt to leverage injection or race conditions if input validation is weak, though IDOR is primarily an authorization flaw rather than an injection issue.

To identify this during a scan, middleBrick tests unauthenticated endpoints that include object identifiers and checks whether different identifiers yield different data without authorization. Findings include references to OWASP API Top 10 API5:2023 — Broken Function Level Authorization, and remediation guidance emphasizes scoping queries to the requester.

Remediation focuses on ensuring that every database query is scoped to the requesting user or tenant. In Chi, you typically have access to the request context and an authenticated subject (e.g., user ID or UUID). Use that subject to constrain queries against Cockroachdb so that users can only access their own data.

First, enforce authorization by comparing the route parameter to the authenticated subject before running SQL. If they do not match, return a 403 Forbidden.

import (
	"context"
	"database/sql"
	"net/http"

	"github.com/go-chi/chi/v5"
)

func getUserHandler(db *sql.DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		userID := chi.URLParam(r, "user_id")
		// Assume getAuthenticatedUserID extracts the subject from context or token
		authUserID := getAuthenticatedUserID(r)
		if userID != authUserID {
			http.Error(w, "forbidden", http.StatusForbidden)
			return
		}
		var email string
		row := db.QueryRowContext(r.Context(), "SELECT email FROM users WHERE id = $1 AND id = $2", userID, authUserID)
		if err := row.Scan(&email); err != nil {
			http.Error(w, "not found", http.StatusNotFound)
			return
		}
		w.Write([]byte(email))
	}
}

A more robust pattern is to always include the authenticated subject in the WHERE clause, even if you already performed an equality check. This ensures the database enforces authorization regardless of future code changes:

row := db.QueryRowContext(r.Context(), "SELECT email FROM users WHERE id = $1 AND owner_id = $2", userID, authUserID)

When using a tenant-aware schema, scope queries by tenant ID as well:

row := db.QueryRowContext(r.Context(), "SELECT email FROM users WHERE id = $1 AND tenant_id = $2", userID, tenantID)

For UUID-based identifiers, validate the format and ensure the subject matches using a constant-time comparison to avoid timing attacks. MiddleBrick’s LLM/AI Security checks can also verify that no prompt injection vectors exist in API documentation that might mislead developers about authorization requirements.

Finally, prefer parameterized queries and avoid string concatenation to prevent SQL injection, which can complement IDOR defenses. The dashboard can track whether endpoints with object references include proper scoping, and the CLI can automate checks for insecure patterns in your Chi routes.