在MySQL中对加密数据进行搜索过滤 [英] Search filter on encrypted data in MySQL

问题描述

查询说明:假设我有一个数据库表，该表以加密形式存储所有用户的数据.我具有管理员可以搜索用户数据的功能.现在的问题是，管理员将在文本框中输入普通文本，我必须根据管理员的输入过滤用户列表(每次更改文本).因此，与此同时，我拥有大量以加密形式存储的数据，我必须根据Admin输入的普通文本对其进行过滤.

Query Description: Let say, I have a database table which stores all users' data in encrypted form. I have a functionality in which the Admin can search the user data. Now the problem is, Admin will enter the normal text in the textbox and I have to filter the user list(on every text change) according to the input of Admin. So meanwhile, I have the bunch of data in encrypted form and I have to filter it on the basis of normal text that Admin enters.

到目前为止，我提出的解决方案是，首先解密所有数据，然后再应用过滤器.但是我很好奇，如果我的数据库中有数百万条记录，那么当前的方法似乎毫无用处且效率低下.

What solution I came up till now is, I am decrypting all the data at first and then applying the filter. But I am curious to know, What if I have millions of records in my database then current way seems useless and inefficient.

有人可以帮助我以最有效的方式搜索加密数据吗?

Can anybody help me to search over encrypted data in the most efficient way?

任何帮助将不胜感激！

谢谢.

推荐答案

到目前为止，我提出的解决方案是，首先解密所有数据，然后再应用过滤器.但是我很好奇，如果我的数据库中有数百万条记录，那么当前的方法似乎毫无用处且效率低下.

What solution I came up till now is, I am decrypting all the data at first and then applying the filter. But I am curious to know, What if I have millions of records in my database then current way seems useless and inefficient.

您是正确的，这不是可扩展的解决方案.如果您想深入研究此问题，请参阅:

You're correct that this is not a scalable solution. If you want a deep dive into this problem, see: Building Searchable Encrypted Databases with PHP and SQL .

您要执行的操作是:

1. 使用经过身份验证的加密按原样存储加密的数据.
2. 在密文旁边存储明文的盲索引.
   • 盲目索引可以是 HMAC-SHA256(纯文本，分隔键)
   • 敏感值应改用 sodium_crypto_pwhash()或 hash_pbkdf2().
   • 要节省空间，请截断盲指标并将其视为Bloom过滤器.

1. Store encrypted data as-is, using authenticated encryption.
2. Store a blind index of the plaintext alongside the ciphertext.
   • A blind index can be HMAC-SHA256(plaintext, separate_key)
   • Sensitive values should use sodium_crypto_pwhash() or hash_pbkdf2() instead.
   • To save on space, truncate the blind index and treat it as a Bloom filter.

这为您提供了两全其美的优势:既可以抵御被动和主动攻击，又可以用于快速SELECT查询的数据加密.

This gives you the best of both worlds: Data encryption that resists both passive and active attacks, but also allows for use in quick SELECT queries.

更新(2019-03-27):而不是自己构建它，而是查看

Update (2019-03-27): Instead of building this yourself, check out CipherSweet which implements this design.

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