CODEWITHSUNDEEP
sql-server
Mike Smith
Mike Smith

Reputation: 642

SQL to find IP Address in Subnet

We recently started using the MaxMind Geolite Database as a lookup for cities based on IP. There are plenty of instructions for getting the data into SQL Server (which I've done). Now I need to figure out how to search for a given IP within a subnet.

DB Schema:

CREATE TABLE GeoIP (   
    network varchar(20) not null,
    geoname_id varchar(20) not null,
    registered_country_geoname_id varchar(20) not null,
    represented_country_geoname_id varchar(20) not null,
    is_anonymous_proxy int,
    is_satellite_provider int,
    postal_code varchar(20),
    latitude Decimal(9,6),
    longitude Decimal(9,6),
    accuracy_radius int
);

The 'network' column has row data with IP/Subnet (ex: 1.0.32.0/19, 1.0.64.0/20, 1.0.80.0/22)

Given a single IP address, I'm trying to write a SELECT statement that will return the geoname_id.

Ex: SELECT geoname_id FROM GeoIP where @user_ip in {some expression}

I'd like to do this without having to explode out the network column into a 'low_ip' and 'high_ip' BIGINT columns. But, if that is the only way to do it, I could also use some help on how to write a global UPDATE statement to add the those columns from the existing data.

Need this for SQL SERVER 2008 so can't use any of the cool Postgres, etc. functionality.

Thanks!!

Upvotes: 3

Views: 9759

Answers (1)

duckbenny
duckbenny

Reputation: 739

From your network column you can already see the number of bits in the netmask and with the help of a little bitwise arithmetic, easily detect if a user ip falls within that network. Therefore I would suggest you split that column into its (binary) network ip and its cidr number.

Let me explain. If we take the first example you provided (10.0.32.0/19) we can see that it's netmask (the "/19" bit) is represented in binary as 19 ones with all the other bits set to zero:

11111111 11111111 11100000 00000000

Let's take a sample user ip of 1.0.32.56:

00000001 00000000 00100000 00111000

You can see that if you take the bitwise AND of that /19 netmask along with the user ip, you'll end up with:

00000001 00000000 00100000 00000000

...which converts to dotted quads as 1.0.32.0. Look familiar?

Anyway, here's the approach I'd take for your problem. First of all we need to use a udf to convert the ip address to binary. I'm shamelessly stealing the one from this answer:

CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END
GO

I also find it helpful to have all of the netmasks in a small lookup table:

CREATE TABLE netmask (
    bits TINYINT PRIMARY KEY,
    binary_mask BINARY(4) NOT NULL
)

INSERT INTO netmask (bits, binary_mask) VALUES
    ( 0, 0x00000000), ( 1, 0x80000000), ( 2, 0xc0000000), ( 3, 0xe0000000),
    ( 4, 0xf0000000), ( 5, 0xf8000000), ( 6, 0xfc000000), ( 7, 0xfe000000),
    ( 8, 0xff000000), ( 9, 0xff800000), (10, 0xffc00000), (11, 0xffe00000),
    (12, 0xfff00000), (13, 0xfff80000), (14, 0xfffc0000), (15, 0xfffe0000),
    (16, 0xffff0000), (17, 0xffff8000), (18, 0xffffc000), (19, 0xffffe000),
    (20, 0xfffff000), (21, 0xfffff800), (22, 0xfffffc00), (23, 0xfffffe00),
    (24, 0xffffff00), (25, 0xffffff80), (26, 0xffffffc0), (27, 0xffffffe0),
    (28, 0xfffffff0), (29, 0xfffffff8), (30, 0xfffffffc), (31, 0xfffffffe),
    (32, 0xffffffff)

Next we create our two new columns and populate them:

ALTER TABLE GeoIP
ADD binary_network BINARY(4), network_bits TINYINT
GO

UPDATE GeoIP
SET binary_network = dbo.fnBinaryIPv4(SUBSTRING(network, 0, PATINDEX('%/%', network))),
    network_bits = CAST(SUBSTRING(network, PATINDEX('%/%', network) + 1, 3) AS TINYINT)

So now we can rewrite your query as:

DECLARE @binary_user_ip BIGINT
SELECT @binary_user_ip = dbo.fnBinaryIPv4(@user_ip)

SELECT geoname_id
FROM GeoIP g
    JOIN netmask n ON g.network_bits = n.bits
WHERE @binary_user_ip & n.binary_mask = g.binary_network

Note - this will only work with IPv4. If you want to detect IPv6 subnets, the general approach is the same but the string conversion and arithmetic will be more... complicated.

Upvotes: 6

Related Questions