SQL Server - SQL Server : - Asked By vinod kumar on 02-Aug-12 09:30 AM

Hi friends,

in the below query, when i'm entering the userid and password, it gives the following result. But  SQL server in not case sensitive. So here I'm entering Password as Capital letters. but in database it was existing as mixing of small letters and Capital letters.

Password is secure purpose we will use. So, what i want is, if i enter Exact password, means which is existing as it is in database at that time only it will give result. Otherwise  it will show invalid Password message Or it gives error. please send query..

Query  :   select * from User_Groups where UserID = 'VINOD' and Password = 'VINOD'

Output :   GroupId UserId  Password  Email
   31  vinod   vInOd NULL


Peter Bromberg replied to vinod kumar on 02-Aug-12 03:46 PM
You should not be storing passwords in the database as plaintext anyway since it creates a security risk.
Hash the password ( you can use FormsAuthentication.HashPasswordForStoringInConfigFile ) and store the hash.
When a user enters their password, hash that and compare it to the hash from the database.
Brian P replied to vinod kumar on 02-Aug-12 05:54 PM
Epic fail for storing unencrypted passwords in the database!  You should store a hashed password in the database.  Your application should then create a hash during the login and query the database using the hash.  The query will return a record only if the hash matches the hashed password stored in the database.

Assuming that your hash will contain both upper and lower case characters, you can use the following query to do a case-sensitive compare:

select * from User_Groups

where UserID = 'VINOD'

and [Password] collate Latin1_General_CS_AI = @hashedPassword collate Latin1_General_CS_AI

You should use a different collation name if your data will contain Unicode or non-Latin characters.