Using SQL to compare hashed passwords

Question

Setup

A LAMP system (Linux, Apache, MySQL, PHP). Access to db is over the loopback interface (maybe not in the future - dependent on hosting solution).

The db contains a table with the username, password hash, hashed password (and some other irrelevant data for this discussion). The DB user for the app only has the execute permission, limiting access to stored procedures only.

Common solution

PHP Applications:

1. Read salt and hashedPW using "select * from table where user=?"
2. Hash user entered password with salt and do a string comparison in app to hashedPW from DB (i know password_hash and password_verify are now better, but is my way better still ?)
3. If strings match, user is authenticated.

My solution

The database has a few stored procedures (SP) relevant to this discussion:

1. check_user(user, HTTP_USER_AGENT, REMOTE_ADDR) SP returns a view of the users table that only returns the salt if username is found in query.
2. check_password(user, hashedPW, HTTP_USER_AGENT, REMOTE_ADDR) SP returns a unique session code (which is made by weakly hashing a random string with the HTTP_USER_AGENT + REMOTE_ADDR parrameters) if the hashes match or nothing if not. this session code is then stored in a SESSION var to be used in each stored procedure call to the DB to authenticate access.

PHP Applications:

1. check user exists in the system "call check_user(user, HTTP_USER_AGENT, REMOTE_ADDR)".
2. If user is found and address is not blocked, DB logs the username lookup, returns salt.
3. If REMOTE_ADDR is blocked, log and return 0
4. Hash user entered password with salt and send string to DB using "call check_password(user, hashedPW, HTTP_USER_AGENT, REMOTE_ADDR)".
5. DB checks the banned IPs list, if username, remote_addr & HTTP_USER_AGENT match, DB logs unsuccessful authentication, DB returns 0
6. If strings match, DB logs successful authentication, DB returns users data in list.
7. If Strings DONT match, DB logs unsuccessful authentication, DB returns 0.

Reasoning

My solution allows for:

1. Hashing is done in PHP, which allows for more processing (rounds / cycles?) to be done on the hashing, better hashing algorithms (such as Argon2).
2. SQL user can be setup with only limited permissions to run stored procedures, this way, even if the SQL connection username and password gets hacked, it can only be used to get the data provided in the stored procedures already in the DB and nothing else, so it cannot get a complete list of users and it cannot get a complete list of hashedPW.
3. Brute force attacks can be detected at the username testing level and stopped there, requiring only one DB call to block the brute force attacks.
4. The client gets shown invalid username or password, regardless of whether check_user or check_password fails.

Are there any drawbacks to this solution?

the only one i can see at the moment is that it cannot use [password_hash] which appears to be the preferred method.

I have looked at others responses to a similar question (here: Is it a good idea to let a database do a password check? and elsewhere), and most people state that hashing is best done in the PHP code, to allow for multi threading (handled by apache) and not tying up the SQL engine with doing things its not really designed for, so i came up with the above ... all feedback gratefully received.

PS i first learned PHP approx 20 years ago, but haven't touched it or any other web dev in almost 10 years, so be gently.

Answer 1

Honestly I don't see any difference from the common solution. You didn't disclose its drawbacks (and yes, password_hash and password_verify are the common solution), neither provided any benefit for your own one.

In the end your approach is almost the same as the classical one save for doing 2 database calls against one and throwing in a lot of unrelated stuff.
So I suppose you just overthought it. Keep it simple, do not reinvent the wheel.