Sql Injection in Echo Go with Cockroachdb

SQL injection in a Go service built with the Echo framework and backed by CockroachDB typically arises when query construction mixes user-controlled input with SQL text. Because CockroachDB speaks PostgreSQL wire protocol, patterns common to PostgreSQL drivers apply, but the risk is not the database alone—it is how the application builds queries. When developers concatenate or interpolate request parameters directly into SQL strings, an attacker can manipulate the syntax to change intent, bypass authentication, or read or modify data.

Consider an Echo handler that retrieves a user profile by an identifier supplied as a URL query parameter:

// Unsafe: concatenating user input into SQL
func getUser(c echo.Context) error {
    id := c.QueryParam("id")
    query := "SELECT id, email, name FROM users WHERE id = '" + id + "'"
    rows, err := db.Query(query)
    if err != nil {
        return err
    }
    defer rows.Close()
    // process rows
    return c.JSON(http.StatusOK, "ok")
}

In this example, an attacker can supply id as ' OR '1'='1 or more targeted payloads tailored to CockroachDB’s SQL dialect, producing sets that were never intended. Because the query is constructed outside of any parameterization, CockroachDB executes the attacker’s injected SQL as part of the statement. Even when using placeholders in the string, incorrect usage with the driver can reintroduce risk. For instance, using fmt.Sprintf to build a placeholder string does not protect you; what matters is using the driver’s bind parameters correctly so that values are transmitted separately from the SQL command structure.

Echo itself does not construct queries, but its routing and parameter extraction features can inadvertently encourage unsafe habits if developers treat c.Param, c.QueryParam, and JSON body fields as safe to concatenate. Injection is not limited to SELECT statements; it can appear in UPDATE, DELETE, or DDL within handlers that build dynamic schemas or table names. Although CockroachDB supports prepared statements, the protection only materializes when the Go code uses them, separating SQL structure from data. Without strict adherence to parameterized patterns across all database interactions, the combination of Echo’s flexibility and CockroachDB’s expressiveness creates a surface where injection can be introduced and exploited.

Remediation centers on strict use of parameterized queries and avoiding dynamic SQL assembly. With CockroachDB and the PostgreSQL wire protocol, use prepared statements via db.Prepare or implicit preparation through db.Query with placeholders. The placeholder style depends on the driver; for pgx, use PostgreSQL-style numbered placeholders ($1, $2) or the pgxpool.Conn prepared statements API. Never build SQL by concatenating strings, even when quoting or escaping appears to be applied.

Here is a safe handler using Echo and CockroachDB with pgx:

// Safe: parameterized query with pgx
func getUserSafe(c echo.Context) error {
    id := c.QueryParam("id")
    var user struct {
        ID    string
        Email string
        Name  string
    }
    // Using pgxpool; db is *pgxpool.Pool
    err := db.QueryRow(c.Request().Context(), "SELECT id, email, name FROM users WHERE id = $1", id).Scan(&user.ID, &user.Email, &user.Name)
    if err != nil {
        return echo.NewHTTPError(http.StatusInternalServerError, "database error")
    }
    return c.JSON(http.StatusOK, user)
}

If you need dynamic table or column names, do not concatenate user input. Instead, use an allowlist to validate identifiers, and construct the query string with strict formatting that does not include external values as identifiers:

// Safe: allowlist for table names
func queryTable(c echo.Context) error {
    table := c.QueryParam("table")
    allowed := map[string]bool{"users": true, "orders": true}
    if !allowed[table] {
        return echo.NewHTTPError(http.StatusBadRequest, "invalid table")
    }
    var count int
    // Table name is not a parameter; ensure it comes only from allowlist
    err := db.QueryRow(c.Request().Context(), fmt.Sprintf("SELECT count(*) FROM %s", table)).Scan(&count)
    if err != nil {
        return err
    }
    return c.JSON(http.StatusOK, map[string]int{"count": count})
}

For statements that must be prepared explicitly, CockroachDB and pgx support named or numbered placeholders. Prepare once and execute many times to reduce parsing overhead and ensure consistent execution plans:

// Safe: explicit prepared statement with pgx
func prepareAndExecute(c echo.Context) error {
    id := c.QueryParam("id")
    stmt, err := db.Prepare(c.Request().Context(), "get_user", "SELECT id, email, name FROM users WHERE id = $1")
    if err != nil {
        return err
    }
    defer stmt.Close(c.Request().Context())
    var user struct {
        ID    string
        Email string
        Name  string
    }
    err = stmt.QueryRow(c.Request().Context(), id).Scan(&user.ID, &user.Email, &user.Name)
    if err != nil {
        return echo.NewHTTPError(http.StatusInternalServerError, "query failed")
    }
    return c.JSON(http.StatusOK, user)
}

Additionally, validate and sanitize inputs where applicable, and prefer strongly typed structs over raw string concatenation. Regular security scans with tools that understand your stack, such as middleBrick, can help detect insecure query construction patterns before they reach production. The CLI makes it straightforward to integrate checks into development workflows: use middlebrick scan <url> to assess endpoints quickly. For teams that require continuous oversight, the Pro plan adds scheduled scans and change alerts, while the GitHub Action can fail builds when risk thresholds are exceeded.