Solved

Stored Procedure to validate format of IP address

Posted on 2008-10-23
12
1,141 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 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