MS SQL Server 2005 - case insensitive password problem

Question

I am checking for username and password for login in a procedure in MS SQL SERVER 2005. Since SQL Server 2005 is case insensitive even if user gives a lowercase password instead of a upper case one, the system allows to login.

What should I do?? Is there any command in Sql Server 2005 which can check the same??

Answer 1

SQL Server 2005 is only by the default collation using case insensitive comparisons. You should not store passwords in plaintext. You should hash them with a reasonably secure hashing algorithm and store the hash. (perhaps you should really use a salt value as well but let's start with hashing).

Answer 2

Use a case sensitive collation - e.g.

...where Password = @password COLLATE SQL_Latin1_General_CP1_CS_AS

and yes, you shouldn't really store plain text passwords in the database!

Answer 3

NEVER NEVER NEVER store plain-text passwords! Store a hash of the password and compare that. You can use the HashBytes() function.

Answer 4

Use a case sensitive collation for the compare, for example:

SELECT
  Id
FROM
  UserTable
WHERE
  UserName = @UserName
  AND Password = @Password COLLATE SQL_Latin1_General_Cp1_CS_AS

Use a collation that matches the character set of the password column.

One a different note - are you sure you want to store clear text passwords in the database? This is well in the Top 5 of the Don'ts when it comes to security decisions.

Answer 5

There are situations where you may want to store clear text passwords so I won't repeat the advice everybody else has, although it's usually sound.

Besides setting the collation, you can also use the varbinary trick like so:

WHERE
  CAST(Password as varbinary(20)) = CAST(@Password as varbinary(20)) AND
  CAST(Username as varbinary(20)) = CAST(@Username as varbinary(20))

The above will also result in a case sensitive search - just remember to set the varbinary length to the same as the field lengths.

To avoid index scans, you can include the case insensitive search as well - that'll make an index seek and the perform the varbinary search afterwards:

WHERE
  Password = @Password AND
  Username = @Username AND
  CAST(Password as varbinary(20)) = CAST(@Password as varbinary(20)) AND
  CAST(Username as varbinary(20)) = CAST(@Username as varbinary(20))