Sql Injection in Gin with Cockroachdb

SQL Injection remains a high-impact risk for Go APIs built with the Gin framework when database interactions are not strictly parameterized. When Gin HTTP handlers construct SQL queries by concatenating user-controlled inputs—such as path parameters, query strings, or JSON payloads—directly into SQL strings, the application becomes vulnerable. CockroachDB, while PostgreSQL-wire compatible and supporting prepared statements, does not prevent injection on its own; it is the way the application builds and executes queries that determines safety.

In a Gin route like /users/:userID, a developer might write code that interpolates userID into a query string before sending it to CockroachDB. Because CockroachDB accepts standard SQL via its lib/pq or pgx wire protocol, the database will execute whatever syntactically valid SQL is sent. If the input contains SQL meta-characters or crafted payloads (e.g., ' OR '1'='1), those will change the intent of the query, leading to authentication bypass, data exfiltration, or unauthorized modification. This is the classic OWASP API Top 10 A03:2023 injection risk, mapped further to PCI-DSS and SOC2 controls around data integrity.

Another common pattern is dynamic ORDER BY or column names based on query parameters. Because CockroachDB does not support parameterized identifiers (such as column or table names), developers sometimes interpolate these values directly. This creates a SQL Injection surface even when data values themselves are parameterized. Attackers can probe with inputs like 1; DROP TABLE users-- or use UNION-based techniques to extract schema information if error messages are leaked. The scanner’s checks for Input Validation and Data Exposure highlight these weaknesses by correlating unsafe query-building patterns with runtime detection of unexpected data returns or error disclosures.

Because middleBrick scans the unauthenticated attack surface, it can identify endpoints where query construction appears unsafe without requiring credentials. The scanner’s OpenAPI/Swagger analysis resolves $ref definitions and cross-references them with runtime observations, helping to confirm whether documented parameters are handled securely. For teams using the CLI, running middlebrick scan https://api.example.com can surface these findings quickly, while the Web Dashboard tracks these results over time and the GitHub Action can fail a build if the risk score drops below your chosen threshold.

To eliminate SQL Injection when using CockroachDB with Gin, always use parameterized queries with placeholders for data values and avoid interpolating identifiers. CockroachDB supports the PostgreSQL wire protocol, so you can use the pgx driver with prepared statements or the database/sql interface with lib/pq. Never construct SQL by string concatenation or Sprintf for queries that include user input.

Here is a safe Gin handler using pgx and the standard database/sql interface with CockroachDB:

import (
    "context"
    "net/http"
    "github.com/gin-gonic/gin"
    "github.com/jackc/pgx/v5/pgxpool"
    "database/sql"
    _ "github.com/lib/pq"
)

// Using pgxpool for CockroachDB
func getUserByEmail(c *gin.Context) {
    pool := c.MustGet("pgxpool").(*pgxpool.Pool)
    var email string
    if err := c.BindJSON(&struct{ Email string }{Email: email}); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": "invalid request"})
        return
    }
    var userID string
    // Parameterized query with pgx
    err := pool.QueryRow(c, context.Background(), "SELECT id FROM users WHERE email = $1", email).Scan(&userID)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "server error"})
        return
    }
    c.JSON(http.StatusOK, gin.H{"user_id": userID})
}

// Using database/sql with lib/pq (CockroachDB compatible)
func getUserByID(c *gin.Context) {
    db := c.MustGet("db").(*sql.DB)
    userID := c.Param("userID")
    var email string
    // Use placeholders; CockroachDB recognizes $1, $2 style
    row := db.QueryRow("SELECT email FROM users WHERE id = $1", userID)
    if err := row.Scan(&email); err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "server error"})
        return
    }
    c.JSON(http.StatusOK, gin.H{"email": email})
}

// Safe dynamic column handling: whitelist allowed columns
func getUsersSorted(c *gin.Context) {
    db := c.MustGet("db").(*sql.DB)
    sortColumn := c.DefaultQuery("sort", "created_at")
    allowed := map[string]bool{"created_at": true, "name": true, "email": true}
    if !allowed[sortColumn] {
        c.JSON(http.StatusBadRequest, gin.H{"error": "invalid sort column"})
        return
    }
    rows, err := db.Query("SELECT id, name, email FROM users ORDER BY " + sortColumn + " LIMIT 100")
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "server error"})
        return
    }
    defer rows.Close()
    // process rows...
    c.JSON(http.StatusOK, gin.H{"status": "ok"})
}

Key points:

• Use $1, $2 placeholders for all data values when using pgx or database/sql with CockroachDB.
• Do not interpolate user input into SQL strings, including for numeric IDs or timestamps; let the driver handle type-safe binding.
• For dynamic identifiers (column or table names), implement a strict whitelist and concatenate only after validation—never use placeholders for identifiers.
• Enable prepared statements where supported; CockroachDB benefits from statement caching via pgx pools, reducing parsing overhead and ensuring consistent handling of parameters.
• The scanner’s Property Authorization and Input Validation checks can detect remaining risks such as missing validation on sort fields or unsafe consumption patterns.

By combining these practices, teams reduce the SQL Injection attack surface to zero for parameterized queries and maintain secure, compliant interactions with CockroachDB through Gin.