使用用户自定义功能时出现字母大小写问题 [英] Problem with the case of letters when using User defined function

问题描述

你好！

我在sql server 2008中创建了一个用户定义的函数(Scaler function).它根据查询返回一个整数值.我面临的问题是它正在同时读取管理员"和管理员". "admin"作为相同的值，然后返回错误的输出.这是函数

Hello!!

I have created a user defined function(Scaler function) in sql server 2008. It returns me an integer value based on the query. The problem that i am facing is that it is reading Both "Admin" & "admin" as same value and then returning me the wrong output. Here is the function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[UFNLogin] 
(
	@UserName varchar(30),@UPassword nvarchar(max)
)
RETURNS int 
AS
BEGIN
declare @Exists as int 
	if (Select count(ID)from Users where UserName=@UserName and UPassword=@UPassword)>1
set @Exists =1
else
Set @Exists=0	
return	@Exists
END

例如，如果我输入数据库中的用户名"Jack"和密码"Admin"，则返回1输出，但是如果我输入"Jack"，"admin"，则返回1作为输出，而admin

For example that if i enter user name "Jack" and Password "Admin" which is in the database, then it is returning 1 output but if i enter "Jack" ,"admin" then also it is returning me 1 as output whereas admin is not in the database.

推荐答案

您可以通过强制COLLOTAION区分大小写来做到这一点:

You can do it by forcing the COLLOTAION to case sensitive:

SELECT COUNT(ID) FROM Users WHERE UserName=@UserName AND UPassword=@UPassword COLLATE SQL_Latin1_General_CP1_CS_AS

>但是，首先您不应该那样做.
切勿以明文形式存储密码-这是主要的安全风险.这里有一些有关如何执行此操作的信息:密码存储:如何进行 [ ^ ]

But, you should not be doing it that way in the first place.
Never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]

这篇关于使用用户自定义功能时出现字母大小写问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！