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.


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.

  • 1
    Sorry, I don't quite follow. And it is not your experience with PHP, but rather the logic. I have a real hard time understanding your proposition. Do you propose to get the password hash in PHP and then send two hashes to a database to be compared? What's the benefit over comparing hashes right in PHP without sending them into a database? The explanation is unclear as well. I don't get the purpose of the step 1 (returning a view). A regular query would return you a hash only if a user has been found all the same. How is the step 2 (returning a session code) is related to comparing hashes? – Your Common Sense Aug 27 '18 at 8:09
  • The idea was to get the password from the user input, hash it and send that to the DB, where the DB does the comparison of the stored hashed and the input hash and says yes or no ... – Jim D Sep 11 '18 at 2:09
  • 1
    From what I understand from your description, nothing much really changes in terms of handling of the hashed password. You still get the salt from the database (note that in many password hash schemes, the salt is made part of the hashed password, so you're going to have to split/join what the hashing functions return/need. Note also that many hashes also require additional parameters). The only difference is in the comparison of the result which you are moving to the database. The rest is an additional level of abstraction between the database and your web server, not related to the hash. – jcaron Sep 11 '18 at 6:59
  • I recommend you check the input/output of several common hash function implementations to see if your scheme would (easily) work for each of them. – jcaron Sep 11 '18 at 7:00

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.

| improve this answer | |
  • it means that the hashed password is never available to the PHP application or anyone using the app username and password to connect to SQL engine ... this in my mind is a good thing, as it limits the possibility of a hacker getting access to your hashes ... – Jim D Sep 11 '18 at 8:14
  • I don't get it. Who is that "anyone using the app"? If your PHP code is compromised, an attacker don't need your silly hashes as they can get the unhashed passwords as well. And if your PHP code is not compromised, what is the problem with having a hash compared in PHP? again you are overthinking it. – Your Common Sense Sep 11 '18 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.