MySQL Encrypt/Decrypt sensitive passwords and other information

I have an idea to store my passwords on mysql database. But I want to make them unreadable by browsing the databases. The final product will be holding more than 1,000 important passwords and I need safety to them. Passwords I store are the FTP details, Database login details, Control Panels and CMS admin area logins etc.

I want to make it a multi-user based application where more than one users can put their passwords in single database.
Passwords are visible to a user only if s/he created it.

The fields I have are:

  • Password Details
  • Login User Name
  • Login Password
  • Login URL, Host Name, SMTP Server name, etc.
  • Email address associated to the password
  • Expiry information
  • Other information
  • User ID and Password Group ID

I wonder how I can use the encryption to all fields: not even a user name should be readable.
Only if a user logged in, s/he can see the list of password groups and passwords.

MySQL dump and browsing the database via PHPMyAdmin should not contain any readable information about the passwords.

FYI, I have already completed a working version of the application that works without encryption.

What is the best encryption method you suggest to me?

You should never encrypt passwords as anyone with access to your server will be able to decrypt them easily.

Instead you should hash the passwords. Hashing is a one way conversion so that determining a value that will produce a given hash cannot be done easily (particularly if you provide a salt where the original value that produces the hash must contain that specific value). I’d recommend a SHA256 hash or as a minimum a SHA1 hash for passwords.

Encrypting other fields would prevent those maintaining the server the database is on from accidentally seeing the values but would not prevent them from deliberately accessing it as they will have access to see what encryption you use and to run the decryption.

Simpy make sure that when someone logs in via your application that they can only see their data and not everyone else’s.

I need to store passwords for retrieval purpose, and it is not the authentication system itself. So, hashing does not work for me at this moment.
The passwords are retrieved from this system and used in somewhere else, like an FTP account password.

My concern is to safeguard the passwords.
Can I somewhat think like: the decryption key will be provided by the user while he logs in to view the passwords?

You could always encrypt them with a password provided by each individual user onscreen (never stored in the database). The only drawback with this is that if the user forgets his/her password they will lose all their passwords.

I can understand the need to store encrypted data that needs to be decryptable. It would be a significant improvement over how a popular (and expensive) product like Plesk works, where it stores all email, client, FTP and DB usernames and passwords in plain text as default, hoping that splitting the data across multiple tables will baffle a would-be intruder. It’s not hard to beat though. If you are using Plesk on your servers then for the love of God, make sure that access to the PSA database is heavily restricted!

You’re probably better off to ask the question in the PHP forum, if it’s PHP that you’re using server-side. PHP has some very powerful encryption available. Take a look here:

mcrypt will probably be the one that you need to look at.

I’ve worked on PCI-DSS compliant systems in the past where we’ve had to store credit card information, and the way that we did it was to obfuscate the code as heavily as possible so that it was difficult for someone to work out what was happening if the filesystem was compromised. We also used a very large key that was partially stored in the database and partially stored in the filesystem. The reason being that if one is compromised then the attacker only gets half of the key and still has a lot of work to do to calculate the rest. We also put checks in place for the web panel to automatically disable any user account that appeared to be abusing the system (ie looked at too many cards in too short a time for it to be legitimate use).

Also, if you REALLY don’t want people to get in, then you need to restrict access to your database as well. Ensure that the root user has a heavy password and disallow any connections from an external IP. Create a user for the website that is unable to create new users or grant/revoke any permissions and only allow it access from the webserver IP. If you’re working on the database remotely then create yourself your own user account and again, restrict the IP to your workstation’s IP. If this varies regularly (dynamic IP) then you’ll need to ensure that you can change it through another account, preferably only accessible from the DB server itself, and SSH in with a secure password and make the change there. If you need to allow external apps access to the data then do it through an API of some kind, and never a direct connection to the database.

I think that ticks most of the boxes. Oh, and if you CAN use a hash instead of encryption, do so!

I would suggest using PHP’s mcrypt functions with the rijndael-256 algorithm to encrypt the data in the DB. Use the user’s login password’s hash as the key for each of their records. That way each record has it’s own key so if one key is discovered, only one record will be compromised instead of the whole table. That also has the added benefit of not storing any key on the filesystem.

If a user forgets their password, the admin can use their hash to decrypt the record, create a new login password for the user, and use the new password hash to re-encrypt the record.

Make sure that the table that contains the login hashes is in a separate database, with a separate user than the table that holds all the encrypted data so that if one DB is compromised, the attacker still doesn’t have access to the other.

Kduv has good advice. That said, lots of people have solved this problem for you, no need to build your own password storage service these days.