Check constraint to validate IP address field.

Question

Hi everyone,

I'm working on a project involving C# and a SQL Server 2008 database. In one of the tables, I have a field (nvarchar(15)) which will contain an IP address.

I'd like to add a check constraint which will validate that the input value is actually an IP address.

I wanted to use a regex to do that, but it seems that this feature is not supported by default. I saw things about writing a customm dll with UDF inside (MSDN tutorial), but I don't really understand how it works (i.e. where should I place the dll ?)

Is there a "simple" way to add such a constraint ? Any solution is welcome.

Thanks in advance !

Answer 1

It shouldn't be handled in the database, it should be handled first and foremost in the application.

There's no harm in then adding a check to the database, but leaving it up to the DB to filter input is very sketchy.

Answer 2

There are several way of doing this - the most performant one would probably be a CLR function in the database.

This is because SQL has fairly poor text manipulation tooling and no native RegEx in SQL Server.

As other have said, this is better handled by an application before insertion to the DB.

Answer 3

The easiest way I can think of is to create a function like fnCheckIP and use this function in the constraint.

There's no need to use UDF.

create function fnCheckIP(@ip varchar(15)) returns bit
AS
begin
    if (@ip is null)
        return null

    declare @num1 int
    declare @num varchar(15)    
    declare @pos int
    while (@ip is not null)
    begin
        set @pos = IsNull(NullIf(charindex('.', @ip), 0), Len(@ip) + 1)
        set @num = substring(@ip, 1, @pos - 1)

        if (isnumeric(@num) = 0) or (not cast(@num as int) between 0 and 255)
            return cast(0 as bit)

        if (len(@ip) - @pos <= 0)
            set @ip = null
        else        
            set @ip = NullIf(substring(@ip, @pos + 1, len(@ip) - @pos), '')
    end

    return cast (1 as bit)
end
go

select dbo.fnCheckIP('127.0.0.1')
select dbo.fnCheckIP('127.0.0.300')

Answer 4

This may not be entirely practical, but one way would be to store the converted string ###-###-###-### into a binary(4) data type. Let the interface fuss around with hyphens and deal with converting the four numbers to binary and back (and this could probably even be done by a caluclated column.) A bit extreme, yes, but with binary(4) you will always be able to turn it into an IP address.