locked
Parameterized SQL and Linq Queries with UNICODE values RRS feed

  • Question

  • Hi 

    I am using VS 2013 for ASP.net C# app to implement a login page. The Login details are stored with unicode characters where userid = "አበበ" and password = "ይለፍ".

    storing the data is working properly but when I want to authenticate the user using e.g. the following code the query returns all rows even when the login details are wrong i.e. the search is not working using unicode strings

    var ss = _db.MyUsers.Where(u => u.UsersID == userid.text && u.Password == password.text);

    and on the database sql prompt I can write 

    select * from MyUsers where UsersId = N'አበበ' it returns just one record, that means it works

    but if I use operator Like 

    UsersId Like N'አ%'  it again doesn't work (return all rows)

    is there any work around to this situation?

    Sunday, February 8, 2015 7:45 AM

Answers

  • This is a collation issue.  You need to configure the collation on the database or on the columns you use for comparison.  Under the default collation you are not getting the string comparison semantics you want.

    eg, run

    select 1 
    where N'ይለፍ' = N'' 
    

    but

    select 1 
    where N'ይለፍ' = N'' collate SQL_Latin1_General_CP850_BIN2

    I'm not sure which collation is best, but any BIN2 collation will give you an exact codepoint comparison, which is a safe choice.  And the code page doesn't matter for NVARCHAR columns.  You can store all the same characters.  The Collation only affects the sorting and comparison behavior.

    You can set the collation at the database level or for particular columns. 

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Fred Bao Monday, February 16, 2015 9:57 AM
    Wednesday, February 11, 2015 3:14 PM

All replies

  • Hello MillaMat,

    For this issue, i suggest that you could post to the SQL Sever forum(if you are using SQL Sever database):

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    There are database experts who would help explain this scenario for you.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 9, 2015 9:23 AM
  • Hi Fred,

    Thank you for the reply, yes I am using SQL server, I will post it there.

    Monday, February 9, 2015 1:33 PM
  • https://msdn.microsoft.com/en-us/library/vstudio/bb738684(v=vs.100).aspx

    I am preety sure that what you are doing with T-SQL you can do with Entity-SQL and EF.

    Monday, February 9, 2015 3:34 PM
  • >var ss = _db.MyUsers.Where(u => u.UsersID == userid.text && u.Password == password.text);

    You can dump out the store query with

    var sql = ss.ToString();

    To see what query is generated.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, February 9, 2015 3:53 PM
  • Hi, Darnold and David 

    The Query generated is this one

    SELECT 
        [Extent1].[UsersID] AS [UsersID], 
       .....
        FROM [dbo].[Users] AS [Extent1]
        WHERE ([Extent1].[UsersID] = @p__linq__0) AND ([Extent1].[Password] = @p__linq__1)

    and as you point out this is not the same query with the one executed on the SQL server db prompt(T-SQL) - it is missing N'.

    But I am used to the old ways of building query as string and executing it later, so how would I add the 'N in LINQ?

    besides for curiosity, why is not the LIKE operator not working even as T-SQL

    Regards

    Tuesday, February 10, 2015 8:49 AM
  • Most of the time I use Linq queries, but there are other times I use Entity SQL. It's that simple with using an EF datareader to populate a model object from the model and return an object or objects in a List<T>  off of the virtual model as the results of a ESQL query.

    https://msdn.microsoft.com/en-us/library/vstudio/bb387145(v=vs.100).aspx

    https://msdn.microsoft.com/en-us/library/vstudio/bb399560(v=vs.100).aspx

    https://msdn.microsoft.com/en-us/library/vstudio/bb738573(v=vs.100).aspx

    https://msdn.microsoft.com/en-us/library/vstudio/bb387118(v=vs.100).aspx

    Tuesday, February 10, 2015 2:29 PM
  • >so how would I add the 'N in LINQ?

    You don't need to.  That's controlled by the type of the parameter, which should be NVARCHAR.  Can you post the table definition?  Is this code-first, or database-first?

    Try to capture this query execution in SQL Profiler.  That will show you parameter values passed.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, February 10, 2015 3:13 PM
  • Hi David,

    This is the table definition, and fields which are required to hold unicode are NVARCHAR. 

    it is code first, database will be recreated if Model changes and seeded with just a record.

    CREATE TABLE [dbo].[Users] (
        [UsersID]      NVARCHAR (20)  NOT NULL,
        [Name]         NVARCHAR (60)  NOT NULL,
        [Email]        NVARCHAR (60)  NOT NULL,
        [Password]     NVARCHAR (20)  NOT NULL,
        [BMonth]       NVARCHAR (20)  NOT NULL,
        [BDate]        INT            NOT NULL,
        [BYear]        INT            NOT NULL,
        [Gender]       NVARCHAR (MAX) NOT NULL,
        [Phone]        NVARCHAR (MAX) NULL,
        [DateCreated]  DATETIME       NOT NULL,
        [status]       NVARCHAR (MAX) NULL,
        [LastLogin]    DATETIME       NOT NULL,
        CONSTRAINT [PK_dbo.Users] PRIMARY KEY CLUSTERED ([UsersID] ASC)
    );


    Wednesday, February 11, 2015 7:15 AM
  • This is a collation issue.  You need to configure the collation on the database or on the columns you use for comparison.  Under the default collation you are not getting the string comparison semantics you want.

    eg, run

    select 1 
    where N'ይለፍ' = N'' 
    

    but

    select 1 
    where N'ይለፍ' = N'' collate SQL_Latin1_General_CP850_BIN2

    I'm not sure which collation is best, but any BIN2 collation will give you an exact codepoint comparison, which is a safe choice.  And the code page doesn't matter for NVARCHAR columns.  You can store all the same characters.  The Collation only affects the sorting and comparison behavior.

    You can set the collation at the database level or for particular columns. 

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Fred Bao Monday, February 16, 2015 9:57 AM
    Wednesday, February 11, 2015 3:14 PM