In migrating user accounts from a non-drupal database, the userid's came over okay, but the clear text passwords were rejected by the drupal database, requirng the user to request a new password by email. Is there a way to have the old passwords be encrypted in such a way so that the old passwords can be accepted? there are about 1000 accounts and likely several accounts' emails have not been updated so some users may not be able to receive new password emails. I have a fresh install so have not attempted to migrate them yet for a second time, so I can follow any steps necessary to make it work. Thanks.

Comments

moshe weitzman’s picture

how do you mean 'rejected'? what did you do. Just a SQL INSERT statement? drupal passwords are ecrypted by the php md5 function. so you can take cleartext passwords from an outside source, md5 them, and then insert into database. mysql can do the md5 during the INSERT if you wish.

nickblue’s picture

by 'rejected' I mean I was able to cut paste the cleartext passwords okay, but users can't logon using the cleartext password, but must request a new one. Since I'm a newbie to the max, do you know of a link that lists a procedure to use md5? I want to convert about 1000 user recordsets so that users won't have to change their passwords.

---------
Visit the Nick Blue online community:
http://www.nickblue.com/community/

grafix-1’s picture

Nick,

Drupal uses MD5 encryption. The way it works:

- User registers on the site with a password like 'hello'
- The password is encrypted (one-way) with MD5 encryption. The password in the db might look like 'a823f87ad8e8de8a8a8d68a76efff87'
- User logs in again after registration; and types in 'hello'
- Drupal encrypts the password the user gave with MD5, and then the given password in MD5 is checked against the MD5 string in the db. If they match, the password was OK

You have two options to migrate the users:
1. insert the username and password with mysql, and let mysql do the MD5 encryption
2. build a custom php script that generates the MD5 hash from the old database, and outputs a SQL script file with the username/password combo for each user.

If you need help, let me know.

Bas

nickblue’s picture

how might I insert the 1000 usernames and passwords with mysql? Ofcourse I would want to include the email address of each user. I have run commands in mysql for permissions, and ofcourse inserting module tables in the drupal database. any steps you can prescribe I appreciate.

Best,
Nick

---------
Visit the Nick Blue online community:
http://www.nickblue.com/community/

javanaut’s picture

You could use MySql's INSERT...SELECT syntax:

INSERT INTO users (uid, name, mail,pass,...) SELECT uid, oldusername, email, md5(clear_password),... FROM old_users_table;

I don't know what all fields you can import, but these are some common ones. You'll need to make sure that the number, types and order of fields match up between the INSERT part and the SELECT part.

grafix-1’s picture

Indeed, that's the way to go. Best way to start: export your current info (name, passwd, email address) and paste it into a spreadsheet. Then use a text concatenate function to build the INSERT statement that Javanaut describes. Next, you feed the script to MySQL.
That should do the trick. :-)

nickblue’s picture

is the syntax you posted exact? my other question is, since the colunns are in an database not related to drupal, would the old_users_table referenced above also need to include the database, for example tsimembers.old_users_table?

And I take it that, the column names after SELECT would be whatever the columns are named in the old table of the non-drupal database?

Here's what I'll enter at the mysql prompt:

INSERT INTO users (uid, name, mail,pass,...) SELECT uid, oldusername, email, md5(clear_password),... FROM old_users_table;

does that sound right?

Thank you, btw.

Nick

---------
Visit the Nick Blue online community:
http://www.nickblue.com/community/

nickblue’s picture

what would be an example of text concatenation? my ignorance is just a tad deeper than I thought:).

---------
Visit the Nick Blue online community:
http://www.nickblue.com/community/

javanaut’s picture

Well, my assumption was that you currently have the user data in a MySQL table in the same database as your fresh Drupal installation. What all fields do you have to import? I assumed you had existing uid, oldusername, email and clear_password fields in your old table (old_users_table) that you'll be importing from. You'll have to replace my assumed field names (in the SELECT part) with the actual field names you are using. Additionally, the "..." in both parts of the statement represent other fields that you may wish to import from your old table as well. If uid, name, mail and pass are the only things you're importing, remove both instances of ",...".

The IMPORT...SELECT syntax lets you perform a SELECT statement on one table and use the results from it to INSERT into another table. In this case, you would be transforming the clear password in the process. This will happen all at once, and you shouldn't need to do any external manipulation of the data using this.

nickblue’s picture

I just need to import these columns from the old mysql database:

colLoginName
colFirstName
colLastName
colEmailAddress
colPassword

So, what I need to do is import the table from my old database into the drupal database, and then:

INSERT INTO users (uid, name, mail, pass) SELECT ID, colLoginName, colEmailAddress, md5(clear_password) FROM tblUserInformation;

does that look good?
---------
Visit the Nick Blue online community:
http://www.nickblue.com/community/

javanaut’s picture

Assuming your ID field is of type int, then that should work. There will be a little bit of cleanup when you're done, though:

There is a sequences table that you'll want to update with the highest uid in your users table.

Get the highest uid from users:
SELECT MAX(uid) FROM users;

Whatever that number is..call it 12345 for example, do the following:

UPDATE sequences SET id=12345 WHERE name='users_uid';

If you don't update the sequences table, you'll have problems when new users try to sign up.

nickblue’s picture

so I can do the UPDATE after the first mysql command you mentioned.

what would be a good command to import the old database into the drupal database so I can do the table thing?

Best,
Nick

---------
Visit the Nick Blue online community:
http://www.nickblue.com/community/

javanaut’s picture

To export your old table:
mysqldump old_db tblUserInformation > old_table.sql

mysql drupal_db < old_table.sql

nickblue’s picture

I followed the steps you outlined. All went well. The only issue I am now experiencing is that:

1. two accounts created in drupal before the table import continued to work unhampered; which is good.
2. one account, the administrator account, ceased to allow me to login, prompting me to request a new password be emailed.
3. all the other new accounts also still require the user to request a new password, as if some issue took place vis a vis md5, even though when you view the user table, you definitely see the md5 encrypted data.

note: even though there are 1212 total records, the sql query to find out the highest uid was 1543. Previously, records were deleted when it was used in the former membership site, so perhaps that's okay. I didn't get any errors running the update using the number 1543.

Is there still hope for me take any steps to prevent having to attempt to email everyone telling them to change their passwords? :)

---------
Visit the Nick Blue online community:
http://www.nickblue.com/community/

javanaut’s picture

The default values in the user table that you're not setting may be causing you some grief. I haven't dug down to that level, but you should look at those first two records in the users table (for the accounts that were created regularly) and perhaps use some of those values as defaults for your other users.

Here's some guessing I would do just looking at my users table:

Specify the 'created' field as NOW(), as well as the 'changed' field.

Set 'status'=1

Set 'init' to their email address.

I'm sure there's something that you could cram in the 'data' field that would help you out, like specifying their roles. You should create a user just the way that you want all of the other users, and copy that data value into all of the other ones. The data field will need at least the user's roles specified.

nickblue’s picture

I'll keep testing as you suggest - it didn't like the entire data field from another authenticated user, and I had to 'execute query' rather than just click on the 'edit' button once the fields could be populated. I used NOW() and zeros were populated, status = 1 and 'init' has the email address. With data empty I was sent to the reset password page; so I'll figure out what info needs to go into the data field to indicated role, and keep on testing.

thanks for your insight!
Nick
---------
Visit the Nick Blue online community:
http://www.nickblue.com/community/

Dublin Drupaller’s picture

Hiya...

I was doing the same..(using 4.5)..i.e. importing a database of users.

Everything went well...but the ROLES flags needed setting after importing all the users.

I have three extra ROLES in my drupal setup.

To fix it was fairly simple. you need to export the user_roles table and detup the NID to match what roles you would like each user to be attributed...

I had details for 2 thousand users to import, so it was worth using something like NAVICAT (if you're a newbie and not an SQL expert) to export the table...bringit into EXCEL and simply fill the columns.

Hope that makes sense and helps.

J

Currently in Switzerland working as an Application Developer with UBS Investment Bank...using Drupal 7 and lots of swiss chocolate

Dublin Drupaller’s picture

Hi Nick,

I'm a non-programmer and was trying to do precisely what you want to do with Drupal - i.e. import a database of users into drupal 4.5

I stumbled across the following programme which was excellent. Has a free fully functional 30-day trial and it's fairly easy to use.

http://ems-hitech.com/mymanager/

Only problem I had was that my original database of users (in Access) had a lot of fields, such as address, phone number etc. and drupal 4.5 stores that info in a profiles_table.

Just importing a username, Email was straightforward enough.

As a tip, re: password thing. Here's a very simple solution (it had to be for me to get it to work) to workaround the password issue.

1. Setup a dummy account in your drupal with a known password.

2. In the SQL table have a look at the encrypted version of that known password.

3. Copy the encrypted version of the password into your clipboard.

4. In eXcel or acess in the PASSWORD column, make them all the same by pastingin the password and filling the entire column of users with teh same password code.

5. When you have updated or imported your old database into the new version of drupal. Email every user with a message to say that you have updated the database and simply point them to the REQUEST NEW PASSWORD link.

Alternatively you know everyone's password, so you can give them the password in the email. Which would not be good from a security point of view for sensitive data, but, the REQUEST NEW PASSWORD die to a database update is fairly okay with most people.

Hope that helps. The url I have pasted above is to a very slick and easy to use SQL database manager that allows you to import/export specific table date into excel/access/comma delimited files etc.

J

Currently in Switzerland working as an Application Developer with UBS Investment Bank...using Drupal 7 and lots of swiss chocolate

neofactor’s picture

I just added this post that might help
http://drupal.org/node/20975

David McIntosh
neofactor.com