Updating MySQL column values to a random 16 char alphanum string

Question

I've created a column in MySQL called password_salt_c

I want to set a random, 16 character, alphanumeric string for every record in the DB.

There are 215,000 records. I tried to do this in PHP but it exhausted all of the memory on the server.

How can this be done in MySQL?

I would suggest not using your own salts for password salting. There are a ton of libraries that will do a better job at that. Not to mention it solves, by default, your problem. — Andrei P., Sep 02 '14 at 20:59
UPDATE ... SET XXX=MD5(RAND()) ... or UUID if you prefer, or a combination go crazy =MD5(CONCAT(RAND(),UUID(),RAND()) — , Sep 02 '14 at 21:01

spencer7593 · Answer 1 · 2014-09-02T21:59:31.910

It's not clear to what problem you are actually trying to solve. (My answer intentionally disregards the name of the column you want to populate, and ignores the context of the question you asked.)

If I needed to assign "a random, 16 character, alphanumeric string" to a column, for all rows in a table, as a one time thing, I'd likely do something like this:

UPDATE mytable SET mycol =
CONCAT(SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      ,SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',FLOOR(1.0+RAND()*62),1)
      )

Note that the expression FLOOR(1.0+RAND()*62) is intended to return a pseudo-random integer in a range of 1 to 62. That's used as an argument to the SUBSTR function, to return a single alphanumeric character. Pound out sixteen repetitions, concatenate the results... voila... a pseudo-random 16 character alphanumeric string.

@Dagon: Seems sufficient to the specified task. (It's not truly "random", the sequence returned by repeated calls to RAND() is repeatable, given the same seed value.) I could shorten the code with a reference to user-defined variable, in place of the literal string; but I just don't know of any shorter "code path" that obtains the specified result using native MySQL functions. — spencer7593, Sep 02 '14 at 22:04
I thought my idea did it with a very short query. MD5 takes care of the alpha-numeric requirement and string length; then rand and or UUID to make them unique — , Sep 02 '14 at 23:46

score 0 · Answer 2 · answered Sep 02 '14 at 21:53

Please don't try to do your own security programming. It is hard to do correctly and easy for badguys to crack if you make the slightest mistake. If you're generating your own large collection of pseudorandom numbers, it becomes extraordinarily easy to make systematic mistakes.

Instead, take a look at the PHP functions password_hash and password_verify.

Instead of storing a separate salt field (as you propose to do), these functions follow the convention of including the salt in the hashed-password string. password_hash follows cryptographic best practice on randomizing the salt.

So, when a user presents a new plaintext password (changes or sets a password) you do this:

$hashed_password = password_hash ( $plaintext, PASSWORD_DEFAULT ); 
unset ( $plaintext );

You should store that $hashed_password string in a varchar(255) column in a database. The function generates a random salt, then uses it with the plaintext password to generate the hashed password, then stores both the salt and the hashed password together. Typically, it runs the hash function ten times in sequence.

(I'm using the unset() function out of an abundance of caution to avoid keeping a copy of the plaintext password in RAM any longer than necessary.)

Then, later when the user presents a password to gain access, you can do this:

if (password_verify( $plaintext, $hashed_password )) {
    /* the password is correct, grant access */
    unset( $plaintext );
    /* do what the user wanted to do. */
}
else {
   /* the password did not match */
}

Updating MySQL column values to a random 16 char alphanum string

2 Answers2