Criteria vs hql; same query different results; password hash

jopache · **Joined:** Wed Jan 28, 2009 3:43 pm **Posts:** 5

I am currently trying out NHibernate 2.0 and am having trouble understanding a query. I am using this for a login, I have a Member object that has a FirstName and a Password property. the password property gets hashed before it gets stored in to the database, and on the login, the password is hashed before it is compared to the value in the database. (FirstName will be replaced with UserName, im just experimenting for now)

I am monitoring the SQL that gets sent to the database with NHibernate Profiler (an awesome tool). My confusion right now is that I can set up a query with hql and Criteria API; and only the hql query works; the uniqueresult() and List() method of the Icriteria object both return null objects.

I look at NHibernate Profiler and look at the sql generated by both hql and criteria and then I go in to SQL Server Management Studio and both queries will run just fine and display the row that I need.. which is why I'm confused.

firstName and password are strings being passed in to the method

hql query

Code:

            string hql = "from Member as mem where mem.FirstName = '" 
                         + firstName + "'" + " and mem.Password = '" 
                         + password + "'";
            IQuery q = session.CreateQuery(hql);
            IList<Member> memList = q.List<Member>();

(I know i should probably be using parameters for this, but its not in its final version)

Criteria

Code:

ICriteria crit = session.CreateCriteria(typeof(Member))
                .Add(Expression.Eq("FirstName", firstName))
                .Add(Expression.Eq("Password", password));
            IList<Member> memList = crit.List<Member>();

(this is only giving me a problem on the password expression, if i comment it out, the query behaves the way it should, but if i comment out firstName and leave just the password expression it also returns null, but again, the sql being spit out is just fine)

hql sql

Code:

select member0_.MEMBER_ID                 as MEMBER1_5_,
       member0_.MEMBER_FNAME              as MEMBER2_5_,
       member0_.MEMBER_LNAME              as MEMBER3_5_,
       member0_.MEMBER_USERNAME           as MEMBER4_5_,
       member0_.MEMBER_EMAIL              as MEMBER5_5_,
       member0_.MEMBER_PASSWORD           as MEMBER6_5_,
       member0_.MEMBER_CHALLENGE_QUESTION as MEMBER7_5_,
       member0_.MEMBER_CHALLENGE_RESPONSE as MEMBER8_5_,
       member0_.ROLE_ID                   as ROLE9_5_
from   MEMBER member0_
where  ( member0_.MEMBER_FNAME = 'hasher' )
       and ( member0_.MEMBER_PASSWORD = '�J��̱��Lsӑ釘/��' )

criteria sql

Code:

SELECT this_.MEMBER_ID                 as MEMBER1_5_0_,
       this_.MEMBER_FNAME              as MEMBER2_5_0_,
       this_.MEMBER_LNAME              as MEMBER3_5_0_,
       this_.MEMBER_USERNAME           as MEMBER4_5_0_,
       this_.MEMBER_EMAIL              as MEMBER5_5_0_,
       this_.MEMBER_PASSWORD           as MEMBER6_5_0_,
       this_.MEMBER_CHALLENGE_QUESTION as MEMBER7_5_0_,
       this_.MEMBER_CHALLENGE_RESPONSE as MEMBER8_5_0_,
       this_.ROLE_ID                   as ROLE9_5_0_
FROM   MEMBER this_
WHERE  this_.MEMBER_FNAME = 'hasher' /* @p0 */
       and this_.MEMBER_PASSWORD = '�J��̱��Lsӑ釘/��' /* @p1 */

If I go in to sql server management studio and run those queries manually, they both return the same row, so it doesn't seem like it's the sql that's the problem.

Any ideas/explanations?

wolli · **Posted:** Thu Jan 29, 2009 3:42 am

The only difference I can see is that in the hql query password is treated as a string and in the criteria as an object. This won't be any difference if password is of type string, but may give you a different result if it's not.

jopache · **Joined:** Wed Jan 28, 2009 3:43 pm **Posts:** 5

wolli wrote:

The only difference I can see is that in the hql query password is treated as a string and in the criteria as an object. This won't be any difference if password is of type string, but may give you a different result if it's not.

the Password property is of type string.

wolli · **Posted:** Thu Jan 29, 2009 8:16 am

What happens if you use parameter in the HQL qeury ? Probably the same problem there ...

jopache · **Joined:** Wed Jan 28, 2009 3:43 pm **Posts:** 5

wolli wrote:

What happens if you use parameter in the HQL qeury ? Probably the same problem there ...

Code:

            string hql1 = "from Member as mem where mem.FirstName = :firstName and mem.Password = :password";
            IQuery q1 = session.CreateQuery(hql1);
            q1.SetParameter("firstName", firstName);
            q1.SetParameter("password", password);
            IList<IMember> memListFirstName = q1.List<IMember>();

I'm confused, this behaves just like the criteria query and returns absolutely nothing. The sql it puts out works fine when i run it under sql server management studio and returns a row..

sql is similar to using the string concatenation for the hql

Code:

select member0_.MEMBER_ID                 as MEMBER1_4_,
       member0_.MEMBER_FNAME              as MEMBER2_4_,
       member0_.MEMBER_LNAME              as MEMBER3_4_,
       member0_.MEMBER_USERNAME           as MEMBER4_4_,
       member0_.MEMBER_EMAIL              as MEMBER5_4_,
       member0_.MEMBER_PASSWORD           as MEMBER6_4_,
       member0_.MEMBER_CHALLENGE_QUESTION as MEMBER7_4_,
       member0_.MEMBER_CHALLENGE_RESPONSE as MEMBER8_4_,
       member0_.ROLE_ID                   as ROLE9_4_
from   MEMBER member0_
where  ( member0_.MEMBER_FNAME = 'hasher' /* @p0 */ )
       and ( member0_.MEMBER_PASSWORD = '�J��̱��Lsӑ釘/��' /* @p1 */ )

just uses parameters instead. Is there a secret to using parameters that I'm not seeing here?

jopache · **Joined:** Wed Jan 28, 2009 3:43 pm **Posts:** 5

I manually added another user to the database, but this time gave him a plain text password.. using that name and plain text password, my paramaterized hql query worked just fine! There must be some kind of character encoding issue when running the criteria or parameterized query (even though concatenating the password to the hql works fine -- this still boggles my mind)

the password field is currently a string and I was using the following code snippet to convert the desired password string into a hashed byte[] and then converting that in to a string to store in the database.. then for a login, the entered password would be hashed and then compared the the entry in the db.

Code:

            HashAlgorithm hashAlg = new SHA1Managed();
            byte[] passwordByteArray = Encoding.UTF8.GetBytes(password);
            byte[] hashedArray = hashAlg.ComputeHash(passwordByteArray);

            return Encoding.UTF8.GetString(hashedArray);

I might just change the password column in the db to a binary type and change the Password property to a byte[] and bypass the encoding the byte[] to a string and see if that works well.

jopache · **Joined:** Wed Jan 28, 2009 3:43 pm **Posts:** 5

Well, in case anyone was wondering/cared.. I changed the column in the DB to type Binary and made the property a byte[] and the criteria and hql queries work with it.