用户查询加密数据库字段 [英] User lookup on encrypted database fields
问题描述
本质上，我拥有一个持有用户数据的表，所有这些表都是AES加密的（在BLOB字段中）。
这意味着这些字段都不能索引，这将减慢该表上的任何查询 - 特别是因为整个表将需要解密之前任何匹配可以做...
... WHERE AES_DECRYPT（`user`.`email`，'{$ sSomeKeyHere}'）='{$ sSubmittedEmail}'
所以，我想要的是一个字段，只包含一个哈希值，未加密的，可以索引为快速查找。最好的查找可能是电子邮件地址的一些衍生（小写，反转和散列或其他可复制的过程），以便您可以有效地搜索电子邮件地址，而无需解密电子邮件地址...但我需要保留
所以，我正在研究的选项：
1：只是小写和SHA- 256（或512）将电子邮件地址插入数据库之前哈希的电子邮件地址
2：稍微复杂一些;小写加上一些其他可复制的功能，可以在填充电子邮件地址之前加密电子邮件地址。
3：从 user.last_login_date （它不加密），并使用它来创建一个带有电子邮件地址的盐化哈希 - 并且每次用户登录时更新查找字段（因为盐会改变）。然而，这需要稍微更复杂的 SELECT 语句，限于MySQL引擎中内置的任何哈希函数，因为我需要使用最后登录日期重新创建哈希值执行搜索。
所以问题是，是否可以选择1？
选项2是否更好？
选项3是否完全像我认为的那样？ >
或者我错过了一些非常明显的东西，实际上是一个更好的解决方案？
选项1。
您的数据可以索引用于搜索和比较，但仅针对加密的搜索字词。
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?
Option 1.
Your data can be indexed for searching and comparison, but only against encrypted search terms.
这篇关于用户查询加密数据库字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
