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.
| Author |
Topic |
|
vivsrms
Starting Member
1 Post |
 Posted - 2012-05-14 : 02:16:09
|
| 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
227 Posts |
Posted - 2012-05-14 : 07:34:34
|
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" |
 |
|
|
HansLindgren
Starting Member
1 Post |
Posted - 2014-08-21 : 09:12:22
|
quote:
Originally posted by vinu.vijayanWhy 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 |
|
|
|
|
|
|
|
|
Validate IP address (ipv4 and ipv6) in SQL server