PHPBB3 Integration with users table - Salman A's Method

[schwarzenneger]

I successfully integrated a PHHPBB3 installation into an existing community website. I now think it is about time I share my knowledge with everyone. I have had the new system running without any problems for over one month so may be it is time to document the process as much as possible for people who want to do the same.

Scenario:

I have one existing system with a "user" table. The key elements of this table:

UserID > Auto increment primary key
Email > varchar with unique constraint; this also acts as the login for the "website"
Password > varchar
IsActive > integer for storing user's "active" status (null or 1)

I also have a fresh phpbb3 installation into which i need to integrate the website users.

I also have to create a list of categories and forums that match the structure of my main websites main categories and sub-categories - a 1 to 1 match. I will discuss this if necessary.

Additional task: I have to integrate the website's interface elements into the phpbb theme. I used simple approach. I will probably not discuss it as there are very easy solutions already available.

[schwarzenneger]

Mapping:

Have to create mapping between the users inside the two tables. These will be referred as:

user (website users)
- and -
phpbb_user (phpbb users)

The question, how should I map website users onto phpbb users? There are many ways to do this. I chose the simplest one to implement. Map user.UserID to phpbb_users.user_id. This way a user can always be looked up into phpbb users and vice versa.

There is one problem however. PHPBB3 fresh installation creates about 50 users by default. These include the super administrator, anonymous and search bots. That means user_id 1 to 50 are unavailable.

I could have created 50 fake users in the user table so that any further website users can be mapped properly. However, I already had about 250+ users by the time integration was supposed to be made. So I chose to map by adding a constant - 1000. So:

Website user #1 becomes phpbb user #1001
Website user #2 becomes phpbb user #1002
...
and so on.

This way I don't have to run any lookup queries when I have for find phpbb user given website user id and vice versa.

[schwarzenneger]

Drawback:

The process creates clones of website users inside phpbb. It should not go the other way round. So, before integration you should disable phpbb "registration". You can do this from the phpbb admin section. When you do this, no new user can "register" him/herself. The registration should be done on the main website. Later in the discussion i will provide the hack for creating the user.

[schwarzenneger]

Next step is to write functions that ease the task of importing the users. While we are on it, it is also desirable to create another function that synchronizes information of existing users. For example, if someone changes his/her email address on the main website, we should have the mechanism that copies the change into the corresponding phpbb user record. These the fields I am interested in:

Email, Password, IsActive

There may be other fields too but in my scenario, there aren't any. I am not storing persons age, date of birth, aim/icq information, location, work information etc. I'll leave it on phpbb user control panel to allow user to edit phpbb related information.

Besides creating new users, the function must place the newly created user in registered users group. I also hacked into phpbb code to find out there are a few other things that are done when a user is created. Usually the default group settings are copied into the the user record.

Note: The way I am implementing the integration it is *not necessary* to create a clone of user into phpbb immediately upon registration although this is a good idea.

[schwarzenneger]

On to the function:

Code: Select all

	function phpbb_synchronize_user( $UserID, $Email, $Password, $IsActive )
	{
		global $cn1;
		$message = array( );
		$librium_default_group_id = 2;
		static $phpbb_config;
		static $group_config;
		if ( is_array( $phpbb_config ) == false )
		{
			$phpbb_config = array( );
			$query = "SELECT config_name, config_value FROM phpbb_config";
			$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
			while ( $myrow = mysql_fetch_assoc( $ident ) )
			{
				$phpbb_config[ $myrow[ "config_name" ] ] = $myrow[ "config_value" ];
			}
		}
		if ( is_array( $group_config ) == false )
		{
			$query = sprintf( "SELECT group_id, group_colour, group_rank, group_avatar, group_avatar_type, group_avatar_width, group_avatar_height FROM phpbb_groups WHERE group_id = %d", $librium_default_group_id );
			$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
			$group_config = mysql_fetch_assoc( $ident );
		}
		$bbuser_newid = $UserID + 1000;
		$bbuser_email = strtolower( $Email );
		$bbuser_passw = $Password;
		$bbuser_class = $IsActive ? 0 : 1;
		$bbuser_login = $bbuser_email;
		$bbuser_login = explode( "@", $bbuser_login );
		$bbuser_login = $bbuser_login[ 0 ];
		$bbuser_login = preg_replace( "/[^a-z0-9]/i", "", $bbuser_login );
		$bbuser_login = $bbuser_login . $bbuser_newid;
		$query = sprintf( "SELECT username, username_clean, user_type FROM phpbb_users WHERE user_id = %d", $bbuser_newid );
		$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
		$myrow = mysql_fetch_assoc( $ident );
		if ( $myrow === false )
		{
			$dataarray = array( );
			$dataarray[ "user_id"                  ] = $bbuser_newid;
			$dataarray[ "user_email"               ] = $bbuser_email;
			$dataarray[ "user_email_hash"          ] = crc32( $bbuser_email ) . strlen( $bbuser_email );
			$dataarray[ "user_password"            ] = md5( $bbuser_passw );
			$dataarray[ "username"                 ] = $bbuser_login;
			$dataarray[ "username_clean"           ] = $bbuser_login;
			$dataarray[ "group_id"                 ] = $librium_default_group_id;
			$dataarray[ "user_type"                ] = $bbuser_class;
			$dataarray[ "user_colour"              ] = $group_config[ "group_colour"        ];
			$dataarray[ "user_rank"                ] = $group_config[ "group_rank"          ];
			$dataarray[ "user_avatar"              ] = $group_config[ "group_avatar"        ];
			$dataarray[ "user_avatar_type"         ] = $group_config[ "group_avatar_type"   ];
			$dataarray[ "user_avatar_width"        ] = $group_config[ "group_avatar_width"  ];
			$dataarray[ "user_avatar_height"       ] = $group_config[ "group_avatar_height" ];
			$dataarray[ "user_lang"                ] = $phpbb_config[ "default_lang"        ];
			$dataarray[ "user_timezone"            ] = $phpbb_config[ "board_timezone"      ];
			$dataarray[ "user_dst"                 ] = $phpbb_config[ "board_dst"           ];
			$dataarray[ "user_dateformat"          ] = $phpbb_config[ "default_dateformat"  ];
			$dataarray[ "user_style"               ] = $phpbb_config[ "default_style"       ];
			$dataarray[ "user_actkey"              ] = "";
			$dataarray[ "user_allow_massemail"     ] = 1;
			$dataarray[ "user_allow_pm"            ] = 1;
			$dataarray[ "user_allow_viewemail"     ] = 1;
			$dataarray[ "user_allow_viewonline"    ] = 1;
			$dataarray[ "user_emailtime"           ] = 0;
			$dataarray[ "user_full_folder"         ] = -3;
			$dataarray[ "user_inactive_reason"     ] = $bbuser_class ? 3 : 0;
			$dataarray[ "user_inactive_time"       ] = $bbuser_class ? time( ) : 0;
			$dataarray[ "user_interests"           ] = "";
			$dataarray[ "user_ip"                  ] = "";
			$dataarray[ "user_last_privmsg"        ] = 0;
			$dataarray[ "user_lastmark"            ] = time( );
			$dataarray[ "user_lastpage"            ] = "";
			$dataarray[ "user_lastpost_time"       ] = 0;
			$dataarray[ "user_lastvisit"           ] = 0;
			$dataarray[ "user_message_rules"       ] = 0;
			$dataarray[ "user_new_privmsg"         ] = 0;
			$dataarray[ "user_notify"              ] = 0;
			$dataarray[ "user_notify_pm"           ] = 1;
			$dataarray[ "user_notify_type"         ] = 0;
			$dataarray[ "user_occ"                 ] = "";
			$dataarray[ "user_options"             ] = 895;
			$dataarray[ "user_pass_convert"        ] = 0;
			$dataarray[ "user_permissions"         ] = "";
			$dataarray[ "user_posts"               ] = 0;
			$dataarray[ "user_sig"                 ] = "";
			$dataarray[ "user_sig_bbcode_bitfield" ] = "";
			$dataarray[ "user_sig_bbcode_uid"      ] = "";
			$dataarray[ "user_unread_privmsg"      ] = 0;
			$dataarray[ "user_regdate"             ] = time( );
			$dataarray[ "user_passchg"             ] = time( );
			$dataarray[ "user_form_salt"           ] = substr( md5( microtime( ) ), 4, 16 );
			$query = "";
			foreach ( $dataarray as $column => $value )
			{
				if ( $query == "" )
				{
					$query .= "INSERT phpbb_users SET ";
				}
				else
				{
					$query .= ", ";
				}
				$query .= sprintf( "%s = '%s'", $column, addslashes( $value ) );
			}
			$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
			$message[ ] = sprintf( "Created user <b>%s</b>", rtext( $bbuser_login ) );
			$query = sprintf( "
				INSERT INTO phpbb_user_group
				( group_id, user_id, group_leader, user_pending )
				VALUES
				( %d, %d, 0, 0 )
				", $librium_default_group_id, $bbuser_newid
			);
			$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
			$message[ ] = "&rsaquo; Assigned user to registered users group";
		}
		else
		{
			$dataarray = array( );
			$dataarray[ "user_email"      ] = $bbuser_email;
			$dataarray[ "user_email_hash" ] = crc32( $bbuser_email ) . strlen( $bbuser_email );
			$dataarray[ "user_password"   ] = md5( $bbuser_passw );
			if ( $myrow[ "username" ] == "" || $myrow[ "username_clean" ] == "" )
			{
				$dataarray[ "username"       ] = $bbuser_login;
				$dataarray[ "username_clean" ] = $bbuser_login;
			}
			if ( $myrow[ "user_type" ] != $bbuser_class )
			{
				$dataarray[ "user_type"            ] = $bbuser_class;
				$dataarray[ "user_inactive_reason" ] = $bbuser_class ? 3 : 0;
				$dataarray[ "user_inactive_time"   ] = $bbuser_class ? time( ) : 0;
			}
			$query = "";
			foreach ( $dataarray as $column => $value )
			{
				if ( $query == "" )
				{
					$query .= "UPDATE phpbb_users SET ";
				}
				else
				{
					$query .= ", ";
				}
				$query .= sprintf( "%s = '%s'", $column, addslashes( $value ) );
			}
			$query .= sprintf( " WHERE user_id = %d", $bbuser_newid );
			$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
			if ( mysql_affected_rows( $cn1 ) )
			{
				$message[ ] = sprintf( "Updated user <b>%s</b>", rtext( $bbuser_login ) );
			}
		}
		return implode( "<br>\n", $message );
	}

[schwarzenneger]

Function description:

Input:

$UserID user id of the user (user = user of the website) whose clone is to be created or whose information is to be updated
$Email is the email address of the user
$Password is the email address of the user
$IsActive is the flag that states whether the user is active or not (1 or NULL)

Local variables:

global $cn1 is a valid, open mysql connection identifier. Its you job to do handle it. Replace with your mysql connection identifier.

$message is An array that will contain strings of information/debug info/whatever

$phpbb_config will contain information from phpbb_config table that will be used later.
$group_config will contain information from phpbb_groups table for group #2 - registered users. This information will be used later.

The above two are made static so that they are faster to fetch if you call the function in a loop (10,000 times for example)

$bbuser_newid contains the id that will be assigned to the new user. As discussed, I wont allow phpbb to generate user ids. Instead I'll use my own to keep things simple. I just add 1000 to the $UserID.
$bbuser_email same as $Email except transformed - trimmed and lowercased
$bbuser_passw same as $Password
$bbuser_class more like a flag for checking if the user is active or not
$bbuser_login is a "generated" phpbb login name. The steps to create this "generated login" are up to you. I just extract the first part of email address, shave it clean and append the $bbuser_newid to make sure it is unique

[schwarzenneger]

Rest of the function is pretty simple. You check for the presence of a phpbb user based on the user id of the website user.

If not found then

• prepare a phpbb user record (most of the process is copied from phpbb native function) and inert
• assign this new user to registered users group

otherwise

• update phpbb user email address, password, username (only in rare cases) and active status

[schwarzenneger]

How to use the function:

IMPORTING USER

Loop over all of your users and call the function:

Code: Select all

		$message = array( );
		$query = "SELECT UserID, Email, Password, IsActive FROM user";
		$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
		while ( $myrow = mysql_fetch_assoc( $ident ) )
		{
			$result = phpbb_synchronize_user( $myrow[ "UserID" ], $myrow[ "Email" ], $myrow[ "Password" ], $myrow[ "IsActive" ] );
			if ( $result )
			{
				$message[ ] = $result;
			}
		}

UPON USER REGISTRATION

Call the function once, pass the ID of the newly inserted user and additional fields that you just stored in the database:

Code: Select all

	mysql_query( "INSERT INTO user YADA YADA", $cn1 ) or die( mysql_error( $cn1 ) );
	$result = phpbb_synchronize_user( mysql_insert_id( $cn1 ), $_POST[ "Email" ], $_POST[ "Password" ], 1 );

UPON USER PROFILE UPDATE

Call the function once, pass the ID logged in user and additional fields that you just stored in the database:

Code: Select all

	mysql_query( "UPDATE user YADA YADA WHERE UserID = {$_SESSION["UserID"]}", $cn1 ) or die( mysql_error( $cn1 ) );
	$result = phpbb_synchronize_user( $_SESSION[ "UserID" ], $_POST[ "Email" ], $_POST[ "Password" ], 1 );

[schwarzenneger]

Finally, Login:

I hacked into phpbb code and invented this solution for seamless login:

• make sure user is logged in
• synchronize information before phpbb login (optional... use it if you do not call the synchronize function frequently)
• use phpbb functions to start a phpbb session

it kind of goes like this:

Code: Select all

session_start( );

force_user_to_login_to_my_website_first( );

#
# synchronize user - optional
#

phpbb_synchronize_user(
	$_SESSION[ "UserID"   ],
	$_SESSION[ "Email"    ],
	$_SESSION[ "Password" ],
	$_SESSION[ "IsActive" ]
);

#
# since we are not storing phpbb username in the website user table
# we need to look it up
#

$query = sprintf(
	"SELECT username FROM phpbb_users WHERE user_id = %d + 1000",
	$_SESSION[ "UserID" ]
);
$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
$myrow = mysql_fetch_assoc( $ident );

if ( $myrow === false )
{
	die( "PHPBB USER NOT FOUND" );
}

#
# phpbb init
#

define( "IN_PHPBB", true );

$phpbb_root_path = "./bb/";
$phpEx = substr( strrchr( __FILE__, "." ), 1 );

require( $phpbb_root_path . "common." . $phpEx );
require( $phpbb_root_path . "includes/functions_user." . $phpEx );
require( $phpbb_root_path . "includes/functions_module." . $phpEx );

$user->session_begin();

#
# hack that i used from phpbb code
#

$result = $auth->login( $myrow[ "username" ], $_SESSION[ "Password" ] );

if ( $result[ "status" ] == LOGIN_SUCCESS )
{
	if ( defined( "IN_CHECK_BAN" ) && $result[ "user_row" ][ "user_type" ] != USER_FOUNDER )
	{
		return;
	}
	$successurl = $_GET[ "successurl" ];
	if ( $successurl == "" )
	{
		$successurl = "/bb/";
	}
	$successurl = reapply_sid( $successurl );
	$successurl = str_replace( "&", "&", $successurl );
	header( "Location: $successurl" );
	exit( 0 );
}
else
{
	die( $result[ "error_msg" ] );
}

[kungfuchris]

Hi Salman

I was glad to find this thread as I have been wanting to do reg this way for a while now. That is without hacking about the actual forum files which could make upgrading a nightmare. I think thats the way these sort of integrations should be done (leave external third party libraries/aplications as they are).

I would like to see the responses of the greater phpBB community about your implementation, I am surprised that no one has not jumped all over this topic since the topic of single sign on is frequently asked about on the forums.

Looking forward to the responses from the community on your method of implementation with regards to pros, cons, security, future of upgrades of phpbb software etc...

[Eelke]

Personally, I had little time when I found this thread and never came back afterwards. I'm surprised there is no function to add users to phpBB and you had to do the "hard work" yourself. Also, I probably would not have picked the solution of adding a constant to your own userIDs and use an otherwise static mapping function and would have either added a column to your own user table or added an extra mapping table.

Have you considered what happens is someone would go directly to a forum URL and is presented with a login box? Or is that the part where you "hacked into phpB code"? I don't believe you need to hack for this, you can create your own authentication module that would do all the work.

[schwarzenneger]

This method does have its problems. For now here is what I have found so far:

* may not work with future versions of phpbb

It is basically a copy of phpbb's native "user_add" function, optimized for my particular setup. If in future the implementation of user_add function changes, this might or might not work.

* Custom Profile Fields not taken into consideration

* statistics (such as "our newest user is") are not automatically updated

* activation email is not sent (this should be OK for most installations)

[schwarzenneger]

I'm surprised there is no function to add users to phpBB and you had to do the "hard work" yourself.

Well actually there is. It returns the newly generated auto number after adding the user. I didn't use it because I wanted to avoid column mapping and extra columns altogether.

Have you considered what happens is someone would go directly to a forum URL and is presented with a login box?

I'd prefer use click the link I provide them that automatically creates a phpbb session and sends user to phpbb installation. Of course they can login as usual provided their "record" has been imported.

[Eelke]

That would not log them in on your own part of the site, though.

"Preferring" users to do something isn't a very technically sound solution (But I'm sure you don't need me to tell you that). Users will be users If you have the technical means, it's better to just make it work, than to "prefer" they not do something. If your SSO-mechanism can be broken by a user who simply goes directly to a forum URL (the forum will send out subscription emails with such URLs, for example), I'm sorry to say your SSO isn't actually SSO...

If you've come as far as you have, taking the standard authentication module and adapting it so that it will log people onto phpBB as well as onto your own site is peanuts. I would highly recommend doing it.

[kungfuchris]

Hi Eelke and schwarzenneger,

Thanks for responding to this thread. Eelke, you mentioned

taking the standard authentication module and adapting it so that it will log people onto phpBB as well as onto your own site is peanuts

Can you share some more light into this?
Do you mean duplicating the authentication module into a new module so that when a user, log in at your own site or registers at your own site this module takes care of what it needs to do??

Where can I find that module? I am looking through the source code for a modules folder???

Thanks again for responding...