[ACCEPTED]-MySQL encrypted columns-encryption

I don't know if there is much sense in encrypting 38 data with user's password hash, especially if you 37 keep hash itself in the database. In that 36 case anyone who can access the encrypted 35 data can also access the password hash and 34 decrypt the data.

Another approach would 33 be to encrypt the data with the application-specific 32 key salted with some user-specific data. However, then 31 you face another problem: how to securely 30 store the application key. To that question 29 I do not know an easy answer, but keeping 28 it in your source code is probably good 27 enough if you fear that your database data 26 can be compromised, but not the source code 25 itself, e.g. if your database is stored 24 off-site (think Amazon S3).

Salting the app 23 key with the user's password helps if you 22 keep only password's hash in the database, but 21 can introduce another security flaw: you 20 have to keep user's password in clear text 19 in the applications session.

As for technical 18 solution, it is quite simple and sample code is available. You could 17 modify it as follows to encrypt the data 16 with the application password salted with 15 password hash:

INSERT INTO secure_table VALUES (
  1,
  AES_ENCRYPT(
    'plain text data',
    CONCAT(@application_password, @user_password))
);

In any case you would have 14 to store your application password somewhere 13 so I don't think that there is an easy approach 12 that provides perfect security.

Another approach 11 I can think of is to ask user for a short 10 PIN which you could use as an encryption 9 key. The PIN would not be stored in the 8 database, but you would need to ask user for 7 it every time you access their data.

And 6 of course your have to think of is the feasibility 5 of the encryption. You won't be able to 4 index or to search it without decryption. It 3 is probably required for a limited set of 2 data (e.g. credit card number), but I wouldn't 1 go to far with it.

To clarify one of the answers mentioned 10 in the question: "user/app key" is a randomly 9 generated private key, which is used to 8 encrypt the data. The private key never 7 changes (unless it's compromised). You encrypt 6 and store the private key with a password. Since 5 the private key is much smaller than the 4 data, it's much cheaper to change the password: you 3 simply decrypt the private key with the 2 old password and re-encrypt it with the 1 new password.

For data that is not user-specific (global), you 25 could maybe use a combination of symmetric 24 and asymmetric cipher. You could have an 23 extra password field that all users are required to enter 22 so that even if one user changes one's password, the 21 global data will still be usable to other 20 users with different passwords.

The extra password can 19 be bitwise xor'ed with another salt-like string inside the 18 source code. Together, it can form the symmetric 17 passkey to decrypt a private key stored in the database 16 (which will never change). After private key is decrypted 15 using the extra password, the private key can decrypt 14 and read all the data in the db. Private key 13 can be stored as session variable.

The public key, as 12 the name suggests, can reside as plain text 11 string in the db. When you need to write to db, use 10 public key to encrypt the data.

You can routinely 9 provide the users with a new extra password and re-encrypt 8 the static private key, followed by an xor'ing with 7 salt-like string.

If the data is user-specific data and not 6 meant for other users, you could use the 5 user's password without the extra-password 4 field to encrypt the private key. The administrator 3 could have another copy of the private keys 2 for specific users, which can be decrypted 1 using his password.

I don't think that's the best approach, unless 4 you're enforcing that users can never change 3 their password, or you have a way to re-encrypt 2 everything each time a user changes his/her 1 password.

You could store another key to encrypt/decrypt 22 user specific data which could be generated 21 when a new user is created. Let's call this 20 new key user key. This user key should also 19 be encrypted in database and the most direct 18 approach would be to encrypt it by means 17 of the user's password or any other data 16 which cointained the password (such as the 15 password and creation/modification time, etc.).

I 14 would keep in user's session the decrypted 13 user key to access user's data at any desired 12 time within session.

The issue of modifying 11 user's password involves re-encrypting the 10 user key by means of the new password which 9 is much more straight forward than re-encrypting 8 the whole bunch of user's data that can 7 be arbitrarily large. The user key remains 6 the same accross the life of the user data 5 in the system.

Of course this method can 4 only be used if authentication is carried 3 out by sending the actual user password 2 to the server at logon, since database only 1 desirably contains the hash of the password.

Say the password is pass1. And there are 24 a bunch of records encrypted with a key 23 generated from this. If the user now resets 22 the password to pass2, I have no way of 21 decrypting the data that was encrypted using 20 pass1

The key would need to be encrypted 19 in a reversable manner, so that it could 18 be decrypted using pass1 and re-encrypted 17 using pass2.

To summarize:

Stored in the database 16 is: the one-way encrypted password (for 15 password checking), the encryption key 14 for other data, reversibly encrypted using 13 the clear password (or at any rate, the 12 password encrypted in some different manner 11 than the way it is stored in the database), and 10 the other data, reversibly encrypted using 9 the clear encryption key.

Whenever you need 8 the other data, you must have the clear 7 (or differently encrypted than as stored 6 in the database) password, read the encryption 5 key, decrypt it with the password, and use 4 that to decrypt the other data.

When a password 3 is changed, the encryption key is decrypted 2 using the old password, encrypted using 1 the new password, and stored.

If you need to access the data without user 2 interaction (for database migration for 1 example), you won't have the key to decrypt.