better way for IP comparison

checking for banned IPs

[LifeinAsia]

(Using MS-SQL 2000.)
We have a table of "banned" IP addresses. Each row contains the first IP of the block and the last IP of the block. Both fields are bigint fields with the IPs converted from dot notation to integer (i.e., "a.b.c.d" is converted to a*256^3 + b*256^2 + c*256 +d).

The list of IPs to match against the table are stored in another table as varchar(16).

Currently, we loop through each row of the list to check, convert to integer, then check if the result is within any of the ranges in the table. All this is done in a stored procedure using fetch to loop through the table to match. This is a nightly procedure matching our log files against the banned IPs table.

Is there a better/more efficient way to do this?

[aspdaddy]

Maybe it would be faster to store each banned ip in a single column, unique, indexed table (same datatye as the source IPs) and then just check to see it exists.

If that works for storage then you could optimise the search it by using a set based set solution instead of the fetch loop.

SELECT Banned.IP FROM Banned INNER JOIN Todays
ON Banned.IP = Todays.IP

Would give the matching list.

Although you might now need a loop to create the banned ranges!

[FalseDawn]

This is a nightly procedure matching our log files against the banned IPs table.

I don't understand why you are matching log file IPs against a "banned" list. Is it just for statistical purposes?
Couldn't you just use your list of banned IPs in iptables or something to block them at the firewall level?

[LifeinAsia]

don't understand why you are matching log file IPs against a "banned" list.

It's a list of banned IPs from one of our advertisers- they don't pay for traffic clicks from those IPs. But others do, so we don't want to do a wholesale ban of those IPs- just remove those IPs from the revenue totals for that partner.

In regards to putting the actual IPs in a table isntead of the ranges, that's something I've thought about as well. It's probably a little more efficient than what we're doing now. I was just hoping for a magic bullet that would give a much bigger efficiency boost.

[FalseDawn]

ah, ok.
I don't think you need to store each individual IP in a table for a set based solution. You could use a subquery - something like:

SELECT ip FROM logtable L WHERE EXISTS (SELECT * FROM banned_ips WHERE ip_low<=L.ip and ip_hi>=L.ip)

Of course, you would need a quick query to convert all your varchar IPs in your logtable to numerics (the [ip] column in the query above, but this is easy.)
Assume ip_low and ip_hi are the lower and upper numeric IP ranges.

The query would give you those IPs that fall within a banned range and would be fast if ip_low and ip_hi were indexed.

[edited by: FalseDawn at 2:17 am (utc) on Nov. 30, 2006]

[aspdaddy]

Thats looks correct, but Im not convinced it would scale very well for large numbers.