CODEWITHSUNDEEP
c#linqentity-framework
Jaggu
Jaggu

Reputation: 6428

Linq performing case insensitive comparison

I want user object only when exact password along with case matches. However this query fetches result even when case for password is not same:

db.Users.Where(u => u.Username.ToLower() == username.ToLower() &&
                        u.Password == password).FirstOrDefault();

What am I missing?

Upvotes: 5

Views: 4489

Answers (7)

ojlovecd
ojlovecd

Reputation: 4902

try altering your column like this:

alter table [Users] alter column [password] nvarchar(100) collate Chinese_PRC_CS_AI

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063884

If the database is configured as case-insensitive, then you cannot get it to do a case-sensitive comparison on the password (unless you resort to TSQL tricks). However! You should not be storing a password - you should be storing a salted hash (ideally salted per user). And the hash (as a blob) should be fine to compare this way.

Upvotes: 3

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

What is the database type of the password and what collation sequence are you using?

It it's the VARCHAR and you use case-insensitive collation, the behavior you describe is exactly what would be expected.

Default collation sequences for some databases may be case-insensitive (e.g. take a look at SQL SERVER – Collate – Case Sensitive SQL Query Search), so you might be using case-insensitive collation without even knowing it. Obviously, you'll need to change the collation to get the correct results.

Upvotes: 1

Jon Hanna
Jon Hanna

Reputation: 113352

Simplest way is to do the username matching in the DB under it's case-insensitve rules and the password matching in .NET under its case-sensitive rules:

db.Users.Where(u => u.Username == username).ToList().Where(u => u.Password == password).FirstOrDefault();

The ToList() moves from db-based LINQ to object-based LINQ, and since there would only be one matching case anyway, the performance impact of doing so is negligible.

Still has the problem of storing a password in a database though!

Upvotes: 7

Ian Nelson
Ian Nelson

Reputation: 58763

Change the collation of the Password column in your Users table to use a case-sensitive collation such as Latin1_General_CS_AS.

Or, as others have commented, consider changing your solution to store hashes of the passwords instead (e.g. an SHA1 hash) and compare hashed values rather than plaintext passwords.

Upvotes: 1

ChrisF
ChrisF

Reputation: 137188

Try:

db.Users.Where(u => string.Compare(u.Username, username, System.StringComparison.OrdinalIngoreCase) == 0 &&
                    string.Compare(u.Password, password) == 0).FirstOrDefault();

Because this is being executed in the SQL you can't use the .NET/Linq comparison methods.

Upvotes: 2

clearpath
clearpath

Reputation: 956

If u.Password and password are of string type, then your code should work as expected.

Upvotes: 0

Related Questions