MySQL, generate a random 42 character string for a column

Question

I need to generate a 42 character string for a column for each row in my table ... it's used as a password recovery string. It must be 42 characters long and would be helpful if it was unique...

If the user forgets the password, generate a string. Upon an email, generating a string of "abc123" I presume it does the following: "SELECT FROM users WHERE recovery_string="abc123"

What do you mean it's "*used as a password recovery string*"? Can you elaborate? Suggest you read [The definitive guide to form based website authentication](http://stackoverflow.com/a/477578), especially "**PART IV: Forgotten Password Functionality**". — eggyal, Sep 26 '13 at 00:28
He means if the user forgets the password, generate a string. Upon an email, generating a string of "abc123" I presume it does the following: "SELECT FROM users WHERE recovery_string="abc123" - Now reset their password... — RobAtStackOverflow, Sep 26 '13 at 00:41
@Anonymous2011: That's what I had feared, but gave the OP the benefit of the doubt. As explained in the article to which I have linked, doing that would be **a very bad idea**. — eggyal, Sep 26 '13 at 00:42
@dcolumbus I have provided you with sufficient code, if you wish to use it. - Could you elaborate why that's a bad idea eggyal? As 9/10 services that integrate a password reset function, works on this principle: Generating a random string, and emailing, associating that string with the account...I'm not saying you are wrong, I'm merely curious. — RobAtStackOverflow, Sep 26 '13 at 00:46
@Anonymous2011: If you took the time to read the article to which I linked, you'd understand that a password reset token is a *password equivalent* and must be salted-hashed just like a password. Storing plaintext tokens in the database is as insecure as storing plaintext passwords in the database. — eggyal, Sep 26 '13 at 00:47

score 0 · Accepted Answer · answered Sep 26 '13 at 00:39

Please see this code, I have generated it just for this question.

It's probably not the best way of doing it I'm sure, but this will work:

Edit: Turned into a function, so you can choose which length. Old code is below.

New code:

    <?php
function GenerateRecoveryString($length)
{
    $time=time();
    $az="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
    //$time and $az is just random stuff to generate a hash out of. 
    $output = md5($time.$az);//The soon to be 42 char string.
    while(strlen($output)<$length)
    {
        $output.=md5($time.$az);//md5 only creates 32 bit strings - Lets create one that's 42 chars.
    }
    $output = substr($output, "0",$length);//The above example created 64 bit string - We need to cut it down.
    str_shuffle($az);//Randomize the string above, to ensure a different output next time.
    echo $output.' Length:'.strlen($output);
    }
    echo GenerateRecoveryString("42");
?>

<?php
$time=time();
$az="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
//$time and $az is just random stuff to generate a hash out of. 
$output = md5($time.$az);//The soon to be 42 char string.
while(strlen($output)<"42")
{
    $output.=md5($time.$az);//md5 only creates 32 bit strings - Lets create one that's 42 chars.
}
$output = substr($output, "0","42");//The above example created 64 bit string - We need to cut it down.
str_shuffle($az);//Randomize the string above, to ensure a different output next time.
echo $output.' Length:'.strlen($output);
?>

score 0 · Answer 2 · answered Sep 26 '13 at 00:44

If you want to do this on MySQL side you can try to use SHA1() by passing the result of concatenation of several columns that is unique. For example id, username an date when an account was created. It will procude you a unique string 40 characters in length.

UPDATE users 
   SET recovery_hash = SHA1(CONCAT(id, username, created));

Here is SQLFiddle demo

MySQL, generate a random 42 character string for a column

2 Answers2