0

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?

Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
NuminousName
  • 200
  • 1
  • 15

4 Answers4

2

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:

Community
  • 1
  • 1
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
-1

Another approach, not with QueryOver, but LINQ:

session.Query<User>().Where(u => SqlMethods.Like(u.Username, "something")).ToList();
Ricardo Peres
  • 13,724
  • 5
  • 57
  • 74
-1

Or, with Criteria:

session.CreateCriteria(typeof(User), "u").Add(Restrictions.Like(Projections.Property("u.Username"), "something")).List<Username>();
Ricardo Peres
  • 13,724
  • 5
  • 57
  • 74
  • Like has very different comparison semantics to equals, and this brings a whole set of security concerns into play – Ruben Bartelink Jul 14 '15 at 10:31
  • 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 – Ruben Bartelink Jul 14 '15 at 10:56
  • Does that explain it sufficiently? – Ruben Bartelink Jul 15 '15 at 07:56
  • It was irony. Your answer is nothing but common sense, and only partial (no mention of indexes, for example). – Ricardo Peres Jul 15 '15 at 10:40
  • 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 :) – Ruben Bartelink Jul 15 '15 at 13:09
-1

Finally, QueryOver:

session.QueryOver<User>().Where(Expression.Sql("Username LIKE ?", "something", NHibernateUtil.String)).List<User>()
Ricardo Peres
  • 13,724
  • 5
  • 57
  • 74