database structure

This forum is now closed as part of retiring phpBB2.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

This forum is now closed due to phpBB2.0 being retired.
Post Reply
DejanPP
Registered User
Posts: 16
Joined: Sun Mar 04, 2007 11:56 pm

database structure

Post by DejanPP » Mon Mar 05, 2007 12:16 am

Hi all, I hope this is right forum to ask this :

I need forum database to be synchronized with another one database, because of that doing next :

- forum registration is dissabled
- another web site registration page will use httpPost to create user in forum and / or to assign user to group, change password etc ...
- forum will have one .php page which receiving parameters (only from another site IP address) and doing basic sql operations, insert and update.

Almost all needed info I found in ./includes/usercp_register.php but one table confuzing me :

There is code :

1. "insert into " . USERS_TABLE . "....."
2. "insert into " . GROUPS_TABLE . "....."
3. "insert into " . USER_GROUP_TABLE . "....."

Insert No 1 and insert No 3 are quite clear but insert into GROUPS_TABLE ...

Do we really need new group for EACH user ? I have already group "Registered Users" made trough admin interface, and field user_active is already "1" (because another web site confirmed user already). Do I shall skip insert No 2 and just to assign predefined ID in insert No 3 ?

Tnx in advance
Dejan

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51698
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Post by Brf » Mon Mar 05, 2007 12:49 am

Yes.
Forum permissions are done with groups. Therefore, when you are in Group Permissions, giving a user permissions on a forum, you are really giving permissions on their personal group.

DejanPP
Registered User
Posts: 16
Joined: Sun Mar 04, 2007 11:56 pm

Post by DejanPP » Mon Mar 05, 2007 1:23 am

quite fast answer, tnx a lot, I can today finish .php page and to continue on .asp tomorow :)

Resume :
All 3 inserts are needed and because user_active is already "1" no more actions is needed.

Aditionaly I need to repeat insert No 3 in order to add user to predefined group.

DejanPP
Registered User
Posts: 16
Joined: Sun Mar 04, 2007 11:56 pm

Almost done ...

Post by DejanPP » Mon Mar 05, 2007 5:09 am

Khm ... sometime I can't stop before finish something :)

Well, if you thin this is usefull I can post all details and upload that as MOD, for a moment critical part is here, if someone see any bug welcome ... :

Short resume : I want forum users to register on main web site and automaticaly to create forum account. For future managment userId need to be same as in another site database and password shall be same :

All registered users are in predefined group by default (because forum still support one forum where unregistered users can write) and donators can see more forums (basicly feature request).

php page :

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = './../';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);

//put your server IP in order to enable this
//by default 127.0.0.1 because may be security problem else
if ($_SERVER['REMOTE_ADDR'] == '127.0.0.1') {
	$query = $HTTP_POST_VARS['q'];
	switch ($query) {
		case 'ue'://userExists
			$userName = $HTTP_POST_VARS['un'];
			echo userExists($userName, $db);
			break;
		case 'ad'://addDonator
			$userId = $HTTP_POST_VARS['ui'];
			echo addDonator($userId, $db);
			break;
		case 'cd'://changeData
			$userName = $HTTP_POST_VARS['un'];
			$userId = $HTTP_POST_VARS['ui'];
			$newPwd = $HTTP_POST_VARS['up'];
			$newMail = $HTTP_POST_VARS['um'];
			echo changeData($userName, $userId, $newPwd, $newMail, $db);
			break;
		case 'uc'://userCreate
			$userName = $HTTP_POST_VARS['un'];
			$userId = $HTTP_POST_VARS['ui'];
			$userPwd = $HTTP_POST_VARS['up'];
			$userMail = $HTTP_POST_VARS['um'];
			echo userCreate($userName, $userId, $userPwd, $userMail, $db);
			break;
		default:
			echo "-1";
	}
}
else {
	echo "-1";
}
$db->sql_close();
//END OF PAGE

function userExists($userName, $dbConn) {
//1 exists, 0 not exists, -1 error
	$nResult = -1;
	$sql = "select count(*) as cnt from " . USERS_TABLE . " where username = '" . $userName . "'";
	if ($result = $dbConn->sql_query($sql)) {
		if ($row = $dbConn->sql_fetchrow($result)) {
			$nResult = $row['cnt'];
		}
		$dbConn->sql_freeresult($result);
	}
	return $nResult;
}

function changeData($userName, $userId, $newPwd, $newMail, $dbConn) {
//1 ok, -1 error
	$nResult = -1;
	$newPwd = str_replace("\'", "''", md5($newPwd));
	$sql = "update " . USERS_TABLE . " set user_password = '" . $newPwd . "', user_email = '" . $newMail . "' where username = '" . str_replace("\'", "''", $userName) . "' and user_id = $userId";
	if ($result = $dbConn->sql_query($sql)) {
		$nResult = 1;
	}
	return $nResult;
}

function addDonator($userId, $dbConn) {
//1 ok, -1 error
	$nResult = -1;
	$sql = "select count(*) as cnt from " . USER_GROUP_TABLE . " where user_id = " . $userId . " and group_id = 5";
	if ($result = $dbConn->sql_query($sql)) {
		if ($row = $dbConn->sql_fetchrow($result)) {
			$rowCnt = $row['cnt'];
			$dbConn->sql_freeresult($result);
			if ($rowCnt > 0) {
				$nResult = 2;
			}
			else {
				$sql = "insert into " . USER_GROUP_TABLE . " (user_id, group_id, user_pending) values ($userId, 5, 0)";
				if ($result = $dbConn->sql_query($sql)) {
					$nResult = 1;
				}
			}
		}
		else {
			$dbConn->sql_freeresult($result);
		}
	}
	return $nResult;
}

function userCreate($userName, $userId, $userPwd, $userMail, $dbConn) {
//1 ok, -1 error
	$nResult = -1;
	$userName = str_replace("\'", "''", $userName);
	$userPwd = str_replace("\'", "''", md5($userPwd));
	$defaultHomeURI = 'http://www.ztbclan.com/zone/';
	$sql = "insert into " . USERS_TABLE . " (user_id, username, user_regdate, user_password, user_email, user_icq, user_website, user_occ, user_from, user_interests, user_sig, user_sig_bbcode_uid, user_avatar, user_avatar_type, user_viewemail, user_aim, user_yim, user_msnm, user_attachsig, user_allowsmile, user_allowhtml, user_allowbbcode, user_allow_viewonline, user_notify, user_notify_pm, user_popup_pm, user_timezone, user_dateformat, user_lang, user_style, user_level, user_allow_pm, user_active, user_actkey) values (" . $userId . ", '" . $userName . "', " . time() . ", '" . $userPwd . "', '" . $userMail . "', '', '" . $defaultHomeURI . "', '', '', '', '', '', '', 0, 0, '', '', '', 1, 1, 0, 1, 1, 0, 1, 1, 0, 'D M d, Y g:i a', 'english', 3, 0, 1, 1, '')";
	if ($result = $dbConn->sql_query($sql, BEGIN_TRANSACTION)) {
		$sql = "insert into " . GROUPS_TABLE . " (group_name, group_description, group_single_user, group_moderator) values ('', 'Personal User', 1, 0)";
		if ($result = $dbConn->sql_query($sql)) {
			$group_id = $dbConn->sql_nextid();
			$sql = "insert into " . USER_GROUP_TABLE . " (user_id, group_id, user_pending) values ($userId, $group_id, 0)";
			if ($result = $dbConn->sql_query($sql)) {
				$sql = "insert into " . USER_GROUP_TABLE . " (user_id, group_id, user_pending) values ($userId, 4, 0)";
				if ($result = $dbConn->sql_query($sql, END_TRANSACTION)) {
					$nResult = 1;
				}
			}
		}
	}
	return $nResult;
}
?>
and test asp page :

Code: Select all

<%@ Language=JScript%><%
var remotePage = 'http://forum.url.com/includes/phpPageName.php';

//just to show test result
Response.Write(userExistInForum('TestUser'));

function modifyUserForumData(cUsername, nId, cPwd, cEmail) {
	var cForm = new String();
	var nRes = new Number();
	cForm = 'q=cd&un=' + Server.URLEncode(cUsername) + '&ui=' + nId.toString() + '&up=' + Server.URLEncode(cPwd) + '&um=' + Server.URLEncode(cEmail);
	nRes = postRemoteSync(remotePage, cForm.valueOf());
	if (nRes == 1)
		return true
	else
		return false;
}

function createForumUser(cUsername, nId, cPwd, cEmail) {
	var cForm = new String();
	var nRes = new Number();
	cForm = 'q=uc&un=' + Server.URLEncode(cUsername) + '&ui=' + nId.toString() + '&up=' + Server.URLEncode(cPwd) + '&um=' + Server.URLEncode(cEmail);
	nRes = postRemoteSync(remotePage, cForm.valueOf());
	if (nRes == 1)
		return true
	else
		return false;
}

function addDonatorToGroup(nDonatorId) {
	var cForm = new String();
	var nRes = new Number();
	cForm = 'q=ad&ui=' + nDonatorId.toString();
	nRes = postRemoteSync(remotePage, cForm.valueOf());
	if (nRes > 0)
		return true
	else
		return false;
}

function userExistInForum(cUsername) {
	var cForm = new String();
	var nRes = new Number();
	cForm = 'q=ue&un=' + Server.URLEncode(cUsername);
	nRes = postRemoteSync(remotePage, cForm.valueOf());
	if (nRes == 0)//if this is -1 say exists, because of error can't add
		return false
	else
		return true;
}

function postRemoteSync(url, content) {
	var httpRequest = Server.CreateObject('MSXML2.XMLHTTP');
	var cRes = new String();

	httpRequest.open('POST', url, false);
	httpRequest.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
	httpRequest.setRequestHeader('Content-length', content.length);
	httpRequest.setRequestHeader('Connection', 'close');
	httpRequest.send(content.valueOf());
	cRes = httpRequest.responseText;
	httpRequest = null;
	return parseInt(cRes, 10);
}
%>

DejanPP
Registered User
Posts: 16
Joined: Sun Mar 04, 2007 11:56 pm

Post by DejanPP » Tue Mar 06, 2007 2:45 am

Warning

I don't need to update code in previous post before I finish all, if someone using that there is potential security problem :

not on all strings is applyed "str_replace(...", in theory on main site someone can put malicious email or password and to apply sql injection. My site validating that and I already did corection in .php page, just wanted to warn you ...

Post Reply

Return to “[2.0.x] MOD Writers Discussion”