MySQL General List,
MySQL 4.1.3-beta, phpMyAdmin 2.5.7-pl1, PHP 4.3.8
MySQL beginner, PHP intermediate, HTML and CSS advanced.
I have a table of information on roughly 150 users. Recently, I have
added a forum ( "Simple Machines Forum" - http://www.simplemachines.org/
) to the web site. Instead of making each user re-sign up for the forum,
I intend to merge the user information I have already so that the users
can transition to the forum with their current usernames and password.
So I want to take my current members table and move the data into
the new forum members table.
The current members tables uses the default PASSWORD encryption
built into MySQL. Although my current MySQL version is 4.1.3, I believe
this is the same password encryption that was used in MySQL 3.2. The
user data was created in an earlier version of MySQL, and later the
database was upgraded. I seem to remember at the time of upgrading that
some setting was required in order for the passwords to continue to
work. My web hosting service was kind enough to handle this issue for
me, but unfortunately this means that I am not entirely clear on what
kinds of settings were required and if they impact the encryption method
currently employed on my web site.
In any case, from within PHP I can confirm a user's password when
they log in by using PASSWORD ('$password') in a MySQL statement. They
are stored in a varchar field with a length of 16.
The new forum members table which is the destination of the data
uses a md5 Hmac encryption method, executed in PHP, and then stored in
MySQL as a varchar field with a length of 100.
The PHP function for the encryption method looks like this:
function md5_hmac($data, $key)
$key = str_pad(strlen($key) <= 64 ? $key : pack('H*', md5($key)), 64,
return md5(($key ^ str_repeat(chr(0x5c), 64)) . pack('H*', md5(($key ^
str_repeat(chr(0x36), 64)). $data)));
When calling the function, I use the following PHP code:
passwd = '". md5_hmac($password, strtolower($username)) . "'"
Since the md5_encryption is handled in PHP, will I be able to create
an equivelant command in MySQL when copying the fields from one table to
If so, what will be the command to extract the passwords from the
original table and encrypt them with the new method in the destination
What I've done so far:
After a little web searching and independent thinking, I thought
that one approach might be to decrypt the password fields and store them
as plain text in the destination table, and then once all the user data
is in, run a PHP command to run through and encrypt all the password
fields with the new encryption. Is this the best approach?
Any assistance is much appreciated. Thank you.