hupden/ projects
blogtools
← blog

Sorting the page you fetched isn't sorting the data

#go#postgres#sql#api-design

My admin dashboard has a ban-history table per fail2ban jail: address, when it was banned, for how long, and how many times. The API returns at most 200 rows, newest first. I wanted the columns sortable, mostly so I could click "ban count" and see the worst repeat offenders.

The first draft did it the way most tables do: fetch the rows once, sort the array in the browser when a header is clicked. It rendered correctly. It sorted correctly. It would have passed any test I'd have thought to write. I caught it in review, and it never shipped. But it's worth writing up, because it is one of the most common ways to be confidently wrong in a dashboard.

What the sort actually ranked

The API query was, in effect:

SELECT ... FROM fail2ban_bans
 WHERE jail = $1
 ORDER BY banned_at DESC
 LIMIT 200

The LIMIT picks which 200 rows exist in the browser, and it picks them by recency. Sorting that array by ban count doesn't find the worst repeat offenders. It finds the worst repeat offenders among the 200 most recent bans, and nothing on screen says so.

That's not a subtle difference:

It's also invisible to testing. With fewer rows than the cap, fetch-then-sort and sort-then-fetch give identical results. The bug only exists above the cap, and test fixtures are almost never that large. A test suite of small fixtures isn't weak evidence here. It's no evidence.

The fix: sort where the limit is

The ordering has to happen before the cap, which means in the query:

ORDER BY <chosen column> <direction>, banned_at DESC, ip
LIMIT 200

Now 200 is "the top 200 by the thing you asked for, ranked across the jail's whole history", and clicking a header re-runs the query instead of reordering local state.

Three details made this more than a one-line change.

ORDER BY can't be a bind parameter

Placeholders bind values. A column name or ASC/DESC is part of the statement's structure, so ORDER BY $2 doesn't do what you want: it orders by a constant. The sort key has to be interpolated into the SQL string, and interpolating anything from a request into SQL is how injection happens.

The answer is to never interpolate the request. Interpolate a lookup result instead:

var historySortExprs = map[string]string{
	"banned_at":        "banned_at",
	"ip":               ipOrderExpr,
	"ban_time_seconds": "ban_time_seconds",
	"ban_count":        "ban_count",
}

var historySortDirs = map[string]string{"asc": "ASC", "desc": "DESC"}

The query parameter is only ever used as a map key. Whatever reaches the SQL string is a literal I wrote in that file. The map is the security boundary, and adding a sort means writing its SQL by hand, which is the intent.

A miss is a 400, not a quiet fallback to the default sort. Serving a different sort than the one requested is the same class of bug this change existed to remove: a plausible-looking answer to a question nobody asked.

One detail about Go worth knowing: since Go 1.17, url.Query() silently drops any parameter containing a ;. A ;-bearing payload therefore arrives as no sort parameter and gets the default. That's safe here, and there's a test pinning it, because it would stop being safe if this ever moved to hand-rolled query parsing.

Ties need a tiebreaker when there's a LIMIT

Sorting by ban count produces huge ties: most addresses have been banned once. Without a secondary key, which of the tied rows land inside the 200 is up to the planner, and can differ from one request to the next. That's the same category of bug again: the result depends on something nobody chose. Hence banned_at DESC, ip after the chosen column. It isn't the sort; it makes the cut deterministic.

Sorting addresses without trusting them

The ip column is text, and text sorts 10.0.0.10 before 10.0.0.9. The obvious fix is ORDER BY ip::inet, which orders addresses properly, and I deliberately didn't use it: the cast raises an error on any value that isn't a valid address. One malformed row, from a parser bug or an odd log line upstream, would take out the entire endpoint rather than sort a little oddly.

So the IP sort pads each dotted-quad octet with lpad(split_part(ip, '.', n), 3, '0'), groups IPv4 ahead of IPv6, and falls back to the raw string for ties. It's less precise than inet for IPv6, and it can't fail on any input. On a dashboard, "degrades" beats "500s".

The label had to change, not just the code

The table used to say "max 200". After the fix that wording is wrong, and not because 200 changed. It described a fixed page of the newest 200 rows, and that's no longer what the endpoint returns.

It now shows a note only when the response comes back exactly full: capped at 200 rows — these are the top 200 by ban count (desc), ranked across all of this jail's history. It doesn't claim more rows exist, because a jail with exactly 200 bans hits the same condition.

Then a small UX consequence: since each header click is now a fetch, the table dims while the new ranking loads, rather than collapsing to a spinner and losing the scroll position on every click.

The general form

Any time an API caps its results, the cap defines a window, and every operation done after the fetch, whether sort, filter or search box, operates on that window and not on the data. Client-side filtering has exactly the same bug: a search box over a fetched page finds matches in the page, and "no results" means "none in the newest 200".

The fix is always the same: do the ordering and filtering where the limit is applied. And if you can't, say on screen what the window is, so the one answer you're giving isn't mistaken for the other.