JPA和SQL Server列加密 [英] JPA and SQL Server column encryption

问题描述

我正在使用基于OpenJPA的WebSphere 7及其JPA 2.0实现，并且有些使我发疯的事情.我必须连接到使用数据库列加密的SQL Server 2008数据库.加密是通过几个数据库命令完成的:

I'm using WebSphere 7 and their JPA 2.0 implementation which is based on OpenJPA, and I have something driving me crazy. I have to connect to a SQL Server 2008 database that uses the database column encryption. The encryption is done by several database commands:

1-通过认证的开放对称密钥解密

1 - OPEN SYMMETRIC KEY DECRYPTION BY CERTIFICATION

2-使用数据库方法EncryptByKey或DecryptByKey执行插入/选择/更新/等

2 - Perform insert/select/update/etc using the database methods EncryptByKey or DecryptByKey

3-闭合对称键

我已经搜索过，看来OpenJPA似乎不支持此功能.有人知道如何使OpenJPA很好地使用这种类型的加密吗?还是我应该跳过该项目的JPA并使用老式的PreparedStatements?

I have searched and it does not appear that OpenJPA supports this functionality. Does anybody know how to get OpenJPA to play nicely with this type of encryption? Or should I just skip JPA for this project and use good old fashioned PreparedStatements?

推荐答案

是的，看起来本地查询只是这样做的唯一方法.结果就是这样的:

So yeah, it does look like doing a native query is only way to do this. So it comes out to something like this:

EntityManager em = getEntityManager();
Query openKey = em.createNativeQuery("OPEN SYMMETRIC KEY MY_KEY  DECRYPTION BY CERTIFICATE MY_CERT");
openKey.executeUpdate();

Query query = em.createNativeQuery("SELECT FIRSTNAME, LASTNAME, CONVERT(varchar, DECRYPTBYKEY(SSN)) as SSN from report where record_id = ?", Report.class);
query.setParameter(1, recordId);
report = (Report) query.getSingleResult();

Query closeKey = em.createNativeQuery("CLOSE SYMMETRIC KEY MY_KEY");
closeKey.executeUpdate();

这篇关于JPA和SQL Server列加密的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！