Sql Injection in Chi with Cockroachdb

SQL injection in a Chi application using CockroachDB arises when user input is concatenated into SQL strings rather than passed as parameters. CockroachDB, like other SQL databases, does not inherently protect you from unsafe query construction in your application code. Chi routes and handler code that builds queries by string interpolation or concatenation can introduce injection points even when using a modern, strongly-typed database driver.

For example, consider a Chi endpoint that retrieves a user by email from CockroachDB by directly embedding a request query parameter into the SQL string:

// Unsafe: string concatenation enables SQL injection
let userEmail = req.query["email"];
let query = "SELECT id, name FROM users WHERE email = '" + userEmail + "'";
let result = await db.query(query);

An attacker can supply ' OR '1'='1 as the email, causing the query to return all users. CockroachDB executes the malicious string as SQL because the driver sends the final query text to the server without escaping. Another common pattern is using fmt.Sprintf to assemble queries, which behaves identically in risk. The vulnerability is not in CockroachDB itself but in how the Chi app builds commands for the database. Because CockroachDB supports standard PostgreSQL wire protocol and SQL syntax, typical injection techniques such as UNION-based extraction, authentication bypass, and boolean-based blind injection apply.

Chi middleware that logs or transforms requests can inadvertently include untrusted data in queries. If request headers, form values, or JSON bodies are used to construct dynamic SQL without validation or parameterization, the attack surface expands. For instance, using URL path segments to build object names or tenant identifiers can lead to injection if not handled with strict allowlists and parameterization.

Additionally, prepared statements in the driver can mitigate injection, but they must be used consistently. Ad-hoc queries that skip prepared statements expose the application even when CockroachDB is deployed with TLS and strong authentication. The OWASP API Top 10 category 'A03:2021 Injection' maps directly to these risks, and compliance frameworks such as PCI-DSS and SOC2 require controls that prevent injection.

Remediation centers on using parameterized queries and avoiding string assembly entirely. With CockroachDB and Chi, you should use the database/sql package with placeholders ($1, $2, ...) supported by the PostgreSQL driver. Never interpolate user data into SQL text.

Here is a safe Chi handler using parameterized queries with CockroachDB:

import (
    "database/sql"
    "net/http"
)

func getUserByEmail(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, req *http.Request) {
        email := req.URL.Query().Get("email")
        var id int
        var name string
        // Safe: parameterized query prevents injection
        err := db.QueryRow("SELECT id, name FROM users WHERE email = $1", email).Scan(&id, &name)
        if err != nil {
            http.Error(w, "user not found", http.StatusNotFound)
            return
        }
        w.Write([]byte(fmt.Sprintf("id: %d, name: %s", id, name)))
    }
}

For dynamic queries where the set of columns or table names must vary, use strict allowlists and never inject those identifiers through user input. For example:

allowedColumns := map[string]bool{"name": true, "created_at": true}
col := req.URL.Query().Get("col")
if !allowedColumns[col] {
    http.Error(w, "invalid column", http.StatusBadRequest)
    return
}
query := fmt.Sprintf("SELECT %s FROM users WHERE email = $1", col)
err := db.QueryRow(query, email).Scan(&name)

Prepare and execute statements explicitly when performing repeated operations to reduce parsing overhead and ensure consistent parameter handling:

stmt, err := db.Prepare("INSERT INTO users (email, name) VALUES ($1, $2)")
if err != nil {
    // handle error
}
defer stmt.Close()
_, err = stmt.Exec(email, fullName)

In a Chi route composed with middleware, ensure that any request-scoped values used in database calls are validated and encoded for SQL context. Use transactions with parameterized statements when multiple dependent operations are required. These practices align with the Pro plan capabilities of middleBrick, which can scan Chi endpoints and map findings to frameworks like OWASP API Top 10 and PCI-DSS, providing prioritized remediation guidance without claiming to fix issues automatically.

For teams using CI/CD, the middleBrick GitHub Action can enforce that new commits do not introduce regression in API security scores. The CLI tool enables scripted scans from the terminal, while the MCP Server lets you trigger checks directly from AI coding assistants within your IDE.