Click here to Skip to main content
15,895,142 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


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.
Posted

1 solution

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

SQL
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.[^]
 
Share this answer
 
Comments
ujjwal uniyal 28-Mar-12 4:59am    
Thanks Sir,

It helped a lot ... :)
OriginalGriff 28-Mar-12 5:26am    
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900