Nosql Injection in Aspnet with Cockroachdb

NoSQL injection occurs when user-controlled input is improperly concatenated into database queries instead of being parameterized. In an ASP.NET application that uses CockroachDB, this often manifests through dynamic construction of SQL-like statements for the distributed SQL database. Because CockroachDB speaks a PostgreSQL-compatible wire protocol, many .NET developers use Npgsql or similar drivers and build queries by string concatenation or interpolation. This pattern is risky when input influences collection/table names, column filters, or ordering clauses.

Consider a profile lookup endpoint in ASP.NET Core that builds a query from an untrusted userId:

var sql = $"SELECT id, email, settings FROM profiles WHERE user_id = '{userId}'";
using var cmd = new NpgsqlCommand(sql, connection);
cmd.ExecuteNonQuery();

If userId is supplied by an attacker, they can alter the query logic. With CockroachDB, a crafted input such as abc' OR '1'='1 changes the intent to return all profiles. Worse, CockroachDB’s support for JSONB and array columns can amplify impact: an input like '; SELECT * FROM sensitive_logins -- can pivot to other statements if multiple queries are allowed or if the driver permits stacked queries.

ASP.NET’s default model binding does not sanitize inputs for NoSQL contexts; it only validates known types. This means developers must explicitly treat all inputs as untrusted. The distributed nature of CockroachDB does not inherently prevent injection; it simply means injected payloads are routed across nodes and can affect consistency or availability if the injected logic triggers heavy scans or cross-node joins. Additionally, dynamic sorting or filtering based on request parameters (e.g., orderBy or filters) can allow injection into clauses that are harder to parameterize, such as column identifiers or table names, which cannot be parameterized in standard SQL APIs.

Another common pattern is using dictionary-based filters translated into WHERE clauses. For example:

var conditions = new List();
var parameters = new NpgsqlParameterList();
int index = 0;
foreach (var filter in filters)
{
    conditions.Add($"{filter.Key} = @p{index}");
    parameters.Add($"@p{index}", filter.Value);
    index++;
}
var sql = $"SELECT * FROM accounts WHERE {string.Join(" AND ", conditions)}";

If filter.Key comes from the client, an attacker can supply email; DROP TABLE accounts -- as a key, leading to unexpected behavior. CockroachDB will execute the statement as written, and without strict input validation on field names, this can corrupt data or expose schema details through error messages.

In summary, the combination of ASP.NET’s flexible request handling and CockroachDB’s expressive SQL dialect creates a surface where NoSQL injection can occur when developers construct queries dynamically using unchecked input. The risk is not in the database engine itself but in how the application builds commands, especially when using string interpolation, concatenation, or unsafe handling of identifiers.

Remediation centers on strict separation of code and data. Always prefer parameterized queries for values, and avoid injecting untrusted input into identifiers or clauses that cannot be parameterized. Use an allowlist for ordering and filtering fields.

For the earlier profile lookup, switch to parameters for values:

var sql = "SELECT id, email, settings FROM profiles WHERE user_id = @userId";
using var cmd = new NpgsqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@userId", userId);
using var reader = await cmd.ExecuteReaderAsync();

This ensures userId is treated strictly as a string value, not executable SQL.

For dynamic filtering where column names must be dynamic, use an allowlist:

var allowedColumns = new HashSet(StringComparer.OrdinalIgnoreCase)
{
    "email", "created_at", "status"
};
if (!allowedColumns.Contains(column))
{
    throw new ArgumentException("Invalid column");
}
var sql = $"SELECT * FROM accounts ORDER BY {column}";

Note that even with allowlists, interpolation is used only for identifiers that are explicitly validated; values within the query should still use parameters.

When working with JSONB in CockroachDB, prefer typed parameters and avoid building JSON via string concatenation. Instead of:

var json = $"{{ \"preferences\": \"{userPref}\" }}";

Use Npgsql’s JSON support:

var sql = "UPDATE profiles SET preferences = @pref WHERE id = @id";
using var cmd = new NpgsqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@pref", NpgsqlDbType.Jsonb, preferencesJsonString);
cmd.Parameters.AddWithValue("@id", profileId);
await cmd.ExecuteNonQueryAsync();

This prevents JSON string injection and ensures proper encoding.

In ASP.NET Core, integrate these practices into service methods and use dependency injection for connection management. Validate inputs early in the pipeline, and apply strict allowlists for any dynamic SQL components. Regularly review logs for unexpected errors that might indicate probing attempts, and ensure error messages are generic to avoid leaking schema details.