I am trying to migrate a db table into oscommerce. the old db stores plaintext passwords. OSCommerce does the following to store a password.

1) pick ten random numbers with mt_rand, concatenate them all, md5 the result, and take the first two chars. this is your salt.

2) concatenate the salt and your plaintext password and md5 the result. This is your hashed password.

3) concatenate your hashed password, a colon, and the 2-char salt and store that in the db.

Here's the code:

  function tep_encrypt_password($plain) {
    $password = '';

for ($i=0; $i<10; $i++) {
  $password .= tep_rand();
}

$salt = substr(md5($password), 0, 2);

$password = md5($salt . $plain) . ':' . $salt;

return $password;
  }

I am struggling to imagine a single query which could accomplish this for all my 20,000 records to be migrated. MySQL has all the functions necessary (concat, substring, md5, rand, etc), but I can't picture the structure of the SQL query which can both generate a distinct salt for each record and then remember it long enough to a) salt the password during hashing and b) get concatenated for insertion.

Any help would be much appreciated. This might be a start:

SELECT SUBSTRING(
    MD5(concat(rand(), rand(), rand(), rand(), rand())),
    1,
    2)

NOTE: That code only concatenates 5 random numbers and they are floats rather than ints, but this shouldn't really matter I don't think.

    its a one off, speed is not an issue, i'lld just loop through the db updating each record using php and mysql, you already have the basics, the only issue is creating the loop.

      Yes it's pretty easy if I use PHP. I was kind of hoping to keep it all in SQL. Oh well.

        sneakyimp;10926491 wrote:

        Yes it's pretty easy if I use PHP. I was kind of hoping to keep it all in SQL. Oh well.

        why? if it's one of, surly fast and easy are the primary motivators.

          So far, my migration is entirely SQL which makes things simple. I was hoping to avoid an extra step. This is probably a pipe dream. I'll write the PHP script. Thanks for your input.

            Write a Reply...