用户查找加密数据库字段
本质上，我有一个保存用户数据的表，所有这些数据都是 AES 加密的（在 BLOB 字段中）。
这意味着这些字段都不能被索引，这会减慢该表上的任何查询 - 特别是因为在进行任何匹配之前整个表需要解密......
... WHERE AES_DECRYPT(`user`.`email`, '{$sSomeKeyHere}') = '{$sSubmittedEmail}'
所以，我想要的是一个只包含哈希值的字段值，未加密，可以索引以用作快速查找。最好的查找可能是电子邮件地址的某种派生（小写、反转和散列或其他可复制过程），以便您可以有效地搜索电子邮件地址，而无需解密电子邮件地址......但我需要保留这一点安全的。
因此，我正在考虑的选项：
1：仅使用小写字母和 SHA-256（或 512）对电子邮件地址进行哈希处理，然后将其插入数据库
2：稍微复杂一些；小写加上一些其他可复制的功能，在散列之前对电子邮件地址进行加扰。
3：从 user.last_login_date （未加密）创建一个盐字符串，并使用它来创建带有电子邮件地址的加盐哈希 - 并在每次用户登录时更新查找字段（因为盐会改变）。然而，这需要一个稍微复杂的 SELECT 语句，仅限于 MySQL 引擎中内置的任何哈希函数，因为我需要使用上次登录日期重新创建哈希来执行搜索。
那么问题来了，只采取选项1可以吗？
选项 2 更好吗？
选项 3 是否像我认为的那样完全矫枉过正？
或者我错过了一些完全明显的事情，实际上有更好的解决方案？
Essentially I've got a table holding user data, all of which is AES encrypted (in BLOB fields).
This means that none of those fields can be indexed which will slow down any queries on that table - especially since the entire table will need decrypting before any matches can be made...
... WHERE AES_DECRYPT(`user`.`email`, '{$sSomeKeyHere}') = '{$sSubmittedEmail}'
So, what I want is a field that just contains a hash value, unencrypted, that can be indexed to use as a quick lookup. The best lookup will probably be some derivative of the email address (lowercase, reversed and hashed or some other replicable process) so that you can effectively search on the email address without having to decrypt the email address... but I need to keep that secure.
So, options I'm mulling over:
1: just lower-case and SHA-256 (or 512) hash the email address before inserting it into the database
2: slightly more convoluted; lower-case plus some other replicable function of scrambling the email address before hashing it.
3: creating a salt string from user.last_login_date (which isn't encrypted) and using that to create a salted hash with the email address - and updating the lookup field every time the user logs in (because the salt will have changed). However this requires a slightly more convoluted SELECT statement, limited to whatever hashing functions are built into the MySQL engine, as I'll need to recreate the hash using the last login date to perform the search.
So the question is, is it OK to just take option 1?
Is option 2 better?
Is option 3 as totally overkill as I think it is?
Or have I missed something totally obvious and there is, in fact, a much better solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
为什么不使用反向的呢？
加密的字符串可以通过
base64或类似的函数存储为VARCHAR类型。Why don't you use reversed one?
And encrypted strings can be stored into
VARCHARtype viabase64or something similar functions.选项 1。
可以对您的数据建立索引以进行搜索和比较，但只能针对加密的搜索词。
Option 1.
Your data can be indexed for searching and comparison, but only against encrypted search terms.