For years I’ve been using this simple design pattern when building a new user authentication backend for a project. Rather than just querying whether a username and password is valid, I perform a conditional
INSERT on a login history table. It’s a pretty obvious idea, but not one that I’ve personally encountered in other developers’ code. So, I figured I’d share… Here’s a typical users table within MySQL (though this approach can be easily adapted to any SQL-based RDBMS):
CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, username VARCHAR(255) NOT NULL UNIQUE, pwd_hash CHAR(96) NOT NULL, PRIMARY KEY(id) ); -- The SHA2() function requires MySQL 5.5.6 and above. -- I'm using the username as a salt for the pwd hash, so we concatenate the two strings. INSERT INTO users (username, pwd_hash) VALUES ('jamie',SHA2(CONCAT('jamie', 'mypassword'),384));
And to check whether we have a valid user, we could just do a
SELECT COUNT(*) on the
users table, like so:
SELECT COUNT(*) FROM users WHERE username = 'jamie' AND pwd_hash = SHA2(CONCAT('jamie', 'mypassword'),384);
This is the method that I most commonly see used. Using PHP, the developer would then likely use the
mysql_num_rows() function to check that exactly one row is returned. Occasionally a developer might select the actual row data instead and use one of the “fetch” functions. This is bit less efficient, but both approaches will work.
However, there is a better way to do this that will automatically track user logins with only 5 minutes of additional work and without needing to run additional queries. First, a new table is added:
CREATE TABLE login_history ( id INT NOT NULL AUTO_INCREMENT, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, ip_addr INT UNSIGNED NOT NULL, username VARCHAR(255) NOT NULL, PRIMARY KEY(id) );
I’m choosing to log the user’s IP address and username, however this could be extended to include the browser’s useragent or other information of interest. I’ve not linked the
username field to the
users via a foreign key because I may wish to use the same table to track invalid login attempts in the near future. If you’ll only concerned with ever tracking valid logins though, the foreign key should be used to aid in maintaining DB consistency.
To authenticate a user, we now simply use the following query:
INSERT INTO login_history (ip_addr, username) SELECT INET_ATON('192.168.1.1'), 'jamie' FROM users WHERE username = 'jamie' AND pwd_hash = SHA2(CONCAT('jamie', 'mypassword'),384);
This will only
INSERT a row into the
login_history table if a match is found on the
users table. Using PHP, the
mysql_ affected_ rows() function will return “1″ if the username/password combo is good or “0″ if not.
The addition of the
login_history table allows me to see how often a particular user is using the application and where they’re coming from. This handy to know in the case of unauthorized usage, or if I wish to reach out to my top users to better understand how they use the application and how I should prioritize feature requests.
I occasionally check-up on the web developer jobs on craigslist.org, and this is the second time this month I’ve noticed this particular scam. When I realize what these asshats are trying to pull, it enrages me. I figured I’d write about here to warn others. In case craigslist pulls the ad here it is:
Now I’ve read a lot job postings in my days and this sounds a tad bit fishy–even for craigslist. Secondly, the guy’s email address leads to a half-ass family website. Hmm… something isn’t kosher. Thirty seconds after googling his email address, I discover this forum posting, dating back only a month:
I think it’s pretty self-explanatory what this guy is trying to pull with his pie-in-the-sky, bogus job listing. Unfortunately, this is the second time this month that I’ve seen this particular scam; and this is in the smaller market of metro Detroit/Ann Arbor. The prior listing was a bit more convincing and probably lured in more developers than one that talked about a job interview with cash prizes attached. It’s something to be aware of out there…
We’ve been together for about five years now. I’ve had a lot of good times and I’ve really enjoyed being with you. However, the more we work together, the more concerned I get about our future. I’m sorry, but you don’t have the elegance that inspires me to want marry you. I think it’s time we broke up.
I think the biggest problem is you just don’t know who you are; it seems to have haunted you your entire life. You originally started life as a useful collection of Perl scripts for maintaining an online resume and were aptly named “‘Personal Home Page Tools”. That’s fine; I think most open-source technologies are born because a single person needed a specific itch scratched. However, then something weird happened. You were recreated by two new, adopted parents in 1997 and quickly promoted to version 3.0. The most obvious hint of this burgeoning identity crisis was that you were stripped of your name and given the nonsensical monkier “PHP: Hypertext Preprocessor” instead. Why were you ashamed of your roots? I think this may have been the first sign that you’re lacking a guiding philosophy found in many other successful open-source projects. You just aren’t well-rooted (unless we’re discussing your security issues again–you’re well-rooted there. But I don’t want to harp on that again today).
I’ve said it before and I’ll say it again: you just aren’t very consistant; it’s hard really get to know you even when I think I understand you. Yes, I do understand your need to be everything to everyone, but that neediness has hurt you along the way. Why do you use underscores in “strip_tags” and “str_ replace” but not in “strlen” or “wordwrap”? And why do you like “ip2long”, but it’s “strtolower”? Don’t even get me started on how you’ve implemented OOP features. The lack of consistancy can be maddening when I’m trying to work with you.
I know, I know… you say that you’re changing and I should be patient. “The next version.” But I think we’ve all heard that before. Quite honestly, I think you have some deep-seated personal issues that need to be resolved first. The important people in your life keep abandoning you when you need them most. This isn’t something a shopping trip and make-over can cure. I’m not trying to be an asshole here, but I just don’t know if you can ever really change.
While I’m talking about personal failings here, I wanted to touch on your… “performance”. Yeah, yeah–most of the time it’s good enough. Sometimes though, I just need it faster. I know there’s a few things you can use to enhance your performance, like APC and Turck MMCache. But we shouldn’t have to use those things. It just adds to the overall complexity and makes the relationship harder to maintain.
I hope we can still be friends. I’m sure I’m going to still be seeing you around a lot and I don’t really have problems working with you. However, you just aren’t marriage material and I need to think about the future. That Ruby down the hall seems to be getting a lot of attention lately; I might flirt with her a bit. I also want to give Python another shake. Her lack of curly braces and semicolons is kinda weird, but she has a nice personality. Most importantly though, Python seems to really understand herself.