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.
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:
- Account 10 asks for row 1 and gets row 1.
- Account 10 asks for row 2, which belongs to account 20, and gets an empty result.
- Account 10 asks for rows 1, 2 and 3 and gets rows 1 and 3 only.
- Account 20 asks for the same three IDs and gets row 2 only.
- Duplicate and out-of-order IDs (3, 1, 1) return each row once, in ID order.
- An empty list and an unknown ID both return an empty list.
- An anonymous caller is refused.
- Five malformed inputs are refused: a string ID, a boolean, 101 IDs, a dictionary inside the list, and a value that is not a list.
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
- OWASP API Security Top 10 2023, API1: Broken Object Level Authorization: https://owasp.org/API-Security/editions/2023/en/0xa1-broken-object-level-authorization/
- OWASP Authorization Cheat Sheet: https://cheatsheetseries.owasp.org/cheatsheets/Authorization_Cheat_Sheet.html
- PostgreSQL documentation, LIMIT and OFFSET: https://www.postgresql.org/docs/current/queries-limit.html