Solved

Stored Procedure to validate format of IP address

Posted on 2008-10-23
12
1,145 Views
Last Modified: 2012-06-27
Hi Experts,

I need to write a stored procedure to take an IP address and check that it is in the correct format (i.e. 4 number separated by dots that are between 0 and 255). I'm not sure if there are any other validations that need to be done on an IP address but that's the most basic check.

Really appreciate your help.
SWD
0
Comment
Question by:SurreyWebDesigner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 1

Expert Comment

by:patricka_0377
ID: 22786118
I would look at using regular expressions
http://www.sqlteam.com/article/regular-expressions-in-t-sql

i think I have used this in the past with good results
http://www.codeproject.com/KB/mcpp/xpregex.aspx
0
 

Author Comment

by:SurreyWebDesigner
ID: 22786276
Hmmmm ... I'd rather just keep it in a stored procedure (if only because I already have one which is doing various other checks and like to just pop this straight in).

This looks a little too complicated for me I'm afraid (I'm not a regex expert at all).

Thanks for the suggestion.
SWD
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22792132
This should get you going:

declare @IP varchar(15),
            @IPTest varchar(15),
            @Counter tinyint

Set @IP = '101.102.103.104'

Set @Counter = 4
While @Counter > 0
      Begin
            Set @IPTest = PARSENAME(@IP, @Counter)
            If ISNUMERIC(@IPTest) = 1
                  Begin
                        If @IPTest BETWEEN 0 And 255
                              Begin
                                    Set @Counter = @Counter - 1
                              End
                        Else
                              Begin
                                    Break
                              End
                  End
            Else
                  Begin
                        Break
                  End
                        
      End

Select      CASE @Counter
                  WHEN 0 THEN 'Success'
                  ELSE 'Error in node ' + CAST(5 - @Counter as varchar(1))
            END
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SurreyWebDesigner
ID: 22794419
Hi acperkins - thanks for this, it's very nearly perfect! A few problems I'm having:

1. I've modified your code to fit in with my current SP but it just hangs when I run it - any ideas??
2. From the testing I've done so far, if you enter too many digits in the last node it doesn't throw an error.

Many thanks and apologies if I'm being dumb with the code below! (always possible!!)

Cheers
SWD
declare @IP varchar(15),
@IPTest varchar(15),
@Counter tinyint,
@FailureDetails varchar(300),
@Status varchar(50)
 
Set @IP = '101.102.103.104'
 
Set @Counter = 4
While @Counter > 0
      Begin
            Set @IPTest = PARSENAME(@IP, @Counter)
            If ISNUMERIC(@IPTest) = 1
                  Begin
                        If @IPTest BETWEEN 0 And 255
                              Begin
                                    Set @Counter = @Counter - 1
                              End
                        Else
                              Begin
                                 SET @Status = 'FAILURE'
			     SET @FailureDetails = @FailureDetails + 'Error in IP; '
                              End
                  End
            Else
                  Begin
                     	SET @Status = 'FAILURE'
			SET @FailureDetails = @FailureDetails + 'Error in IP; '
                  End
                        
      End
 
	If(@Counter<>0)
		BEGIN
			SET @Status = 'FAILURE'
			SET @FailureDetails = @FailureDetails + 'Error in IP node ' + CAST(5 - @Counter as varchar(1))
		END

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22804701
Try it this way:

Declare @IP varchar(50),
		@IPTest varchar(50),
		@Counter tinyint,
		@FailureDetails varchar(300),
		@Status varchar(50)
 
Set @IP = '101.102.103.1041234564'
 
Set @Counter = 4
While @Counter > 0
	Begin
		Set @IPTest = PARSENAME(@IP, @Counter)
		If ISNUMERIC(@IPTest) = 1 And LEN(@IPTest) <= 3
			Begin
				If CAST(@IPTest As smallint) BETWEEN 0 And 255
					Begin
						Set @Counter = @Counter - 1
					End
				Else
					Begin
					--			SET @Status = 'FAILURE'
					--			SET @FailureDetails = @FailureDetails + 'Error in IP; '
						BREAK
					End
			End
		Else
			Begin
--				SET @Status = 'FAILURE'
--				SET @FailureDetails = @FailureDetails + 'Error in IP; '
				BREAK
			End
 
	End
 
If @Counter <> 0
	BEGIN
		Select	@Status = 'FAILURE',
				@FailureDetails = 'Error in IP node: ' + CAST(5 - @Counter as varchar(1)) + ', Value: ' + @IPTest
	END

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22804719
>>1. I've modified your code to fit in with my current SP but it just hangs when I run it - any ideas??<<
You omitted exiting out of the loop when it failed hence a never ending loop.

>>2. From the testing I've done so far, if you enter too many digits in the last node it doesn't throw an error.<<
You are right.  I made the assumption that the max number of characters was 15.  I have changed it to 50, but it should be modified to whatever size you use.

This code might be clearer:
Declare @IP varchar(50),
		@IPTest varchar(50),
		@Counter tinyint,
		@FailureDetails varchar(300),
		@Status varchar(50)
 
Set @IP = '101.102.103.1041234564'
 
Set @Counter = 4
While @Counter > 0
	Begin
		Set @IPTest = PARSENAME(@IP, @Counter)
		If ISNUMERIC(@IPTest) = 1 And LEN(@IPTest) <= 3
			Begin
				If CAST(@IPTest As smallint) BETWEEN 0 And 255
					Begin
						Set @Counter = @Counter - 1
					End
				Else
					Begin
						-- SET @Status = 'FAILURE'
						-- SET @FailureDetails = @FailureDetails + 'Error in IP; '
						BREAK
					End
			End
		Else
			Begin
				-- SET @Status = 'FAILURE'
				-- SET @FailureDetails = @FailureDetails + 'Error in IP; '
				BREAK
			End
 
	End
 
If @Counter <> 0
	BEGIN
		Select	@Status = 'FAILURE',
				@FailureDetails = 'Error in IP node: ' + CAST(5 - @Counter as varchar(1)) + ', Value: ' + @IPTest
	END

Open in new window

0
 

Author Comment

by:SurreyWebDesigner
ID: 22815736
Hi,

Brilliant - thanks very much, we're almost there now I feel!

One thing that doesn't seem to be working is if I have less than 4 nodes in the IP address it isn't returning anything for @FailureDetails. And I've uncommented the necessary lines.

Thanks again for your help - I'm a bit of a newbie on this stuff so apologies if I'm missing something.

Cheers
SWD
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 22818132
Replace:
@FailureDetails = 'Error in IP node: ' + CAST(5 - @Counter as varchar(1)) + ', Value: ' + @IPTest

With:
@FailureDetails = 'Error in IP node: ' + CAST(5 - @Counter as varchar(1)) + ', Value: ' + ISNULL(@IPTest, '')
0
 

Author Closing Comment

by:SurreyWebDesigner
ID: 31509204
Spot on! Thanks very much for your expert help!

Cheers
SWD
0
 

Expert Comment

by:sqlsaint
ID: 34264509
Here is a function - Cut and Paste!!!

drop function IsIPAddress
go
create function IsIPAddress (@String varchar(100)) returns bit as
begin

declare  @Result      int,
      @Pos      int,
      @Dots      int,
      @Val      varchar(3)
      
select      @Result      = 1,
      @Pos      = 0,
      @Dots      = 0,
      @Val      = ''

while @Pos < len(@String)
   begin
      select @Pos = @Pos + 1
      if substring(@String, @Pos, 1) = '.'
         select @Dots = @Dots + 1
      else
         begin
            select @Val = @Val + substring(@String, @Pos, 1)
            
            -- check the segment length
            if len(@Val) > 3
               begin
                  select @Result = 0
                  return @Result
               end

            -- check that the segment is a number
            if isnumeric(@Val) <> 1
               begin
                  select @Result = 0
                  return @Result
               end

            -- check that the segment number is less then 255
            if convert(int, @Val) > 255
               begin
                  select @Result = 0
                  return @Result
               end
         end
   end

-- Count the segments "Dots"
if @Dots <> 3
   begin
      select @Result = 0
      return @Result
   end

return @Result

      
end
go

set nocount on

select dbo.IsIPAddress('1.2.3.4')
select dbo.IsIPAddress('111.112.113.114')
select dbo.IsIPAddress('266.2.3.4')
select dbo.IsIPAddress('a1.2.3.4')
select dbo.IsIPAddress('1.2.3.4.5')
0
 

Expert Comment

by:sqlsaint
ID: 34264519
Whoops - test first - use this one...

drop function IsIPAddress
go
create function IsIPAddress (@String varchar(100)) returns bit as
begin

declare  @Result      int,
      @Pos      int,
      @Dots      int,
      @Val      varchar(3)
      
select      @Result      = 1,
      @Pos      = 0,
      @Dots      = 0,
      @Val      = ''

while @Pos < len(@String)
   begin
      select @Pos = @Pos + 1
      if substring(@String, @Pos, 1) = '.'
         select @Dots = @Dots + 1
      else
         begin
            select @Val = @Val + substring(@String, @Pos, 1)
            
            -- check the segment length
            if len(@Val) > 3
               begin
                  select @Result = 0
                  return @Result
               end

            -- check that the segment is a number
            if isnumeric(@Val) <> 1
               begin
                  select @Result = 0
                  return @Result
               end

            -- check that the segment number is less then 255
            if convert(int, @Val) > 255
               begin
                  select @Result = 0
                  return @Result
               end
         end
   end

-- Count the segments "Dots"
if @Dots <> 3
   begin
      select @Result = 0
      return @Result
   end

-- Check to make sure that the last charcter is not a dot
if substring(@String, len(@String), 1) = '.'
   begin
      select @Result = 0
      return @Result
   end


return @Result

      
end
go

set nocount on

-- Should Pass
select dbo.IsIPAddress('1.2.3.4')
select dbo.IsIPAddress('111.112.113.114')

-- Should Fail
select dbo.IsIPAddress('a1.2.3.4')
select dbo.IsIPAddress('1.2.3')
select dbo.IsIPAddress('1.2.3.')
select dbo.IsIPAddress('1.2.3.4.5')
select dbo.IsIPAddress('266.2.3.4')
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34266782
You do realize this thread is 2 years old, right?

But surely you jest.  Why would you want to loop through each character?  That has to be a dog.  There are far better ways of retrieving delimited strings.  Besides ISNUMERIC() (yes, I realize the accepted solution also uses it) is a lousy way to test for integers.  You are better of using something like NOT LIKE '%[^0-9]%'  You can see for yourself with any of the following:
$1.2.3.4
-1.2.3.4
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question