OOmnistu

Blog / Web & Ecommerce

Web & Ecommerce

Scope every data export to the signed-in account

Scope data exports to the authenticated account, not the request. Executed SQLite fixtures check cross-account IDs, anonymous refusal and malformed input.

OOmnistu
Reviewed 2026-11-18 · 5 min read

A "download my data" button looks harmless. The user picks some records, the server builds a file, and the file arrives. The risk sits in one detail: which records does the query actually read? If the export endpoint trusts an account ID or a list of record IDs from the request and does nothing else, one account can ask for another account's rows by changing a number.

OWASP ranks this class of flaw, broken object level authorization, as the first risk in its 2023 API Security Top 10. Its guidance is direct: every function that takes a client-supplied record ID needs an authorization check, and the check should depend on the authenticated user's policy, not on the ID alone. The OWASP Authorization Cheat Sheet adds that permissions should be enforced on every request and that access should be denied by default.

This article tests one small piece of that advice: the database query behind an export. It does not test login, sessions, download links or a real Omnistu endpoint. The article and its fixture were prepared with AI assistance and reviewed on 18 November 2026.

Take the account from the principal, not the request

The fixture has a documents table with three rows. Rows 1 and 3 belong to account 10. Row 2 belongs to account 20. The export function receives a principal, meaning the authenticated caller, and a list of requested IDs.

def export_rows(db, principal, requested_ids):
    if principal is None:
        raise PermissionError('authentication required')
    if (not isinstance(requested_ids, list) or len(requested_ids) > 100
            or any(type(x) is not int for x in requested_ids)):
        raise ValueError('bounded integer IDs required')
    ids = list(dict.fromkeys(requested_ids))
    if not ids:
        return []
    marks = ','.join('?' for _ in ids)
    return db.execute(
        'SELECT id,title FROM documents WHERE account_id=? '
        f'AND id IN ({marks}) ORDER BY id',
        (principal['account_id'], *ids)).fetchall()

Three choices matter here.

The account ID comes from principal, which the server produced after authentication. The request body never supplies it. If a client could send its own account_id, the filter would only prove what the client claimed.

The account condition is part of the same WHERE clause as the ID list. The code does not fetch rows by ID and then try to remove the wrong ones afterward. A row the caller may not see never leaves the database in this path.

The ID values are bound as parameters. Only the number of placeholders is built into the SQL text, and that number comes from a list the function has already limited to 100 integers.

What the fixture checks

The script runs seven normal cases and several refusals:

Booleans deserve a note. In Python, True is an instance of int, so a plain isinstance check would accept it as ID 1. The fixture uses type(x) is int to refuse it.

The last assertion reads the table again and confirms it still holds three rows. The test output on Python 3.10.12 with SQLite 3.37.2 reports all cases passing.

Decide what a denied ID looks like

The fixture returns an empty result for an ID the caller does not own. That is the same answer as for an ID that does not exist, which avoids confirming that a private record is present. A real API may choose a different contract, for example a 403 for a request that names forbidden IDs. Pick one behavior, document it, and make sure it does not leak existence through different messages or timing you can easily avoid.

Ordering matters for a separate reason. A stable ORDER BY id keeps export files predictable between runs. If you later add paging with LIMIT and OFFSET, the PostgreSQL documentation warns that results are unpredictable without an ORDER BY that makes the order unique. Keep the ownership filter in the paged query too, so page 2 cannot widen the scope that page 1 had.

What this does not prove

A passing query test is one layer. It does not show that the endpoint authenticates callers correctly, that a generated file is stored under an unguessable name, or that the file link expires and is bound to the account. It does not cover roles inside an account, such as a viewer who may read some documents but not export them. It uses SQLite and synthetic rows, not a production database, and it makes no claim about performance or any real incident.

Treat it as a regression test for the narrowest and most common mistake: a lookup by ID with no owner condition. Add tests at the endpoint and storage layers for the rest.

Sources