MySQL: Generate Unique Salted, Hashed, Logins
February 9, 2015
Description
In the process of creating a SAML Identity Provider (IdP) & Service Provider (SP) solution for a unified login system, I needed to generate user information for testing. The applicable setting in the SAML IDP config/authsources file was:
1 'myaccount-sql' => array(
2 'sqlauth:SQL',
3 'dsn' => 'mysql:host=localhost;port=3333;dbname=myaccount_db',
4 'username' => 'myaccount_user',
5 'password' => 'password1234',
6 'query' => 'SELECT uid FROM users WHERE uid = :username AND password = SHA2(CONCAT((SELECT salt FROM users WHERE uid = :username), :password),256);',
7 ),So, I needed to load a username, hashed & salted password, and salt value for each row for testing.
MySQL Code
The following mysql code does the trick:
1 SET @uid = 'username';
2 SET @password ='password1234';
3 SET @salt=MD5(RAND(LAST_INSERT_ID()));
4 INSERT INTO users(uid,password,salt) VALUES (@uid, SHA2(CONCAT(@salt, @password),256), @salt);Explanation
The uid & password (first two lines) can be set to any value. The third line then sets the salt as a pseudo-random (enough for testing) MD5 hash. The final line inserts the uid and salt, as well as a hashed password created by concatenating the salt and password and then creating a SHA-256 hash.