SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict bimalpoudel's Avatar
    Join Date
    Feb 2009
    Location
    Kathmandu, Nepal
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?
    Bimal Poudel @ Sanjaal Framework over Smarty Template Engine
    ASKING INTERESTING QUESTIONS ON SITEPOINT FOURM

    Hire for coding support - PHP/MySQL

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,840
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Addict bimalpoudel's Avatar
    Join Date
    Feb 2009
    Location
    Kathmandu, Nepal
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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?
    Bimal Poudel @ Sanjaal Framework over Smarty Template Engine
    ASKING INTERESTING QUESTIONS ON SITEPOINT FOURM

    Hire for coding support - PHP/MySQL

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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: http://php.net/manual/en/refs.crypto.php

    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!

  6. #6
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by kduv; Aug 14, 2012 at 17:21. Reason: Typo

  7. #7
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,638
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •