Linq performing case insensitive comparison

Question

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?

@Rob: `ToLower` is used on the username, not on the password. — Fredrik Mörk, Oct 20 '11 at 11:13
Looks good to me - your problem is probably somewhere else. BTW, you could use `string.Equals` with `StringComparison.InvariantCultureIgnoreCase` for comparing user name case-insensitively, to avoid creating a pair of new `string` objects for each iteration of the LINQ loop. — Branko Dimitrijevic, Oct 20 '11 at 11:15
My database has username: admin and password: Admin and if I provide user: admin and password: AdMiN it still works. — Jaggu, Oct 20 '11 at 11:15
@Rob: such details are surprisingly easy to overlook. I do it all the time. — Fredrik Mörk, Oct 20 '11 at 11:16
If your database does not perform case sensitive comparison you might wanna look at the generated SQL — Rune FS, Oct 20 '11 at 11:19
@Hohhi - agreed, hash the passwords and the string comparison issue disappears as a pleasant side-effect. — Ian Nelson, Oct 20 '11 at 11:26
@Ian: You are trying to change the whole point of the question. What if this is not about password and something else? Please provide solution to my original question. See my title is 'Linq performing case insensitive comparison' and not how to deal with passwords. — Jaggu, Oct 20 '11 at 11:27
I found this link: http://stackoverflow.com/questions/3843060/linq-to-entities-case-sensitive-comparison — Jaggu, Oct 20 '11 at 11:30

score 7 · Accepted Answer · answered Oct 20 '11 at 11:33

7

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!

answered Oct 20 '11 at 11:33

Jon Hanna

110,372
10
146
251

Your solution looked the most sane to me. – Jaggu Oct 20 '11 at 11:35
There's something a bit unsatisfying in doing two types of LINQ filtering, but it should hopefully do the trick. – Jon Hanna Oct 20 '11 at 11:41
Yes there will be a negligible performance hit but it should be OK. – Jaggu Oct 20 '11 at 11:43
4

Exactly. Developer time matters too, and you've got bigger fish to fry than that little quibble (like the fact that you've got passwords stored in your database!). – Jon Hanna Oct 20 '11 at 11:46
It is not about performance, it is philosophically wrong. Coding is about writing down the rules of the world around us. If someone would verify this thing filtering twice, what would you say about such person? – Yurii Hohan Oct 20 '11 at 11:47
@Hohhi: Then what do you want me to do? Imagine this is not about passwords and something else? Do you want me to hash every single string column in my database? I am using EF 4.1 Code First and Sql Server Compact Edition. EF code first has problems when you change model whole database gets recreated from scratch i.e dropped and recreated. Do you want me to keep changing collation like this everytime model gets changed and database is dropped and recreated? The collation thing is good for EF 4.0 general but not code first and hashing is not at all an option for anything other than passwords. – Jaggu Oct 20 '11 at 11:51
2

It is not philosophically wrong. It is to my mind æsthetically unpleasant to filter twice. However, we're filtering once by one set of rules and once by another; philosophically it's perfect, as the "rules of the world" in this case have two different filtering cases with slightly different rules. – Jon Hanna Oct 20 '11 at 11:55
@Jaggu, I do not want you to do anything, I just say it is not right and this is not the way it should be done IMHO – Yurii Hohan Oct 20 '11 at 11:55
@Hohhi: Can you give me an alternate solution better than Jon? – Jaggu Oct 20 '11 at 11:58
@Jaggu, this code says: find me a list of users with UserName equal to param which is already wrong. It should say: find the user with UserName and may be even throw if there are two of them. You are authenticating ONE user. What kind of lists are we talking about? Do you get my point? – Yurii Hohan Oct 20 '11 at 12:06
@Hohhi: Yes I get, but did you read my previous comment? "Can you give me a better solution that what Jon gave?" – Jaggu Oct 20 '11 at 12:10
1

@Jaggu, I'd write the next way: var user = users.Single(u => u.UserName == username); if (user == null) throw new ArgumentException("No such user"); Console.WriteLine(user.Password==password); – Yurii Hohan Oct 20 '11 at 12:20
@Hohhi: Ok, more lines of code but basically you are also doing a Linq to Object only but I do get your point of philisophy and business rules and stuff. Good. – Jaggu Oct 20 '11 at 12:29
1

@Hohhi after you fix the bug (`Single` will throw where the desired result is a null), the test is essentially the same as what I have above. – Jon Hanna Oct 20 '11 at 13:16
var user = users.FirstOrDefault(u => u.UserName == username); if (user == null) throw new ArgumentException("No such user"); Console.WriteLine(user.Password==password); – Yurii Hohan Oct 20 '11 at 13:18

score 3 · Answer 2 · answered Oct 20 '11 at 11:34

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.

score 2 · Answer 3 · answered Oct 20 '11 at 11:18

2

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.

answered Oct 20 '11 at 11:18

ChrisF

134,786
31
255
325

@Jaggu - now that is odd. Try brackets around the `string.Compare()`? – ChrisF Oct 20 '11 at 11:22
doesn't help. I am using EF 4.1 code first. Can that be a issue? – Jaggu Oct 20 '11 at 11:24
@Jaggu - Don't know, sorry. The only other thing is to explicitly set comparison type on the `password` check to `Ordinal`. – ChrisF Oct 20 '11 at 11:25
I found this link: http://stackoverflow.com/questions/3843060/linq-to-entities-case-sensitive-comparison – Jaggu Oct 20 '11 at 11:30

score 1 · Answer 4 · answered Oct 20 '11 at 11:31

1

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.

answered Oct 20 '11 at 11:31

Ian Nelson

57,123
20
76
103

This table is created by code first and not me and sadly it has all nvarchar types. Code first has problems of dropping and recreating everything once your model changes so I need to do this everytime which is not good for me. – Jaggu Oct 20 '11 at 11:36
Thanks but couldn't find the collation you are talking about on this list: http://msdn.microsoft.com/en-us/library/ms174596%28v=SQL.100%29.aspx – Jaggu Oct 20 '11 at 12:35

score 1 · Answer 5 · answered Oct 20 '11 at 11:33

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.

score 0 · Answer 6 · answered Oct 20 '11 at 11:13

0

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

answered Oct 20 '11 at 11:13

clearpath

916
8
24

It is not working. I am using Entity Framework 4.1 code first. No matter if I input admin or Admin, both way works and I get result – Jaggu Oct 20 '11 at 11:14

score 0 · Answer 7 · answered Oct 20 '11 at 11:43

0

try altering your column like this:

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

answered Oct 20 '11 at 11:43

ojlovecd

4,812
1
20
22

Linq performing case insensitive comparison

7 Answers7