While working on a SP today, I noticed that SQL Server does a case-insensitive string match in a query. Hence in scenarios where passwords are to be validated, using a query as "WHERE Password =@Password" will give valid results if the user enter password as “admin” or
"ADMIN" eventhough when the password is set as "aDmiN". In this post, we will resolve this issue using a very simple method.
Let us consider below is our normal SQL procedure that validates a user from the tblUser table.
CREATE PROCEDURE [dbo].[tblUserSelect_Authenticate]
@Username nvarchar(50),
@Password nvarchar(50)
AS
BEGIN
SELECT * FROM tblUser WHERE Username = @Username AND Password = @Password
END
To tell SQL do a case-sensitive search, we will modify the above procedure as below. Please note we added a “COLLATE SQL_Latin1_General_CP1_CS_AS” to the field we want an exact match for.
CREATE PROCEDURE [dbo].[tblUserSelect_Authenticate]
@Username nvarchar(50),
@Password nvarchar(50)
AS
BEGIN
SELECT * FROM tblUser WHERE Username = @Username AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS
END
Keep learning and sharing! Cheers!