Pii Leakage in Aspnet with Cockroachdb

Pii Leakage in an ASP.NET application using CockroachDB typically occurs when sensitive data is handled without adequate controls around storage, retrieval, and exposure through API endpoints. In this stack, risk arises not from CockroachDB itself, which is a distributed SQL database, but from how the ASP.NET layer interacts with it and what it returns to clients.

One common pattern is deserializing request data into entity models that include fields like Email, PhoneNumber, NationalId, or HealthStatus, then passing those models directly to CockroachDB queries without considering output filtering. If an endpoint returns a full entity to the client, PII such as government identifiers or contact details can be exposed unintentionally. For example, a GET /users/{id} endpoint might map query results to a User object containing sensitive attributes and serialize the entire object as JSON in the response.

Another vector is logging or error handling. ASP.NET middleware that logs query parameters or exception details can inadvertently capture PII before it reaches CockroachDB. If logs are centralized or retained for debugging, sensitive values like passwords, tokens, or personal messages may be stored in clear text. CockroachDB does not inherently redact these values; it stores what the application sends.

Connection string and configuration mishandling can also contribute. If connection strings or sensitive settings are stored in appsettings.json and accidentally exposed through debug endpoints or misconfigured static file middleware, an attacker who gains limited access can retrieve database credentials and explore data. While CockroachDB enforces strong internal authentication, the exposure of connection details from ASP.NET configuration can facilitate further reconnaissance.

Additionally, insufficient input validation allows injection or manipulation that may lead to excessive data retrieval. An attacker supplying unexpected query parameters or manipulating filters might cause the application to request broader result sets than intended, returning more PII than necessary. This aligns with common injection and data exposure patterns seen in the OWASP API Top 10.

In a middleBrick scan, such issues may surface as findings in Data Exposure, Input Validation, and Unsafe Consumption categories. The scanner evaluates how endpoints handle unauthenticated access and whether responses include sensitive fields without proper authorization or masking.

Remediation focuses on minimizing data exposure in the ASP.NET layer and enforcing strict data handling when communicating with CockroachDB. Below are targeted code examples using Npgsql, the standard .NET data provider for CockroachDB.

1. Select only required fields

Avoid mapping entire table rows to entities that contain PII. Instead, project into a DTO that includes only necessary, non-sensitive fields for the current context.

using var conn = new NpgsqlConnection("Host=my-cockroachdb.example.com;Port=26257;Database=mydb;SslMode=Require;Trust Server Certificate=true");
await conn.OpenAsync();

// Safe: select only public fields
var sql = "SELECT id, username, display_name FROM users WHERE id = @id";
await using var cmd = new NpgsqlCommand(sql, conn);
cmd.Parameters.AddWithValue("id", userId);
await using var reader = await cmd.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
    var userPublic = new
    {
        Id = reader.GetGuid(0),
        Username = reader.GetString(1),
        DisplayName = reader.IsDBNull(2) ? null : reader.GetString(2)
    };
    return Results.Ok(userPublic);
}

2. Parameterized queries to prevent injection and over-fetch

Always use parameters rather than string interpolation to avoid injection that could lead to unintended data exposure.

var sql = "SELECT email, phone FROM customers WHERE tenant_id = @tid AND status = @status LIMIT 100";
await using var cmd = new NpgsqlCommand(sql, conn);
cmd.Parameters.AddWithValue("tid", tenantId);
cmd.Parameters.AddWithValue("status", "Active");
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    // Process limited, necessary fields only
}

3. Mask or omit sensitive fields in responses

Create view models that exclude or transform PII before serialization. For fields that must be returned under strict controls, apply conditional inclusion.

public class UserProfileResponse
{
    public Guid Id { get; set; }
    public string Username { get; set; } = string.Empty;
    public string? DisplayName { get; set; }
    [JsonIgnore] // Or conditionally include via DTO mapping
    public string Email { get; set; } = string.Empty;
    [JsonIgnore]
    public string PhoneNumber { get; set; } = string.Empty;
}

// In controller
var user = await GetUserFromCockroachdb(userId);
var response = new UserProfileResponse
{
    Id = user.Id,
    Username = user.Username,
    DisplayName = user.DisplayName,
    // Email and PhoneNumber are omitted from JSON output
};
return Results.Ok(response);

4. Secure configuration and secrets management

Do not store connection strings in source-controlled config files. Use environment variables or a secrets provider and ensure they are not logged.

// Use environment variables and avoid logging connection details
var host = Environment.GetEnvironmentVariable("COCKROACH_HOST") ?? "localhost";
var password = Environment.GetEnvironmentVariable("COCKROACH_PASSWORD");
var connString = $"Host={host};Port=26257;Database=mydb;User ID=appuser;Password={password};SslMode=Require;Trust Server Certificate=true";

5. Structured logging without PII

Ensure logging frameworks do not capture sensitive request data. Configure filters to exclude PII fields.

// Example for ASP.NET Core
builder.Logging.ClearProviders();
builder.Logging.AddConsole(options => options.Filter = (category, scope) =>
{
    // Avoid logging sensitive state; implement custom filters as needed
    return true;
});

6. Use views or stored procedures for controlled access

Define database views that expose only non-sensitive columns and grant access to those views from ASP.NET, reducing the surface for accidental PII exposure.

-- CockroachDB SQL example
CREATE VIEW public.user_profiles AS
SELECT id, username, display_name, created_at FROM users;

-- In ASP.NET, query the view instead of the table
var sql = "SELECT id, username, display_name FROM public.user_profiles WHERE id = @id";

7. Validate and sanitize inputs

Apply strict validation on incoming query parameters and payloads to prevent over-fetching or manipulation that leads to unintended PII disclosure.

var email = "[email protected]";
if (!EmailValidator.IsValid(email))
{
    return Results.BadRequest("Invalid input");
}