Formula Injection in Chi with Cockroachdb

Formula injection occurs when untrusted input is interpreted as a formula or expression by a downstream system. In a Chi-based Go service that uses CockroachDB as the backend, this typically arises when user-controlled values are interpolated into SQL strings, JSON payloads, or response serialization logic rather than being passed as bound parameters. CockroachDB, while PostgreSQL-wire compatible, still processes dynamic SQL the same way as other SQL engines: if a query is constructed by concatenating strings, an attacker can inject formulas, SQL fragments, or type-casting expressions that alter execution flow or data exposure.

Consider a Chi route that builds a SQL query using string concatenation or formatting to filter records by tenant or date:

// Unsafe: formula/SQL injection via concatenation
func unsafeHandler(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        tenant := r.URL.Query().Get("tenant")
        query := fmt.Sprintf("SELECT * FROM invoices WHERE tenant_id = %s", tenant)
        rows, err := db.Query(query)
        // handle rows and err
    }
}

If tenant is provided as 1; DROP TABLE invoices -- or 1 OR 1=1, CockroachDB executes the injected SQL, potentially exposing or destroying data. Even when using database/sql with ?placeholders, improper usage such as dynamically constructing identifiers (table or column names) cannot be parameterized and must be sanitized explicitly; failing to do so enables formula injection through crafted identifiers or type expressions:

// Unsafe: dynamic identifier injection
table := r.URL.Query().Get("table")
query := "SELECT * FROM " + table // identifier cannot use placeholders
rows, err := db.Query(query)

Chi’s middleware patterns can inadvertently propagate injection risks when request context is used to assemble downstream queries. For example, using URL path parameters or headers to build CockroachDB queries without validation or escaping enables attackers to inject arithmetic or logical formulas that change result sets or bypass intended filters. Additionally, if the service serializes query results into JSON and user-controlled fields are embedded without escaping, formula injection can extend into the output layer, leading to unintended script execution when the JSON is consumed by a frontend.

The OWASP API Top 10 category API1:2023 – Broken Object Level Authorization often intersects with formula injection when object identifiers (record IDs, tenant IDs) are derived from input. In CockroachDB, this can manifest as unauthorized data access across tenants via injected conditions like OR 1=1 or type coercion such as 0; SELECT 1::regclass::oid::regclass::text to probe schema information. Data exposure findings from middleBrick scans frequently highlight these patterns in unauthenticated endpoints where query construction lacks strict input validation and separation of data from commands.

Remediation focuses on strict input validation, using parameterized queries for data values, and rejecting or safely encoding dynamic identifiers. For CockroachDB compatibility, treat all user input as untrusted and avoid string interpolation for SQL construction.

1. Use parameterized queries for data values

Always use db.Query or db.Exec with ? placeholders for values. CockroachDB supports this pattern natively through the PostgreSQL wire protocol.

// Safe: parameterized query
func safeHandler(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        tenant := chi.URLParam(r, "tenantID")
        var query string
        // Validate format before using
        if !isValidUUID(tenant) {
            http.Error(w, "invalid tenant", http.StatusBadRequest)
            return
        }
        query = "SELECT * FROM invoices WHERE tenant_id = $1"
        rows, err := db.Query(query, tenant)
        // handle rows and err
    }
}

Note: Use $1, $2 style placeholders (PostgreSQL syntax) with CockroachDB. Do not concatenate validated values even after validation; keep using placeholders.

2. Strict validation for identifiers and dynamic table/column names

For identifiers that cannot be parameterized (e.g., table names), maintain an allowlist or strict regex pattern. Do not rely on escaping alone.

// Safe: allowlist-based identifier selection
var allowedTables = map[string]bool{
    "invoices": true,
    "customers": true,
}

func tableHandler(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        table := chi.URLParam(r, "table")
        if !allowedTables[table] {
            http.Error(w, "invalid table", http.StatusBadRequest)
            return
        }
        query := "SELECT * FROM " + table // safe: table is from allowlist
        rows, err := db.Query(query)
        // handle rows and err
    }
}

If dynamic columns are required, validate against a predefined schema map instead of raw input.

3. Encode output and enforce type constraints

When serializing query results to JSON, ensure proper escaping and type assertions. Use strongly-typed structs and avoid interface{} maps that could propagate injected strings as executable formulas in downstream consumers.

// Safe: typed structs reduce injection surface
type Invoice struct {
    ID        string  `json:"id"`
    TenantID  string  `json:"tenant_id"`
    Amount    float64 `json:"amount"`
}

func responseHandler(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        var inv Invoice
        row := db.QueryRow("SELECT id, tenant_id, amount FROM invoices WHERE id = $1", chi.URLParam(r, "id"))
        if err := row.Scan(&inv.ID, &inv.TenantID, &inv.Amount); err != nil {
            http.Error(w, "not found", http.StatusNotFound)
            return
        }
        // Write JSON safely; encoding/json escapes characters properly
        json.NewEncoder(w).Encode(inv)
    }
}

Combine these practices with middleBrick’s OpenAPI/Swagger analysis (which resolves $ref definitions across spec versions) to detect insecure query construction in unauthenticated scans. The free tier supports a limited number of scans to validate remediation; the Pro plan enables continuous monitoring so that any regression in query safety is flagged in CI/CD via GitHub Action or MCP Server integrations before deployment.