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.
Rounding datetimes and timestamps in PostgreSQL
I’m working on a time billing application and needed a way to round punches to the nearest 15 minute increment from within the Postgres DB. The function below is the most elegant solution I could come up with. It could be easily modified to always round-up or round-down by replacing ROUND() with either CEIL() or FLOOR(), respectively.
CREATE OR REPLACE FUNCTION round_timestamp(
ts timestamptz
,round_secs int
) RETURNS timestamptz AS $$
DECLARE
_mystamp timestamp;
_round_secs decimal;
BEGIN
_round_secs := round_secs::decimal;
_mystamp := timestamptz 'epoch'
+ ROUND((EXTRACT(EPOCH FROM ts))::int / _round_secs) * _round_secs
* INTERVAL '1 second';
RETURN _mystamp;
END; $$ LANGUAGE plpgsql IMMUTABLE;
SELECT * FROM round_timestamp('2010-03-04 11:39:11', 900);
round_timestamp
------------------------
2010-03-04 11:45:00-05
(1 row)
Base36 Conversion in PostgreSQL
While these functions suited my needs, I’m not entirely certain that this is the best approach. Anytime you’re performing looping operations from within the database, you really ought to think hard as to whether there’s a better solution. I haven’t profiled these functions, but if you’re planning to make extensive use of them in a production environment, I’d recommend that you do so. Additionally, it could be argued that shifting the base of an integer is a presentational thing and ought to be done at the application-level anyhow.
CREATE OR REPLACE FUNCTION base36_encode(IN digits bigint, IN min_width int = 0)
RETURNS varchar AS $$
DECLARE
chars char[];
ret varchar;
val bigint;
BEGIN
chars := ARRAY['0','1','2','3','4','5','6','7','8','9'
,'A','B','C','D','E','F','G','H','I','J','K','L','M'
,'N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
val := digits;
ret := '';
IF val < 0 THEN
val := val * -1;
END IF;
WHILE val != 0 LOOP
ret := chars[(val % 36)+1] || ret;
val := val / 36;
END LOOP;
IF min_width > 0 AND char_length(ret) < min_width THEN
ret := lpad(ret, min_width, '0');
END IF;
RETURN ret;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
CREATE OR REPLACE FUNCTION base36_decode(IN base36 varchar)
RETURNS bigint AS $$
DECLARE
a char[];
ret bigint;
i int;
val int;
chars varchar;
BEGIN
chars := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
FOR i IN REVERSE char_length(base36)..1 LOOP
a := a || substring(upper(base36) FROM i FOR 1)::char;
END LOOP;
i := 0;
ret := 0;
WHILE i < (array_length(a,1)) LOOP
val := position(a[i+1] IN chars)-1;
ret := ret + (val * (36 ^ i));
i := i + 1;
END LOOP;
RETURN ret;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;