IP Range Overlap check - SQL Server Forums

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)

IP Range Overlap check

Author

Topic

srinufrnds
Starting Member

2 Posts

Posted - 2010-05-20 : 01:30:06

I have a table which contains IPRangeStart and IPRangeEnd columns of varchar type.
I need to write a stored proc which needs to insert a new IP range into that table, If there is a IPRange Overlap then it needs to return true else return false.
Ex:
The table IPRangeStart as 10.23.144.128 and IPRange endas 10.23.144.255
Now the storedproc tries insert new range say 10.23.144.100 to
10.23.144.150, In this case there is overlap of IPs then should return true.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-20 : 03:19:47

This is an example of how you can implement a security mechanism of not having overlapping IP ranges.
It uses an AFTER TRIGGER. Copy the code and run each query in a batch of it's own.
What you need to do later, is the run the ALTER TABLE and CREATE TRIGGER queries on your table after changing the relevant table names and column names.
Good luck!

-- Create original table
CREATE TABLE	MyIPs
		(
			IPID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			FromIP VARCHAR(15) NOT NULL,
			ToIP VARCHAR(15) NOT NULL
		)
GO

-- Populate original table
INSERT	MyIPs
	(
		FromIP,
		ToIP
	)
SELECT	'190.154.11.17',
	'190.156.180.25'
GO

-- Display content of original table
SELECT	*
FROM	MyIPs
GO

-- Add conversion rules for IP octets to IP number
ALTER TABLE	MyIPs
ADD		ipFrom AS CAST(16777216.0 * PARSENAME(FromIP, 4) + 65536 * PARSENAME(FromIP, 3) + 256 * PARSENAME(FromIP, 2) + PARSENAME(FromIP, 1) AS BIGINT),
		ipTo AS CAST(16777216.0 * PARSENAME(ToIP, 4) + 65536 * PARSENAME(ToIP, 3) + 256 * PARSENAME(ToIP, 2) + PARSENAME(ToIP, 1) AS BIGINT)
GO

-- Display content of altered table
SELECT	*
FROM	MyIPs
GO

-- Now create a trigger to prevent overlapping IP ranges
CREATE TRIGGER	dbo.trgMyIPs
ON		dbo.MyIPs
AFTER		UPDATE,
		INSERT
AS

IF EXISTS	(
			SELECT		*
			FROM		dbo.MyIPs AS w
			INNER JOIN	(
						SELECT	CAST(16777216.0 * PARSENAME(FromIP, 4) + 65536 * PARSENAME(FromIP, 3) + 256 * PARSENAME(FromIP, 2) + PARSENAME(FromIP, 1) AS BIGINT) AS ipFrom,
							CAST(16777216.0 * PARSENAME(ToIP, 4) + 65536 * PARSENAME(ToIP, 3) + 256 * PARSENAME(ToIP, 2) + PARSENAME(ToIP, 1) AS BIGINT) AS ipTo,
							IPID
						FROM	inserted
					) AS i ON i.ipFrom <= w.ipTo
						AND i.ipTo >= w.ipFrom
						AND i.IPID <> w.IPID
		)
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Overlapping IP ranges found.', 16, 1)
	END
GO

-- Inserting a new valid IP range
INSERT	MyIPs
	(
		FromIP,
		ToIP
	)
SELECT	'191.154.11.17',
	'191.156.180.25'
GO

-- Display content of altered table
SELECT	*
FROM	MyIPs
GO

-- Trying to insert an invalid IP range
INSERT	MyIPs
	(
		FromIP,
		ToIP
	)
SELECT	'189.154.11.17',
	'192.156.180.25'
GO

-- Display content of altered table
SELECT	*
FROM	MyIPs
GO

-- Clean up
DROP TABLE	MyIPs
GO

N 56°04'39.26"
E 12°55'05.63"

srinufrnds
Starting Member

2 Posts

Posted - 2010-05-20 : 04:21:41

Thanks a lot Peso

This is what i am actually looking for...
Thanks a ton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources