6

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.

Improve this question
3
  • 1
    Would you be OK with a stored procedure?
    – FrustratedWithFormsDesigner
    Feb 10, 2011 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, 2011 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, 2012 at 17:54

3 Answers 3

Reset to default
2

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.

Improve this answer
6
  • +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, 2011 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, 2011 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, 2011 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, 2011 at 23:13
  • @Randolph Potter - wouldn't the database be handling it for the API as well?
    – JeffO
    Feb 11, 2011 at 3:14
0

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.

Improve this answer
3
  • 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, 2011 at 22:51
  • Oh yes, forget the salt. Never mind.
    – James
    Feb 10, 2011 at 22:53
  • Interesting idea.
    – user2039
    Feb 10, 2011 at 22:54
0

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.

Improve this answer
1
  • 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, 2011 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.