Hack 59. Migrate to MD5 Passwords

Hack 59. Migrate to MD5 Passwords

Use a migration script to turn your plain-text passwords into MD5-encrypted passwords.

From years of consulting work, I can tell you that although people say their web applications have encrypted passwords, they often do not. Realistically, though, encrypting passwords is just not that difficult to do. Even worse, any site that can send you the exact text of your password when you click the "I forgot my password" link stores a copy of your password in clear text somewhere. Needless to say, this isn't a good thing.

So why are encrypted passwords so important? First, because anyone who gets access to the database through a security hole can get access to the entire system. Second, most people talk about using different passwords on different accounts, but end up using the same, or similar, passwords, simply because it's easier. Getting a password on one machine can mean having access to other, possibly more important accounts. This hack describes how to migrate a table of users and passwords from plain text to MD5 encryptions.

6.10.1. The Code

Save the code in Example 6-27 as schema.sql.

Example 6-27. The original schema file

DROP TABLE IF EXISTS users;
CREATE TABLE users (
	id MEDIUMINT NOT NULL AUTO_INCREMENT,
	name TEXT,
	pass TEXT,
	PRIMARY KEY( id ) 
);

Save the code in Example 6-28 as users.sql.

Example 6-28. The original nonencoded passwords

INSERT INTO users VALUES ( 0, "jack", "toronto" ); 
INSERT INTO users VALUES ( 0, "megan", "omaha" );

Save the code in Example 6-29 as migrate.php.

Example 6-29. The script to migrate from plain-text passwords to MD5-encoded passwords

<?php
require_once( "DB.php" );
$dsn = 'mysql://root:password@localhost/migpass';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$res = $db->query( "SELECT id, pass FROM users", array() );

$sth = $db->prepare( "UPDATE users SET pass=MD5(?) WHERE id=?" );

while( $res->fetchInto( $row ) )
{
	$db->execute( $sth, array( $row[1], $row[0] ) ); 
}
?>

Save the code in Example 6-30 as list.php.

Example 6-30. Example 6-30. The script to list the users

<?php
require_once( "DB.php" );
$dsn = 'mysql://root:password@localhost/migpass';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$res = $db->query( "SELECT id, name, pass FROM users", array() );

$sth = $db->prepare( "UPDATE users SET pass=MD5(?) WHERE id=?" );

while( $res->fetchInto( $row ) )
{
	print( $row[0]." - ".$row[1]." - ".$row[2]."\n" ); 
}
?>

Save the code in Example 6-31 as check.php.

Example 6-31. The script to check the password encoding

<?php
require_once( "DB.php" );
$dsn = 'mysql://root:password@localhost/migpass';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$user = "jack";
$pass = "toronto";

$res = $db->query( "SELECT id, name FROM users WHERE name=? AND pass=MD5(?)", 
	array( $user, $pass ) );

while( $res->fetchInto( $row ) ) 
{
	print( $row[0]." - ".$row[1]."\n" ); 
}
?>

There's really not much going on here; the guts of the conversion are handled by PHP in the MDB() function used within the migrate.php script.

6.10.2. Running the Hack

This hack uses the PHP command-line interpreter, so start on the command line by loading the MySQL database with the schema and some sample user data:

	% mysqladmin --user=root --password=password create migpass
	% mysql --user=root --password=password migpass < schema.sql
	% mysql --user=root --password=password migpass < users.sql

The first command creates the database. The second creates the users table within the database, and the final line adds the sample user accounts.

The next thing to do is to see whether the data has been loaded properly:

	% php list.php
	1 - jack - toronto
	2 - megan - omaha

This shows that there are two accounts in the table. The first is for a user named "jack" with a plain-text password of "toronto." The second is for a user named "megan" with a password of "omaha."

The next step is to migrate the passwords using the PHP scripts and then check for success:

	% php migrate.php
	% php list.php
	1 - jack - 79cca97018f48e834a46f1b634e9a427
	2 - megan - c365303299c8e35dbd443faa065feb5f

The first command converts the password text in the tables by running it through the MD5( ) conversion function in MySQL. The list.php script then shows the current contents of the database with the encrypted passwords.

The last step is to check to make sure that we can still authenticate users by using the MD5 function in the SQL query:

	% php check.php
	1 - jack

The check.php script tries to authenticate the account named "jack" with the incoming text, "toronto," for the password. Instead of using just the plaintext version of "toronto," the modified code needs to run the password through the MD5 function and compare that with the password value in the database. The same text run through MD5 will always produce the same output, so this is a valid way to compare MD5-encrypted passwords.

Because the application and database no longer store plain-text versions of users' passwords, the web application can no longer send out clear-text versions of these passwords if users request them. In addition to this migration script, then, the web application will also need to alter the flow behind the "forgot my password" functionality. Usually, this involves resetting the password to a safe (and random) value and then sending that new password to the email account associated with the account. Emails like this should provide a link that allows the account holder to reset the password to something he can remember (at least for a couple of days) and expiring the password sent in clear text after a few days.

6.10.3. See Also

• "Create a Login System" [Hack #57]

• "Apply Security by Role" [Hack #58]