Excluding IP-ranges

[Snedker]

Hi there,

I really need some input of how to approach my little assignment.

A customer wants to exclude all US IP-ranges from accessing part of
his website. From http://www.ipaddresslocation.org/ I've collected
about 16,000 ranges in the format

208.202.120.0 208.203.111.255
208.203.114.0 208.203.244.127
....

I don't yet know how many individual IP's then ranges totals, but I
reckon we speak millions.

Now, when a user hits that certain webpage a lookup should be
performed, checking if user is in a range. If he is, he's banned from
that part of the page.

My idea is to save to a table (MSSQL) that contains 4 columns - one
column for each part of the IP. When I import the ranges I save each
IP in that interval, ending up with all (or so) current US IP-
addresses. Each of the four columns are indexed.

A lookup will then use a phonebook-kinda-lookup (SELECT COUNT(*) FROM
myTable WHERE col1=62 AND col2=63 AND col3=64 and col4=65). If it
returns 0, user is allowed.

Is that a crappy approach, or should I consider something else?


Thanks in advance for any input.

Regards /Snedker

[Gaurav Vaish \(a.k.a. MasterGaurav\)]

Re: Excluding IP-ranges

Hi there,

>
I really need some input of how to approach my little assignment.
>
A customer wants to exclude all US IP-ranges from accessing part of
>
I don't yet know how many individual IP's then ranges totals, but I
reckon we speak millions.

Check with www.apnic.net

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Re: Excluding IP-ranges

Snedker wrote:

Hi there,
>
I really need some input of how to approach my little assignment.
>
A customer wants to exclude all US IP-ranges from accessing part of
his website. From http://www.ipaddresslocation.org/ I've collected
about 16,000 ranges in the format
>
208.202.120.0 208.203.111.255
208.203.114.0 208.203.244.127
...
>
I don't yet know how many individual IP's then ranges totals, but I
reckon we speak millions.
>
Now, when a user hits that certain webpage a lookup should be
performed, checking if user is in a range. If he is, he's banned from
that part of the page.
>
My idea is to save to a table (MSSQL) that contains 4 columns - one
column for each part of the IP.

First of all, you should convert each IP address to a single 32 bit
value. There is no reason to compare four values when you can compare one.

When I import the ranges I save each
IP in that interval, ending up with all (or so) current US IP-
addresses. Each of the four columns are indexed.

If only 10% of the IP range is allocated to US, that makes a table with
430 million records...

You should definitely store the ranges, not the individual IP addresses.

A lookup will then use a phonebook-kinda-lookup (SELECT COUNT(*) FROM
myTable WHERE col1=62 AND col2=63 AND col3=64 and col4=65). If it
returns 0, user is allowed.
>
Is that a crappy approach, or should I consider something else?
>
>
Thanks in advance for any input.
>
Regards /Snedker

--
Göran Andersson
_____

http://www.guffa.com

[Mark Rae [MVP]]

Re: Excluding IP-ranges

"Snedker" <Morten.Snedker @gmail.comwrote in message
news:a083ee3c-f210-4adf-acbe-834f1acf2f27@a1 g2000hsb.google groups.com...

Is that a crappy approach,

Certainly not particularly efficient...

or should I consider something else?

As Göran has suggested, you should store the ranges as int, not char /
varchar - that will make the entire comparison logic a breeze.

Then, create a stored procedure which accepts a regular IP address as a
parameter and returns the number of "hits" against your table - so long as
the IP address ranges don't overlap, the only possible return values should
be 0 or 1.

Everything you need is here:

http://sqlserver2000.databases.aspfaq.com/how-should-i-store-an-ip-address-in-sql-server.html

--
Mark Rae
ASP.NET MVP

http://www.markrae.net