How To do Case Sensitive String Match in SQL Server

Posted: 27th October 2016 by admin in Uncategorized

Friends,

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.

 
  1. CREATE PROCEDURE [dbo].[tblUserSelect_Authenticate]
  2. @Username nvarchar(50),
  3. @Password nvarchar(50)
  4. AS
  5. BEGIN
  6. SELECT * FROM tblUser WHERE Username = @Username AND Password = @Password
  7. 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.

 
  1. CREATE PROCEDURE [dbo].[tblUserSelect_Authenticate]
  2. @Username nvarchar(50),
  3. @Password nvarchar(50)
  4. AS
  5. BEGIN
  6. SELECT * FROM tblUser WHERE Username = @Username AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS
  7. END

Keep learning and sharing! Cheers!

*