User Login with a single query and per-user password salt

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"

Answer 2

I see no drawbacks with it. "gahooa" gave an excellent answer as well. It'll shift the processing cost from your web server to your database server, but that's just a question for you as to which one is better suited to the load.

Answer 3

...and which is the server in 'in the server-side code (PHP)'? I come from a world where the DBMS is the server and everything else is the client. I suspect you're looking at a web-server as the 'server-side' and the DBMS as a separate gizmo, not necessarily as a server itself. Ah well, such is the theory of relativity of viewpoints. (And don't get me started on X11 servers vs clients!)

Yes, it is reasonable to simply collect the salt and the salted, hashed password for the user name in a single operation, and then generate the SHA result of the provided password and the salt in PHP, comparing it with the retrieved hashed password value. It even means that the password does not travel from the PHP to the database server, so it doesn't matter if that communication is encrypted or not (in the situation where the PHP and the DBMS are not on the same machine). It also offloads the computation from the DBMS to the PHP; whether this is a benefit depends on the relative workloads of the PHP and the DBMS.

As another answer points out, it is possible to get the answer by sending the user's supplied password to the DBMS and having the DBMS do the hash computation. Beware of simply quoting the user's input - escape it to prevent SQL injection. This potentially exposes the password to snooping on the trip from PHP to DBMS. How much that matters depends on your infrastructure.

Answer 4

I think your approach is generally sound. More important than how many queries you use, is whether you are retrieving the valid database password over the wire even when a bad password is input by the user. Your query does this, whereas by making use of an encryption function on your db server, you are avoiding this and making things more secure.

To make it even more secure, use a stored procedure or function that hides the type of encryption (or the fact that any is even being used) from anyone who is sniffing SQL queries over the wire, or through some other means (e.g., causing queries to be visible by using SQL injection vulnerabilities, or by exposing the query in an error message).