How to make Nhibernate Case sensitive query using QueryOver?

Question

I have a simple database with users table, it have simple admin user with

UserName= "Admin"
Password="admin"

I am using NHibernate to query over this table to login form. Suppose the login form inserted UserName="ADMIN" and password="ADMIN" both in upper case.

The system should not allow login. However when I use the query like this

using (var session = NhibernateHelper.OpenSession())
{
  return new List<User>
         (session.QueryOver<User>()
                 .Where(u => u.UserName == userName)
                 .And(u => u.Password == password)
                 .Future());
}}

The system ignores the case sensitivity and selects the user. So how can I make case sensitive query?

Plight

See also https://mcmap.net/q/1713895/-cannot-compare-strings-during-nhibernate-queryover-lt-t-gt (which is doing the opposite - forcing an insensitive comparison) – Juta

Answer 1

We can specify COLLATE directly as a part of SQL column evaluation

session
    .QueryOver<User>()
    // expecting that user name could be any case 
    // if not, we can use the same as for password below
    .Where(u => u.UserName == userName)
    // instead of this
    //.And(u => u.Password == password)
    .And(Expression.Sql(" Password = ? COLLATE Latin1_General_CS_AS"
       , password, NHibernateUtil.String));
    .Future()
    ;

The above statement will use Latin1_General_CS_AS where CS means: Case sensitive and AS means Accent sensitive

Also, there is some draft of a custom LikeExpression, which could consume the COLLATE string as a const or from setting:

• Nhibernate QueryOver collation without hard coded column name

Underfoot

isn't there any extension method to do this ? – Plight

Well, in this case, as far as I know - no. But it is easy to introduce new, custom one ;) I'd say. – Brogan

I am new to Nhibernate, can you please suggest reference on how to create custom one ? Thanks – Plight

Extension methods are not about NHibernate!!! ;) these are C# world! stackoverflow.com/questions/13840413 this link contains a draft how to do nice extension ;) – Brogan

So, does my answer helped you? did you manage to get case sensitive (CA) comparison? – Brogan

.And(Expression.Sql(" Password = ? COLLATE Latin1_General_CS_AS" , password, NHibernateUtil.String)); is not supported by Mysql. – Leveroni

Answer 2

Another approach, not with QueryOver, but LINQ:

session.Query<User>().Where(u => SqlMethods.Like(u.Username, "something")).ToList();

Maggs

Like has very different comparison semantics to equals, and this brings a whole set of security concerns into play – Juta

Depending on how the database is set up, this may not yield a case sensitive comparison. – Batholith

Answer 3

Or, with Criteria:

session.CreateCriteria(typeof(User), "u").Add(Restrictions.Like(Projections.Property("u.Username"), "something")).List<Username>();

Maggs

Like has very different comparison semantics to equals, and this brings a whole set of security concerns into play – Juta

If my username I submit is e.g. Ad%, then I'll know if you have a (single) user called Admin or Administrator. This general approach can enable nay amount of user enumeration – Juta

Does that explain it sufficiently? – Juta

It was irony. Your answer is nothing but common sense, and only partial (no mention of indexes, for example). – Maggs

Security issues are not ironic. For me indexing is very far behind that in terms of relevance. While you might be familiar with the tradeoffs and realise the validity of the concern, responding in this manner (be it ironic, passive agressive or genuine inquiry) does not help people coming across these answers and taking them at face value. Caveat emptor indeed :) – Juta

Answer 4

Finally, QueryOver:

session.QueryOver<User>().Where(Expression.Sql("Username LIKE ?", "something", NHibernateUtil.String)).List<User>()

Maggs

Like has very different comparison semantics to equals, and this brings a whole set of security concerns into play – Juta