Mass Assignment in Chi with Cockroachdb

Mass Assignment in a Chi-based service that uses CockroachDB as the backend occurs when user-supplied JSON or form data is mapped directly to database columns or struct fields without explicit allowlisting. Because CockroachDB is PostgreSQL-wire compatible, common Go SQL patterns such as db.Exec or db.NamedExec with named parameters behave like standard PostgreSQL operations, but the risk is introduced in the application layer, not in CockroachDB itself.

Chi encourages explicit routing and middleware composition, yet developers sometimes bind request payloads directly to structs that mirror database columns and then pass those structs to queries. If a struct contains fields like IsAdmin, Role, or TenantID, and the binding does not filter or whitelist fields, an attacker can inject values for sensitive columns. For example, a user registration payload that includes {"email":"attacker@example.com","password":"secret","is_admin":true} can escalate privileges when the handler decodes JSON into a struct with exported IsAdmin bool and uses it in an INSERT or UPDATE.

With CockroachDB, the impact can be more pronounced in distributed scenarios where schemas include multi-tenant columns such as tenant_id. If an application uses dynamic SQL generation or ORM-style helpers that build UPDATE statements from all non-zero struct fields, an attacker can modify tenant_id to cross-tenant boundaries, effectively accessing or altering data belonging to other customers. This is a BOLA/IDOR pattern enabled by insufficient property authorization rather than a CockroachDB-specific flaw, but the database’s strict SQL semantics mean that injected values are applied exactly as provided, without additional safeguards.

The combination of Chi’s flexible request handling and CockroachDB’s PostgreSQL compatibility means that unchecked binding leads to direct, executable SQL changes. A vulnerable handler might look like a normal Chi route:

func updateUser(w http.ResponseWriter, r *http.Request) {
    var u User
    if err := binding.JSON.Bind(r, &u); err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest); return
    }
    query := `UPDATE users SET email=$1, is_admin=$2 WHERE id=$3`
    _, err := db.Exec(query, u.Email, u.IsAdmin, u.ID)
    // error handling omitted
}

If the User struct includes additional fields such as Role or TenantID and the handler does not explicitly set them, an attacker controlling the request body can manipulate these fields by adding them to the JSON, causing unexpected privilege or tenant changes. The scanner’s BOLA/IDOR and Property Authorization checks are designed to detect such unchecked parameter mappings, while Input Validation and Unsafe Consumption checks highlight missing allowlists.

Remediation focuses on strict binding, explicit field selection, and server-side defaults. Always bind to a minimal DTO (Data Transfer Object), then map only approved fields to your domain model or SQL statement. Never rely on automatic struct-to-column mapping in handlers that reach CockroachDB. This practice aligns with OWASP API Top 10 #1 (Broken Object Level Authorization) and mitigates BOLA/IDOR risks regardless of the underlying database.

Secure remediation for Mass Assignment in Chi with CockroachDB centers on explicit field handling, input validation, and avoiding dynamic mapping of user input to SQL columns. Below are concrete, idiomatic examples that you can adopt directly.

1. Use a whitelisted DTO and explicit mapping

Define a request struct that contains only the fields you intend to accept, and map them explicitly before constructing SQL queries.

// DTO for accepted input
type UpdateUserDTO struct {
    Email string `json:"email" validate:"required,email"`
}

// Domain model
type User struct {
    ID        int64
    Email     string
    IsAdmin   bool
    TenantID  string
}

func updateUserSecure(w http.ResponseWriter, r *http.Request) {
    var dto UpdateUserDTO
    if err := binding.JSON.Bind(r, &dto); err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest); return
    }

    // Explicit mapping; sensitive fields are set server-side
    query := `UPDATE users SET email=$1, updated_at=now() WHERE id=$2`
    _, err := db.Exec(query, dto.Email, chi.URLParam(r, "id"))
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError); return
    }
    w.WriteHeader(http.StatusOK)
}

This ensures that fields such as is_admin or tenant_id cannot be altered by the client.

2. Use named queries with explicit column lists

When using db.NamedExec, always specify the columns to update rather than relying on struct field names. This prevents accidental updates to sensitive columns.

type SafeUpdate struct {
    Email string `db:"email"`
    ID    int64  `db:"id"`
}

func updateWithNamed(w http.ResponseWriter, r *http.Request) {
    var su SafeUpdate
    if err := binding.JSON.Bind(r, &su); err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest); return
    }

    stmt, named := db.PrepareNamed(`UPDATE users SET email=:email WHERE id=:id`)
    if err != nil { /* handle */ }
    defer stmt.Close()

    _, err := named.Exec(su)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError); return
    }
    w.WriteHeader(http.StatusOK)
}

3. Enforce server-side defaults and role checks

For fields like is_admin or tenant_id, compute values on the server based on authentication context or configuration, never from user input.

func updateWithServerDefaults(w http.ResponseWriter, r *http.Request) {
    var dto UpdateUserDTO
    if err := binding.JSON.Bind(r, &dto); err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest); return
    }

    // Assume user identity resolved elsewhere
    userID := chi.URLParam(r, "id")
    tenantID := resolveTenant(r) // e.g., from JWT or session

    query := `UPDATE users SET email=$1, tenant_id=$2, is_admin=$3 WHERE id=$4`
    _, err := db.Exec(query, dto.Email, tenantID, false, userID)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError); return
    }
    w.WriteHeader(http.StatusOK)
}

These patterns directly address Mass Assignment by removing implicit mapping, adding input validation, and ensuring that sensitive columns are controlled server-side. They work naturally with CockroachDB’s SQL semantics and integrate cleanly with Chi’s middleware model.