We all know the rules for good passwords. They should be at least 90 characters long, have no recognizable words or phrases, consist of 30% lowercase characters, 30% uppercase characters, and 40% special characters, and they should be changed daily, if not hourly. Where I work, if you forget your password, you're fired on the spot and recommended for execution.

OK, maybe I'm exaggerating a little, but let's quit jerking each other around and get serious. Password security is a big deal. Enrique knows this as well as the rest of us. Sadly, two developers he worked with missed the message.

Enrique was doing maintenance work on an application that allows users to register to buy and sell antiques. The registration process was simple. Enter a username, address, phone number, email address, and password, and you're in. Of course, if the username is taken, you're asked to enter a different username. And of course, if the password you've chosen is in use by another user, your registration fails.

No, I'm not kidding. Passwords had to be unique throughout the system. And judging by lists of user passwords I've seen, many users probably encountered this issue when trying to use a password of "password." The password field of the user table was also the primary key field making it impossible to have duplicate passwords, and they were stored unencrypted to make the verification process easier.

On the bright side, the original developers hadn't forgotten to set a unique constraint on the username, but Enrique was intrigued enough to email them and ask them about the architecture. They sent back a database diagram, and what Enrique saw next chilled him to the bone. The password field was used as the foreign key throughout the system. To reiterate, every table that recorded a bit of user information used an unencrypted password to identify the user.

A lot of words ran through Enrique's head, most of which can't be printed here. His biggest concern, though, was "what if the user wants to change their password?" See, most users have grown accustomed to luxuries like the ability to change their password. He fired back another email asking this very question.

"Well, we'd first check that no one else was using that password. Then we run sp_change_password."

sp_change_password consisted of a long list of UPDATE statements; one for each table that had any user related information in it. Any time new tables were added, they'd have to remember to update sp_change_password. None of these updates were done within a transaction.

Enrique asked about referential integrity — if a field was updated, other tables would point to data that didn't exist anymore. "Oh, we had that problem the first time, so we removed all of the foreign key constraints in the database and it works now." Since referential integrity wasn't preserved, cascading updates were impossible, requiring sp_change_password to be built, but then referential integrity couldn't be preserved, and now the circle is complete.

Enrique knew he'd have to push the other developers and let them know how bad of a design this was, so he pointed out possible system exploits. "Say someone made a script that'd create accounts like crazy, guessing different passwords each time. If a password error came back, they'd have a user's password. If a username error came back, they'd have a user's username. Eventually a matrix of usernames and passwords would be compiled, then it'd be a simple matter of attempting to log in."

"Ohhh... umm... I guess you're right," was all the developer could muster. "But then we'd have to change every table to use a username as the foreign key, maybe even apply constraints on the server, and change the token each user carries throughout the application to be their username!" It was a major change, but Enrique insisted they do the work.

The next day, the boss summoned Enrique to his office. "I hear there might be some major delays in our project, and I heard a funny rumor that you have something to do with it."

Enrique explained the situation, and fortunately his boss was not only reasonable, but had enough technical knowledge to understand the problem. He insisted that further development would come after Enrique's proposed changes to the system and that all proposed changes would require Enrique's approval before implementation.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!