用户使用单个查询和每用户密码盐登录

Question

I've decided to implement a user login using a per-user salt, stored in the database. The salt is prefixed to a password which is hashed with SHA and stored in the databse.

In the past when I wasn't using a salt I would use the typical method of counting the number of rows returned by a query using the user inputted username and password. With a per user salt however, you need to get the salt before you can compare it with the stored password hash.

So to avoid having two queries (1 to get the salt and another to validate the input credentials) I decided to get the salt AND the hashed password in a single query based on the inputted username. Something like

SELECT users.salt, users.password
        FROM users   
        WHERE username = ?'

and then in the serverside code (PHP) I concatenate the salt with the inputted password, hash it and compare it with the password already taken from the database.

If that isn't clear, I guess the key difference is that in the latter method I am checking credentials in PHP wheras before this was done in the database.

Are there any drawbacks of this method, in terms security or otherwise

Answer 1

You could accomplish this in one query like this:

SELECT 
   SHA1(CONCAT(users.salt, "password-input")) = users.passwordhash
WHERE 
   username = "username-input"