Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts

    password() problem [RESOLVED]

    Hello, we just moved our websites over to another datacenter and in the process noticing that we are having troubles logging into some of our sites.

    Just as a quick test I manually updated a test user's password to password=password('hello') but the funny thing is when I try to login with hello, no record found. I echo the query and paste it into mysql and it shows empty results. SO I then queried for it to show me the record for this user and it shows the encrypted password to start with an * and then all characters are CAPS. No matter what I update the password to, this is the case. All alpha chars are caps and the pass starts off with an *.

    Has anyone run into this before? All of this worked before and our data looks ok in our tables but if you update the pass, it screws it up bad.

    Thank you for any help with this.
    Last edited by tripwater; 09-11-2006 at 10:55 PM.

  • #2
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Using php 4.4.4
    mysql 4.1.21

  • #3
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Found out that mysql changed it's password mech in 4.1. So instead of doing the intelligent thing and creating a new_password() they made it so you had go back and change all of your password() to old_password() in order for your old databases to work properly. Got to love it

    Thanks

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    the issue isn't mysql so much as it is php. it is php that doesn't recognize the different password hashing algorithm.

  • #5
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Sorry but at the time I had this issue, I was in a shell dealing with mysql only. Using no php. So I have to disagree. I had passwords that were stored in the DB tables from a previous version of mysql when it was on the old server and I could not retrieve any of the records in Mysql with a standard select query in the shell until I called old_password() instead of password(). These are mysql functions used in a shell prompt on the server.

    Thanks for the response though

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    then your version of mysql was not upgraded properly. From the manual:

    The password hashing mechanism was updated in MySQL 4.1 to provide better security and to reduce the risk of passwords being intercepted. However, this new mechanism is understood only by MySQL 4.1 (and newer) servers and clients, which can result in some compatibility problems. A 4.1 or newer client can connect to a pre-4.1 server, because the client understands both the old and new password hashing mechanisms. However, a pre-4.1 client that attempts to connect to a 4.1 or newer server may run into difficulties. For example, a 3.23 mysql client that attempts to connect to a 5.1 server may fail with the following error message:

    shell> mysql -h localhost -u root
    Client does not support authentication protocol requested
    by server; consider upgrading MySQL client
    if you were using a 4.1 or newer client you could be able to connect to an older server.


    Here is the relevant area in the manual.

    This too from the manual on upgrading from 4.0 to 4.1:
    After upgrading, update the grant tables to obtain the new longer Password column that is needed for more secure handling of passwords. The procedure uses mysql_fix_privilege_tables and is described in Section 5.5.1, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”. If you do not do this, MySQL does not use the new more secure protocol to authenticate. Implications of the password-handling change for applications are given later in this section.
    Which is here in the manual.

    I'm not saying you should have been aware of both issues, but if your upgrade had updated the grant table you wouldn't have run into this issue.

    But again, you are correct, it isn't specifically a php error.

  • #7
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    are you not talking about mysql-users, while the OP is talking about users of an application running off the database?

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    GJay, you are right, I'm specifically talking of switching between versions of mysql. Adding users to the mysql database for permissions to interface with mysql server is the ONLY place you should use the mysql password hash. in all other circumstances you should NOT use it for that very reason.

    I was guessing they were talking about the back end for an upgrade.

    Obviously if tripwater is using the mysql password hash for regular tables you should look into using MD5 or SHA1 passwords and use them instead. otherwise the next time mysql changes their hashing algorithm you will run into the same problem. note too that SHA1 and MD5 passwords are transferable across database applications (should you move to mssql for instance) and mysql password hash is applicable to mysql only. They are also more secure and more difficult to hack than the mysql password hash.

  • #9
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Yes I am using this for a table field. To store someone's password, not the mysql login.

    SO if I was to use the MD5 encryption how do I check when decrypting? For example if I was to insert a user with

    Code:
    insert into new_accounts values ('login', MD5('password'));
    then want to check against what they type for a login, would it be

    Code:
    select * from new_accounts where Login='login' and Password=MD5('password')

    Is this the method? If not, could you please explain how to accomplish this? And I will start using this instead. Also how large does the field in the table need to be to hold a password that is encrypted with md5? Right now I use a char(16) binary.

    THank you again for your time.

  • #10
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    that's exactly how to do it, and I believe md5 requires a 32-length field


  •  

    Posting Permissions

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