How to Store Two Way Encrypted Passwords (and Credit Cards) in MySQL

As part of my open source IMAP mail filtering applet, Filtered, I wrote code to securely encrypt and store email user names and passwords in a MySQL database so that they can be decrypted as well. The technique is outlined below.

Typically, passwords should be hashed in with a salt using one way encryption but in order to access user’s email accounts via IMAP, two way encryption was needed. Google doesn’t currently support OAuth authentication for free Gmail accounts.

I do not recommend storing credit cards in your own database – but if you have to, the techniques described below are directly applicable. Instead, use services such as Stripe which handle credit card security for you.

I do not even recommending storing email usernames and passwords in your database if you can avoid it. If you have to, here is one approach to doing so:

Warning: If you store email accounts on your server, you may become a target for hackers. Furthermore, if your server is compromised e.g. through SQL injection or other vector, everyone’s email account will be readily accessible. Use this code at your own risk. We are not liable for this code.

To avoid a rainbow table attack vector, we encrypt the username with the password with salts on both ends of the string. The salts are generated using the random string generator below.

We store an site_encryption_key variable on the server in a .ini file somewhere where it can be read by the application but not publicly accessible via http.

Warning: If someone discovers this and gains access to your database, they will likely be able to unlock your passwords. This file must be protected from public view – and your server must be protected from unauthorized access, electronically and physically.

Using the overall encryption key and the individual account salt we use php’s mcrypt library to encrypt the string. The individual salt slows down a brute force attack, an attack where they have the database access but not the site wide encryption key. This is common with SQL injection where usernames and passwords are stored in a commonly named user table.

Some developers prefer bcrypt over mcrypt for password storage, but bcrypt only provides one way password storage.

public function createCredentials($username,$password) {
     $salt = $this->random_string(4,4).'::'.$this->random_string(4,4);
     $salts = explode('::',$salt);
     $str = $salts[0].'::'.$username.'::'.$password.'::'.$salts[1];
     $td = mcrypt_module_open('tripledes', '', 'ecb', '');
     $iv = mcrypt_create_iv(mcrypt_enc_get_iv_size($td), MCRYPT_RAND);
     $site_encryption_key = Yii::app()->params['site_encryption_key'];     // stored in your ini file somewhere on your server
     mcrypt_generic_init($td, $site_encryption_key, $iv);
     $encrypted_data = mcrypt_generic($td, $str);
     mcrypt_generic_deinit($td);
     mcrypt_module_close($td);
     $result = array($salt,$encrypted_data);
     return $result;
   }

public function random_string($num_characters=5,$num_digits=3)
   {
     // via http://salman-w.blogspot.com/2009/06/generate-random-strings-using-php.html
       $character_set_array = array();
       $character_set_array[] = array('count' => $num_characters, 'characters' => 'abcdefghijklmnopqrstuvwxyz');
       $character_set_array[] = array('count' => $num_digits, 'characters' => '0123456789');
       $temp_array = array();
       foreach ($character_set_array as $character_set) {
           for ($i = 0; $i < $character_set['count']; $i++) {
               $temp_array[] = $character_set['characters'][rand(0, strlen($character_set['characters']) - 1)];
           }
       }
       shuffle($temp_array);
       return implode('', $temp_array);
   }

To store it to MySQL, we use base64_encoding so it can be stored in a standard text field.

// mysql definition
// 'cred' => 'string NOT NULL',
   $model->cred=base64_encode($salt_cred[1]); // storing the resulting encrypted string

To get the credentials, we read the encrypted string from MySQL, base64 decode the encrypted string and then use mcrypt to decrypt the string:

public function getCredentials($cred) {     
     $account_salt = Yii::app()->params['site_encryption_key'];     
     $cred = base64_decode($cred);
     $td = mcrypt_module_open('tripledes', '', 'ecb', '');
     $iv = mcrypt_create_iv(mcrypt_enc_get_iv_size($td), MCRYPT_RAND);
     mcrypt_generic_init($td, $site_encryption_key, $iv);
     $result = mdecrypt_generic($td,$cred);
     mcrypt_generic_deinit($td);
     mcrypt_module_close($td);
     $cred = explode('::',$result);
     array_shift($cred);
     array_pop($cred);   
     return $cred;
// $cred[0] is the username 
// $cred[1] is the unencrypted password
// unset($cred) when you are done with this
   }

Another precaution is to not to allow users to edit their passwords. Force them to add accounts from scratch. This prevents someone from leaving a machine logged in by accident and having someone visually discover their password. If you do need to display the password, use a type=”password” for the input field to display asterisks in place of the actual letters.

I hope this is helpful to you. Please feel free to leave feedback in the comments. Follow me @reifman.