sql strictly equals, is there something? [duplicate]

Question

Possible Duplicate:
SQL server ignore case in a where expression

basically I need to check something like this

select * from users where name = @name, pass = @pass

the problem is that 'pass' = 'pAsS'

is there something more strict for string comparison in sql (ms sql-server)

Answer 1

It's down to your collation, which it would seem is case insensitive. For example, the standard collation is Latin1_General_CI_AS, where the CI means case insensitive. You can force a different collaction for a different comparison:

select  *
from    users
where   name = @name
and     pass COLLATE Latin1_General_CS_AS = @pass COLLATE Latin1_General_CS_AS

Incidentally, you shouldn't be storing passwords in your database - you should be salting and hashing them.

Answer 2

As several others have already posted you can use collations in your query or change the collation of your "pass" column to be case sensitive. You may also change your query to use the VARBINARY type instead of changing collation:

SELECT * FROM users 
WHERE name = @name
AND pass = @pass
AND CAST(pass AS VARBINARY(50)) = CAST(@pass AS VARBINARY(50))

Note that I left in the pass = @pass clause. Leaving this line in the query allows SQL Server to use any index on the pass column.

Answer 3

You need to use a case sensitive collation for the comparison:

SELECT * FROM users 
WHERE name = @name, pass = @pass
COLLATE  SQL_Latin1_General_Cp1_CS_AS

See this article for more details.