How to generate random string to salt passowrd in sql?

Question

Got a question salting passwords in sql:

The code below salts a particular password by randomly generating a 10 character string:

Update Teacher 
SET    TeacherSalt = SUBSTRING(MD5(RAND()), -10), 
       TeacherPassword = SHA1(CONCAT('009b9b624aaecc4b3217dcd4bfee15ab704745d7',SUBSTRING(MD5(RAND()), -10)))
WHERE TeacherPassword = '009b9b624aaecc4b3217dcd4bfee15ab704745d7'

But what my question is that I want to change the salt so that the string it is generating comes from all of these characters:

./ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789

There are 63 characters. The php way of doing this is below:

$salt = ""; 
for ($i = 0; $i < 40; $i++) { 
   $salt .= substr(
     "./ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789", 
     mt_rand(0, 63), 
     1); 
}

But how can I write this in the sql way above?

Answer 1

It can be done in MySQL. The random word generation is not as pretty though. Regarding generating and applying the salts, that part isn't hard.

Use 2 statements to first generate salts for everyone, then apply them. (Note: if you really only want to apply it to one account, then add a WHERE clause.)

mysql> select * from salty;
+------+------+------+
| id   | pw   | salt |
+------+------+------+
|    1 | fish | NULL |
|    2 | bird | NULL |
|    3 | fish | NULL |
+------+------+------+

(Note that user 1 & 3 happen to have the same password. But you don't want them to be the same once salted and hashed.)

mysql> update salty set salt=SUBSTRING(MD5(RAND()), -10);

mysql> select * from salty;
+------+------+------------+
| id   | pw   | salt       |
+------+------+------------+
|    1 | fish | 00fe747c35 |
|    2 | bird | ee4a049076 |
|    3 | fish | 6a8285f03c |
+------+------+------------+

(Note: I'll show the specific-alphabet version later)

mysql> update salty set pw=sha1(concat(pw,salt));

mysql> select * from salty;
+------+------------------------------------------+------------+
| id   | pw                                       | salt       |
+------+------------------------------------------+------------+
|    1 | ac1b74c36b4d2426460562e8710bd467bd034fc8 | 00fe747c35 |
|    2 | d63d035f9cac1ac7c237774613b8b702d8c227df | ee4a049076 |
|    3 | 6a0b1e36f489ef959badf91b3daca87d207fb5de | 6a8285f03c |
+------+------------------------------------------+------------+

There you have in two statements, each row uniquely salted and hashed.

Now for randomly generating words of a specified alphabet, there's an ugly trick with ELT(). For a 10-letter word of a 64-character alphabet:

UPDATE salty SET salt=CONCAT(
  ELT(1+FLOOR(RAND()*64), 
  '.','/',
  'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
  'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
  '0','1','2','3','4','5','6','7','8','9'),
  ELT(1+FLOOR(RAND()*64), 
  '.','/',
  'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
  'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
  '0','1','2','3','4','5','6','7','8','9'),
  ELT(1+FLOOR(RAND()*64), 
  '.','/',
  'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
  'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
  '0','1','2','3','4','5','6','7','8','9'),
  ELT(1+FLOOR(RAND()*64),  
  '.','/',
  'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
  'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
  '0','1','2','3','4','5','6','7','8','9'),
  ELT(1+FLOOR(RAND()*64), 
  '.','/',
  'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
  'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
  '0','1','2','3','4','5','6','7','8','9'),
  ELT(1+FLOOR(RAND()*64), 
  '.','/',
  'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
  'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
  '0','1','2','3','4','5','6','7','8','9'),
  ELT(1+FLOOR(RAND()*64), 
  '.','/',
  'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
  'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
  '0','1','2','3','4','5','6','7','8','9'),
  ELT(1+FLOOR(RAND()*64), 
  '.','/',
  'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
  'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
  '0','1','2','3','4','5','6','7','8','9'),
  ELT(1+FLOOR(RAND()*64), 
  '.','/',
  'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
  'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
  '0','1','2','3','4','5','6','7','8','9'),
  ELT(1+FLOOR(RAND()*64), 
  '.','/',
  'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
  'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
  '0','1','2','3','4','5','6','7','8','9')
)

mysql> select * from salty;
+------+------+------------+
| id   | pw   | salt       |
+------+------+------------+
|    1 | fish | TzHO0e5I/k |
|    2 | bird | 65xLptoDZ3 |
|    3 | fish | JNok/SfmkG |
+------+------+------------+    

Hideous, isn't it? But doing that in a single MySQL statement may be much quicker than looping through in PHP and making one (or two) queries per row, especially if you have to apply it to a table with millions of records; a single ugly query vs making millions of queries one at a time.

But as the others say, SHA1 really isn't a good enough hash anymore.

If you do have a lot of records, it might make sense to use a couple of MySQL queries like that to update all the records to use SHA2 as an interim solution, then individually update them to stronger hashes over the course of time using PHP. You'd need some way to know which hash a given record used, of course.

As a side note, if you are only updating a single row (as in your example), then you could perhaps use a MySQL variable to temporarily hold the random generated string long enough to update two columns of the row:

mysql> SET @salt=SUBSTRING(MD5(RAND()), -10); UPDATE salty SET salt=@salt,pw=SHA1(CONCAT(pw,@salt)) WHERE id=2; SET @salt=NULL;

That way the same value in @salt is used for both setting salt and in the pw calculation. It won't work for an update of multiple rows though (they'd all end up with the same salt).