0

I have a java app with JPA and I'm using oracle DB. One property of an entity needs to be encrypted before saved into the DB. I'm using application encryption, Base64. I have a named query searching for the entity according to this property. Now due to the encryption I need to first encrypt the value coming from the app and search in the DB for the encrypted value, but the query can't find the entity with this value.

Is there an issue with named queries with encrypted values?? Is there a solution for that?

Thanks

user2307713
  • 1
  • 1

1 Answers1

0

Couple of things, Base64 is encoding not encrypting so is not a secure way to hide data. Unless you are encrypting it with something else before base64 encoding you might want to change it.

The DB Shouldn't have a problem with it, it should just treat it like any other String. Though I have had this problem before when the database field length was less then the required length to store the encrypted string.

Check in the database manually in case it was truncated when you inserted the password. Also run a test in a SQL client to make sure it behaves unexpectedly, if it works in a SQL client your application is not sending the correct value to the database (or is filtering it out based on some other field)

cowls
  • 24,013
  • 8
  • 48
  • 78
  • The field length is long enough and the value is not truncated. When I just execute the query as is in the DB it's fine. The problem is with the named query. – user2307713 May 01 '13 at 16:32
  • What happened in the sql client? – cowls May 01 '13 at 16:32
  • This is the named query - @NamedQuery(name="Consumer.findConsumerByUsernameAndStatus", query="SELECT consumer FROM Consumer consumer WHERE consumer.status <>:status AND consumer.userName =:userName") – user2307713 May 01 '13 at 16:44
  • And the find method - public Consumer find(Object[] identifier,EntityManager entityManager) throws ModelException { try{ TypedQuery query=entityManager.createNamedQuery(NAMED_QUERY,Consumer.class); query.setParameter("userName",((String)convertIdentifier(identifier)).toLowerCase()); query.setParameter("status",Consumer.Status.RETIRED); query.setMaxResults(1); return query.getSingleResult(); }catch(NoResultException e){ //if no record found then return null return null; } } – user2307713 May 01 '13 at 16:44
  • And the userName is the encrypted property. – user2307713 May 01 '13 at 16:48
  • I would log the result of this: `(String)convertIdentifier(identifier)).toLowerCas‌​e()` and confirm the value logged matches the value in the db. And also confirm that the user is not having status that matches whatever `Consumer.Status.RETIRED` is – cowls May 01 '13 at 16:50
  • Ohhh there was an issue with the - toLowerCas‌​e() of course.. Thanks! – user2307713 May 01 '13 at 16:54