Sql Injection in Gorilla Mux with Cockroachdb

SQL injection in a Gorilla Mux service that uses CockroachDB occurs when user-controlled input is concatenated into SQL strings rather than passed as parameters. Because CockroachDB wire protocol treats placeholders differently depending on the driver, failing to use placeholders correctly can expose queries to injection even when the SQL appears structured.

Gorilla Mux provides route variables (e.g., /users/{id}) and query parameters that developers often incorporate directly into SQL strings. Consider a handler that builds a query using string concatenation:

query := "SELECT * FROM users WHERE id = '" + r.URL.Query().Get("id") + "'"

If the id parameter contains malicious input such as ' OR '1'='1, the resulting query changes its intent, potentially bypassing authentication or extracting other users’ data. CockroachDB supports the PostgreSQL wire protocol, so many Go drivers rely on $1, $2 style placeholders (for pgx) or ? style placeholders (for database/sql drivers like cockroachdb). The vulnerability arises not from CockroachDB itself but from how the developer constructs statements and binds parameters.

Another common pattern is dynamic sorting or table names, which cannot be parameterized with placeholders. For example:

orderBy := r.URL.Query().Get("order")
query := fmt.Sprintf("SELECT * FROM users ORDER BY %s", orderBy)

An attacker can supply id; DROP TABLE users-- or similar payloads. Since this pattern cannot use value placeholders, the risk is high. Additionally, if the application reuses query building across endpoints and mishandles escaping, secondary injection surfaces may appear in stored procedures or JSONB key lookups.

SSRF and external network calls are not directly relevant here, but improper handling of error messages can leak schema details that aid an attacker in refining injection attempts. The key takeaway is that SQL injection is a statement-level issue: any dynamic SQL construction without strict parameterization or strict allowlists for identifiers invites compromise regardless of the underlying database.

Remediation focuses on using parameterized queries for values and strict allowlists for identifiers. For CockroachDB with the pgx driver, use numbered placeholders ($1, $2). With database/sql drivers that support CockroachDB, use ? placeholders and ensure you pass arguments in the correct order.

Safe example with pgx (CockroachDB native)

import "github.com/jackc/pgx/v5"

func getUser(w http.ResponseWriter, r *http.Request) {
    vars := mux.Vars(r)
    id := vars["id"]
    var email string
    // Using pgx with placeholders prevents injection
    err := conn.QueryRow(r.Context(), "SELECT email FROM users WHERE id = $1", id).Scan(&email)
    if err != nil {
        http.Error(w, "not found", http.StatusNotFound)
        return
    }
    w.Write([]byte(email))
}

Safe example with database/sql (cockroachdb driver)

import (
    "database/sql"
    _ "github.com/cockroachdb/cockroach-go/v2/crdb"
)

func getUserByEmail(w http.ResponseWriter, r *http.Request) {
    email := r.URL.Query().Get("email")
    var userID string
    // Using ? placeholders with sql.QueryRow is safe for values
    err := db.QueryRow("SELECT id FROM users WHERE email = ?", email).Scan(&userID)
    if err != nil {
        http.Error(w, "not found", http.StatusNotFound)
        return
    }
    w.Write([]byte(userID))
}

Allowlist-based identifier handling

When sorting or selecting columns, do not use placeholders. Instead, validate against a strict allowlist:

allowedColumns := map[string]bool{"created_at": true, "name": true, "email": true}
col := r.URL.Query().Get("col")
if !allowedColumns[col] {
    http.Error(w, "invalid column", http.StatusBadRequest)
    return
}
query := "SELECT * FROM users ORDER BY " + col // col is safe

Prepared statements for repeated queries

Prepare statements at initialization to reduce parsing overhead and ensure consistent parameter handling:

stmt, err := db.Prepare("SELECT id, email FROM users WHERE status = ?")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close()

rows, err := stmt.Query("active")
if err != nil {
    http.Error(w, "server error", http.StatusInternalServerError)
    return
}

These patterns align with remediation guidance for findings such as BOLA/IDOR and Input Validation, reducing the attack surface to what middleBrick would detect in an unauthenticated scan.