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

I want to give credit where due: Ray's answer above provided all of the insight for my response. In reviewing his answer I implemented it, however I cleaned up the way in which it was done via using functions and functions that call other functions. This is the cleaned up version of Ray's answer. (Thanks Ray for you great response!) This is also extremely much faster than having php generate it.

I created two functions. The first returns a single randomly selected character. The second recursively calls the first to build any length randomly generated string (such as used in a salt pepper scenario, etc). The second has been limited to string length of 255, however you can modify this easily enough. You can change the returned characters to any valid characters you want, just remember to count how many are in the array and thus change the 64 to how ever many you have entered. I intentionally re-arranged the order of all the characters in the array from what Ray had, just to fully randomize the output.

Function generation is blocked by default and must be entered at the command line vice through script (I use Dbeaver) so that a root admin (on the database) can be the only one entering it. I opted for this more secure route than changing my logging like some people recommend, or attempting to define whom the function is being generated by at the script beginning.

Because the function definition requires ; included in it, one must first change the command line delimiter to $$, then once completion of entered function, change it back to ; This is only because I am entering it via the console and not from my preferred tool (Dbeaver).

The function cannot use DECLARE, must instead use SET. The @ in front of SET variable name is required as it portrays we are setting user defined variables.

Now for the functions:

/* Testing Purposes:
    SELECT f_generateRandomLetter64();
*/

DROP FUNCTION IF EXISTS f_generateRandomLetter64;

DELIMITER $$

CREATE FUNCTION f_generateRandomLetter64()
RETURNS varchar(1)
CONTAINS SQL
READS SQL DATA
NOT DETERMINISTIC

BEGIN
    SET @thisResult = '';

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

    RETURN @thisResult;
END $$

DELIMITER ;

The next function:

/* Testing Purposes:
        SELECT f_generateRandomString(128);
        SELECT LENGTH(f_generateRandomString(10));
*/

DROP FUNCTION IF EXISTS f_generateRandomString;

DELIMITER $$

CREATE FUNCTION f_generateRandomString(var_strLength int)
RETURNS varchar(255)
CONTAINS SQL
READS SQL DATA
NOT DETERMINISTIC

BEGIN
    SET @thisResult = '';

  WHILE var_strLength > 0 DO
    SET @thisResult = CONCAT( @thisResult,f_generateRandomLetter64() );
    SET var_strLength = var_strLength -1;
  END WHILE;

    RETURN @thisResult;
END $$

DELIMITER ;

Answer 2

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

Answer 3

To put it simply, work it out in php. First grab the information from your table (note, use auto-incrementing IDs rather than the password - which might not be unique - to pick your row)

     function makeMeASalt($max=40){
         $i = 0;
         $salt = "";
         $characterList = "./ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
         while ($i < $max) {
            $salt .= $characterList{mt_rand(0, (strlen($characterList) - 1))};
            $i++;
         }
         return $salt;
     }

     $hash=crypt($password.makeMeASalt(40))

     $query="Update Teacher 
         SET TeacherSalt = '".$salt."', TeacherPassword = ".$hash."
         WHERE TeacherID = '".$teacherid."'";
     mysql_query($query) or die(mysql_error())

This also gets rid of the problem that's already been mentioned where your salt was not the same between fields!

Answer 4

If you really want to salt randomly than it can be done only by generating the random salt with php and encrypting the password with that salt and storing both salt key and password in two fields of the table. Table must have salt field and password field. However if you just want to use mysql to do encryption than have a look in here http://dev.mysql.com/doc/refman/5.5/en//encryption-functions.html

When we validate a user's login credentials we follow the same process, only this time we use the salt from our database instead of generating a new random one. We add the user supplied password to it, run our hashing algorithm, then compare the result with the hash stored in that user's profile.

The links below may give you some more ideas.

How do you securely store a user's password and salt in MySQL?

Where do you store your salt strings?

How insecure is a salted SHA1 compared to a salted SHA512

Salt Generation and open source software

I hope you got the idea now.