Fix missing object-level authorization (IDOR) in C# and ASP.NET Core

public Task<Invoice?> FindAsync(ClaimsPrincipal user, long id) =>
    Task.FromResult(_db.Query(command =>
    {
        command.CommandText = """
            SELECT id, tenant_id, owner_id, number, amount_cents, status FROM invoices
            WHERE id = $id
            """;
        command.Parameters.AddWithValue("$id", id);
    }).SingleOrDefault());

public Task<IReadOnlyList<Invoice>> ListAsync(ClaimsPrincipal user) =>
    Task.FromResult<IReadOnlyList<Invoice>>(_db.Query(command =>
    {
        command.CommandText = """
            SELECT id, tenant_id, owner_id, number, amount_cents, status FROM invoices
            WHERE tenant_id = $tenant
            ORDER BY id
            """;
        command.Parameters.AddWithValue("$tenant", user.FindFirstValue(InvoiceClaims.Tenant));
    }));

public Task<IReadOnlyList<Invoice>> ExportAsync(ClaimsPrincipal user, IReadOnlyList<long> ids) =>
    Task.FromResult<IReadOnlyList<Invoice>>(_db.Query(command =>
    {
        command.CommandText = """
            SELECT id, tenant_id, owner_id, number, amount_cents, status FROM invoices
            WHERE id IN (SELECT value FROM json_each($ids))
            ORDER BY id
            """;
        command.Parameters.AddWithValue("$ids", JsonSerializer.Serialize(ids));
    }));

// Members read their own invoices; billing admins read every invoice in their own
// tenant. The tenant test sits outside the parentheses, so it binds admins too:
//   tenant_id = $tenant AND (owner_id = $user OR $isBillingAdmin)
private static void BindScope(SqliteCommand command, ClaimsPrincipal user)
{
    command.Parameters.AddWithValue("$tenant",
        user.FindFirstValue(InvoiceClaims.Tenant) ?? throw new InvalidOperationException("no tenant claim"));
    command.Parameters.AddWithValue("$user",
        user.FindFirstValue(ClaimTypes.NameIdentifier) ?? throw new InvalidOperationException("no user id claim"));
    command.Parameters.AddWithValue("$isBillingAdmin", user.IsInRole(InvoiceClaims.BillingAdmin));
}

public Task<Invoice?> FindAsync(ClaimsPrincipal user, long id) =>
    Task.FromResult(_db.Query(command =>
    {
        command.CommandText = """
            SELECT id, tenant_id, owner_id, number, amount_cents, status FROM invoices
            WHERE id = $id AND tenant_id = $tenant AND (owner_id = $user OR $isBillingAdmin)
            """;
        command.Parameters.AddWithValue("$id", id);
        BindScope(command, user);
    }).SingleOrDefault());

public Task<IReadOnlyList<Invoice>> ListAsync(ClaimsPrincipal user) =>
    Task.FromResult<IReadOnlyList<Invoice>>(_db.Query(command =>
    {
        command.CommandText = """
            SELECT id, tenant_id, owner_id, number, amount_cents, status FROM invoices
            WHERE tenant_id = $tenant AND (owner_id = $user OR $isBillingAdmin)
            ORDER BY id
            """;
        BindScope(command, user);
    }));

public Task<IReadOnlyList<Invoice>> ExportAsync(ClaimsPrincipal user, IReadOnlyList<long> ids) =>
    Task.FromResult<IReadOnlyList<Invoice>>(_db.Query(command =>
    {
        command.CommandText = """
            SELECT id, tenant_id, owner_id, number, amount_cents, status FROM invoices
            WHERE id IN (SELECT value FROM json_each($ids))
              AND tenant_id = $tenant AND (owner_id = $user OR $isBillingAdmin)
            ORDER BY id
            """;
        command.Parameters.AddWithValue("$ids", JsonSerializer.Serialize(ids));
        BindScope(command, user);
    }));

private static readonly OperationAuthorizationRequirement Read = new() { Name = nameof(Read) };

/// <summary>Owners may read their invoices; billing admins may read any invoice.</summary>
public sealed class ReadHandler : AuthorizationHandler<OperationAuthorizationRequirement, Invoice>
{
    protected override Task HandleRequirementAsync(
        AuthorizationHandlerContext context, OperationAuthorizationRequirement requirement, Invoice invoice)
    {
        if (invoice.OwnerId == context.User.FindFirstValue(ClaimTypes.NameIdentifier)
            || context.User.IsInRole(InvoiceClaims.BillingAdmin))
        {
            context.Succeed(requirement);
        }
        return Task.CompletedTask;
    }
}

public async Task<Invoice?> FindAsync(ClaimsPrincipal user, long id)
{
    var invoice = _db.Query(command =>
    {
        command.CommandText = """
            SELECT id, tenant_id, owner_id, number, amount_cents, status FROM invoices
            WHERE id = $id
            """;
        command.Parameters.AddWithValue("$id", id);
    }).SingleOrDefault();
    if (invoice is null)
    {
        return null;
    }
    var result = await _authorization.AuthorizeAsync(user, invoice, Read);
    return result.Succeeded ? invoice : null;
}

public Task<IReadOnlyList<Invoice>> ListAsync(ClaimsPrincipal user) =>
    ReadableAsync(user, _db.Query(command =>
    {
        command.CommandText = """
            SELECT id, tenant_id, owner_id, number, amount_cents, status FROM invoices
            ORDER BY id
            """;
    }));

public Task<IReadOnlyList<Invoice>> ExportAsync(ClaimsPrincipal user, IReadOnlyList<long> ids) =>
    ReadableAsync(user, _db.Query(command =>
    {
        command.CommandText = """
            SELECT id, tenant_id, owner_id, number, amount_cents, status FROM invoices
            WHERE id IN (SELECT value FROM json_each($ids))
            ORDER BY id
            """;
        command.Parameters.AddWithValue("$ids", JsonSerializer.Serialize(ids));
    }));

private async Task<IReadOnlyList<Invoice>> ReadableAsync(ClaimsPrincipal user, List<Invoice> invoices)
{
    var readable = new List<Invoice>();
    foreach (var invoice in invoices)
    {
        if ((await _authorization.AuthorizeAsync(user, invoice, Read)).Succeeded)
        {
            readable.Add(invoice);
        }
    }
    return readable;
}

// Every route needs a signed-in user. The ClaimsPrincipal comes from the authentication
// middleware; nothing here reads a user, tenant or role from the request.
var invoices = app.MapGroup("/invoices").RequireAuthorization();

invoices.MapGet("/", async (ClaimsPrincipal user, IInvoiceService service) =>
    Results.Ok(await service.ListAsync(user)));

// Missing and not yours get the same answer, so IDs cannot be probed.
invoices.MapGet("/{id:long}", async (long id, ClaimsPrincipal user, IInvoiceService service) =>
    await service.FindAsync(user, id) is { } invoice ? Results.Ok(invoice) : Results.NotFound());

invoices.MapGet("/export", async (string ids, ClaimsPrincipal user, IInvoiceService service) =>
{
    if (!InvoiceIds.TryParse(ids, out var requested))
    {
        return Results.BadRequest(new { error = $"ids must be 1 to {InvoiceIds.Max} invoice IDs, comma-separated" });
    }
    var rows = await service.ExportAsync(user, requested);
    // All or nothing: one missing or unreadable ID refuses the whole export, so a
    // partial file is never mistaken for the full one.
    return rows.Count == requested.Count
        ? Results.Text(InvoiceCsv.Format(rows), "text/csv", Encoding.UTF8)
        : Results.NotFound();
});