检查约束以验证IP地址字段

有几种方法可以做到这一点-最有效的方法可能是 CLR function 在数据库中。

这是因为SQL有相当差的文本操作工具,而且SQL Server中没有本地regex。

正如其他人所说,在插入到数据库之前,应用程序可以更好地处理这一点。

我能想到的最简单的方法是创建一个函数 fnCheckIP 在约束中使用这个函数。

不需要使用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')

它不应该在数据库中处理,应该首先在应用程序中处理。

然后向数据库添加一个检查并没有什么坏处,但是将检查留给数据库来过滤输入是非常粗略的。

这可能并不完全可行,但有一种方法是将转换后的字符串存储为二进制(4)数据类型。让接口用连字符来处理,并处理将四个数字转换为二进制和返回(这可能甚至可以通过计算列来完成。)有点极端,是的,但是对于二进制(4),您将 总是 能够将其转换为IP地址。

此解决方案类似于 Paulo's 但是使用这两种方法都需要去掉逗号字符,因为isNumeric允许逗号,这将引发强制转换为int错误。

CREATE FUNCTION fn_ValidateIP
(
    @ip varchar(255)
)
RETURNS int
AS
BEGIN
    DECLARE @Result int = 0
        IF
            @ip not like '%,%' and
            len(@ip) <= 15 and
            isnumeric(PARSENAME(@ip,4)) = 1 and
            isnumeric(PARSENAME(@ip,3)) = 1 and
            isnumeric(PARSENAME(@ip,2)) = 1 and
            isnumeric(PARSENAME(@ip,1)) = 1 and
            cast(PARSENAME(@ip,4) as int) between 1 and 255 and
            cast(PARSENAME(@ip,3) as int) between 0 and 255 and
            cast(PARSENAME(@ip,2) as int) between 0 and 255 and
            cast(PARSENAME(@ip,1) as int) between 0 and 255
            set @Result = 1
        ELSE
            set @Result = 0
    RETURN @Result
END

select dbo.fn_ValidateIP('127.0.0.1')

在Oracle之后的10年左右,sqlserver得到了本机编译(有限制)

    ALTER function fn_ValidateIPv4
(
@ip varchar(255)
)
RETURNS int
--WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION  
AS
BEGIN 
--ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
/* only sql2016 native Compilation **/
DECLARE @len_ip as int;
SET @len_ip =  len(@ip);
DECLARE @firstBlock varchar(4) = '';
DECLARE @secondBlock varchar(4) = '';
DECLARE @thirdBlock varchar(4) = '';
DECLARE @fourthBlock varchar(4) = '';
DECLARE @countDot as smallint = 0;
DECLARE @l_i as smallint = 0;

DECLARE @l_curChar varchar(1) = 'X';

DECLARE @Result int = 0
    IF (@len_ip <= 15)
    BEGIN
        WHILE (@l_i < @len_ip) 
        BEGIN
            set @l_i += 1;
            set @l_curChar = substring(@ip,@l_i,1);
            if @l_curChar = '.'
                SET @countDot += 1
            ELSE
            BEGIN
                IF @l_curChar IN ( '0','1','2','3','4','5','6','7','8','9' )
                BEGIN
                    IF @countDot = 0 
                        SET @firstBlock = @firstBlock + @l_curChar;
                    IF @countDot = 1
                        SET @secondBlock = @secondBlock + @l_curChar;
                    IF @countDot = 2
                        SET @thirdBlock = @thirdBlock + @l_curChar;
                    IF @countDot = 3
                        SET @fourthBlock = @fourthBlock + @l_curChar;
                    IF  @countDot > 3
                        set @firstBlock = 'AAA'; -- force error 
                END
                ELSE set @firstBlock = 'AAA'; -- force error                

            END;
        END;            

        IF ( @countDot = 3 and
            cast(@fourthBlock as int) between 1 and 255 and
            cast(@thirdBlock as int) between 0 and 255 and
            cast(@secondBlock as int) between 0 and 255 and
            cast(@firstBlock as int) between 0 and 255 
            )           
            set @Result = 1;
    END;

    /*
    select dbo.fn_ValidateIPv4( '127.0.0.258' );        
    */
RETURN @Result
END;

我必须删除不支持的内置函数isNumeric等…