对加密字段进行部分搜索 [英] Partial search on Encrypted field
问题描述
最近我遇到了一个问题，即对数据库字段（例如SSN）进行加密，但仍然必须对其进行部分搜索。
Recently I am assigned with a problem, encrypting the database field such as SSN but still have to keep the "partial searching" working on it.
例如：SSN 123-45-6789在数据库中被加密为abcdxyz。当用户在搜索框中输入 2345时，它必须出现在结果中。
E.g: SSN 123-45-6789 is encrypted to abcdxyz in the database. When user enters "2345" to the search box then it has to come up in the result.
我们在数据库中有数百万条记录。我们正在使用SQL Server 2008 R2。我一直在四处搜寻，但仍未找到任何好的解决方案。
We have millions records in the database. We are using SQL server 2008 R2. I has been googling around but still not see any good solution yet. Is there any good solution for this ?
感谢您的帮助。
推荐答案
不，没有。如果可以搜索加密的字段，那么根据定义，它将被严重加密。可能的选择是：
No, there isn't. If it would be possible to search an encrypted field then by definition it would be badly encrypted. The possible alternatives are:
- 搜索已解密的值。在实践中，这实在难以忍受，因为每个查询都必须解密数百万个值。
- 仅搜索完全匹配项。由于加密的值是固定的，因此只有在加密安全的哈希值与加密数据一起存储并且对哈希值进行搜索的情况下，才可以进行精确匹配搜索。
- 使用透明数据加密
- Search on decrypted values. This is unbearable slow in practice as millions of values have to be decryption for each query.
- Only search exact matches. Since the encrypted values are salted, the exact match search is only possible if a cryptographically secure hash is stored along with the encrypted data and the search is done on the hash value.
- Use Transparent Data Encryption instead of column encryption.
选项3）到目前为止是最好的，但需要企业版许可。如果TDE不可用，则必须删除部分搜索的要求，没有现实的方法可以满足它。
Option 3) is by far the best, but requires Enterprise Edition licensing. If TDE is not available, then the requirement for partial search must be removed, there is no realistic way to satisfy it.
这篇关于对加密字段进行部分搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
