Check names in a database for invalid characters from a list of permitted characters-CodePudding

The permitted characters are

([0-9a-zA-Z \.,\?\(\)\[\]:;'\-=\/@$%\*&!"])*

The code does not return the correct message. I have tried several collations to no avail.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE [dbo].[namecheck]
GO

CREATE PROCEDURE [dbo].[namecheck]
    /*@first varchar(40),@middle varchar(40),*/
    @surname varchar(40),
    @message varchar(125) OUTPUT
AS
    SELECT @message = 'OK'

    --([0-9a-zA-Z \.,\?\(\)\{\}:;'\|\-_=\\/@#$%\*=&"])*     = name permitted characters
    --([0-9a-zA-Z \.,\?\(\)\[\]:;'\-=\/@$%\*&!"])*          = address permitted characters

    IF @surname LIKE '%[^0-9a-zA-Z/[]%' ESCAPE '/' COLLATE Latin1_General_100_CS_AS
        --or @surname like'%  %' collate Latin1_General_CI_AS
    BEGIN;
        SELECT @message  = 'The surname name contains valid characters'
        RETURN 12
    END
    ELSE BEGIN;
        SELECT @message  = 'The surname name contains invalid characters'
        RETURN 13
    END

    RETURN 100
GO


DECLARE @returncode int, @submsg varchar(225), @surname varchar(40)

SELECT @surname = 'Bum' 

--select @surname = 'Bumë'  /*asci 135*/
--select @surname

EXECUTE  @returncode = namecheck @surname, @message = @submsg OUTPUT

SELECT @submsg AS message, @returncode AS returncode
GO

CodePudding user response：

You should check for the presence of any non whitelisted characters:

if @surname like '%[^0-9a-zA-Z .,?()\[\]:;''=/@$%*&!"-]%' escape '\' collate Latin1_General_100_CS_AS
    begin
    select @message  = 'The surname name contains invalid characters'
    return 13
    end
else
    begin
    select @message  = 'The surname name contains valid characters'
    return 12
    end

CodePudding user response：

Something like :

CREATE OR ALTER FUNCTION dbo.F_VALID_STRING (@STRING NVARCHAR(max))
RETURNS BIT
AS
BEGIN
IF @STRING LIKE REPLICATE('[A-Z0-9]', LEN (@STRING)) COLLATE Latin1_General_100_CI_AS
   RETURN 1;
DECLARE @STR NVARCHAR(max) = TRANSLATE(@STRING, ' \.,?()[]:;''-=/@$%*!"', '&&&&&&&&&&&&&&&&&&&&&');
SET @STR = REPLACE(@STR, '&', '');
IF LEN(@STR) <> LEN(@STRING)
   RETURN 0;
RETURN 1
END
GO

May help you...