Query to validate IPAddress - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query to validate IPAddress

Author

Topic

san1850
Starting Member

2 Posts

Posted - 2012-05-03 : 13:26:38

Ip addresses are stored in my table in any of following ways.
192.12.34.12
192.12.*.12
192.*.34.12

Now in my applicaiton i want to validate user ip address aganist this table. Now if user ip is 192.12.34.12 or 192.12.45.12 or 192.56.34.12 it should be valid. Any number in star which falls under valid ip address need to be treated as valid ip address. How can i create a sql query or a stored proc which will validate logged in users ip address across this table.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-03 : 13:41:04

You can use PARSENAME function to do this, like shown below - but it is not as tight as you may need it - for example, you might want to set the min and max digits each section is allowed to have etc.

	CASE WHEN 
		(PARSENAME(@ip,1) NOT LIKE '%[^0-9]%' OR PARSENAME(@ip,1) = '*') AND
		(PARSENAME(@ip,2) NOT LIKE '%[^0-9]%' OR PARSENAME(@ip,2) = '*') AND
		(PARSENAME(@ip,3) NOT LIKE '%[^0-9]%' OR PARSENAME(@ip,3) = '*') AND
		(PARSENAME(@ip,4) NOT LIKE '%[^0-9]%' OR PARSENAME(@ip,4) = '*')
	THEN 1 ELSE 0 END

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-03 : 13:45:15

validate the IP inside your application with a regular expression.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

san1850
Starting Member

2 Posts

Posted - 2012-05-04 : 10:53:24

sunitabeck, not quite sure how this will work.
For instance user iP i 192.13.45.123 and i have Ip stored in db as 192.13.*.123. My query should return me a true boolean.

I cant use regex as this not entered by user i need to validate ip across ips in db table.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-05-04 : 11:15:45

I would have used a table that truly represents ip addresses broken down by octet


create table ipranges(firstoctet varchar(3), secondoctet varchar(3), thirdoctet varchar(3), fourthoctet varchar(3))

will save you lots of headache otherwise you will be doing string parsing and chances are great that you will miss something as is always the case in string parsing and get unwanted results. a design reconsideration might be in order I believe.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-05-04 : 11:23:12

ip addresses are numeric values, int values. a better approach would be to use tables not with varchar values but ints. then you do the following and will get the results you need


declare @iptocheck table(firstoctet int, secondoctet int, thirdoctet int, fourthoctet int)
insert into @iptocheck
select 192, 13, 45, 123


declare @ipranges table(firstoctet int, secondoctet int, thirdoctet int, fourthoctet int)
insert into @ipranges
select 192, 12, 34, 12
union
select 192,12,NULL, 12
union
select 192,NULL,34,12   
union
select 192,13,NULL,123   

select * 
  from @ipranges ir
  inner join @iptocheck itc
  on  itc.firstoctet = COALESCE(ir.firstoctet, itc.firstoctet)
  and  itc.secondoctet = COALESCE(ir.secondoctet, itc.secondoctet)
  and  itc.thirdoctet = COALESCE(ir.thirdoctet, itc.thirdoctet)
  and  itc.fourthoctet = COALESCE(ir.fourthoctet, itc.fourthoctet)

select CASE 
          WHEN COUNT(itc.firstoctet) = 1 THEN 'IsValid'
          ELSE 'IsNotValid'
       END 
  from @ipranges ir
  inner join @iptocheck itc
  on  itc.firstoctet = COALESCE(ir.firstoctet, itc.firstoctet)
  and  itc.secondoctet = COALESCE(ir.secondoctet, itc.secondoctet)
  and  itc.thirdoctet = COALESCE(ir.thirdoctet, itc.thirdoctet)
  and  itc.fourthoctet = COALESCE(ir.fourthoctet, itc.fourthoctet)

You get the result you want


firstoctet	secondoctet	thirdoctet	fourthoctet
192	13	45	123

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 11:25:19

quote:
Originally posted by san1850

sunitabeck, not quite sure how this will work.
For instance user iP i 192.13.45.123 and i have Ip stored in db as 192.13.*.123. My query should return me a true boolean.

I cant use regex as this not entered by user i need to validate ip across ips in db table.

Seems to me it does return true. Regardless, in your shoes, if you are trying to restrict the range of IP addresses, yosiasz's suggestion seems like a good one.

In any case, this query returns 1 when I run it, so not sure what you meant by it is not working for you.

DECLARE @ip VARCHAR(32) = '192.13.*.123';
SELECT
CASE WHEN 
		(PARSENAME(@ip,1) NOT LIKE '%[^0-9]%' OR PARSENAME(@ip,1) = '*') AND
		(PARSENAME(@ip,2) NOT LIKE '%[^0-9]%' OR PARSENAME(@ip,2) = '*') AND
		(PARSENAME(@ip,3) NOT LIKE '%[^0-9]%' OR PARSENAME(@ip,3) = '*') AND
		(PARSENAME(@ip,4) NOT LIKE '%[^0-9]%' OR PARSENAME(@ip,4) = '*')
	THEN 1 ELSE 0 END;

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-05-04 : 11:29:55

the other question that begs an answer is why are you doing what you are doing? sounds like you are trying to create your own DHCP...
just curious on why you would do ip validation using sql server

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-05-04 : 11:39:42

it should be way easier than that and I assume you don't need to worry about illegal characters, you are just checking if the VALID IP string matches your table. I would do this.


drop table #test
select '192.12.34.12' as ip 
into #test
union all
select '192.12.*.12' union all
select '192.*.34.12'

declare @myip as varchar(40)
set @myip = '192.12.168.12'

/*IF IT EXISTS IT's Valid)*/
select * from #test a
where @myip like replace(ip,'*','%')

set @myip = '192.255.34.12'
select * from #test a
where @myip like replace(ip,'*','%')

set @myip = '193.255.31.22'

select * from #test a
where @myip like replace(ip,'*','%')

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources