simonw / simonw/datasette

Add limit on the size in KB of data returned from a single query

Open
#235 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

medium
Dominant language
Python
Stars
11.5k
Forks
904
Avg merge
4d 17h
Merged PRs (30d)
18

Description

Datasette limits the number of rows returned to 1,000 and limits the time spent executing a SQL query to 1000ms - and both of these limits can be customized.

It does not have a limit on the size of the response returned. It's possible to compose maliciously large SQL responses in a small number of rows using mechanisms like the group_concat() aggregate function. It would be good to avoid malicious SQL creating 100MB+ responses and potentially crashing the server.

I think the easiest place to implement that is here:

https://github.com/simonw/datasette/blob/f3f42957128c1e7ece584d45d9167f2ac003a3b8/datasette/app.py#L175-L190

Currently we use cursor.fetchmany() to fetch up to 1,001 rows at once. Instead, we could switch to iterating through cursor.fetchone() (or just using for row in cursor) and keeping a running tally of the size of the response as we go - maybe just using rough_response_size += len(str(row)). If that goes above a certain threshold we can terminate the response with an error, like we do with timelimits.

The bigger challenge here is understanding how well this approach works and what impact it will have on overall Datasette performance. I think I need #33 for this.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start in datasette/app.py at the linked lines, where cursor.fetchmany() currently retrieves up to 1,001 rows, and review the related work in issue #33. Determine how to track response size while iterating rows and how the limit should terminate the response, then assess the performance impact. Done means oversized query responses are rejected without affecting normal results or the existing row and time limits.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sql, sqlite
Domain
api, backend, security
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.