Category Archives: Development

A better way to authenticate users via a SQL database.

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.

Share

Developers: Beware of scams posing at pre-employment tests on Craigslist.

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:

We need a person who is an expert with PHP and CGI/PERL. Pay will be $80,000 to $125,000 per year plus full medical and more. You will also get a laptop. Our recruiting for this position is a little bit unique. Below you will find a project to complete. Your performace on this project will determine if you become a permanent employee with us.

1. There Are Awards For The First 10 To Complete The Project
1: $1000
2-5: $500
5-7: $100
7-10: $50
10+ no award but still have the opportunity for employment.

YOU MUST COMPLETE THE BELOW PROJECT BEFORE SUNDAY JANUARY 21 2007.

What we need is a form with the following fields:
First Name
Last Name
Email Address
Username
Country (drop down list if possible)
Referred By
Do you agree to the terms and conditions?

Details:
-All Fields EXCEPT the Referred by field should be required.

-THE FORM MUST BE LIMITED TO 1 ENTRY PER PERSON. EITHER VIA IP ADDRESS, COOKIES, OR ANY OTHER WAY YOU CAN THINK OF.

- The form must end at a success page.

-When the form is submitted the information from the form needs to be sent to: promotions@freerollsource.net (use sendmail, smtp, mailto etc.)

-You must host this form on a free host of you choice (Must be a completely new account since we will need the account info to review your work).
Here is a list of some free hosts you can use that support cgi/perl and php (with MySQL Database). Go here for a list of some free hosts: http://www.free-webhosts.com/webhosting-01.php

We actually have a CGI script that does all this. It is acceptable to use it if you install it correctly. Email me to get the CGI script.

Once you have completed the project, email me with the URL to the form, as well as the information for the free host you used. I will request some additional information about your background as well. A resume would be nice thing to have ready.

GOOD LUCK! Feel free to email me (daniel@traudts.com) if you have any trouble.

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:

Hello,

I know my way around php and html; but there is one thing I cannot figure out to do.

Right now, I have a standard form that is send to my email address once completed.

What I need to be able to do is limit sign-up to one per IP address. I was hoping someone knew how to do this. I am willing to pay for information that helps me complete the task. Thank you.

Feel free to email me: daniel@traudts.com (NO SPAM!)

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…

Share

Dear PHP, I think it’s time we broke up.

Dear PHP,

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.

Love,
Jamie

Share