Nosql Injection in Flask with Cockroachdb

NoSQL injection occurs when user-controlled input is improperly concatenated into a database query, allowing an attacker to alter query logic. In a Flask application using CockroachDB, the risk arises when the application builds queries for the CockroachDB SQL interface using string formatting or concatenation instead of parameterized patterns. CockroachDB speaks PostgreSQL wire protocol and accepts SQL; if Flask passes raw strings into SQL statements, special syntax such as ' OR '1'='1 can change predicate logic.

Consider a Flask route that authenticates a user by username. A vulnerable pattern might look like:

query = "SELECT id, password_hash FROM users WHERE username = '" + username + "'"
cur.execute(query)

If username is supplied by an attacker as ' OR 1=1 --, the executed SQL becomes SELECT id, password_hash FROM users WHERE username = '' OR 1=1 -- ', potentially bypassing authentication. CockroachDB will execute this SQL as written, returning all rows. Even with CockroachDB’s strict SQL compliance, injection is possible when input is not isolated from command structure.

Additional risks emerge when dynamic database or table names are used. For example, constructing object names via string interpolation exposes schema traversal or enumeration:

table_name = user_supplied_value
query = f"SELECT * FROM {table_name}"  # unsafe

An attacker may supply a value like users; DROP TABLE profiles; -- depending on client handling, attempting to manipulate multiple statements if the driver permits them. CockroachDB supports multiple statements in some drivers when not explicitly disabled, so unsanitized input can lead to unexpected execution paths.

Another vector involves JSON or KV operations. If Flask builds JSON-based filters by concatenating strings and then passes them to CockroachDB’s JSON functions, malformed or augmented JSON can bypass intended filters:

filter_json = '{"username": "%s"}' % user_input
cur.execute("SELECT data FROM tenant_data WHERE filter @> CAST($1 AS JSON)", (filter_json,))

If the injection modifies the JSON structure, it can change the filter semantics. While CockroachDB’s JSONB type enforces some validity, injection can still produce unintended matches or errors, revealing data layout.

Flask’s common use of request parameters for constructing queries amplifies these issues. Query strings, form fields, and headers can all feed into SQL if not rigorously validated. The absence of an ORM or strict query builder in some Flask projects increases the chance of ad-hoc SQL assembly with CockroachDB.

Remediation centers on strict separation of code and data. Always use parameterized queries with placeholders and let the CockroachDB driver handle escaping. For Flask with CockroachDB, adopt a driver that supports prepared statements and named parameters, such as psycopg2 or cockroachdb Python driver, and avoid string interpolation entirely.

Safe parameterized query example:

from flask import request
import psycopg2

def get_user(username):
    conn = psycopg2.connect(dsn="postgresql://user:pass@cockroachdb-host:26257/dbname")
    cur = conn.cursor()
    # Correct: parameter passed separately, not concatenated
    cur.execute("SELECT id, email FROM users WHERE username = %s", (username,))
    result = cur.fetchone()
    cur.close()
    conn.close()
    return result

For dynamic table or column names (which cannot be parameterized), enforce a strict allowlist:

allowed_tables = {"users", "products", "orders"}
table_name = request.args.get("table")
if table_name not in allowed_tables:
    return "Invalid table", 400
query = f"SELECT * FROM {table_name}"  # safe after validation
cur.execute(query)

When working with JSON filters, construct JSON safely using Python structures and pass them as parameters:

import json
from flask import request

user_filter = {"username": request.args.get("username")}
# Build JSON in Python, not via string formatting
filter_json = json.dumps(user_filter)
cur.execute("SELECT data FROM tenant_data WHERE filter @> CAST($1 AS JSON)", (filter_json,))

Use Flask extensions that encourage safe patterns. If you adopt an ORM or query builder compatible with CockroachDB, ensure it generates parameterized SQL. For raw SQL, always prefer placeholders ($1, $2) and pass values as a tuple or dict, never as part of the command string.

Finally, apply least-privilege database roles for Flask connections. A role with read-only access for SELECT queries reduces the impact of any residual injection. Combine this with input validation and allowlists to create defense-in-depth around CockroachDB in your Flask application.