#1
  1. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,398
    Rep Power
    594

    Password and encrypt problem


    I have recently upgraded my Linux from Mandriva to CENTOS and have encountered a problem with 'password' and 'encrypt' which worked previously. The current version of MySQL is 14.14. I have a table that includes both an 'encrypt'ed and 'password' password. So I do the following:

    UPDATE mytable SET pwd=ENCRYPT('somepw'),pwdp=PASSWORD('somepw') WHERE username='myuser';

    The result is that 1 row was successfully updated. Next I do the following:

    SELECT username FROM mytable WHERE pwd=ENCRYPT('somepw');

    Result is 0 rows returned. I also tried:

    SELECT username FROM mytable WHERE pwdp=PASSWORD('somepw');

    Again the result is 0 rows returned.

    Now comes the interesting part. If I rerun the same update then look at the resultant strings in the table, 'pwd' is the same each time while 'pwdp' is different each time. First it does not make sense to me that 'pwdp' would change but explains why no rows were returned. Could that be some config problem? Second, while 'pwd' is consistent as expected, it makes no sense that it is not found in the table. Can someone help me make sense of all this and fix it? TIA.
    Last edited by gw1500se; October 11th, 2012 at 05:27 AM.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    Per the MySQL manual, you shouldn't use ENCRYPT or PASSWORD for storing application passwords in a MySQL database; the reason behind this is the fact that MySQL may log them into plaintext log files.

    ENCRYPT will substitute a random salt if you don't pass one in the second argument, so the fact that it returns no results isn't surprising; however, I am surprised that it would appear the same in the result.

    Some modes for PASSWORD will use a random salt too, I'm guessing that's probably what's happening here. I'm not too familiar with how it works and the manual is light on details since it is really only intended to be used for storing MySQL user account passwords. There is a system variable called "old_passwords" that you can set to change the behavior of PASSWORD.

    The current version of MySQL is 14.14
    MySQL is only up to version 5.6
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo