-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Execute HQL SELECT CONVERT HashBytes
PostPosted: Mon Nov 13, 2017 4:23 am 
Newbie

Joined: Mon Nov 13, 2017 4:11 am
Posts: 3
I have a j2EE application with JPA, hibernate 5.2.1.Final, WildFly 10, MSSQLServer 2012

I can't select a user with this select:
SELECT u FROM UserVO u WHERE u.email = :email AND u.password = CONVERT(VARCHAR(50), HashBytes('MD5', :password), 2)

The real SQL looks right:
2017-11-13 18:17:19 INFO [stdout:71] Hibernate:
2017-11-13 18:17:19 INFO [stdout:71] /* UserVO.findByEmailAndPassword */ select
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.IdUser as IdUser1_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.IdCompany as IdCompan6_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.Email as Email2_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.FirstName as FirstNam3_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.LastName as LastName4_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.Password as Password5_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.IdUserType as IdUserTy7_12_
2017-11-13 18:17:19 INFO [stdout:71] from
2017-11-13 18:17:19 INFO [stdout:71] Users uservo0_
2017-11-13 18:17:19 INFO [stdout:71] where
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.Email=?
2017-11-13 18:17:19 INFO [stdout:71] and uservo0_.Password=CONVERT(VARCHAR(50), HashBytes('MD5', ?), 2)

I can execute on database and work, but on application I don't get any results.

When a I do the same query without the password part, I get result.
SELECT u FROM UserVO u WHERE u.email = :email AND u.password = :password

Looks like something doesn't like the CONVERT part.

Regards,
Felipe Leite


Top
 Profile  
 
 Post subject: Re: Execute HQL SELECT CONVERT HashBytes
PostPosted: Mon Nov 13, 2017 5:27 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Try running the native SQL and see if it works.

If it doesn't, then you have an issue with that WHERE clause.


Top
 Profile  
 
 Post subject: Re: Execute HQL SELECT CONVERT HashBytes
PostPosted: Tue Nov 14, 2017 3:06 am 
Newbie

Joined: Mon Nov 13, 2017 4:11 am
Posts: 3
This doesn't work:
em.createNativeQuery("SELECT * FROM Users WHERE email = ? and Password = CONVERT(VARCHAR(50), HashBytes('MD5', ?), 2)").setParameter(1, userVO.getEmail()).setParameter(2, userVO.getPassword()).getResultList();

This work:
em.createNativeQuery("SELECT * FROM Users WHERE email = ? and Password = CONVERT(VARCHAR(50), HashBytes('MD5', '"+userVO.getPassword()+"'), 2)").setParameter(1, userVO.getEmail()).getResultList();

Looks like something at CONVERT part. I don't know, is not changing the value.

What could be?
=(


Top
 Profile  
 
 Post subject: Re: Execute HQL SELECT CONVERT HashBytes
PostPosted: Tue Nov 14, 2017 5:46 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Try with named parameters.


Top
 Profile  
 
 Post subject: Re: Execute HQL SELECT CONVERT HashBytes
PostPosted: Mon Nov 20, 2017 4:00 am 
Newbie

Joined: Mon Nov 13, 2017 4:11 am
Posts: 3
I tried already:

<named-query name="UserVO.findByEmailAndPassword">
<query>SELECT u FROM UserVO u WHERE u.email = :email AND u.password = CONVERT(VARCHAR(50), HashBytes('MD5', :password ), 2)</query>
</named-query>

This produces the query at the first post.

What can be?

Thanks!


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.