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?

2 Answers 2

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.

3
  • 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. Commented Jan 22, 2015 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
    Commented Jan 22, 2015 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
    Commented Feb 9, 2015 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).

1
  • 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. Commented Jan 22, 2015 at 22:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .