There is a layer in my database called .
Somebody created it, presumably by accident, and it sat there for months looking harmless.
It was the only layer in the system that never served a single tile, and nobody noticed, because it was empty anyway.
That layer turned out to be a symptom of a SQL injection vulnerability.
This post is about the design that produced it — which I still think is a good design — and the four things I got wrong inside it.
The setup A web GIS with about 2.7 million features: 1.8 million points, 697,000 lines, 172,000 polygons.
Users create layers through the UI, upload data into them, edit geometry, and expect to see it on a map.
The features do not live in a table per layer.
They live in three tables — one for points, one for lines, one for polygons — with a foreign key and a JSON column for attributes: That's a deliberate trade.
A table per layer means DDL every time a user clicks "new layer", a migration story that never ends, and a schema that drifts.
Three generic tables mean one schema, one set of indexes, and layers that are just rows in a metadata table.
The cost lands on the tile server.
The pattern Martin serves vector tiles from PostGIS.
Point it at a database and it discovers spatial tables and views and publishes each as an MVT endpoint.
It can be told to publish views but not tables: So: give every layer its own view.
A Django signal on the model creates it: A user creates a layer in the browser.
Five seconds later — one — it is a live tile endpoint.
No migration, no deploy, no restart.
There are 106 of these now.
I still like this.
Everything below is what it cost.
Edge 1: the layer named The signal built the view name like this: is a filled in by users, with no validation on it.
It goes straight into DDL through an f-string.
A layer named executes on save, with whatever privileges the application's database role happens to hold.
I did not find this by thinking about attackers.
I found it because of the layer called , whose view was missing: An unquoted identifier can't start with a digit.
The exception was caught, logged, and swallowed, and that layer quietly had no tiles.
A user-supplied string that breaks SQL syntax is the same string that could complete it.
The failure was the tell.
The fix is : Rendered with a hostile name, the whole thing lands inside one quoted identifier with the quote doubled: And is now a perfectly legal view name, so that layer works too.
Edge 2: quoting changes your names This is the part that would have caused a bad afternoon if I'd shipped the obvious fix.
An unquoted identifier in Postgres is folded to lower case.
A quoted one is not.
Every one of those 106 views was created unquoted, so they're all lower case in the catalog — while 23 layers have capital letters in their names.
Switch naively to and stops resolving to the existing and creates a second view beside it.
The old one keeps existing.
Martin keeps publishing both.
Half your layers quietly fork.
So the derivation has to reproduce what Postgres was doing implicitly: That ASCII-only detail matters here: four layers have Cyrillic names, and would have renamed them.
The existing views were created by Postgres's rule, not Python's, and the two disagree outside ASCII.
I verified it before merging by running the new function over every layer and diffing against the catalog: 102 layers, 0 names changed.
That check took a minute and was the only thing standing between me and 23 orphaned views.
Edge 3: two code paths, two shapes There were two places that created these views: the signal, and a management command for bulk regeneration.
Over time they diverged.
The command included symbology columns for point styling; the signal didn't.
The database ended up holding two shapes of the same thing — 15 views with symbology columns, 24 identical point layers without.
That's not just untidy, because of a rule worth memorising: can add columns at the end.
It cannot remove them, and it cannot reorder them.
So saving a layer whose view had the "wrong" shape failed with .
Caught, logged, swallowed — and the view silently kept its old definition.
This had already caused a 500 on an unrelated endpoint, because the failed statement poisoned the caller's transaction.
Two fixes.
First, the column set is no longer a flag anyone can pass; it's derived from the geometry type, because only the point table has a column — the command had been passing for lines and polygons too, where it could only ever have failed.
Second, and this is the useful bit: the optional columns moved to the end of the SELECT list.
Because can append, 24 views could be brought into line with no interruption at all.
Only the 15 that needed reordering required + : Column order is invisible to clients — MVT attributes are named.
Choosing it deliberately turned most of a migration into a no-op.
Edge 4: the view namespace is global, the layer namespace wasn't Layer names were unique per project.
View names are unique per database.
So two projects both had a layer called .
Both mapped to one view.
Whichever was saved last owned it, and the other layer served the wrong project's features — with no error anywhere, because from Postgres's point of view nothing was wrong.
The tempting fix is to rename the views: instead of .
It's correct, and it's a coordinated deploy — tiles are requested by name, so every client has to change on the same day.
The cheaper fix follows from noticing why the frontend works at all: it builds the tile URL from the layer name it reads from the API.
If layer names are unique, tile addresses are unique for free.
So the constraint belongs on the layer name, not on a new naming scheme: Comparing the derived name, so and collide, and so do and .
One of the two existing duplicates was an empty layer in a test project; renaming it cost nothing, and the frontend followed automatically because it reads the name from the API rather than remembering it.
While I was in there: renaming a layer created a view under the new name and left the old one behind, publishing a source no layer pointed at.
A now remembers the previous name so can drop it — unless another layer is using it.
Would I build it this way again Yes, with the edges filed off.
A generic feature table plus a view per layer gets you user-created layers that become tile endpoints in seconds, without DDL migrations or deploys, and Martin's does the discovery for free.
For an internal tool where people create layers as part of their work, that's the right shape.
But it means your users write DDL identifiers, indirectly, by typing a name into a form.
Once you accept that, four things follow, and I got all four wrong first: Compose DDL with , never an f-string.
The failure that reveals it may look like a syntax error, not an attack.
If you're adding quoting to something that ran unquoted, reproduce the old folding exactly and diff every existing name before you ship.
Put optional columns last, so can add them without a drop.
Check whether your derived namespace is wider than the namespace you enforce uniqueness in.
Ours was, by exactly one level.
The layer named serves tiles now.
It's still empty.