6

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.

Improve this question
1
  • if you read the source code for MySQL's rand() function, you can tell that SELECT HEX(SHA2(CONCAT(NOW(),RAND()),512)) is incredibly inadequate. First, because it returns a float from two long values divided, it can't possibly have more than 64 bits of real entropy. Taking collisions into account, it's probably 32 bits only. Given that there are logs and timestamps everywhere, now() only adds a few more bits. Get a huge (256-bits) true random string from the user, and concat with that value plus a unique number; roughly like Fortuna keystream. That RNG can give about 1MB of bits. Commented Jul 5, 2016 at 19:13

3 Answers 3

Reset to default
4
+25

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()

Improve this answer
6
  • if we assume that rand() is a bad function, and now() correlates too closely with time stamps related to these updates in audits and logs; how does this compare to an actual cryptorandom function that is designed for this (creating iv and keys, etc). Commented Jul 2, 2016 at 1:16
  • I did some extensive checking up on this, and this accepted answer is not right. Look at the source code for MySQL rand(). It is far less than than 64 bits, while time correlates with timestamps everywhere else. 512 vs 256 doesn't matter, as the hash cycles through too few values, with easy correlations. Think: "I'm thinking of a hash of a number from 1 to 100"... Here is a fix: Supply a one-time random (256bit or more) arg. CONCAT(NOW(),RAND(),gibberish). Replace gibberish as often as possible. It's effectively the Fortuna PRNG where small random 256bits safely expanded up to about 1MB. Commented Jul 7, 2016 at 17:52
  • why not just use a UUID then.... Commented Jul 7, 2016 at 18:09
  • it needs to come from outside of MySQL (from the user) because MySQL's rand is very weak. a 256-bit random number cannot be created from a 64-bit float, even if that float was totally random. timestamp is not as useful as you think. you run an update statement that makes a bunch of rand calls within a few milliseconds, and leaves a timestamp that says when it happened. Commented Jul 7, 2016 at 18:11
  • the use case is a stored procedure that generates random keys. Commented Jul 7, 2016 at 18:19
0

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).

Improve this answer
0 
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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.