SHA1 + Salt Hashing in SQL

Question

I have written the following procedure:

delimiter ;;
CREATE PROCEDURE hashPassword()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM users INTO n;
SET i=0;
WHILE i<n DO  
    SET @salt := FLOOR(RAND() * 0xFFFFFFFF);
    SELECT password FROM users LIMIT i,1 INTO @pwd;
    SELECT SHA1(CONCAT(@pwd, @salt)) INTO @hashedpwd;
        UPDATE users
        SET salt=@salt, hashedPassword=@hashedpwd
        WHERE password=@pwd;
    SET i := i + 1; 
END WHILE;
END;
;;

It stores the hashed string in the 'hashedPassword' column and the salt in the 'salt' column. The problem I'm having is that when you start off with the same plain password, you end up with the same salt and hash which is obviously not what I want.

Shouldn't SET @salt := FLOOR(RAND() * 0xFFFFFFFF); produce a random string independent from the password?

I'm quite lost, can anyone shed a light?

Solution

It was a simple fix, all I had to do was change the select statement to:

SELECT username, password  INTO @username, @pwd FROM users LIMIT i,1;

and replace WHERE password=@pwd; with WHERE username=@username .

score 1 · Accepted Answer · answered Aug 06 '14 at 10:09

1

Well, since you are updating your table with that query:

UPDATE users
SET salt=@salt, hashedPassword=@hashedpwd
WHERE password=@pwd;

it writes the same salt/hashed password for all rows with the given password. Thats the reason for the data you are seeing..

I'm pretty sure your RAND creates different random values each time, but you just update all rows at once with the same data.

answered Aug 06 '14 at 10:09

Olli

1,708
10
21

1

why not use the first select to fetch all data and use those for your updates? maybe cursors would help you here... see http://stackoverflow.com/questions/1745165/looping-over-result-sets-in-mysql for some details – Olli Aug 06 '14 at 10:19

SHA1 + Salt Hashing in SQL

1 Answers1