Formula Injection in Gin with Cockroachdb

Formula Injection occurs when untrusted user input is used to construct formulas or expressions that are later evaluated, often in spreadsheet-like features or dynamic query building. In a Gin-based Go API that uses CockroachDB as the backend, this risk arises when input such as query parameters, path segments, or JSON body fields are interpolated into SQL or constructed for execution without strict validation or parameterization. CockroachDB, while PostgreSQL-wire compatible, does not inherently protect against formula-style injection when developers build SQL strings dynamically.

Consider an endpoint that accepts a filter expression from a client to build a dynamic WHERE clause: the client provides a column name and a value, and the server constructs a SQL string. If the column name is concatenated directly, an attacker can inject expressions like 1=1; DROP TABLE users or use crafted input to manipulate query logic. Because CockroachDB supports rich SQL syntax, injected formulas can include subqueries, arithmetic, or functions that change behavior or expose data. The Gin framework itself does not modify SQL; the risk is introduced by application code that builds queries using string concatenation or improper escaping.

Additionally, if the API exposes endpoints that generate or evaluate expressions for business logic (for example, computing derived fields based on user-supplied formulas), and those formulas are persisted or passed to CockroachDB without sanitization, injection can occur. Attack patterns include using SQL meta-characters, comment sequences, or type-confusion payloads to bypass intended filters. Because CockroachDB is often used in distributed, high-availability environments, the impact of successful injection can be widespread if compromised queries affect multiple nodes or tenants.

With middleBrick scanning this API, checks such as Input Validation, Property Authorization, and BFLA/Privilege Escalation run in parallel to detect endpoints where user input influences SQL. The scanner cross-references OpenAPI specifications with runtime behavior to identify mismatches: for example, a path parameter declared as a string but used in dynamic SQL without placeholders. Unauthenticated endpoints are particularly at risk, as attackers can probe formula injection without credentials. middleBrick does not fix the code but provides prioritized findings with severity and remediation guidance, helping teams address the root cause in Gin services backed by CockroachDB.

To remediate formula injection in Gin when interacting with CockroachDB, always use parameterized queries with prepared statements or an ORM that supports placeholders. Never concatenate user input directly into SQL strings. Below are concrete, working examples in Go using the pgx driver, which is compatible with CockroachDB.

Safe parameterized query example

// Correct: using parameterized queries with pgx
func getUserByEmailSafe(c *gin.Context) {
    email := c.Query("email")
    if email == "" {
        c.JSON(http.StatusBadRequest, gin.H{"error": "email is required"})
        return
    }

    conn, err := pgx.Connect(context.Background(), os.Getenv("COCKROACH_URL"))
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "db connection failed"})
        return
    }
    defer conn.Close(context.Background())

    var user User
    // Use placeholders ($1, $2) to avoid formula injection
    err = conn.QueryRow(context.Background(), "SELECT id, name, email FROM users WHERE email = $1", email).Scan(&user.ID, &user.Name, &user.Email)
    if err != nil {
        c.JSON(http.StatusNotFound, gin.H{"error": "user not found"})
        return
    }
    c.JSON(http.StatusOK, user)
}

Safe dynamic filtering with whitelisting

// Correct: validate column names against a whitelist
var allowedColumns = map[string]bool{"name": true, "email": true, "created_at": true}

func listUsersSafe(c *gin.Context) {
    column := c.DefaultQuery("column", "created_at")
    order := c.DefaultQuery("order", "ASC")

    if !allowedColumns[column] {
        c.JSON(http.StatusBadRequest, gin.H{"error": "invalid column"})
        return
    }
    if order != "ASC" && order != "DESC" {
        c.JSON(http.StatusBadRequest, gin.H{"error": "invalid order"})
        return
    }

    conn, err := pgx.Connect(context.Background(), os.Getenv("COCKROACH_URL"))
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "db connection failed"})
        return
    }
    defer conn.Close(context.Background())

    query := fmt.Sprintf("SELECT id, name, email FROM users ORDER BY %s %s", column, order)
    rows, err := conn.Query(context.Background(), query)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "query failed"})
        return
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": "scan failed"})
            return
        }
        users = append(users, u)
    }
    c.JSON(http.StatusOK, users)
}

Prepared statements with sql.DB (if using database/sql)

// Correct: using database/sql with prepared statements
func createUserSafe(w http.ResponseWriter, r *http.Request) {
    var req struct {
        Name  string `json:"name"`
        Email string `json:"email"`
    }
    if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
        http.Error(w, "invalid request", http.StatusBadRequest)
        return
    }

    conn, err := pgx.Connect(context.Background(), os.Getenv("COCKROACH_URL"))
    if err != nil {
        http.Error(w, "db connection failed", http.StatusInternalServerError)
        return
    }
    defer conn.Close(context.Background())

    // Use placeholders; CockroachDB supports $1-style placeholders via pgx
    _, err = conn.Exec(context.Background(), "INSERT INTO users (name, email) VALUES ($1, $2)", req.Name, req.Email)
    if err != nil {
        http.Error(w, "insert failed", http.StatusInternalServerError)
        return
    }
    w.WriteHeader(http.StatusCreated)
}

Additional guidance: validate and sanitize all inputs, use an allowlist for column and table names, and avoid eval-like behaviors. middleBrick’s Input Validation and Property Authorization checks can help surface endpoints where dynamic SQL or expression evaluation may lead to formula injection. By combining strict input validation with CockroachDB’s support for parameterized queries, teams can reduce the attack surface significantly in Gin services.