Solved

Stored Procedure to validate format of IP address

Posted on 2008-10-23
12
1,131 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now