Match IP_Address from an IP_network

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

SQL Server 2005 Forums

Transact-SQL (2005)

Match IP_Address from an IP_network

Author

Topic

osbertv
Starting Member

6 Posts

Posted - 2007-11-12 : 06:21:20

Anybody has a function or script to match ip address from a table using a network and subnet as the criteria.

suppose I have a table named valid_ip with columns (id,ip_network,ip_subnet).. and i wan't to search if '192.168.1.5' is a valid ip from the table.... ex.

declare @ip varchar(15)
set @ip = '192.168.1.5'
if exists(select * from valid_ip where IPinSUBNETof(@ip,ip_network,ip_subnet))
 print @ip + ' is a valid ip'

I hope someone understand my question. if someone has something this IPinSUBNETof function it will be most helpful in radius or AAA applications.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 06:23:06

quote:
This section is for any originally written scripts you'd like to share with the group. Please don't repost content from books or other web sites unless you are the original author or copyright holder.

Use LIKE operator.

Moderator, please move this topic.

E 12°55'05.25"
N 56°04'39.16"

osbertv
Starting Member

6 Posts

Posted - 2007-11-12 : 06:38:26

Peso, I'm sorry if you did not understand my question. if you have knowledge about IPv4 or IPv6 you would understand that the LIKE operator would not be easy to use in validating an ip address.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 06:44:29

moved from script library

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-12 : 09:08:26

quote:
Originally posted by osbertv

Peso, I'm sorry if you did not understand my question. if you have knowledge about IPv4 or IPv6 you would understand that the LIKE operator would not be easy to use in validating an ip address.

Maybe you can enlighten us how this should be done ? After all, we are only DBA not Network Administrator.

Please post your table DDL, sample data and expected result.

KH
[spoiler]Time is always against us[/spoiler]

osbertv
Starting Member

6 Posts

Posted - 2007-11-12 : 20:03:09

suppose i have this table: (valid_ip)
id ip_network ip_subnet
-- ------------- ---------------
1 192.168.1.0 255.255.255.128
2 10.1.1.64 255.255.255.240
3 172.16.11.0 255.255.255.252

as for network rule, the valid ips of :
row_id 1 is from 192.168.1.0 to 192.168.1.127
row_id 2 is from 10.1.1.64 to 10.1.1.79
row_id 3 is from 172.16.11.0 to 172.16.11.3

if the query checks if '192.168.1.5' is a valid ip from the 'valid_ip' table, it would return row#1 since it includes '192.168.1.5' from its list.
it would be easy to search if i just populate all the valid ip's on a table but i don't want to go into that since there are over 16 million ip's in IPv4.

i hope there are other NetAd/DBa/Programmer guys out there who already have solution for this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-12 : 20:17:52

I am just a DBa. Am i doing it correctly ? You did not explain that you need to perform bitwise AND on the IP with the MASK to compare.

DECLARE @valid_ip TABLE
(
	id 		int,
	ip_network 	varchar(15),
	ip_subnet	varchar(15)
)

INSERT INTO @valid_ip
SELECT	1, '192.168.1.0', '255.255.255.128' UNION ALL
SELECT	2, '10.1.1.64',   '255.255.255.240' UNION ALL
SELECT	3, '172.16.11.0', '255.255.255.252'

DECLARE @ip 	varchar(15),
	@ip1	int,
	@ip2	int,
	@ip3	int,
	@ip4	int
SELECT	@ip = '192.168.1.5'
SELECT	@ip1  = CONVERT(int, dbo.fnParseString(-1, '.', @ip)),
	@ip2  = CONVERT(int, dbo.fnParseString(-2, '.', @ip)),
	@ip3  = CONVERT(int, dbo.fnParseString(-3, '.', @ip)),
	@ip4  = CONVERT(int, dbo.fnParseString(-4, '.', @ip))

SELECT	id, ip_network, ip_subnet
FROM
(
	SELECT	id,
		ip_network,
		net1  = CONVERT(int, dbo.fnParseString(-1, '.', ip_network)),
		net2  = CONVERT(int, dbo.fnParseString(-2, '.', ip_network)),
		net3  = CONVERT(int, dbo.fnParseString(-3, '.', ip_network)),
		net4  = CONVERT(int, dbo.fnParseString(-4, '.', ip_network)),
		ip_subnet,
		mask1 = CONVERT(int, dbo.fnParseString(-1, '.', ip_subnet)),
		mask2 = CONVERT(int, dbo.fnParseString(-2, '.', ip_subnet)),
		mask3 = CONVERT(int, dbo.fnParseString(-3, '.', ip_subnet)),
		mask4 = CONVERT(int, dbo.fnParseString(-4, '.', ip_subnet))
	FROM	@valid_ip
) i
WHERE	net1 & mask1 = @ip1 & mask1
AND	net2 & mask2 = @ip2 & mask2
AND	net3 & mask3 = @ip3 & mask3
AND	net4 & mask4 = @ip4 & mask4

/*
id          ip_network      ip_subnet       
----------- --------------- --------------- 
          1 192.168.1.0     255.255.255.128

(1 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 20:57:48

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92534

E 12°55'05.25"
N 56°04'39.16"

osbertv
Starting Member

6 Posts

Posted - 2007-11-12 : 23:13:40

thanks KHTAN your bitwise AND condition works.


DECLARE @valid_ip TABLE (
 id int,
 ip_network 	varchar(15),
 ip_subnet	varchar(15)
)
INSERT INTO @valid_ip
SELECT	1, '192.168.1.0', '255.255.255.128' UNION ALL
SELECT	2, '10.1.1.64',   '255.255.255.240' UNION ALL
SELECT	3, '172.16.11.0', '255.255.255.252'

if exists(select * from @valid_ip 
	where dbo.fn_IPinRANGE('192.168.1.240',ip_network,ip_subnet)=1)
print 'IP valid' 
else print 'IP not valid'


-- function code
ALTER function [dbo].[fn_IPinRANGE]
(
@ip varchar(15),
@net varchar(15),
@mask varchar(15)
)
returns int as
BEGIN

declare @str1 varchar(3), @str2 varchar(15), @pos1 int, @pos2 int
declare @ip1	int, @ip2 int, @ip3 int, @ip4 int
declare @net1	int, @net2 int, @net3 int, @net4 int
declare @mask1	int, @mask2 int, @mask3 int, @mask4 int
declare @ret bit

select @pos1 = 1, @pos2 = charindex('.',@ip)
select @ip1 = convert(int,substring(@ip,@pos1,@pos2-@pos1))
select @pos1 = @pos2+1, @pos2 = charindex('.',@ip,@pos1)
select @ip2 = convert(int,substring(@ip,@pos1,@pos2-@pos1))
select @pos1 = @pos2+1, @pos2 = charindex('.',@ip,@pos1)
select @ip3 = convert(int,substring(@ip,@pos1,@pos2-@pos1))
select @ip4 = convert(int,substring(@ip,@pos2+1,3))

select @pos1 = 1, @pos2 = charindex('.',@net)
select @net1 = convert(int,substring(@net,@pos1,@pos2-@pos1))
select @pos1 = @pos2+1, @pos2 = charindex('.',@net,@pos1)
select @net2 = convert(int,substring(@net,@pos1,@pos2-@pos1))
select @pos1 = @pos2+1, @pos2 = charindex('.',@net,@pos1)
select @net3 = convert(int,substring(@net,@pos1,@pos2-@pos1))
select @net4 = convert(int,substring(@net,@pos2+1,3))

select @pos1 = 1, @pos2 = charindex('.',@mask)
select @mask1 = convert(int,substring(@mask,@pos1,@pos2-@pos1))
select @pos1 = @pos2+1, @pos2 = charindex('.',@mask,@pos1)
select @mask2 = convert(int,substring(@mask,@pos1,@pos2-@pos1))
select @pos1 = @pos2+1, @pos2 = charindex('.',@mask,@pos1)
select @mask3 = convert(int,substring(@mask,@pos1,@pos2-@pos1))
select @mask4 = convert(int,substring(@mask,@pos2+1,3))

return case when (
@net1 & @mask1 = @ip1 & @mask1 AND
@net2 & @mask2 = @ip2 & @mask2 AND
@net3 & @mask3 = @ip3 & @mask3 AND
@net4 & @mask4 = @ip4 & @mask4) then 1 else 0 end

END

Peso thanks for the IP address and subnet mask calculator

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources