how to query and edit Password field

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard DavisSenior Web DeveloperCommented:
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
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."
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));
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.