Why is SQL Server ignoring emoji's in equals statement?

Question

Came across an interesting problem today and wanted to know the reasons behind the behavior. I have a users table with usernames and I query against that to pull the unique user out and then do password checks with all the hashing and salty goodness.

However, if I put emojis in the query, the user is still pulled out of the database and I'd like to know why and what setting need to be applied. I am using EF, but I tested the raw T-SQL and the behavior is the same so EF is not the culprit.

SELECT TOP 1 * 
FROM Users 
WHERE username = N'someuser' --Works as expected

SELECT TOP 1 * 
FROM Users 
WHERE username = N'some🐶user🐶' --ALSO WORKS!

I can put emojis anywhere and as many as I want and the user is still returned. I can obviously put C# code in place that would do additional checks so this issue is solvable there, but I'd like it solved at the database level as there may be many other queries that do string comparisons.

Emojis in the password aren't a problem since hashing and salting will be applied in C# so emojis in a password are fine.

Answer 1

The collation that you're using doesn't support the comparison of the emoji characters; so it ignores them. You can use a specific collation during the comparison if you want things to work as you'd normally expect:

select *
from (values
    (N'some🐶user🐶', N'someuser')
  , (N'someuser', N'someuser')
  , (N'some🐶user🐶', N'some🐶user🐶')
) as a (L, R)
where a.L = a.R collate Latin1_General_100_CI_AS_SC;

See the MSDN article Collation and Unicode Support for more info.