how to query and edit Password field

Posted on 2006-04-25
Last Modified: 2013-12-12
i have a simple table like this: name, address, email, password.
i insert records using password encryption like so ... PASSWORD('$password')
the password is "test" and it looks something like this "378b243e220ca493" when it is stored in mysql

i have an edit page and when i query a record the password field is displayed "378b243e220ca493". i want it to display "test" instead so that i can edit it.

Question by:hiepho
    LVL 28

    Accepted Solution

    To edit the password you must be loged in if you are using cookie or session retrive the password from the cookie or the session to display it in the edit box.

    LVL 12

    Assisted Solution

    Although gamebits is correct about his suggestion, the only drawback to such an approach is that if the server is setup to expire the cookie or session after a certain time, or worse, if the session or cookie is set to expire when the browser closes, than this information will be unretrievable unless sessions or cookies can be managed in a database where they can be re-activated at a later time.

    So, in response to your question, the nice part about storing passwords in a field using encryption is good for security, but bad for stuff like what you are asking about. I kept this in mind when I designed a website that stores the user's passwords in their record as an md5 encrypted password, but my alternative was to record their password into a seperate table along with their user_id so that if they needed to retrieve their passwords, they could simply supply some required information via a form and have the original password emailed to the email account that they originally used when they signed up on the site.

    I hope this offered a potential solution for you instead of seeming like a dismal 'no can do' answer that my response started out sounding like, but using gamebits suggestion will work as long as the existing session or cookie has yet to expire.

    Good luck
    LVL 6

    Assisted Solution

    Quote from the MySQL manual:

    "PASSWORD() encryption is one-way (not reversible)."


    Once you've used the password function on your value 'test' and it's changed back to '378b243e220ca493', there do not exist ANY functions that will change it back. Period. That's the way it was designed, in order to be more secure (so that system admins can't view everyone else's passwords).

    So gamebits is correct when he points out that you need to retrieve this password from somewhere else in your code. For instance, when you log in, store the password that you enter to $_SESSION['password'], and then use that to execute your MySQL queries. Then, if for some reason you need to retrieve what you actually entered as your password, there it is, in your session variables. Or you could put it in a cookie.

    But again, once it's encrypted and put into the database, there's no getting it back because PASSWORD() and other similar functions are one-way.

    Side note: Do not use PASSWORD() to store user's passwords. It's really a bad idea. The MySQL people have changed the algorithms in the PASSWORD() function from time to time with new upgrades, so although it works now, if you were to ever upgrade to a future version of MySQL, the PASSWORD() function might be different--in which case no one's passwords would work anymore. Use a one-way encryption function that is standardized and will not change, like MD5(), for instance. The MySQL manual even says to do this. Quote, from the same reference listed above:

    "The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider MD5() or SHA1() instead."
    LVL 9

    Assisted Solution

    you must install mcrypt.dll to extensions...

    $key = "yourkey";
    $password = "yourpass";

    function encrypt($key, $plain_text) {
          $plain_text = trim($plain_text);
          $iv = substr(md5($key), 0,mcrypt_get_iv_size (MCRYPT_CAST_256,MCRYPT_MODE_CFB));
          $c_t = mcrypt_cfb (MCRYPT_CAST_256, $key, $plain_text, MCRYPT_ENCRYPT, $iv);
          return trim(chop(base64_encode($c_t)));
    function decrypt($key, $c_t) {
          $c_t =  trim(chop(base64_decode($c_t)));
          $iv = substr(md5($key), 0,mcrypt_get_iv_size (MCRYPT_CAST_256,MCRYPT_MODE_CFB));
          $p_t = mcrypt_cfb (MCRYPT_CAST_256, $key, $c_t, MCRYPT_DECRYPT, $iv);
          return trim(chop($p_t));
    LVL 6

    Expert Comment

    So what tolgaong is saying here is that instead of using the MySQL function PASSWORD(), you should use the PHP function encrypt() that he just defined in his comment. So you would make your queries like this:

    "SELECT * FROM users WHERE username = '$username' AND PASSWORD = '" . encrypt($key, $password) . "'";

    Then you can actually retrieve this data and decrypt it using the PHP function decrypt() that he defined above. You could decode passwords using a query like this:

    "SELECT password FROM users WHERE [something something something]"

    And then with the MySQL result, you could get the password like this:

    $password = decrypt($key, $rowset[0]['password']);


    But all-in-all, that's very complicated. To top things off, as tolgaong has already astutely pointed out, you'd have to make sure that your webserver supports the mcrypt_ functions in PHP. Why do you need to decrypt passwords anyway? It's MUCH simpler to just use one-way encryption and then forget about them.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
    Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now