Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 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: 1582
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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


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: 1582
Location: Romania
Try with named parameters.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


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  
Post new topic Reply to topic  [ 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.