I am currently struggling with how to correctly implement storage of encrypted fields in a MySQL db, without knowing the encryption key. The result I am trying to achieve is to encrypt certain pieces of user provided information, which if I was legally obliged to provide to third parties would be encrypted without me being able to provide the key/s. Thus I concluded full partition encryption wouldn't make much sense, as I'd need to know the password for starting/resetting the server.

The only solution I could come up was to use the standard MySQL AES encryption, and derive the key from plaintext user password when it is supplied upon registration and login. A small problem associated with this is that I won't be able to process the encrypted data unless the user is logged in (hence cronjobs are out of the question, which might be quite troublesome), and I will have to keep the password in session, which isn't too much trouble I suppose.

I would be open to any alternative suggestions, or improvements of the simple implementation above, including using multiple servers if that would help.

  • 1
    You can't encrypt data without knowing the encryption key at least temporarily - but you can do it without knowing the decryption key (public key based encryption), But given the objective, since public-key encryption on the server still means you temporarily know the plain text message, an alternative would be to just do the encryption at the client? – symcbean Feb 1 '14 at 0:05
  • The problem is that I am not aware of any asymmetric encryption MySQL solutions, so I guess I would have to use the PHP OpenSSL extension, although I have absolutely no idea how to go about simple and user-friendly implementation. Unfortunately I am not familiar with client-side encryption, but I will look into it. Thanks for the suggestions! – cyber-guard Feb 3 '14 at 19:15

PBKDF2 is Password Based Key Derivation, and is used for situations like this. The wiki lists several different implementations in various languages. It works as you describe (with a salt and pseudo-random function).

I'd caution you to think thoroughly about the model -- i.e., how will you handle password resets? Or lost passwords? Sounds like the user would lose his/her data with this model.

  • 1
    Thanks, and you make an excellent point. There will only be few fields encrypted, which are not essential from the point of the app's backend (meaning that upon password reset, the encrypted data would be removed and re-entered by the user). I am aware that it is not by far the best model, however it is the only one where I don't end up with the encryption key on my end. Only alternative I could think of is to have master encryption key for all the fields, which would be also encrypted, and the key to the master key would be held on a third party server, which seems quite clumsy... – cyber-guard Jan 31 '14 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.