I am supposed to come up with a plan to encrypt our existing personal information and be able to retrieve it and show it back to user through application if required. This is not PCI information nor password, so it requires a two way journey that is encrypt and decrypt both (and for the moment to keep things simple no tokenization, no public private key encryption either), as well as query the information through JPA if required.
Here is what I have: application engine JBOSS 7, database PostGres, Java EE 7 and JPA 2.
What I have thought of? Converting the existing data in postgres is doable with pgcrypto using
ALTER TABLE table_name ALTER column_name TYPE bytea USING pgp_sym_encrypt(column_name, key, [other options])
If there are any dependencies they can be handled but they are not a lot. Decryption and searching or displaying on the same would be
SELECT pgp_sym_decrypt(column_name, key) AS column_name FROM table_name WHERE ...
The entity files can be handled by just changing their data type and so on.
Where I am stuck? The system uses JPA 2 (hibernate implementation) for the queries presently that take advantage of the fields being in plain text. If I update the database the queries that are present would fail, they would need to rewritten anyways to handle encrypted data. But I would have to use native queries in JPA instead of JPQL, which could lead to problems in future in case we change our database.
So the question is there any way in JPA or JPQL other than native calls to query data? I did have a look at jasypt but documentation says it is for hibernate, and it looks specifically pertaining to encryption decryption only.
When I insert new data into table via JPA where do I encrypt it? Should I encrypt the data in the Java world using some cipher algorithm and then insert the bytes into table column. Or is there any elegant JPA way of doing this. Also do note that even if I take care that encryption algorithm used in pgcrypto and that from Java library are same, will they cause any inconsistency problem when we try to compare those data.
Are there better approaches to the problem? I do not mean in terms of security for now but in terms of ease of implementation and future robustness. We have lot of old code that we have recently updated to JSR 299 specification, and I would like to keep updates to minimal.
I only seek answer for first bullet point, the rest two are additional details if someone experienced wants to chip in.
