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 aSELECT 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.