Merging two tables
I need to do this just once... I have table zip_codes and table hardiness_zones In this case, the key will be the actual zip codes. hardiness_zones has two fields, zone_start and zone_end, these are all empty in the zip_codes table. I just need to move the data over, where the zip codes are ==. 4.0.18-standard-log UPDATE zip_codes, hardiness_zones SET zip_codes.zone_start=hardiness_zones.zone_start, zip_codes.zone_end=hardiness_zones.zone_end; Is that correct, and how can I test this before I do the real thing, aside from working on a copy of the table, is there something where I can run it and have it not really do anything, kinda like EXPLAIN, but not exactly. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merging two tables
Scott Haneda [EMAIL PROTECTED] wrote on 12/16/2005 08:46:29 PM: I need to do this just once... I have table zip_codes and table hardiness_zones In this case, the key will be the actual zip codes. hardiness_zones has two fields, zone_start and zone_end, these are all empty in the zip_codes table. I just need to move the data over, where the zip codes are ==. 4.0.18-standard-log UPDATE zip_codes, hardiness_zones SET zip_codes.zone_start=hardiness_zones.zone_start, zip_codes.zone_end=hardiness_zones.zone_end; Is that correct, and how can I test this before I do the real thing, aside from working on a copy of the table, is there something where I can run it and have it not really do anything, kinda like EXPLAIN, but not exactly. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. When joining tables, you need to specify the conditions on which the two tables are supposed to match. If you do not, you get a Cartesian product of the two tables. I don't see your matching condition. Here is how I would write it (using an explicit JOIN) UPDATE zip_codes zc INNER JOIN hardiness_zones hz ON hz.zip_code = zc.zipcode SET zc.zone_start = hz.zone_start ,zc.zone_end = hz.zone_end; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Merging two tables which contain passwords with different encryption methods
MySQL General List,
Server specifications:
MySQL 4.1.3-beta, phpMyAdmin 2.5.7-pl1, PHP 4.3.8
My specifications:
MySQL beginner, PHP intermediate, HTML and CSS advanced.
The situation:
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,
chr(0x00));
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)) . '
The Questions:
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
another?
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
table?
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.
Dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merging two tables which contain passwords with different encryption methods
Dave wrote: [snip] 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. It did change between MySQL 3.2 and 4.1. You need the old-passwords configuration directive, it is in the MySQL manual at dev.mysql.com. [snip] The Questions: 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 another? 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 table? 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? You can't decrypt the password fields. That's the point of *one-way* hashes of the type that PASSWORD() and MD5() use. You need to either get all your users to reset their passwords, or continue to use the old password hashing algorithm until they do. -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ If you find my advice useful, please consider donating to a poor student! You can choose whatever amount you think my advice was worth to you. http://tinyurl.com/7oa5s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merging two tables which contain passwords with different encryption methods
It did change between MySQL 3.2 and 4.1. You need the old-passwords configuration directive, it is in the MySQL manual at dev.mysql.com. Thank you. I believe the old-passwords configuration has already been set by my web host. You can't decrypt the password fields. That's the point of *one-way* hashes of the type that PASSWORD() and MD5() use. I suspected something like this was the case, which was why I wanted to double check here first. Thank you for confirming. You need to either get all your users to reset their passwords, or continue to use the old password hashing algorithm until they do. I believe I will need to use the new password hashing algorithm, because using the old one would require me to reconfigure the PHP code for the forum, which would be a level of complexity beyond my capabilities. So I now understand that I can not decrypt the passwords into any kind of plain text and then re-encrypt them. And I also understand that the most likely course of action from this point will be to get the users to update their passwords. However, just to double check and be 100% sure. Is there no way to convert directly from one password field to the other? Something where I, the administrator, can never see the plain text version of the password? Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merging two tables which contain passwords with different encryption methods
Dave wrote: [snip] I believe I will need to use the new password hashing algorithm, because using the old one would require me to reconfigure the PHP code for the forum, which would be a level of complexity beyond my capabilities. So I now understand that I can not decrypt the passwords into any kind of plain text and then re-encrypt them. And I also understand that the most likely course of action from this point will be to get the users to update their passwords. However, just to double check and be 100% sure. Is there no way to convert directly from one password field to the other? Something where I, the administrator, can never see the plain text version of the password? No, because that would require MySQL to 'see' the plain text version of the password. For MySQL to do that, the proces would look something like: PASSWORD()ed password --- plain text --- MD5ed password The first link in that chain is mathematically impossible (even if you never saw the plain text password, it would have to get to that stage in order to be put through the MD5 algorithm). -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ If you find my advice useful, please consider donating to a poor student! You can choose whatever amount you think my advice was worth to you. http://tinyurl.com/7oa5s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merging two tables which contain passwords with different encryption methods [SOLVED]
No, because that would require MySQL to 'see' the plain text version of the password. For MySQL to do that, the proces would look something like: PASSWORD()ed password --- plain text --- MD5ed password The first link in that chain is mathematically impossible (even if you never saw the plain text password, it would have to get to that stage in order to be put through the MD5 algorithm). Okay, that makes the situation very clear. I now understand that MySQL can not manipulate encrypted passwords for the purpose of changing encryption methods. While this is not very convenient for my current circumstances, I do also understand that this is for security purposes, and protects the user's privacy. I will have to copy over the user data, inserting in some temporary passwords, and then require the users to create their own afterwards. Thank you for your explanation. I can now make a plan to go forward with the merging of the user tables. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
