检查数据库中的名称是否存在允许字符列表中的无效字符

发布于 2025-01-09 08:05:13 字数 458 浏览 1 评论 0原文

CREATE PROCEDURE [dbo].[namecheck]
    (@surname varchar(40))
AS
if @surname like '%[^0-9a-zA-Z .,?()\[\]:;''=/@$%*&!"-]%' ESCAPE '\' COLLATE Latin1_General_100_CS_AS
    RETURN 1

RETURN 2
GO

DECLARE @returncode int

EXECUTE @returncode = namecheck 'Bum'

SELECT @returncode AS returncode

EXECUTE @returncode = namecheck 'Bumë' 

SELECT @returncode AS returncode

GO

无论字符串的有效性如何，返回代码都是相同的，例如“Bum”应为返回代码 2，“Bumë”应为返回代码 1

原文

CREATE PROCEDURE [dbo].[namecheck]
    (@surname varchar(40))
AS
if @surname like '%[^0-9a-zA-Z .,?()\[\]:;''=/@$%*&!"-]%' ESCAPE '\' COLLATE Latin1_General_100_CS_AS
    RETURN 1

RETURN 2
GO

DECLARE @returncode int

EXECUTE @returncode = namecheck 'Bum'

SELECT @returncode AS returncode

EXECUTE @returncode = namecheck 'Bumë' 

SELECT @returncode AS returncode

GO

the return code is the same regardless of the validity of the string eg 'Bum' should be returncode 2 and 'Bumë' should be return code 1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

友谊不毕业 2025-01-16 08:05:13

您应该检查是否存在任何非白名单字符：

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

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

回复收藏 0 原文

放血 2025-01-16 08:05:13

比如：

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

可以帮助你...

Something like :

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

May help you...

回复收藏 0 原文

~没有更多了~