Nosql Injection in Fastapi with Cockroachdb

Nosql Injection occurs when user input is directly concatenated into database queries without validation or parameterization. In a Fastapi application using Cockroachdb, the risk arises not from Cockroachdb itself being a NoSQL database—it is a SQL database—but from how query-building code treats structured data in a schemaless or dynamic way, such as when storing and later querying JSONB columns or when constructing dynamic SQL from user-controlled keys.

Consider a Fastapi endpoint that accepts filter criteria as JSON and passes them to a Cockroachdb query. If the application builds SQL by interpolating JSON keys into string templates, an attacker can supply keys like id, email, or nested JSON paths to alter query logic. For example, a filter {"email": "[email protected]"} might become WHERE email = '[email protected]', while malicious input {"email": "[email protected]", "1": "1"} can transform it into WHERE email = '[email protected]' AND 1=1, bypassing intended filters. This is similar to classic SQL Injection in impact—data exfiltration, authentication bypass, or data modification—but it often originates from unsafe handling of JSON input and dynamic query assembly rather than direct string concatenation of SQL keywords.

Another common scenario involves JSONB columns where developers query using dynamic key–value pairs. If a Fastapi route extracts keys from request JSON and uses them to construct conditions such as WHERE column->>{user_key} = user_value without restricting user_key, an attacker can probe schema information or force type errors to infer data. Because Cockroachdb supports rich JSONB operators, unsafe usage of operators like @> or ->> with untrusted input can expose injection surfaces. The combination of Fastapi’s flexible request parsing and Cockroachdb’s JSONB capabilities increases the impact of insecure coding patterns, especially when input validation is limited to basic type checks rather than strict allowlists.

In threat modeling terms, this maps to the OWASP API Top 10 controls around input validation and authentication/authorization weaknesses. Without strict schema enforcement and query parameterization, attackers can manipulate JSON payloads to achieve unintended access or data exposure. middleBrick’s checks for Input Validation and Property Authorization are designed to surface these risks by correlating OpenAPI specifications with runtime behavior, ensuring that dynamic query construction is flagged when it deviates from secure baselines.

To prevent Nosql Injection in Fastapi with Cockroachdb, always use parameterized queries or an ORM that enforces separation of data and command logic. Avoid building WHERE clauses by string concatenation, especially when keys or operators are derived from user input. Below are concrete, safe patterns using Cockroachdb’s Python driver (psycopg or async variants) with Fastapi.

Safe parameterized query with dynamic filters

Instead of injecting user-provided keys directly, validate keys against an allowlist and use placeholder syntax for values. Cockroachdb accepts positional parameters like $1, $2, etc., which ensure values are never interpreted as executable SQL.

from fastapi import FastAPI, HTTPException
import psycopg
from pydantic import BaseModel

app = FastAPI()

class FilterRequest(BaseModel):
    allowed_keys = {"email", "status", "created_at"}
    filters: dict[str, str]

@app.post("/users/search")
async def search_users(body: FilterRequest):
    allowed_keys = {"email", "status", "created_at"}
    for key in body.filters.keys():
        if key not in allowed_keys:
            raise HTTPException(status_code=400, detail=f"Invalid filter key: {key}")

    conditions = []
    values = []
    for idx, (key, value) in enumerate(body.filters.items(), start=1):
        conditions.append(f"{key} = ${idx}")
        values.append(value)

    query = f"SELECT id, email, status FROM users WHERE {' AND '.join(conditions)}"
    async with psycopg.connect() as conn:
        rows = await conn.fetch(query, *values)
        return [dict(row) for row in rows]

Safe JSONB querying with operator validation

When working with JSONB columns, validate JSON path components and avoid passing raw user input to operators like ->>. Use hardcoded paths or map user-friendly names to safe expressions.

from fastapi import FastAPI, HTTPException
import psycopg
from pydantic import BaseModel

app = FastAPI()

class JsonFilter(BaseModel):
    field: str  # e.g., "preferences.theme"
    value: str

@app.post("/items/json-query")
async def query_json_field(filter: JsonFilter):
    allowed_fields = {"preferences.theme", "preferences.lang", "metadata.source"}
    if filter.field not in allowed_fields:
        raise HTTPException(status_code=400, detail="Invalid JSON field")

    # Safe: field is validated; value uses parameterization
    query = "SELECT id, data FROM items WHERE data ->> $1 = $2"
    async with psycopg.connect() as conn:
        rows = await conn.fetch(query, filter.field, filter.value)
        return [dict(row) for row in rows]

These examples emphasize input validation and parameterization, which align with middleBrick’s checks for Input Validation and BOLA/IDOR. By combining allowlist-based key validation with positional placeholders, you ensure that even JSONB queries remain safe from injection. middleBrick’s OpenAPI/Swagger analysis can further verify that your endpoint definitions match these secure patterns, reducing the risk of misconfiguration in dynamic query building.