Block ciphers, salt, AES, MySQL, and best practices around credential storage

Question

I have a situation where I must store a password, as I am building a system to connect to another system. This other system only allows for a single user account, and the only way to connect to it is via a password. A hash is not appropriate here. I must store the password in a way that I can retrieve it.

Now, with the knowledge that this is not a perfect system, I am trying to limit damage should someone get access to the database somehow. As this database will need to be used by varying platforms, I have decided to use MySQL's own built-in encryption functions. This way, I don't need to worry about finding compatible encryption/decryption algorithm implementations for various languages and systems. I can just use MySQL's function in a query.

When storing a password, I will use AES_ENCRYPT("password", "encryption key"). Then I realized that I should probably use some salt so that if they were able to get one password, it would be harder to get others. But wait! What is the point? If they are able to get one password, they must have the encryption key, yes?

Besides, this is a block cipher. Salts can be near-useless in some situations.

/* Returns 8CBAB2A9260975FF965E5A7B02E213628CBAB2A9260975FF965E5A7B02E21362FBB5D173CBAFA44DC406B69D05A2072C */
SELECT HEX(AES_ENCRYPT("passwordpasswordpasswordpassword", "encryption key"));

/* Returns 8CBAB2A9260975FF965E5A7B02E213628CBAB2A9260975FF965E5A7B02E21362C49AF8D5B194770E64FEF88767206391 */
SELECT HEX(AES_ENCRYPT("passwordpasswordpasswordpassworda", "encryption key"));

My Questions

• Am I right in thinking that there is no reason to have a salt when using symmetrical encryption for situations like mine?

• Given that I must store a password in a way that allows me to retrieve the original value, are there any other methods I should be considering? (I am aware that I need to be careful about where and how the encryption key is stored, and that I need to secure my MySQL logs as well.)

Answer 1

Usually for standard AES you'd supply a nonce (the IV), in order to avoid the problem you describe.

A way to drastically improve the quality of the encrypted data is to use a different master password for every account instead of varying the IV. Basically this is some data which you mix with the password. You can do this many ways, the simplest is to do a concat.

E.g.

1. Create a random sequence.
2. Store nonce || HEX(AES_ENCRYPT(password_to_store, master_password || nonce)
3. Retrieve by extracting the nonce, then decrypt the data with master_password || nonce.

Here is an example, with the unique nonce 'iej383u8fjeiw' (Each time you encrypt you need to generate a new one)

SELECT CONCAT('iej383u8fjeiw', ':', HEX(AES_ENCRYPT("password", CONCAT("master_password", "iej383u8fjeiw")))) 
-> "iej383u8fjeiw:61224653D4DA33D57A42FE5E5E10DEA9"

SELECT AES_DECRYPT(UNHEX(SUBSTRING_INDEX('iej383u8fjeiw:61224653D4DA33D57A42FE5E5E10DEA9', ':', -1)), CONCAT('master_password', SUBSTRING_INDEX('iej383u8fjeiw:61224653D4DA33D57A42FE5E5E10DEA9', ':', 1))) 
-> "password"

Or with variables:

SELECT CONCAT(nonce, ':', HEX(AES_ENCRYPT(password_to_encrypt, CONCAT(master_password, nonce)))) 
-> encrypted password

SELECT AES_DECRYPT(UNHEX(SUBSTRING_INDEX(encrypted_password, ':', -1)), CONCAT(master_password, SUBSTRING_INDEX(encrypted_password, ':', 1)))
-> password_to_encrypt

That said, although significantly more secure than the version without a nonce, there are plenty of weaknesses and attacking vectors left. For example, logging of queries or sniffing mysql packets will reveal both password and master password!