0

Sorry for this probably noobish question. So far, I've read password comparison/verification is implicitly understood to be made on the application layer and not in the database. For example in PHP, it would be done using the password_verify() function and not via a query like

SELECT * WHERE username = foo AND password_hash = $2y$10$blahblah

What would be a solid case against this? Or is this a perfectly valid approach to password comparison/verification?

3

Best practice is to used salted hashes for password. Comparing two salted hashes involves removing the salt from the stored password, appending to the clear-text password and rehashing and comparing equality. Because of this, it is not something that could easily be part of a SQL WHERE statement.

While I suppose this could be implemented at the DB layer, most implementations will retrieve the user by ID, and then compare the hashes in the app layer.

  • This is a good answer, but is omitting an important detail. Some DBMS have hash functions built in, e.g. SELECT * FROM user WHERE userid=? AND passwd=SHA1(?). Good password hashing uses "hard" hashes, though, such as bcrypt, scrypt, PBKDF2, etc., which are not built in to most DBMS. – Mark E. Haase Jan 22 '15 at 22:08
  • 1
    Fair enough. And of course there's always the possibility you are writing something DB-agnostic, so having this in the app layer gives you greater compatibility across data platforms. – Steven Jan 22 '15 at 23:03
  • 1
    @mehaase, there is another, far greater, problem with your example query: many db systems keep logs of the query parameters, which would in this case mean that the password is stored in plain text in the log files. – Monika Feb 9 '15 at 11:23
2

There's no security reason why you can't hash the password manually and compare using an SQL statement. There are non-security reasons to do so, though.

SELECT hashed_password FROM users WHERE userid=?

Retrieving the hashed password from the database and comparing in code only requires a single database access.

SELECT salt FROM users WHERE userid=?
SELECT 1 FROM users WHERE userid=? AND hashed_password=?

Comparing in SQL requires two accesses: one to get the salt (you are using per-user salts, right?) and one to perform the check. The added performance penalty may be an issue (or it may not).

  • Depends on which hash function you are using. The DBMS may have an implementation of the hash function; in that case, you can perform the check in a single query. – Mark E. Haase Jan 22 '15 at 22:09

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.