[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL PASSWORD() function

Posted on 2006-06-07
8
Medium Priority
?
459 Views
Last Modified: 2008-02-01
So I'm revamping a client's already existing website and tearing my hair out over how poorly designed the existing framework is. The previous developer has all the user passwords encrypted in the database using MySQL's PASSWORD() function, rather than using something like md5() or sha1(). Trust me, I understand completely that this is bad practice, but I need to try and salvage the situation. The MySQL manual itself cautions against doing this, since they've been known to change their PASSWORD() algorithms from time to time. What I'm wondering is this:

What is the PHP equivalent of MySQL's password function? (I'm looking for code here--some function or algorithm that will produce the same value as MySQL's PASSWORD() function.) He's got MySQL v4.0.27, and I'd like to have the passwords encrypted by PHP functions in a consistent manner so that future upgrades to MySQL don't all of a sudden "break" his entire website.
0
Comment
Question by:soapergem
  • 4
  • 3
8 Comments
 
LVL 6

Author Comment

by:soapergem
ID: 16858712
Just to clarify, MySQL 4.0.27's password function produces a 16-character result. Here's a sample query:

SELECT PASSWORD('a') AS pass      =>    60671c896665c3fa
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 1500 total points
ID: 16859467
I doubt there is a PHP equivalent to the MySQL PASSWORD() function.

If you don't want to use the existing password mechanism, you are going to have to add a new column (new_password) and then ...

1 - Get username and see if it is in the DB.
2 - Is the new_password column blank.
     Yes
          Verify password using existing mysql PASSWORD() function.
          If OK, then use PHP to create new_password and empty existing password column.
     No
         Verify password using new_password column.

0
 
LVL 8

Expert Comment

by:hiteshgupta1
ID: 16859587
yes,there is no function in PHP which is equivalent to MYSWL PASSWORD() function.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Author Comment

by:soapergem
ID: 16863329
I don't need a built-in function, I just need a function. Make no mistake, if MySQL is capable of producing these password hashes, so is PHP; it's just a matter of knowing what algorithm to use (which I do not). I will use your suggestion to create a alternate passwork hash, though, RQualding, but I doubt that all of the users come to the site, so that would only work for a limited percent of the users (which means I have to leave this conversion method up indefinitely).
0
 
LVL 6

Author Comment

by:soapergem
ID: 16863538
Maybe I'm asking too much, but here are the complete contents of the file "password.c" from the MySQL 4.0.27 source, in case this helps anyone.

/* Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB

   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
   the Free Software Foundation; either version 2 of the License, or
   (at your option) any later version.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */

/* password checking routines */
/*****************************************************************************
  The main idea is that no password are sent between client & server on
  connection and that no password are saved in mysql in a decodable form.

  On connection a random string is generated and sent to the client.
  The client generates a new string with a random generator inited with
  the hash values from the password and the sent string.
  This 'check' string is sent to the server where it is compared with
  a string generated from the stored hash_value of the password and the
  random string.

  The password is saved (in user.password) by using the PASSWORD() function in
  mysql.

  Example:
    update user set password=PASSWORD("hello") where user="test"
  This saves a hashed number as a string in the password field.
*****************************************************************************/

#include <my_global.h>
#include <my_sys.h>
#include <m_string.h>
#include "mysql.h"


void randominit(struct rand_struct *rand_st,ulong seed1, ulong seed2)
{                                    /* For mysql 3.21.# */
#ifdef HAVE_purify
  bzero((char*) rand_st,sizeof(*rand_st));      /* Avoid UMC varnings */
#endif
  rand_st->max_value= 0x3FFFFFFFL;
  rand_st->max_value_dbl=(double) rand_st->max_value;
  rand_st->seed1=seed1%rand_st->max_value ;
  rand_st->seed2=seed2%rand_st->max_value;
}

static void old_randominit(struct rand_struct *rand_st,ulong seed1)
{                                    /* For mysql 3.20.# */
  rand_st->max_value= 0x01FFFFFFL;
  rand_st->max_value_dbl=(double) rand_st->max_value;
  seed1%=rand_st->max_value;
  rand_st->seed1=seed1 ; rand_st->seed2=seed1/2;
}

double my_rnd(struct rand_struct *rand_st)
{
  rand_st->seed1=(rand_st->seed1*3+rand_st->seed2) % rand_st->max_value;
  rand_st->seed2=(rand_st->seed1+rand_st->seed2+33) % rand_st->max_value;
  return (((double) rand_st->seed1)/rand_st->max_value_dbl);
}

void hash_password(ulong *result, const char *password)
{
  register ulong nr=1345345333L, add=7, nr2=0x12345671L;
  ulong tmp;
  for (; *password ; password++)
  {
    if (*password == ' ' || *password == '\t')
      continue;                  /* skipp space in password */
    tmp= (ulong) (uchar) *password;
    nr^= (((nr & 63)+add)*tmp)+ (nr << 8);
    nr2+=(nr2 << 8) ^ nr;
    add+=tmp;
  }
  result[0]=nr & (((ulong) 1L << 31) -1L); /* Don't use sign bit (str2int) */;
  result[1]=nr2 & (((ulong) 1L << 31) -1L);
  return;
}

void make_scrambled_password(char *to,const char *password)
{
  ulong hash_res[2];
  hash_password(hash_res,password);
  sprintf(to,"%08lx%08lx",hash_res[0],hash_res[1]);
}

static inline unsigned int char_val(char X)
{
  return (uint) (X >= '0' && X <= '9' ? X-'0' :
             X >= 'A' && X <= 'Z' ? X-'A'+10 :
             X-'a'+10);
}

/*
** This code assumes that len(password) is divideable with 8 and that
** res is big enough (2 in mysql)
*/

void get_salt_from_password(ulong *res,const char *password)
{
  res[0]=res[1]=0;
  if (password)
  {
    while (*password)
    {
      ulong val=0;
      uint i;
      for (i=0 ; i < 8 ; i++)
      val=(val << 4)+char_val(*password++);
      *res++=val;
    }
  }
  return;
}

void make_password_from_salt(char *to, ulong *hash_res)
{
  sprintf(to,"%08lx%08lx",hash_res[0],hash_res[1]);
}


/*
 * Genererate a new message based on message and password
 * The same thing is done in client and server and the results are checked.
 */

char *scramble(char *to,const char *message,const char *password,
             my_bool old_ver)
{
  struct rand_struct rand_st;
  ulong hash_pass[2],hash_message[2];
  if (password && password[0])
  {
    char *to_start=to;
    hash_password(hash_pass,password);
    hash_password(hash_message,message);
    if (old_ver)
      old_randominit(&rand_st,hash_pass[0] ^ hash_message[0]);
    else
      randominit(&rand_st,hash_pass[0] ^ hash_message[0],
             hash_pass[1] ^ hash_message[1]);
    while (*message++)
      *to++= (char) (floor(my_rnd(&rand_st)*31)+64);
    if (!old_ver)
    {                                    /* Make it harder to break */
      char extra=(char) (floor(my_rnd(&rand_st)*31));
      while (to_start != to)
      *(to_start++)^=extra;
    }
  }
  *to=0;
  return to;
}


my_bool check_scramble(const char *scrambled, const char *message,
                   ulong *hash_pass, my_bool old_ver)
{
  struct rand_struct rand_st;
  ulong hash_message[2];
  char buff[16],*to,extra;                  /* Big enough for check */
  const char *pos;

  hash_password(hash_message,message);
  if (old_ver)
    old_randominit(&rand_st,hash_pass[0] ^ hash_message[0]);
  else
    randominit(&rand_st,hash_pass[0] ^ hash_message[0],
             hash_pass[1] ^ hash_message[1]);
  to=buff;
  for (pos=scrambled ; *pos ; pos++)
    *to++=(char) (floor(my_rnd(&rand_st)*31)+64);
  if (old_ver)
    extra=0;
  else
    extra=(char) (floor(my_rnd(&rand_st)*31));
  to=buff;
  while (*scrambled)
  {
    if (*scrambled++ != (char) (*to++ ^ extra))
      return 1;                              /* Wrong password */
  }
  return 0;
}
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16868294
Maybe.

But if you create a single mailshot saying that they require a login by the end of the year or their account MAY be closed ...

And if they don't login for 7 months, you can be fairly confident of that being a dead account.
0
 
LVL 6

Author Comment

by:soapergem
ID: 16871172
Yeah, that's a good idea. Thanks.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16883732
NP.

In fact, you could simply hive the dead accounts off and make a small amendment to the login code.

If their login details cannot be found in the live DB, then see if they are an old account returning and restore there account from.

So, 7 months to move live to old and say another 12 months and kill the old.

Any data attached to the old accounts is an issue.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month18 days, 10 hours left to enroll

834 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