在MySQL数据库中对加密数据进行通配符搜索? [英] Wildcard searching of encrypted data in a MySQL database?

问题描述

我正在构建一个小型Web应用程序，该应用程序将为每个插入的人保存大约10条信息.出于数据保护的考虑，必须对大多数信息进行加密.

I am in the process of building a small web application which will hold around 10 pieces of information for every person inserted. Due to data protection the majority of this information must be encrypted.

使用CodeIgniter框架和CodeIgniter加密类，我可以在将应用程序信息存储在数据库中之前对其进行编码.CodeIgniter加密类使用PHP的mcrypt函数以及AES_256密码.

Using the CodeIgniter framework and the CodeIgniter encryption class I can encode the information on the application side before storing it in the database. The CodeIgniter encryption class uses PHP's mcrypt function along with the AES_256 cipher.

我遇到的问题是，我需要允许应用程序的用户在以后使用通配符搜索(可能还通过API)搜索存储的信息.

The problem I have is that I need to allow the users of the application to search the information stored using a wildcard search, Possibly also via an API at a later date.

任何人都会遇到类似问题的解决方案.我已经阅读了有关MySQL AES_ENCRYPT和AES_DECRYPT的信息，但它们仍然需要以明文形式来回传递密钥，我不愿意这样做.

Any body come across a solution for a similar problem. I've read about the MySQL AES_ENCRYPT and AES_DECRYPT but they still require passing a key back and forth in plain text which I am reluctant to do.

我目前的结论是，如果我希望继续沿这条路线前进，那么每次进行搜索(显然不好)时，全表解密是我唯一的解决方案.

I am currently at the conclusion that if I wish to continue on this route then a full table decryption is my only solution every time a search is made (obviously not good).

推荐答案

好吧，您必须先解密才能搜索解密的文本.

Well, you can't search in decrypted text without decoding it first, that is true.

但是，这并不意味着没有解决办法.例如，您可以为数据和哈希(sha1，md5，crc32，选择一个)倒排索引用于搜索.然后，您要做的就是哈希您正在使用的搜索词，在索引中查找它们并检索任何匹配的记录，这将只是表的一小部分，而不是整个表.

However, that doesn't mean that there are no ways around this. For example, you could make an inverted index of your data and hash (sha1, md5, crc32, pick one) the keys used for searching. All you have to do then is hash the search terms you're using, look them up in the index and retrieve any record that matches, which will only be a small part of the table instead of the entire thing.

通过对数据进行哈希处理(使用盐！)，可以避免以不安全的方式存储数据，同时仍可以对数据进行搜索，因为已为其建立了索引.在您真正确定哪些文档匹配之前，不需要解密.

By hashing the data (use a salt!), you avoid storing the data in an unsafe way, while you can still search through the data because you made an index for it. No decryption required until you're actually sure which documents match.

这篇关于在MySQL数据库中对加密数据进行通配符搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！