I had 1,186 blacklisted IP ranges sitting in a database table. In the middle of an active scraper attack I went to look at how they were enforced, and found out they weren’t. The stored procedure that runs on every request, the one that’s supposed to be the gate, never reads that table. The ranges were intelligence, not enforcement. A fresh scraper coming from a range I had already flagged sailed straight through, every time, because nothing on the request path ever asked the question I thought it was asking.

This is a Windows / IIS / MSSQL stack. Classic ASP front end, stored procs doing the per-request work, the whole thing running on a box that already runs hot on tournament days. So the failure mode here isn’t exotic. It’s the most boring possible bug: a table that looks load-bearing, that everybody assumes is load-bearing, that nothing actually loads from.

Two tables, one of them lying

The per-request filter lives in the IP filter module, which calls a stored proc. On paper this is the bouncer. Every request comes in, the SP runs, blocked traffic gets a 302 and bounced.

Here is what the SP actually checks: the per-IP blocked-addresses table. One flag per exact IP address. Is this specific address blocked yes or no. That’s the whole question it asks.

Here is what it does not check: the IP-ranges table. That’s the 1,186-row table. Ranges, not individual addresses. CIDR-ish blocks of address space I’d flagged over time as belonging to scrapers, hosting providers, abusive ASNs. The table where all my accumulated “these networks are trouble” knowledge lived.

The SP never joins to it. Never reads it. So the logic in practice is: “is this one specific IP, that I have personally seen and flagged before, in the blocked list?” And a scraper’s whole job is to not be an IP you’ve seen before. Distributed scrapers pull fresh IPs from a hosting provider’s pool. Each one hits you once or twice and is never seen again. Every one of them belongs to a range I had flagged. None of them was an IP in the per-IP table. So the gate, asking the wrong question, waved all of them through.

The table wasn’t enforcing anything. It was decorative. It made the database look like it had an opinion about these ranges, and that look was the whole problem, because it let me believe the gate was closed when it was wide open.

Where the enforcement actually was

The blocking that did work was somewhere else entirely. Windows Firewall, roughly 190 rules covering about 27,000 ranges. A different layer, a different mechanism, maintained by a completely different process than the DB table. The 1,186 rows in the IP-ranges table and the 27k ranges in the firewall were two separate universes that nobody had reconciled.

That gap between the two is exactly the hole the attackers walked through. The DB “knew” about ranges the firewall didn’t, and the DB’s knowledge was inert. So a range could be flagged in the database, look handled on a dashboard, and have zero effect on a single packet.

You can have two security surfaces, both real, both populated, and still have a wide-open door, because the populated one isn’t on the path and the path one isn’t reconciled with it.

The cost differential decides the strategy

Once you know the enforcement lives at two possible layers, app or TCP, the next question is which one to push the block into. During the incident I got a clean side-by-side measurement, and it’s the most useful number from the whole episode.

I re-blocked about 70,000 rows in the per-IP table that had been auto-cleared (there was a separate decay bug quietly un-blocking things, which is its own story). Re-flagging those rows dropped CPU about 15 points. Real, but modest. And it makes sense why it’s modest: the SP still runs on every request. It still has to look the IP up, decide it’s blocked, and emit a 302. The work of recognizing and rejecting each request still happens inside IIS, on your CPU, request after request.

Then I added one correctly-scoped Windows Firewall rule against the range the swarm was actually using. That dropped CPU about 60 points.

Same conceptual goal, blocking bad traffic, four times the relief, because of where the packet dies. A TCP-dropped packet costs zero IIS CPU. The request never becomes a request. There’s no ASP, no SP, no 302, no log write. The kernel drops it and moves on. The app-layer block, by contrast, pays the full price of admitting the connection just to turn it away politely.

So under load the rule is simple: the cheapest block is the one that happens earliest, at the lowest layer. App-layer filtering is fine for precision work. It is the wrong tool when you’re trying to shed load, because the load is the cost of running the filter at all.

Why I didn’t just fix the SP

The obvious response is “okay, make the SP consult the IP-ranges table too.” And that’s not wrong as a direction, but it’s genuinely non-trivial here.

It’s 1,186 rows, evaluated against every request, on a box already CPU-bound during exactly the moments you most need the check. A naive range lookup per request is a per-request cost multiplier on the hottest path in the system, during the attack, on hardware that’s already pinned. The cure can deepen the symptom. Range matching on every request is something you design carefully, with the right indexing and the right short-circuiting, not something you bolt on at 1 AM while the site is slow.

There’s also operational friction specific to this stack that shaped how any fix had to land. The stored procs aren’t in source control. Changes go in through a remote ODBC connection wrapped in an explicit transaction: smoke-call the change then roll back to prove it parses and behaves, then re-run and commit. Concretely:

-- Deploy an SP change with no source control: prove it, then commit.
-- Run over the remote ODBC connection, wrapped in one explicit transaction.
BEGIN TRANSACTION;


ALTER PROCEDURE dbo.usp_IpFilter_Check  -- your edited body here
AS
BEGIN
  -- ... new logic that also consults the IP-ranges table ...
END;


-- Smoke-call against a known-bad IP from a flagged range and a known-good IP.
EXEC dbo.usp_IpFilter_Check @ip = '203.0.113.7';   -- expect: blocked
EXEC dbo.usp_IpFilter_Check @ip = '198.51.100.20'; -- expect: allowed


-- Inspect the results. If anything is off:
ROLLBACK TRANSACTION;   -- nothing persisted, proc reverts to prior body
-- Only when the smoke calls behave:
-- COMMIT TRANSACTION;

None of that is a reason to skip the fix. It’s a reason the fix is a deliberate piece of work and not a one-liner, and that’s exactly how a decorative table stays decorative for a long time. The friction protects the comforting lie.

So go check yours before the next attack does it for you. Pick one IP that sits inside a range your table already flags, then read the actual enforcement path and confirm it queries that range table rather than only the per-IP flag. If the range table is never named anywhere on the code path a live request walks, it is decorative no matter how many rows it holds. When you wire it in, deploy the procedure change inside an explicit transaction, smoke-call it against one known-bad and one known-good address, and commit only once both come back the way you expect.