MySQL secure random string generator

Posted on

Question :

I am looking to generate a cryptographically secure string for password reset in MySQL. I know I could do this at the application level in PHP; but that requires the OpenSSL extension which many of my customers might not have.

If I can do it in MySQL 5 in a secure way that is widely available that would be ideal. Is this possible?

Note: This is for generating a secure token for password reset. It doesn’t have anything to do with a secure connection, so using https is not a solution.

Answer :

There are many encryption methods available in mySQL.

If you need two way encryption you could use aes_encrypt which has the accompanying aes_decrypt

If if you only need a secure hash then you could use sha2

The following statement could get you a similar result to openssl_random_pseudo_bytes

SELECT HEX(SHA2(CONCAT(NOW(), RAND(), UUID()), 512));

The statement above takes NOW() and concatenates it with RAND() and a UUID(), then performs a 512 bit SHA2() encryption on the result, and then converts that to HEX()

This is MySQL’s random number generator:

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);
}

You will need to pass in a large random value from the outside. Say that you need a 512 bit random number that is guaranteed unique. This may suffice:

sha2(concat(now(),rand(),gibberish512,id),512)

Say that gibberish512 is an arg to a stored procedure that calls rand on a lot of values, all within a few milliseconds, and a timestamp gives away when that was. MySQL rand() has less than 64bits of entropy on its own. passing in a random argument from the outside (generated from random typing, dice rolls, or a proper cryptorandom prng, etc).

BEGIN

declare r tinytext;
declare s tinytext default '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare n int;

set r='';

while length(r)<=16 do
    set n=rand() * 61;
    set r=concat(r,substr(s,n,1));
end while;

select r;

END

Leave a Reply

Your email address will not be published. Required fields are marked *