SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Validate IP address (ipv4 and ipv6) in SQL server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vivsrms
Starting Member

India
1 Posts

Posted - 05/14/2012 :  02:16:09  Show Profile  Reply with Quote
Hi,

I have an application where user is giving ip address (either ipv4 or ipv6) as a string. I have to check using SQL statements if the ip address entered is valid or not. I have found few UDF's for ipv4 validation but could not find any thing for ipv6.

Can any one help me on this.

Thanks in advance.

vinu.vijayan
Posting Yak Master

India
227 Posts

Posted - 05/14/2012 :  07:34:34  Show Profile  Reply with Quote
quote:
Originally posted by vivsrms

Hi,

I have an application where user is giving ip address (either ipv4 or ipv6) as a string. I have to check using SQL statements if the ip address entered is valid or not. I have found few UDF's for ipv4 validation but could not find any thing for ipv6.

Can any one help me on this.

Thanks in advance.



Why do want SQL Server to do the validation.
Actually, you should validate the user input at the front end and then pass only the valid Ips to sql server.



N 28° 33' 11.93148"
E 77° 14' 33.66384"
Go to Top of Page

HansLindgren
Starting Member

Malta
1 Posts

Posted - 08/21/2014 :  09:12:22  Show Profile  Reply with Quote
quote:
Originally posted by vinu.vijayan
Why do want SQL Server to do the validation.
Actually, you should validate the user input at the front end and then pass only the valid Ips to sql server.



I disagree. You should always do validation of the data going into your SQL server... You should also always validate your data in your DAL as well as your front end.
You never ever want garbage data in your DB. It will mess your whole life up :)

Quick T-SQL IPv4 validation:

	IF	PARSENAME( @IPv4Address , 4 ) IS NULL OR
		PARSENAME( @IPv4Address , 4 ) LIKE '%[^0-9]%' OR
		PARSENAME( @IPv4Address , 3 ) LIKE '%[^0-9]%' OR
		PARSENAME( @IPv4Address , 2 ) LIKE '%[^0-9]%' OR
		PARSENAME( @IPv4Address , 1 ) LIKE '%[^0-9]%'
		THROW 50000, 'Invalid IPv4 address!',1
	DECLARE @ip1 TINYINT,@ip2 TINYINT,@ip3 TINYINT,@ip4 TINYINT
	BEGIN TRY
		SELECT 
			@ip1 = CAST( PARSENAME( @IPv4Address , 4 ) AS TINYINT), 
			@ip2 = CAST( PARSENAME( @IPv4Address , 3 ) AS TINYINT),
			@ip3 = CAST( PARSENAME( @IPv4Address , 2 ) AS TINYINT),
			@ip4 = CAST( PARSENAME( @IPv4Address , 1 ) AS TINYINT)
	END TRY
	BEGIN CATCH
		THROW 50000, 'Invalid IPv4 address!',1
	END CATCH

Note: you must run SQL Server 2005+ for the above to work

Edited by - HansLindgren on 08/21/2014 10:29:10
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000