Database Conversion

[uk_martin]

Hi

As I may have mentioned elsewhere, I'm trying to convert an Access database to a schema that can be inserted to a modphobb3 user database table via phpMyAdmin.

I've created a test site, created a couple of users and taken a backup of the database to see how the data is stored so how I can replicate it. I looked at the table fields, and I think that I can export the Access database in a way that will be understood for a data insertion. However, there is one snag. That is the passwords.

I created a couple of test users, and gave them both the same password "changeme" The "hash" in the database for this is different for each user.

I know what the current passwords are from the database, but I don't have a way to convert them to phpBB3 "hash".

I can't just leave the field blank, so what would be the consequences of giving everyone the same hash value for "changeme"? Could everyone (2000+ users when the system goes live) enter "changeme" and get in? If not, if they press the "lost password" button will they be sent anything that will make sense?

Thanks in advance.

Martin

[LDM]

No no, it is much easier than that with up to date convert instructions. You add ModphpBB3 to your current forum as opposed to the other way round.

No user needs to change passwords or anything.

[uk_martin]

No, the point here is that there is no existing forum.

I have a site that I want to convert to modphpbb3 BUT the current site is a mixture of all sorts of old fashioned things, and its access was protected by .htaccess/.htpasswd files.

I kept a backup of all the applied-for usernames and passwords, plus real names and e-mail addresses, in case of a need like this. My backup info was in an MS Access database file, and now I need to export it in a way that I can import into the forum, so that 2000+ users don't have to re-register.

Much of the information on a "virgin" profile is pretty standard, so many of the fields can have default values, for the users to change once that they have logged in. Some of the fields though are variable, with values for names, user names, e-mail addresses, time zones etc. I've worked out a way to insert these variables into a text string, but the big question is about the password variable. I expected that the hash for the password would be the same if the password was the same (in my case it was "changeme") As the hash values for two test accounts produced different values, it got me wondering what the consequences would be of using the same hash values, and what if I got the password hash wrong in the data insertion.

Thanks

[LDM]

What was the forum platform you originally used for a test site? The 2000 users must have or currently be on a platform of some sort. There surely must be an easier way round it all.

[uk_martin]

There isn't really anything other than a Directory on a web site, with password protection offered via a .htaccess and .htpasswd file. That directory contains stuff written in "flat" HTML, with other java games applets etc etc. There's a phpBB forum in there too that about 150 of the 2000 members subscribed to, having registered for a second time once they were in the password protected area in the first place. The modded phpBB3 platform with an arcade, chatroom etc etc will provide everything that's in the old fashioned site, and more, and in a modern setting.

The info on the .htpasswd file is exactly the same as is in the Access database.

What I'm finding when examining the phpBB3 database table backup, is that the information for each user looks like this:

(231, 0, 10, '', 0, '92.236.168.21', 1233787487, 'third user', 'third user', '$H$9tCfk/mArwekNdcj.K0xvpuBzBmCzA1', 1233787487, 0, 'three@three.com', 91124832215, '', 0, 1233787487, 0, '', '', 0, 0, 0, 0, 0, 0, 0, 'en', '0.00', 0, 'D M d, Y g:i a', 1, 0, '', 0, 0, 0, 0, -3, 0, 0, 't', 'd', 0, 't', 'a', 0, 1, 0, 1, 1, 1, 1, 895, '', 0, 0, 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1f7572049cc85f3e')

Navy Blue=User ID
Red=Username
Dark Red = Username_clean
Blue=password
Dark Blue = pass_check
Green=e-mail
Orange=email_hash
Purple=lastmark
Yellow=form_salt

User ID is incremental, so that's no problem to replicate. Username and username_clean are text strings which again pose no problems. Pass_check I've seen set to zero, so that can be replicated. e-mail is another simple text string. Lastmark is a hash for a date, so that can be replicated. form_salt I've seen on other cases as '' so no issues there. All other values are either constant amongst all new registered users, or can be replaced by zeros.

Where there is an issue is over the hash values for password, and, thinking about it, the e-mail address. If I knew what can safely be put into these fields, without the system rejecting them, then I can do some spreadsheet and text processing work to replicate the sql syntax needed by the system to spoof it into believing that my membership have been forum members all along.

Hope that this gives a better insight into where I am with this project and what I hope to achieve.

[LDM]

Jeez, that looks like an immense job!

I would guess the biggest part is going to be the password issue as it is sure going to be totally different and I don't even think it would work. Not being negative and would love you to prove me wrong but that looks to be one hefty job.

If the users are currently in a phpBB2, converting them over (and correctly) to phpBB3 would be a piece of cake to do. That way all those details you have supplied of the tables would just be left for the converter to breeze through.

[uk_martin]

Well, you're right about the password being the crux of it. For one user I typed in "changeme" as the password, and the hash of that was $H$9TH.AiQiZpOBYScQJcFIIhPAcP27Nc0 and for the next user that I created which I typed in "changeme" as the password in exactly the same way, the hash came out as $H$9tCfk/mArwekNdcj.K0xvpuBzBmCzA1

That's the bit that I don't understand. Why the hash's for the same password are so different. I'll line them up below to illustrate the comparison...

$H$9TH.AiQiZpOBYScQJcFIIhPAcP27Nc0
$H$9tCfk/mArwekNdcj.K0xvpuBzBmCzA1

4 common characters and then all goes haywire.

If I can be sure that one of these hash's can be replicated throughout as a constant rather than a variable, without causing any "damage" to the database, then I'd be happy to get the rest on board. As I say, the rest is all a matter of juggling around stuff in a table/spreadsheet, then word-processing the rest to come up with the syntax. After all this is why computers exist, to make our lives easy, isn't it?

Cheers

[LDM]

phpBB3's password structure is so different from any other and totally different to phpBB 2. This is why that when converting to different boards to or from phpBB3, users have to request a new password. It uses Multiple Runs, Salted, and/or MD5.

This is why the 2 to 3 converter is so brilliant. It is easy to convert any board to phpBB2 so with the converter it is not impossible to convert any board via converting to phpBB2 then using the converter to 3 (in theory)

I tested an IPB convert, through SMF to phpBB2 to 3 but somewhere along the convert, the passwords were not done which I gave up on. Though in theory, it should have worked and would of if I had the effort to tinker more with it.

[uk_martin]

I wouldn't mind getting to a point where a user can enter their user name, and click on forgotten password, and they then get an e-mail with a new password in it. I could explain that to the masses and it's a lot easier than re-registering from scratch.

hmmm...now just to find the time to have a play with the various theories...

[LDM]

Again, I'm not sure it is going to work. You would need to have a password in there.

What exactly was the site and why is it in this format?