How To do Case Sensitive String Match in SQL Server
Friends,
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!