Type Confusion in Cockroachdb

Type confusion occurs when an API incorrectly interprets the data type of a user-supplied parameter, leading to unintended behavior. In CockroachDB—a distributed SQL database with PostgreSQL compatibility—this vulnerability often arises at the boundary between the application layer and the database driver. Attackers exploit type confusion to bypass authorization checks, inject malicious SQL, or extract sensitive data by supplying values of an unexpected type that the database coerces or misinterprets.

CockroachDB-specific attack patterns include:

• Integer vs. String Coercion: An API endpoint expects an integer ID (e.g., /users/123) but fails to enforce type constraints. An attacker passes a string like 123 OR 1=1. If the application concatenates this directly into a SQL query, CockroachDB may implicitly cast the string to an integer (truncating at non-numeric characters) or, worse, treat it as part of the query if quoting is mishandled. For example, a vulnerable Go snippet might use fmt.Sprintf("SELECT * FROM users WHERE id = %s", id). If id is a string containing "1 OR 1=1", the resulting query becomes SELECT * FROM users WHERE id = 1 OR 1=1, returning all rows.
• Array/Scalar Confusion: CockroachDB supports array types (e.g., INT[]). An API that expects a single integer but receives an array (e.g., id[]=1) might cause the driver to send an array to the database. If the query uses = instead of ANY, CockroachDB may throw an error—but if error messages leak information, it confirms the parameter's influence. Conversely, if the API expects an array but receives a scalar, the database might wrap the scalar in an array, altering query semantics.
• JSONB Type Juggling: CockroachDB's JSONB type stores binary JSON. An API that expects a JSON object but receives a string might lead to unexpected parsing. For instance, passing "{\"key\": \"value\"}" (a string) versus {"key":"value"} (an object) can result in different query outcomes when using -> operators. Attackers may probe for differences to infer schema or bypass filters.
• Boolean/Enum Confusion: CockroachDB's BOOL or custom ENUM types can be confused via string representations. Supplying "true" vs. true or "admin" vs. admin::role might change query results if the driver does not strictly validate types.

A concrete CockroachDB-specific code path involves the pgx driver (commonly used with Go). When using db.Query with arguments, pgx relies on Go's type system to send parameters. If the API passes a string for a INT column, pgx attempts to parse it as an integer. However, if the string contains non-numeric characters, it may be sent as a VARCHAR and then implicitly cast by CockroachDB, which can lead to unexpected matches (e.g., WHERE id = '123abc' might match id = 123 if CockroachDB casts the string to integer by truncation). This behavior is database-specific and can be exploited.

Detecting type confusion in CockroachDB APIs requires testing how the application handles mismatched types and observing the database's response patterns. Manual testing involves sending parameters of incorrect types (e.g., strings for integers, arrays for scalars) and analyzing HTTP responses for data leakage, error messages, or behavioral changes. However, this is error-prone and unscalable.

middleBrick automates this detection by scanning the API endpoint with a battery of type confusion probes tailored for CockroachDB's type system. The scanner:

• Inspects the OpenAPI specification (if available) to understand expected parameter types and then sends deliberately mismatched types.
• Tests common CockroachDB coercion behaviors: e.g., sending a string like "1 UNION SELECT ..." for an integer parameter to see if it gets interpreted as SQL syntax.
• Probes array handling by sending both scalar and array values for parameters declared as arrays, and vice versa.
• Checks for differences in response length, status codes, or content that indicate type-sensitive logic (e.g., returning more records when an integer parameter is injected with OR 1=1).
• Analyzes error messages for CockroachDB-specific syntax (e.,g., ERROR: invalid input syntax for type integer: "abc") that leak type information.

For example, consider a Go API endpoint that queries CockroachDB:

func GetUser(w http.ResponseWriter, r *http.Request) {
    id := r.URL.Query().Get("id")
    row := db.QueryRow("SELECT * FROM users WHERE id = " + id)
    // ...
}

middleBrick would send requests with id=1 (valid) and id=1 OR 1=1 (malicious). If the latter returns multiple users, it indicates type confusion leading to SQL injection. The scanner also tests with id[]=1 (array) to see if the query fails or returns unexpected data.

middleBrick's report includes a per-category breakdown for "Property Authorization" and "Input Validation", highlighting exactly which parameters exhibited type confusion, the payloads used, and the observed responses. This allows developers to pinpoint vulnerable code paths quickly.

Remediating type confusion in CockroachDB APIs requires strict type enforcement at the application layer and proper use of parameterized queries. CockroachDB's SQL dialect follows PostgreSQL standards, so leveraging prepared statements with explicit type casting is essential.

1. Use Parameterized Queries with Explicit Types
Never concatenate user input into SQL strings. Instead, use placeholders and pass arguments as correctly typed Go values. The pgx driver will then send parameters with their binary representation, avoiding implicit casting surprises.

// VULNERABLE
idStr := r.URL.Query().Get("id")
row := db.QueryRow(fmt.Sprintf("SELECT * FROM users WHERE id = %s", idStr))

// REMEDIATED
idStr := r.URL.Query().Get("id")
id, err := strconv.Atoi(idStr) // Validate as integer
if err != nil {
    http.Error(w, "Invalid ID", http.StatusBadRequest)
    return
}
row := db.QueryRow("SELECT * FROM users WHERE id = $1", id)

2. Validate Input Against Expected Types
For complex types like arrays or JSON, parse and validate before database interaction. Use Go's json.Unmarshal for JSONB fields and ensure the structure matches the expected schema. For arrays, split string inputs and convert to the appropriate slice type.

// Example: Expecting an array of integers for a JSONB column
idsParam := r.URL.Query().Get("ids")
var ids []int64
for _, part := range strings.Split(idsParam, ",") {
    n, err := strconv.ParseInt(part, 10, 64)
    if err != nil { /* handle error */ }
    ids = append(ids, n)
}
// Use with ANY: SELECT * FROM items WHERE id = ANY($1)
rows, err := db.Query("SELECT * FROM items WHERE id = ANY($1)", ids)

3. Leverage CockroachDB's Type System for Casting
When dynamic queries are unavoidable, use explicit CAST in SQL to force types, but only after validating the input format. For example:

// If you must accept a string that should be an integer:
validatedID := regexp.MustCompile(`^\d+$`).FindString(idStr)
if validatedID == "" { /* error */ }
row := db.QueryRow("SELECT * FROM users WHERE id = $1::INT", validatedID)

4. Handle CockroachDB-Specific Types Carefully
For DECIMAL or FLOAT columns, use Go's math/big.Rat or float64 with awareness of precision. For UUID, use github.com/google/uuid and validate. For ENUM, compare against a whitelist of allowed string values.

5. Test with middleBrick
After remediation, rescan the API with middleBrick to verify that type confusion findings are resolved. The scanner will no longer detect significant response differences when sending mismatched types, and the risk score should improve.

By combining strict input validation, parameterized queries, and CockroachDB-aware type handling, you can eliminate type confusion vulnerabilities and ensure that your API behaves predictably under all inputs.