User login with one request and user salt for each user

Question

I decided to implement the user login using the salt for each user stored in the database. The salt has a password prefix that is hashed with the SHA and stored in the database.

In the past, when I didn’t use salt, I would use a typical method of counting the number of rows returned by a query using the user name and password entered by the user. Along with the user's salt, you need to get the salt before you can compare it with the saved password hash.

So, in order to avoid two requests (1 to get the salt, and the other to check the input credentials), I decided to get the salt AND the hashed password in one request based on the username entered. Sort of

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

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

If this is not clear, I think the key difference is that in the last method, I check the credentials in PHP before it is done in the database.

Are there any security flaws in this method or otherwise

Answer 1

You can execute this in one query:

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

Answer 2

... and which is the server in "in server-side code (PHP)"? I come from a world where the DBMS is the server, and everything else is the client. I suspect that you look at the web server as a “server” and the DBMS as a separate thing, not necessarily as the server itself. Well, that is the theory of relativity of points of view. (And don't make me run X11 servers against clients!)

Yes, it makes sense to simply collect the salt and the salty, hashed password for the username in one operation, and then generate the SHA result of the provided password and salt in PHP, comparing it with the received password hash value. It even means that the password does not move from PHP to the database server, so it does not matter whether this message is encrypted or not (in a situation where PHP and the DBMS are not on the same computer). It also downloads calculations from DBMS to PHP; whether this is an advantage depends on the relative workload of PHP and the DBMS.

As another answer indicates, you can get the answer by sending the password provided by the user to the DBMS and have a DBMS for computing hashes. Beware of simply quoting user input - avoid it to prevent SQL . This potentially provides a tracking password when traveling from PHP to DBMS. How important this is depends on your infrastructure.

Answer 3

I think your approach usually sounds. More important than the number of queries you use is that you retrieve a valid database password over the cable, even when the wrong password is entered by the user. Your request does this, while using the encryption function on your db server, you avoid this and make 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 of them are even used) from anyone who sniffs SQL queries by posting or in other ways (for example, by making queries visible with using SQL injection vulnerabilities or by submitting a request with an error message).

Answer 4

I do not see any flaws. "gahooa" gave a great answer. It will transfer the cost of processing from your web server to your database server, but it is only a question for you which one is best for downloading.