Registering via sql

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
vince_45
Registered User
Posts: 11
Joined: Thu Jun 05, 2008 9:10 pm

Registering via sql

Post by vince_45 »

Hello,

I have a web site with a link to a phpbb forum for the members to discuss.
When people register on my website, I would like to register then at the same time in the forum database as user. Simple sql request it should be.. but !

I succeed in registering a new user in the users table but cannot login..
I duplicated an existing user changing only its ID and username to see if I could register the user wrongly.. but still impossible to login.
I also went through all the forum tables looking for somwhere else to register a new user but did not find anything..

Could you please help me to understand what's happening and how to solve this frustrating issue ?

This question I have seen quite often but no satisfying answer although it seems a basic solution for many people driving a website and a linked forum. Could you please help me (us) ?

Thank you very much !

Vince
Last edited by ric323 on Mon Dec 29, 2008 11:02 pm, edited 1 time in total.
Reason: Topic icon changed
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Registering via sql

Post by espicom »

Creating a user has several steps, the most important of which are creating the entry in the users table (did you remember to encode the password using the MD5 algorithm before saving it?), and creating an entry for that user in the users_group table. The second part affects their ability to obtain "special permissions" (read or do things that require permissions more strict that "everyone").
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
User avatar
Brf
Support Team Member
Support Team Member
Posts: 53401
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Registering via sql

Post by Brf »

In phpbb2, you have to create a personal phpbb_groups record for each user too, and a phpbb_user-group record to connect the user to their group.
vince_45
Registered User
Posts: 11
Joined: Thu Jun 05, 2008 9:10 pm

Re: Registering via sql

Post by vince_45 »

Thank you very much for your answers !

I indeed did not identify the inserts to do in groups tables.
So I made the insterts.. but still impossible to login.

Hereunder are esports of what is in the database for the user I created. Do you see anything wrong ?

INSERT INTO `phpbb_users` (`user_id`, `user_active`, `username`, `user_password`, `user_session_time`, `user_session_page`, `user_lastvisit`, `user_regdate`, `user_level`, `user_posts`, `user_timezone`, `user_style`, `user_lang`, `user_dateformat`, `user_new_privmsg`, `user_unread_privmsg`, `user_last_privmsg`, `user_login_tries`, `user_last_login_try`, `user_emailtime`, `user_viewemail`, `user_attachsig`, `user_allowhtml`, `user_allowbbcode`, `user_allowsmile`, `user_allowavatar`, `user_allow_pm`, `user_allow_viewonline`, `user_notify`, `user_notify_pm`, `user_popup_pm`, `user_rank`, `user_avatar`, `user_avatar_type`, `user_email`, `user_icq`, `user_website`, `user_from`, `user_sig`, `user_sig_bbcode_uid`, `user_aim`, `user_yim`, `user_msnm`, `user_occ`, `user_interests`, `user_actkey`, `user_newpasswd`) VALUES
(312, 1, 'membre312', 'aade86c627bef71f7c0ea9991e5aa268', 1228910716, 0, 0, 1226305824, 0, 0, 0.00, 1, 'french', 'd M Y h:i a', 0, 0, 0, 0, 0, NULL, 1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, '', 0, '[email protected]', '', '', '', '', NULL, '', '', '', '', '', '', NULL);


INSERT INTO `phpbb_user_group` (`group_id`, `user_id`, `user_pending`) VALUES
(312, 312, 0);


INSERT INTO `phpbb_groups` (`group_id`, `group_type`, `group_name`, `group_description`, `group_moderator`, `group_single_user`) VALUES
(312, 1, '', 'Personal User', 0, 1);

Thanks you for your help,

Vince
User avatar
ric323
Former Team Member
Posts: 22910
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: Registering via sql

Post by ric323 »

What do you mean "impossible to login" ? What happens when you try?
Does it report that the password doesn't match the username?
Does this member show up in the memberlist?
What about if you login as admin, and change this username's password in the admin panel, can you then login?
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions
vince_45
Registered User
Posts: 11
Joined: Thu Jun 05, 2008 9:10 pm

Re: Registering via sql

Post by vince_45 »

Hello,

By "impossible to login", I mean that after seizing user (email adress in fact because I changed it) and password it says : "uncorrect or inactive username or wrong password".

I precise the forum address if that can help : http://loiretemploi.com/protec/forum/

The member is visible in the memberlist yes.

I tried to login as admin and also as a user "normally" registered (not via sql request done by me) and I can log in.

I have tried twice to modify the password in the admin panel. The modification is well done (I compared the hashed password with other users with same password) but still the message : "uncorrect or inactive username or wrong password"

Kind of frustrating..
vince_45
Registered User
Posts: 11
Joined: Thu Jun 05, 2008 9:10 pm

Re: Registering via sql

Post by vince_45 »

Could someone help please ?

I would appreciate this "gift" for Xmas a lot ! ;-)

Thank you
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Registering via sql

Post by espicom »

The problem is difficult to diagnose, because we do not have access to your system. The error is rather ambiguous, because each possibility (inactive user name, incorrect user name, or incorrect password)has different causes and cures.

Are you sure the user name is being saved the same way in your script as phpBB uses? If a user name contains characters that are "specially coded" for display in HTML, how they are stored in the database makes a big difference. For example, a user name of "bob" can be stored as "%62%6f%62", and it will display the same, but not BE the same. If this happens to the password, it is virtually impossible to "see", because the MD5 value is still pretty much undecipherable to humans, anyway. ;)

What you posted of your SQL query looks like it sets the user to "active", but you need to confirm that; if you bring the new user up in the admin control panel, does it show them as active? If you use the admin control panel to change their password, can they then log on?
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
vince_45
Registered User
Posts: 11
Joined: Thu Jun 05, 2008 9:10 pm

Re: Registering via sql

Post by vince_45 »

Hello !

Thank you very much for your answer.

I understand it is not so simple not having all information at your disposal, but the web site address and the extracts of database can not help you at all ?

To answer one by one your questions :

Q : Are you sure the user name is being saved the same way in your script as phpBB uses?
A : Yes it is. The name I insert via SQL request is exactly the same as the one I see when I look in the database afterward. But I do precise that I made modifications in the login.php in order to log with the email address instead of the username. When I try to register, I copy the email address directly in the database and then put it in the username field of the login page. It works for members register by phpbb registration page but not for the one I created myself.

Q : If this happens to the password, it is virtually impossible to "see", because the MD5 value is still pretty much undecipherable to humans, anyway.
A : In order to avoid MD5 encoding issue, I copied the MD5 chain of a user created by the phpbb registration page (for this user I can login) and put it in the password field of the user I created by myself. This way, no possibility of error do to password encoding.

Q : What you posted of your SQL query looks like it sets the user to "active", but you need to confirm that; if you bring the new user up in the admin control panel, does it show them as active? If you use the admin control panel to change their password, can they then log on?
A : When I searh and edit thie user (membre312) in the admin control panel, it appears as active yes. I can change the password, do it, try to login.. but it is still impossible.

Do not hesitate to ask for further information, I really go crazy with this issue !

Thank you,

Vince
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Registering via sql

Post by espicom »

Q : Are you sure the user name is being saved the same way in your script as phpBB uses?
A : Yes it is. The name I insert via SQL request is exactly the same as the one I see when I look in the database afterward. But I do precise that I made modifications in the login.php in order to log with the email address instead of the username. When I try to register, I copy the email address directly in the database and then put it in the username field of the login page. It works for members register by phpbb registration page but not for the one I created myself.
This means we are now dealing with more than one possible source of error. In your sample insert, you did NOT copy the email address to the username, so the fields are different. This problem, as originally described, was primarily one of "getting the insert correct to match the login program", but now, it is also, "figure out what the login program is expecting", since it isn't the regular login program.

So, please clarify for me, just what two pieces of information do users supply to log in to your board?
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
vince_45
Registered User
Posts: 11
Joined: Thu Jun 05, 2008 9:10 pm

Re: Registering via sql

Post by vince_45 »

Thank you for your quick answer !

The two pieces of information to supply to log are :
- the email address (named user_email in the database) and
- the password (named user_password in the database).

Vince
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Registering via sql

Post by espicom »

OK, then I need to know what you replaced lines 55-140 in login.php with. This is the standard file's lines:

Code: Select all

	if( ( isset($HTTP_POST_VARS['login']) || isset($HTTP_GET_VARS['login']) ) && (!$userdata['session_logged_in'] || isset($HTTP_POST_VARS['admin'])) )
	{
		$username = isset($HTTP_POST_VARS['username']) ? phpbb_clean_username($HTTP_POST_VARS['username']) : '';
		$password = isset($HTTP_POST_VARS['password']) ? $HTTP_POST_VARS['password'] : '';

		$sql = "SELECT user_id, username, user_password, user_active, user_level, user_login_tries, user_last_login_try
			FROM " . USERS_TABLE . "
			WHERE username = '" . str_replace("\\'", "''", $username) . "'";
		if ( !($result = $db->sql_query($sql)) )
		{
			message_die(GENERAL_ERROR, 'Error in obtaining userdata', '', __LINE__, __FILE__, $sql);
		}

		if( $row = $db->sql_fetchrow($result) )
		{
			if( $row['user_level'] != ADMIN && $board_config['board_disable'] )
			{
				redirect(append_sid("index.$phpEx", true));
			}
			else
			{
				// If the last login is more than x minutes ago, then reset the login tries/time
				if ($row['user_last_login_try'] && $board_config['login_reset_time'] && $row['user_last_login_try'] < (time() - ($board_config['login_reset_time'] * 60)))
				{
					$db->sql_query('UPDATE ' . USERS_TABLE . ' SET user_login_tries = 0, user_last_login_try = 0 WHERE user_id = ' . $row['user_id']);
					$row['user_last_login_try'] = $row['user_login_tries'] = 0;
				}
				
				// Check to see if user is allowed to login again... if his tries are exceeded
				if ($row['user_last_login_try'] && $board_config['login_reset_time'] && $board_config['max_login_attempts'] && 
					$row['user_last_login_try'] >= (time() - ($board_config['login_reset_time'] * 60)) && $row['user_login_tries'] >= $board_config['max_login_attempts'] && $userdata['user_level'] != ADMIN)
				{
					message_die(GENERAL_MESSAGE, sprintf($lang['Login_attempts_exceeded'], $board_config['max_login_attempts'], $board_config['login_reset_time']));
				}

				if( md5($password) == $row['user_password'] && $row['user_active'] )
				{
					$autologin = ( isset($HTTP_POST_VARS['autologin']) ) ? TRUE : 0;

					$admin = (isset($HTTP_POST_VARS['admin'])) ? 1 : 0;
					$session_id = session_begin($row['user_id'], $user_ip, PAGE_INDEX, FALSE, $autologin, $admin);

					// Reset login tries
					$db->sql_query('UPDATE ' . USERS_TABLE . ' SET user_login_tries = 0, user_last_login_try = 0 WHERE user_id = ' . $row['user_id']);

					if( $session_id )
					{
						$url = ( !empty($HTTP_POST_VARS['redirect']) ) ? str_replace('&', '&', htmlspecialchars($HTTP_POST_VARS['redirect'])) : "index.$phpEx";
						redirect(append_sid($url, true));
					}
					else
					{
						message_die(CRITICAL_ERROR, "Couldn't start session : login", "", __LINE__, __FILE__);
					}
				}
				// Only store a failed login attempt for an active user - inactive users can't login even with a correct password
				elseif( $row['user_active'] )
				{
					// Save login tries and last login
					if ($row['user_id'] != ANONYMOUS)
					{
						$sql = 'UPDATE ' . USERS_TABLE . '
							SET user_login_tries = user_login_tries + 1, user_last_login_try = ' . time() . '
							WHERE user_id = ' . $row['user_id'];
						$db->sql_query($sql);
					}
				}

				$redirect = ( !empty($HTTP_POST_VARS['redirect']) ) ? str_replace('&', '&', htmlspecialchars($HTTP_POST_VARS['redirect'])) : '';
				$redirect = str_replace('?', '&', $redirect);

				if (strstr(urldecode($redirect), "\n") || strstr(urldecode($redirect), "\r") || strstr(urldecode($redirect), ';url'))
				{
					message_die(GENERAL_ERROR, 'Tried to redirect to potentially insecure url.');
				}

				$template->assign_vars(array(
					'META' => "<meta http-equiv=\"refresh\" content=\"3;url=login.$phpEx?redirect=$redirect\">")
				);

				$message = $lang['Error_login'] . '<br /><br />' . sprintf($lang['Click_return_login'], "<a href=\"login.$phpEx?redirect=$redirect\">", '</a>') . '<br /><br />' .  sprintf($lang['Click_return_index'], '<a href="' . append_sid("index.$phpEx") . '">', '</a>');

				message_die(GENERAL_MESSAGE, $message);
			}
		}
		else
Does the email address look different when you register via phpBB vs. your registration?
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
vince_45
Registered User
Posts: 11
Joined: Thu Jun 05, 2008 9:10 pm

Re: Registering via sql

Post by vince_45 »

Hello !

Hereafter is the code from login.php lines 55 to 140.

About your question "Does the email address look different when you register via phpBB vs. your registration?" --> No the email address looks exactly the same.

Code: Select all

	if( ( isset($HTTP_POST_VARS['login']) || isset($HTTP_GET_VARS['login']) ) && (!$userdata['session_logged_in'] || isset($HTTP_POST_VARS['admin'])) )
	{
		$username = isset($HTTP_POST_VARS['username']) ? phpbb_clean_username($HTTP_POST_VARS['username']) : '';
		$password = isset($HTTP_POST_VARS['password']) ? $HTTP_POST_VARS['password'] : '';

		$sql = "SELECT user_id, user_email, user_password, user_active, user_level, user_login_tries, user_last_login_try
			FROM " . USERS_TABLE . "
			WHERE user_email = '" . str_replace("\\'", "''", $username) . "'";
		if ( !($result = $db->sql_query($sql)) )
		{
			message_die(GENERAL_ERROR, 'Error in obtaining userdata', '', __LINE__, __FILE__, $sql);
		}

		if( $row = $db->sql_fetchrow($result) )
		{
			if( $row['user_level'] != ADMIN && $board_config['board_disable'] )
			{
				redirect(append_sid("index.$phpEx", true));
			}
			else
			{
				// If the last login is more than x minutes ago, then reset the login tries/time
				if ($row['user_last_login_try'] && $board_config['login_reset_time'] && $row['user_last_login_try'] < (time() - ($board_config['login_reset_time'] * 60)))
				{
					$db->sql_query('UPDATE ' . USERS_TABLE . ' SET user_login_tries = 0, user_last_login_try = 0 WHERE user_id = ' . $row['user_id']);
					$row['user_last_login_try'] = $row['user_login_tries'] = 0;
				}
				
				// Check to see if user is allowed to login again... if his tries are exceeded
				if ($row['user_last_login_try'] && $board_config['login_reset_time'] && $board_config['max_login_attempts'] && 
					$row['user_last_login_try'] >= (time() - ($board_config['login_reset_time'] * 60)) && $row['user_login_tries'] >= $board_config['max_login_attempts'] && $userdata['user_level'] != ADMIN)
				{
					message_die(GENERAL_MESSAGE, sprintf($lang['Login_attempts_exceeded'], $board_config['max_login_attempts'], $board_config['login_reset_time']));
				}

				if( md5($password) == $row['user_password'] && $row['user_active'] )
				{
					$autologin = ( isset($HTTP_POST_VARS['autologin']) ) ? TRUE : 0;

					$admin = (isset($HTTP_POST_VARS['admin'])) ? 1 : 0;
					$session_id = session_begin($row['user_id'], $user_ip, PAGE_INDEX, FALSE, $autologin, $admin);

					// Reset login tries
					$db->sql_query('UPDATE ' . USERS_TABLE . ' SET user_login_tries = 0, user_last_login_try = 0 WHERE user_id = ' . $row['user_id']);

					if( $session_id )
					{
						$url = ( !empty($HTTP_POST_VARS['redirect']) ) ? str_replace('&', '&', htmlspecialchars($HTTP_POST_VARS['redirect'])) : "index.$phpEx";
						redirect(append_sid($url, true));
					}
					else
					{
						message_die(CRITICAL_ERROR, "Couldn't start session : login", "", __LINE__, __FILE__);
					}
				}
				// Only store a failed login attempt for an active user - inactive users can't login even with a correct password
				elseif( $row['user_active'] )
				{
					// Save login tries and last login
					if ($row['user_id'] != ANONYMOUS)
					{
						$sql = 'UPDATE ' . USERS_TABLE . '
							SET user_login_tries = user_login_tries + 1, user_last_login_try = ' . time() . '
							WHERE user_id = ' . $row['user_id'];
						$db->sql_query($sql);
					}
				}

				$redirect = ( !empty($HTTP_POST_VARS['redirect']) ) ? str_replace('&', '&', htmlspecialchars($HTTP_POST_VARS['redirect'])) : '';
				$redirect = str_replace('?', '&', $redirect);

				if (strstr(urldecode($redirect), "\n") || strstr(urldecode($redirect), "\r") || strstr(urldecode($redirect), ';url'))
				{
					message_die(GENERAL_ERROR, 'Tried to redirect to potentially insecure url.');
				}

				$template->assign_vars(array(
					'META' => "<meta http-equiv=\"refresh\" content=\"3;url=login.$phpEx?redirect=$redirect\">")
				);

				$message = $lang['Error_login'] . '<br /><br />' . sprintf($lang['Click_return_login'], "<a href=\"login.$phpEx?redirect=$redirect\">", '</a>') . '<br /><br />' .  sprintf($lang['Click_return_index'], '<a href="' . append_sid("index.$phpEx") . '">', '</a>');

				message_die(GENERAL_MESSAGE, $message);
			}
		}
		else
Thank you,

Vince
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Registering via sql

Post by espicom »

Create a file named "debugging.log" in your forum's main directory, and set its permissions to "777" (world writable). If you have access rights to do it, it would actually be better to create this file outside the area of the server that is accessible to the public, but not everyone can do that. If you do, you'll want to put the full path to the file in place of "debugging.log" in the following.

The following changes are TEMPORARY. Keep the original login.php, because you will want to replace it immediately after running this test, especially if you cannot put the debugging.log file outside the web-accessible area! Also, remove the debugging.log file as soon as you copy it to your computer, because it WILL have the username/password information for anyone who logged in during the test!

Find:

Code: Select all

          if ( !($result = $db->sql_query($sql)) )
          {
             message_die(GENERAL_ERROR, 'Error in obtaining userdata', '', __LINE__, __FILE__, $sql);
          }
After, insert these lines:

Code: Select all

$debug = "'$username' / '$password' \n'$sql'";
error_log("$debug\n",3,"debugging.log");
Find:

Code: Select all

          if( $row = $db->sql_fetchrow($result) )
          {
After, insert:

Code: Select all

$debug = print_r($row,TRUE);
error_log("$debug\n",3,"debugging.log");
Remove any entries in debugging.log that do NOT correspond to your TEST user entries, and post what's left here, inside of a CODE block.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
vince_45
Registered User
Posts: 11
Joined: Thu Jun 05, 2008 9:10 pm

Re: Registering via sql

Post by vince_45 »

This to be a good clue to solve this issue, thank you !

Here is what the debugging.log says (first test is for a member created by phpBB, and the second one for a member directly created by myself) :

Code: Select all

'[email protected]' / 'xxxxxx' 
'SELECT user_id, user_email, user_password, user_active, user_level, user_login_tries, user_last_login_try
			FROM phpbb_users
			WHERE user_email = '[email protected]''
Array
(
    [user_id] => 4
    [user_email] => [email protected]
    [user_password] => aade86c627bef71f7c0ea9991e5aa268
    [user_active] => 1
    [user_level] => 0
    [user_login_tries] => 0
    [user_last_login_try] => 0
)

'loiretemploi@loiretemploi' / 'xxxxxx' 
'SELECT user_id, user_email, user_password, user_active, user_level, user_login_tries, user_last_login_try
			FROM phpbb_users
			WHERE user_email = 'loiretemploi@loiretemploi''

Thanx,

Vince
Last edited by Brf on Sat Jan 03, 2009 1:25 pm, edited 1 time in total.
Reason: Removed passwords
Locked

Return to “2.0.x Support Forum”