Comapring IP Addresses in select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comapring IP Addresses in select statement

Author

Topic

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-03-01 : 04:49:24

So in my DB I store 2 Ip Addresses

Start and End Ip address.

On my website they can enter a starting and end ip address range.

So I.e

203.12.0.0 to 203.12.255.255

Then I need to check if the users IP Address falls into this range.

The Ip Addresses are actually stored as they appear in DB i.e

203.12.0.0

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-01 : 05:03:34

Try like this

Declate @input_ip varchar(25)

set @input_ip='203.12.0.120'

select * from table_name
where ((parsename( @input_ip,2) between parsename(start_ip,2)and parsename(end_ip,2))
or (parsename( @input_ip,1) between parsename(start_ip,1)and parsename(end_ip,1)))
and parsename( @input_ip,3)=parsename( start_ip,3) and
parsename( @input_ip,4)=parsename( start_ip,4)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-03-01 : 05:16:10

Will that cater for such example as

203.12.0.0 to 203.12.255.255

and the IP Address must start with 203.12 i.e 203.12.200.12

and not just any number between these 2 ranges ?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-01 : 05:22:28

ok, then and two more conditions
parsename( @input_ip,2)<=225 and parsename( @input_ip,1)<=225

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-03-01 : 06:24:53

That Didnt work for me I have

SELECT userId, IPAddress, IPAddressTo
FROM tblUserIPAddress
WHERE (PARSENAME(@input_ip, 2) BETWEEN PARSENAME(IPAddress, 2) AND PARSENAME(IPAddressTo, 2) OR
PARSENAME(@input_ip, 1) BETWEEN PARSENAME(IPAddress, 1) AND PARSENAME(IPAddressTo, 1)) AND (PARSENAME(@input_ip, 3) = PARSENAME(IPAddress, 3))
AND (PARSENAME(@input_ip, 4) = PARSENAME(IPAddress, 4))

I tried IP Address '104.203.0.1'

And i had this range in my DB 140.203.0.0 to 140.203.255.255

Any Ideas ?

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-03-01 : 06:30:47

I had something like this before

SELECT * FROM tblUserIPAddress WHERE (cast(" & ipAddressToCheckQ & " as BIGINT) BETWEEN CAST(REPLACE(REPLACE(IPAddress, '.0.0', '000000'), '.', '') AS BIGINT) AND CAST(REPLACE(REPLACE(IPAddressTo, '.0.0', '000000'), '.', '') AS BIGINT))"

And it work quite well upto a point. Except it only worked for some ranges. Basically the problems are with range 3 and 4 sometimes they have 1,2 or 3 numebers which causes issues for my compare.

What i was looking for that if in range 3 and 4 (I.e last six numbers of an IP address) if they had less than 3 numbers to add a 0 there so I always 12 numbers in my IP Address to make a clean compare.

So not only
REPLACE(IPAddressTo, '.0.0', '000000')
but also
REPLACE(IPAddressTo, '.0.1', '000001')
REPLACE(IPAddressTo, '.0.2', '000002')
REPLACE(IPAddressTo, '.12.2', '012002')
etc etc

But there is to many ranges to cover

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-03-01 : 06:47:24

Sorry After doing a more attemptys I found Senthil your query does work do a certain point I added your last part.

Heres one case which it doesnt pass though
SELECT userId, IPAddress, IPAddressTo
FROM tblUserIPAddress
WHERE (PARSENAME(@input_ip, 2) BETWEEN PARSENAME(IPAddress, 2) AND PARSENAME(IPAddressTo, 2) OR
PARSENAME(@input_ip, 1) BETWEEN PARSENAME(IPAddress, 1) AND PARSENAME(IPAddressTo, 1)) AND (PARSENAME(@input_ip, 3) = PARSENAME(IPAddress, 3))
AND (PARSENAME(@input_ip, 4) = PARSENAME(IPAddress, 4)) AND (PARSENAME(@input_ip, 2) <= 225) AND (PARSENAME(@input_ip, 1) <= 225)

147.252.0.0 To 147.252.255.255

When I Tried
147.252.92.47

It didnt retrieve
but for example it does
147.252.12.12

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-03-01 : 07:35:49

I just cant figure out why the following wont work

Declare @input_ip varchar(50)

set @input_ip='147.252.92.47'

SELECT PARSENAME(IPAddressTo, 2) AS Expr1
FROM tblUserIPAddress
WHERE (PARSENAME(@input_ip, 2) BETWEEN PARSENAME(IPAddress, 2) AND PARSENAME(IPAddressTo, 2))

When for example this works

Declare @input_ip varchar(50)

set @input_ip='147.252.192.47'

SELECT PARSENAME(IPAddressTo, 2) AS Expr1
FROM tblUserIPAddress
WHERE (PARSENAME(@input_ip, 2) BETWEEN PARSENAME(IPAddress, 2) AND PARSENAME(IPAddressTo, 2))

Ranges is 147.252.0.0 To 147.252.255.255

Sorry for spam just wanted to give you guys an example.

Anyone awake to help ? :P

Thanks again

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-01 : 08:15:24

What you need to do is to convert the IP address in to a number column using the following function:

ALTER FUNCTION dbo.ip2num (
  @ip varchar(15)
  )
RETURNS bigint
AS

BEGIN

DECLARE @num bigint

SET @num = 
  256 * 256 * 256 * CAST(PARSENAME(@ip, 4) AS bigint) + 
  256 * 256 * CAST(PARSENAME(@ip, 3) AS bigint) + 
  256 * CAST(PARSENAME(@ip, 2) AS bigint) + 
  CAST(PARSENAME(@ip, 1) AS bigint)

RETURN @num

END

And then you need to do something like this:

SELECT *
FROM table
WHERE dbo.ip2num(@MyInputIP) BETWEEN dbo.ip2num(StartIP) AND dbo.ip2num(EndIP)

This will however have a HUGE performance impact if you have a large table if IP-ranges so if you need to solve this you should create 2 new columns in your table and store the numeric version of the ip address also.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-03-01 : 09:18:27

Hey Lumbago this work fine right.

I created the function and tested with query window and all good.

But when I try and run from my classic asp script I get

'ip2num' is not a recognized function name.

Any ideas how to get over this hump ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 09:20:22

Did you qualify the object owner when calling the function?

Madhivanan

Failing to plan is Planning to fail

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-03-01 : 09:24:58

Hey Ye do you mean adding i.e dbo. in front of it. I did that now and it works

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 09:27:59