How to check case sensitive password in sql server08

Question

Hi All, In my application I am sending password to database,lets say my Password is PassworD123. Now this is giving me proper value ,but when i am using password123..its also giving me the proper value.So how to chaeck for case sensitive data in SQL server. Any demo code will help. Thanks.

Answer 1

Why not use encryption to store the passwords in the database such as md5 because they will return different hashed versions for example

Md5 of password123 = 482c811da5d5b4bc6d497ffa98491e38

Md5 of PassworD123 = bbac8ba35cdef1b1e6c40f82ff8002ea

and when you compare them 2 they are clearly different.

I think you are using ASP therefore i dont know if it has an md5() function built in but php does have it. Another thing you should know is that if you are storing passwords in a database its better to store them using some sort of encryption that cannot be reversed

Answer 2

The immediate answer to your query is here: http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html

However I think your approach to storing / comparing passwords is a bit wrong. You should not be storing the password directly in the database. At-least MD5 it or something.

Answer 3

Well, the short answer is to use a case-sensitive collation - the longer answer is don't store plaintext passwords in your database!

Answer 4

You can use COLLATE clause in your T-SQL statement.

Ex.

SELECT * FROM dbo.TableName WHERE Password = @ password COLLATE SQL_Latin1_General_CP1_CS_AS