I am designing a login system for a project, and have an issue about it requiring two trips to the database when a user logs in.

  1. User types in username and password
  2. Database is polled and password hash is retrieved for comparative purposes (first trip)
  3. Code tests hash against entered password (and salt), and if verified, resets the session ID
  4. New session ID and username are sent back to the database to write a row to the login table, and generate a login ID for that session.

EDIT: I am using a random salt.

Does this design make sense? Am I missing something? Is my concern about two trips unfounded?

Comments and suggestions are welcome.

  • 1
    Would you be OK with a stored procedure? – FrustratedWithFormsDesigner Feb 10 '11 at 22:39
  • I'm not against stored procedures at all. I prefer doing as much data manipulation where the data is. What are your thoughts in this regard? – user2039 Feb 10 '11 at 22:49
  • @RandolphWest I love doing as much data manipulation where the data is! Seriously, some people get really angry on this point. – Anthony Apr 1 '12 at 17:54

Assuming you can choose a predictable salt (assuming you are only concerned with rainbow tables) you could always salt/hash the password, send that and the username to the database, OR if you implement you salt/hash in the database and connect to your database securely you can send username and plaintext or reversibly encrypted password then do the verification, logging and return the session ID in one go.

If the salt is randomly assigned and stored in the database (you are concerned with complexity in addition to rainbow tables) or your database cannot do the salt/has or you cannot get a truly secure connection to the database then the approach you describe is probably best.

Assuming a reasonably good database and that you site isn't google I wouldn't worry about the performance in two trips too much and there are things you can do to make certain that the statements stay in cache in many databases. Often that helps more than reducing the trips.

  • +1 Thanks. I'm doing random salt and storing it in the DB. Granted, I'm not planning for the project to be as big as Google (I couldn't afford the bandwidth), and I would use caching as much as possible. If I don't hear any other responses on this question, I'll happily accept this answer. My biggest concern is that I was "doing it wrong" in the first place. – user2039 Feb 10 '11 at 22:52
  • I am not a mysql guy, but I think they have some encryption functions and SSL support, so passing it encrypted or plaintext and doing the salt/hash in the db would not be an unreasonable option IMHO – Bill Feb 10 '11 at 23:01
  • I did briefly flirt with doing the encryption / decryption in the database. I decided to do it in code instead, so that the API can handle that. I do think I'll stick with my original idea now, because the second step is technically an audit trail as opposed to the actual login step (semantics, I know). I'll add in some tests to check for performance on login under load later in the development stage. Thanks for your help. – user2039 Feb 10 '11 at 23:09
  • Not to further complicate matters, but if you want a fail-secure audit trail you might want to consider logging the attempt before validating it. -- Good Luck regardless – Bill Feb 10 '11 at 23:13
  • @Randolph Potter - wouldn't the database be handling it for the API as well? – JeffO Feb 11 '11 at 3:14

if you want to do it in one trip, and it's in the same table,

update user set session stuff where password hash = whatever

then check how many rows were altered.

But DB queries should be really fast if everything is set up properly, so I wouldn't worry to much.

  • Altho, a gotcha to watch, MySQL will return zero if you match one row but nothing actually changes ie. You try to set the field to what it already was. – James Feb 10 '11 at 22:51
  • Oh yes, forget the salt. Never mind. – James Feb 10 '11 at 22:53
  • Interesting idea. – user2039 Feb 10 '11 at 22:54

I had the same dilemma. I ended up doing 2 trips to the database.

It would be nice to hash within the stored procedure. But I use a library to hash, and was too lazy too look up a hashing library that can be easily used in T-SQL.

One trip is more elegant, but the user will not notice 2 trips. It is NOT a bottle neck. Hell, google purposely increased their hits 16-fold with their suggest, and auto-search. They could have just sit back and waited for people to press the search button and reap the performance benefit. If google thinks a 16 fold hit increase on the world's most popular website is OK then I'm ok with 2 hits.

  • I specifically don't want to hash within the stored proc, to separate concerns. You make a good point about the Google Instant though, so you get +1 :-). – user2039 Mar 7 '11 at 1:11

Your Answer

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