Nginx + PHP + MySQL on Windows in 6 minutes

The last time I posted a tutorial on Nginx, there wasn’t a native port of the server available. Riez Opuz posted a link to his Xenstack project on that post that prompted me to write the rest of what I’ve been putting off. It’s a good way to tweak the stack to your own needs.

I tried to leave this as “in 5 minutes”, but then I remembered how long it would take to download MySQL… Even on broadband.

Kevin Worthington had very kindly provided a Cygwin build that ran on Windows, however Nginx now has a Windows build that we can use and this time, we can add MySQL to the list as well. To keep everything compatible, we’ll be using the 32 bit versions for all downloads.

Once you’ve also downloaded Nginx (0.8.53 at the time of this post), head on to the PHP libraries and remember to download the Windows Libraries only (5.3.3 as of today) and select the thread safe version. The first steps are the same with the exception of the download link to MySQL and we need the no-install download.

Make sure to follow this directory structure!

Extract the Nginx files to C:\nginx
Extract PHP to C:\nginx\php
Extract MySQL to C:\nginx\mysql

First, let’s configure MySQL

MySQL no-install is a freakin’ huge download so feel free to delete mysql-test, Embedded, sql-bench and folders named debug once unzipped. If you want to minimize the folder even more, you can optionally delete any .pdb files. This would come in handy if you want to deploy the whole ensamble on a thumb drive or package it for a demo application and are really penny-pinching the available storage space.

Once the cleanup is complete, copy my-medium.ini in C:\nginx\mysql\ into my.ini. I think the medium configuration takes care of most uses and, for a moderately busy site, it fares pretty well.

Always try to copy exising files before making changes instead of outright renaming them. This way, if something goes wrong with the new configuration, we still have the original handy to start over..

Open up the newly copied my.ini file and change the [client] block to match the following.


[client]
#password	= your_password
port		= 3306
socket		= c:/nginx/mysql/tmp/mysql.sock

Note the Unix style forward-slashes.

Now in the [mysqld] block in the same file, change to match the following :


[mysqld]
port		= 3306
socket		= c:/nginx/mysql/tmp/mysql.sock
basedir		= c:/nginx/mysql
datadir		= c:/nginx/mysql/data
bind-address	= localhost
enable-named-pipe
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

Now let’s try and run our MySQL server

Start a new command line window…
Note: If you’re running Windows Vista or above with UAC enabled, you need to right click on the command line link and select “Run as administrator”.. If you get a message saying “Install/Remove of the Service Denied!” when trying to start MySQL later on, then you probably have UAC running, so this step is very important.

Navigate to C:\nginx\mysql\bin\ and run :

mysqld --install-manual

There should be a slight delay followed by a “Service successfully installed”. We then must run :

net start mysql

…And if there are no errors noted, then Congratulations!

Before we proceed, we need to run some housekeeping operations. In the same command line window, run :

mysqladmin -u root password newpassword

Where newpassword is your new MySQL root password. This is an important step toward securing your installation.

Now that we’ve changed our root password enter the following :

mysql -u root -p

Which will give you a password prompt. Enter your newpassword created before. Once you’re logged in, you’re at the MySQL console.

If you need to change your root password at a future date, run mysql as above type the following :

update mysql.user set password=PASSWORD('new-newpassword') where user='root';

Note that passwords are encoded before storage in the database, so we need to run the PASSWORD function on our new-newpassword. Once that’s done, be sure to run :

flush privileges;

Now we need to remove all the junk that came with the server.

Delete the test databases and anonymous users (Always remember the semicolon at the end!) :

delete from mysql.user where user='root' and host!='localhost';
drop database test;
delete from mysql.db where db='test' or db='test\_%';

And finally flush privileges and quit :

flush privileges; quit;

Now if we need to, we can stop MySQL by running the following (in C:\nginx\mysql\bin\ as an Administrator of course):

net stop mysql

And if we need to remove it from our services entirely, run the following :

mysqld --remove

Onward to setting up PHP

Advertisements

Forum core source dump (as of July 31)

I would have finished this a while ago, but I’m in the middle of moving to a new apartment. Until that’s over, I figured I’ll dump what I have so far on the forum.

And I’ve finally settled on a name for this. I shall hereby call this forum script : theForum. Yeah, points for originality there. And of course, this will contain errors, incomplete functions/functionality and other terrible things, so be careful when using any part of this in your own projects. And on top of that the WordPress source code formatting is a bit off making error checking even more vital.

WARNING: This will be a very, very, very long post (7 pages total)!

index.php

/*

	#### theForum : A brand you can trust ####

*/



/******************************************************************************

	Modifying anything below will void the (non-existent) warranty!

*******************************************************************************/

// WARNING: Keep these intact.

// Root location
$rt = dirname(__FILE__) . '/';

// Get the main logic
require_once($rt . 'lib/main.php');

config.php

/******************************************************************************

	Edit your database settings here... (These values go in quotes)

*******************************************************************************/

// Your database server
$presets['db_host'] = 'localhost';

// Database name
$presets['db_name'] = 'wrex';

// Database admin name
$presets['db_user'] = 'wrex_user';

// Database admin password
$presets['db_pass'] = 'password';

// Database type
$presets['db_type'] = 'MySQL';


/******************************************************************************

	Edit your forum settings here... (Some settings require quotes)

*******************************************************************************/


// Forum title (you can include some HTML tags, but they will be stripped for page <title> tags)
$presets['site_title'] = 'the<span>Forum</span>';

// Slogan
$presets['site_slogan'] = 'A brand you can trust';

// Copyright notice
$presets['site_copyright'] = 'Copyright © 2009. All rights reserved.';

// Full site url
$presets['site_url'] = 'http://localhost/';


// Topics per page
$presets['page_topic_limit'] = 15;

// Replies per page
$presets['page_reply_limit'] = 10;


// The relative path to the forums for various paths in themes
$presets['site_path'] = '/';

$presets['site_path_seperator'] = ' » ';

// Present friendly, SEO friendly, URLs by avoiding querystring usage
// (this requires a .htaccess file for Apache or a map/ISAPI filter for IIS)
$presets['site_friendly_urls'] = '1';

// Allow self service creation of accounts. Admins can still create users. (True = 1 / False = 0)
$presets['site_registration'] = '1';

// The default template and styles.
$presets['site_theme'] = 'Section5';

// Closed for maintenance. If set to true, the "Forums closed" message will be displayed
// True = 1 / False = 0
$presets['site_closed'] = '0';

// Forums closed message in HTML format
$presets['site_closed_message'] = '<p>The forums are closed for maintenance. Please check back later.</p>';

// Welcome message (Don't change the "WELCOME" parts). You need to use HTML.
$presets['site_welcome'] =< <<WELCOME



<h2>Welcome
<h3>This is a demonstration template</h3>
<p>Rockwell Accessible is an experimental theme designed to improve web accessibility while maintaining a logical HTML structure. This is one variation of the base theme which is an example of customizability. The idea is to make any changes to the stylesheet while leaving the rest of the HTML intact. It includes provisions for error/notification formatting as well as text and link highlighting.</p>
<p>Read the accompanying <a href="https://eksith.wordpress.com/2009/02/16/rockwell-accessible/">blog post</a>.</p>



WELCOME;

// Footer HTML including copyrights and links etc...
$presets['site_footer'] =< <<FOOTER

	<p>
	<p class="powered">
		<a href="https://eksith.wordpress.com/"><img src="/templates/RockwellGradient/img/ouroboros.png" alt="Design by Eksith" longdesc="https://eksith.wordpress.com/" height="30" width="30" /></a>
	</p>

FOOTER;


/******************************************************************************

	##################### End casual editing #####################
	Edit below (if you know what you're doing) at your own risk!

*******************************************************************************/



// Root location of this file. All file inclusions depend on this.
$presets['site_root'] = dirname(__FILE__) . '/';

// Write enabled directories are required. Please disable execute privileges on these as a security precaution.
$presets['site_upload_directory'] = $presets['site_root'] . 'data/uploads';
$presets['site_cache_directory'] = $presets['site_root'] . 'data/cache';
$presets['site_cache_duration'] = '160';

// The template url is dependent on the SITE_THEME and SITE_TEMPLATE values.

$presets['site_template_directory'] = $presets['site_path'] . 'templates/';
$presets['site_template_url'] = $presets['site_path'] . 'templates/' . $presets['site_theme'] . '/';

// Default language to use in two letter format (preset is English).
$presets['site_lang'] = 'en';


// Security regular expressions
$presets['core_regex_valid_username'] = '/^[\w-_]{4,50}/iu';
$presets['core_regex_valid_displayname'] = '/^[\w\s\.-_]{0,100}/iu';
$presets['core_regex_valid_email'] = '/^[a-zA-Z0-9._-]+@[a-zA-Z0-9-]+\.[a-zA-Z.]{2,5}$/i';
$presets['core_regex_valid_querystring'] = '/^[\d-_a-zA-Z]/i';
$presets['core_regex_valid_htmlparams'] = '/^[\w-_]{4,20}/i';
$presets['core_regex_valid_colors'] = '/^#(([a-fA-F0-9]{3}$)|([a-fA-F0-9]{6}$))/i';
$presets['core_regex_invalid_search'] = '/^[~!\|\'()\[\]\< \>;]/i';

// Formatting allowed HTML tags and allowed corresponding attributes

// Convert deprecated tags.
// Convention: array([old tag], [new tag + formatting], [end tag]);
$presets['ui_format_tags_deprecated'] = array(
	array('b', 'strong', 'strong'),
	array('i', 'em', 'em'),
	array('s', 'span style="text-decoration:strikethrough;"', 'span'),
	array('strike', 'span style="text-decoration:strikethrough;"', 'span'),
	array('center', 'div style="text-align:center"', 'div'),
	array('left', 'div style="text-align:left"', 'div'),
	array('right', 'div style="text-align:right"', 'div'),
	array('u', 'span style="text-decoration:underline;"', 'span')
);

// Allowed HTML tags. All others, except for those found above, will be stripped
// Convention: array([tag name], [allowed attributes]);
$presets['ui_format_tags'] = array(
	array('a', 'style,class,title,href,rel,target'),
	array('abbr', 'style,class,title'),
	array('acronym', 'style,class,title'),
	array('blockquote', 'style,class,title,dir,lang,xml:lang'),
	array('br', 'style,class'),
	array('caption', 'style,class,title'),
	array('cite', 'style,class,title'),
	array('code', 'style,class,title'),
	array('del', 'style,class,title'),
	array('em', 'style,class,title'),
	array('h1', 'style,class,title'),
	array('h2', 'style,class,title'),
	array('h3', 'style,class,title'),
	array('h4', 'style,class,title'),
	array('h5', 'style,class,title'),
	array('h6', 'style,class,title'),
	array('hr', 'style,class'),
	array('img', 'style,class,src,height,width,border,hspace,vspace,alt,longdesc'),
	array('ins', 'style,class,title,cite'),
	array('li', 'style,class,title'),
	array('ol', 'style,class,title'),
	array('p', 'style,class,title,align,dir,lang,xml:lang'),
	array('pre', 'style,class,title,dir,lang,xml:lang'),
	array('strong', 'style,class,title'),
	array('span', 'style,class,title,dir,lang,xml:lang'),
	array('table', 'style,class,title,border\-collapse,bgcolor,background'),
	array('tfoot', 'style,class,title,align'),
	array('thead', 'style,class,title,align'),
	array('tbody', 'style,class,title,align'),
	array('td', 'style,class,title,align,valign'),
	array('th', 'style,class,title,align,valign'),
	array('tr', 'style,class,title,align,valign'),
	array('ul', 'style,class,title')
);

// Prohibited keywords in attribute values (from W3C)
// It is STRONGLY recommended that you not remove any of these 
// as certain browsers may allow their execution in unpredictable ways.
$presets['ui_format_invalid_attributes'] = array(
	'onabort',
	'onactivate',
	'onafterprint',
	'onafterupdate',
	'onbeforeactivate',
	'onbeforecopy',
	'onbeforecut',
	'onbeforedeactivate',
	'onbeforeeditfocus',
	'onbeforepaste',
	'onbeforeprint',
	'onbeforeunload',
	'onbeforeupdate',
	'onblur',
	'onbounce',
	'oncellchange',
	'onchange',
	'onclick',
	'oncontextmenu',
	'oncontrolselect',
	'oncopy',
	'oncut',
	'ondataavaible',
	'ondatasetchanged',
	'ondatasetcomplete',
	'ondblclick',
	'ondeactivate',
	'ondrag',
	'ondragdrop',
	'ondragend',
	'ondragenter',
	'ondragleave',
	'ondragover',
	'ondragstart',
	'ondrop',
	'onerror',
	'onerrorupdate',
	'onfilterupdate',
	'onfinish',
	'onfocus',
	'onfocusin',
	'onfocusout',
	'onhelp',
	'onkeydown',
	'onkeypress',
	'onkeyup',
	'onlayoutcomplete',
	'onload',
	'onlosecapture',
	'onmousedown',
	'onmouseenter',
	'onmouseleave',
	'onmousemove',
	'onmoveout',
	'onmouseover',
	'onmouseup',
	'onmousewheel',
	'onmove',
	'onmoveend',
	'onmovestart',
	'onpaste',
	'onpropertychange',
	'onreadystatechange',
	'onreset',
	'onresize',
	'onresizeend',
	'onresizestart',
	'onrowexit',
	'onrowsdelete',
	'onrowsinserted',
	'onscroll',
	'onselect',
	'onselectionchange',
	'onselectstart',
	'onstart',
	'onstop',
	'onsubmit',
	'onunload'
);




/******************************************************************************

	######################## End editing ########################

*******************************************************************************/

// Class loader (required)
function __autoload($class)
{
	global $presets;
	
	// Try and get a class file
	$cfile = $presets['site_root'] . 'lib/' . $class . '.class.php';
	
	// Or module file
	$mfile = $presets['site_root'] . 'modules/' . strtolower($class) . '/module.php';
	
	if(file_exists($cfile))
		require_once($cfile);
	elseif(file_exists($mfile))
		require_once($mfile);
	else
		exit("Unable to Find $class in the library folder or plugin modules folder");
	
	if(!class_exists($class, false))
		exit("Unable to load $class class");
}

Forum Core.class.php progress update

Just to show that I haven’t been twiddling my thumbs all this time, I thought I’d post what I have so far on the forum script I’ve been working on. Last Monday, I posted the updated database SQL.

Now there are 12 tables in all. I forgot to include the “usersgroups” table and now there’s a “floodcheck” table as well. Originally, I planned to use a purely cookie or session based check for flooding, but these can be easily spoofed. Flood checking will now take place in the database class.

I’ve written up the basics on Database.class.php (the parent class for all database abstraction), MySQL.class.php, config.php (I’ve finalised on the presets) and index.php, but this should give you an idea of where I’m going with this.

Note: The license is MIT, but this is only because Google Code doesn’t allow the ISC license or Public Domain dedications at this time. I plan to release a separate version of the code (functionally identical to this one) without any license and with my usual disclaimer.

WARNING: This code is highly experimental and will contain omissions, exceptions and egregious coding errors. This is just a progress update, so please feel free to treat it as such.

For some reason the sourcecode formatting puts the empty() function twice as, “emptyempty”. This is a WordPress formatting issue. The plain version doesn’t have the double entry.

/**
* Core class.
* Used for core forum functions, formatting and security.
*
* @author Eksith Rodrigo
* @package Core
* @access public
* @version 0.1
* @license http://www.opensource.org/licenses/mit-license.php MIT License
*/

final class Core
{
	static private $instance;	// Singleton instance for this class
	private $cookies;		// Boolean to hold cookie capability (true/false)
	private $props;			// Presets array passed during object construction
	private $db;			// Database object passed during object construction
	
	
	/**
	* Core constructor. 
	* This is a private function. Use "getInstance" below to get a fresh object.
	*
	* @param array $p the presets in the core passed by reference.
	* @param object $d the database object (MySQL, SQLite et al) passed by reference.
	*/
	private function __construct(&$p, &$d)
	{
		$props = $p;
		$db = $d;
		$cookies = $this->cookiesEnabled();
	}
	
	
	/**
	* Singleton instance.
	*
	* @param array $p The presets in the core passed by reference.
	* @param object $d The database object (MySQL, SQLite et al) passed by reference.
	* @return object Core Singleton instance
	*/
	static function getInstance(&$p, &$d)
	{
		if(!isset(self::$instance))
			self::$instance = new Core($p, $d);
		
		return self::$instance;
	}
	
	
	
	/******************************************************************************
		Posting status and user input
	******************************************************************************/
	
	/**
	* Checks if user is currently browsing the index page.
	*
	* @return bool true If on the index page. Defaults to false.
	*/
	public function browsingIndex()
	{
		if(!isset($_GET['forum']) && !isset($_GET['topic']) && !isset($_GET['page']) && !isset($_GET['section']))
			return true;
			
		return false;
	}
	
	
	/**
	* Checks if user is currently browsing a specific forum.
	*
	* @return bool true If on a forum page. Defaults to false.
	*/
	public function browsingForum()
	{
		if(isset($_GET['forum']) && !isset($_GET['topic']) && !isset($_GET['section']))
			return true;
			
		return false;
	}
	
	
	/**
	* Checks if user is currently browsing a specific topic.
	*
	* @return bool true If on a topic page. Defaults to false.
	*/
	public function browsingTopic()
	{
		if(isset($_GET['forum']) && isset($_GET['topic']) && !isset($_GET['section']))
			return true;
			
		return false;
	}
	
	
	/**
	* Checks if user is currently browsing a specific section. I.E. Plugin module.
	*
	* @return bool true If on a section page. Defaults to false.
	*/
	public function browsingSection()
	{
		if(!isset($_GET['forum']) && !isset($_GET['topic']) && isset($_GET['section']))
			return true;
			
		return false;
	}
	
	
	/**
	* Gets the current page index.
	*
	* @return int $p Filtered output defaults to 1 (first page).
	*/
	public function currentPage()
	{
		$p = 1;
		if(isset($_GET['page']))
			$p = $this->getDefaultInt($_GET['page'], 1);
		
		return $p;
	}
	
	
	/**
	* Status: Posting a new forum topic.
	*
	* @return bool true If $_POST array key "forum" is present and "topic" is absent. Defaults to false.
	*/
	public function postingNewTopic()
	{
		if(isset($_POST['forum']) && !isset($_POST['topic']))
			return true;
		
		return false;
	}
	
	
	/**
	* Status: Posting a new topic reply.
	*
	* @return bool true If $_POST array keys "forum" and "topic" are present. Defaults to false.
	*/
	public function postingNewReply()
	{
		if(isset($_POST['forum']) && isset($_POST['topic']))
			return true;
		
		return false;
	}
	
	
	/**
	* Status: User is attempting to login.
	*
	* @return bool true If $_POST array keys "username" and "password" are present. Defaults to false.
	*/
	public function checkLogin()
	{
		if(isset($_POST['username']) && isset($_POST['password']))
			return true;
		
		return false;
	}
	
	
	/**
	* Status: User is attempting to login.
	*
	* @return bool true If $_GET array keys "logout" is set and the "user" session is present. Defaults to false.
	*/
	public function checkLogout()
	{
		if(isset($_GET['logout']) && !isset($_GET['login']) && isset($_SESSION['user']))
			return true;
		
		return false;
	}
	
	
	/**
	* Gets the currently requested forum ID. 
	* Priority is given to the $_POST "forum" key.
	*
	* @return int $f_id Filtered output defaults to 0 (no forum requested).
	*/
	public function postForumID()
	{
		$f_id = 0;
		if(isset($_POST['forum']))
			$f_id = $this->getDefaultInt($_POST['forum'], 0);
		elseif(isset($_GET['forum']))
			$f_id = $this->getDefaultInt($_GET['forum'], 0);
		
		return $f_id;
	}
	
	
	/**
	* Gets the currently requested topic ID. 
	* Priority is given to the $_POST "topic" key.
	*
	* @return int $t_id Filtered output defaults to 0 (no topic requested).
	*/
	public function postTopicID()
	{
		$t_id = 0;
		if(isset($_POST['topic']))
			$t_id = $this->getDefaultInt($_POST['topic'], 0);
		elseif(isset($_GET['topic']))
			$t_id = $this->getDefaultInt($_GET['topic'], 0);
		
		return $t_id;
	}
	
	
	/**
	* Gets the currently requested parent (topic or forum) ID. 
	* Priority is given to the $_POST "parent" key.
	*
	* @return int $p_id Filtered output defaults to 0 (no parent item requested).
	*/
	public function postParentID()
	{
		$p_id = 0;
		if(isset($_POST['parent']))
			$p_id = $this->getDefaultInt($_POST['parent'], 0);
		elseif(isset($_GET['parent']))
			$p_id = $this->getDefaultInt($_GET['parent'], 0);
		
		return $p_id;
	}
	
	
	/**
	* Gets the posted subject title form field data.
	*
	* @return string $_t Filtered output defaults to '' (blank string).
	*/
	public function postTitle()
	{
		$_t = '';
		if(isset($_POST['title']))
			$_t = $this->getDefaultString($_POST['title'], '');
		
		return $_t;
	}
	
	
	/**
	* Gets the posted content data (message body) form field data.
	*
	* @return string $_c Filtered output defaults to '' (blank string).
	*/
	public function postContent()
	{
		$_c = '';
		if(isset($_POST['content']))
			$_c = $this->getDefaultHtml($_POST['content'], '');
		
		return $_c;
	}
	
	
	/**
	* Gets the posted author name form field data
	*
	* @return string $_a Filtered output defaults to '' (blank string).
	*/
	public function postAuthor()
	{
		$_a = '';
		if(isset($_POST['author']))
			$_a = $this->getDefaultString($_POST['author'], '');
		
		return $_a;
	}
	
	
	/**
	* Gets the posted author email form field data.
	*
	* @return string $_e Filtered output defaults to '' (blank string).
	*/
	public function postEmail()
	{
		$_e = '';
		if(isset($_POST['email']))
			$_e = $this->getDefaultString($_POST['email'], '');
		
		return $_e;
	}
	
	
	/**
	* Gets a database compatible datetime stamp.
	*
	* @return date Current date time in "Year-Month-Date Hour:Minute:Second" format.
	*/
	public function postDate() {
		return date('Y-m-d H:i:s');
	}
	
	
	
	
	/******************************************************************************
		User status and identity
	******************************************************************************/
	
	
	/**
	* Gets user data into variables passed by reference. 
	*
	* @deprecated This function is deprecated in favor of the one with fewer parameters below it 
	* @see function getUserDataArray.
	*
	* @param int $userID user ID key. Defaults to 0 on error
	* @param string $user username. Defaults to '' on error
	* @param array $priv user privileges. Defaults to empty array on error
	* @param int $msgID message ID key. Defaults to 0 on error
	* @param string $exc error message text.
	*/
	public function getUserData(&$userID, &$user, &$priv, &$msgID, &$exc)
	{
		try
		{
			$raw = explode('::', $this->loggedInUser());
			list($userID, $user, $msgID) = explode(',' $raw[0]);
			$priv = $this->getPrivileges($raw[1]);
			
			// Filter everything
			$userID = $this->getDefaultInt($userID, 0);
			$user = $this->getDefaultString($user, '');
			$msgID = $this->getDefaultInt($msgID, 0);
		}
		catch (Exception $exc)
		{
			$userID = 0;
			$user = '';
			$priv = array();
			$msgID = 0;
		}
	}
	
	
	/**
	* Gets user data as an array.
	*
	* @param array $data user data (id, name, privileges, messageID). Defaults to error message on exception.
	* @return array|string Array with user data if successful or exception message as string on error.
	*/
	public function getUserDataArray()
	{
		$data = null;
		try
		{
			$raw = explode('::', $this->loggedInUser());
			
			// First part of "raw" (user info)
			list($userID, $user, $msgID) = explode(',' $raw[0]);
			
			$data = array(
				"id" => $userID,
				"username" => $this->getDefaultString($user, ''),
				"messageID" = $this->getDefaultInt($msgID, 0),
				"privileges" => $this->getPrivileges($raw[1])	// Second part of "raw" (privileges)
			);
		}
		catch (Exception $exc)
		{
			$data = $exc;
		}
		return $data;
	}
	
	
	/**
	* Saves user data into session and cookies.
	*
	* @deprecated This function is deprecated in favor of the one with fewer parameters below it.
	* @see function setUserDataArray
	*
	* @param int $userID Unique user key
	* @param string $name Username
	* @param array $priv Designated user parameters
	* @param int msgID Display message number
	*/ 
	public function setUserData(&$userID, &$user, &$msgID, &$priv)
	{
		$data = $this->encrypt($userID . ',' . $user . ',' . $msgID . '::' . implode('', $priv));
		
		// Destroy and restart the current session
		session_regenerate_id(true);
		
		$_SESSION['user'] = $data;
		
		if($cookies)
			setcookie("user", $data);
	}
	
	
	/**
	* Saves user data into session and cookie
	*
	* @param array $info Raw user data including id, name, message ID and privileges array
	*/
	public function setUserDataArray(&$info)
	{
		list($userID, $user, $msgID, $priv) = $info;
		
		$data = $this->encrypt($userID . ',' . $user . ',' . $msgID . '::' . implode('', $priv));
		
		// Destroy and restart the current session
		session_regenerate_id(true);
		
		$_SESSION['user'] = $data;
		
		if($cookies)
			setcookie("user", $data);
	}
	
	
	/**
	* Check if user can support cookies
	*
	* @return bool $cookies True if cookies are enabled by the browser, false if else
	*/
	public function cookiesEnabled()
	{
		if(empty($cookies))
		{
			setcookie("cookies", time());
			if(isset($_COOKIE["cookies"]))
			{
				$cookies = true;
				unset($_COOKIE["cookies"]);
			}
			else
			{
				$cookies = false;
			}
		}
		
		return $cookies;
	}
	
	
	/**
	* Check if user has a valid login session or saved cookie
	*
	* @return string $ret Formatted string pattern containing user ID, name, display name message ID and privilege flags
	*/
	public function loggedInUser()
	{
		// Prevent hijack
		if(!isset($_SESSION['user']))
			session_regenerate_id();
		
		$ret = null;
		
		if(isset($_SESSION['user']))
			$ret = $this->decrypt($_SESSION['user']);
		elseif(isset($_COOKIE["user"]))
			$ret = $this->decrypt($_COOKIE['user']);
		else
			$ret = '0,0,0::000000000000000000000';
		
		return $ret;	
	}
	
	
	/**
	* Authenticates user based on posted username and password form fields
	*
	* @return bool true If authentication went without problems. Defaults to false.
	*/
	public function login()
	{
		// Make sure the current user is logged out first
		if(isset($_SESSION['user']) || isset($_COOKIE['user']))
			$this->logout();
		
		if(isset($_POST['username']) && isset($_POST['password']))
		{
			$username = $this->getDefaultString($_POST['username'], '');
			$password = $this->getDefaultString($_POST['password'], '');
			
			// Invalid or empty form data
			if(empty($username) || empty($password))
				return false;
			
			// Connect to database and get user by name
			$data = $db->getLoginData($username);
			
			// User doesn't exist
			if(empty($data))
				return false;
			
			// Verify returned authentication data
			$encPass = $this->decrypt($data['pass']);
			$encSalt = $this->decrypt($data['salt']);
			
			// Password matches, login the user and return true
			if(str_replace($encSalt, '', $encPass) == $password)
			{
				$udata = array($data['id'], $username, 0, implode('', $data['priv']));
				$this->setUserDataArray($udata);
				
				return true;
			}
		}
		
		return false;
	}
	
	
	/**
	* Logout by removing 'user' session and cookie data.
	*/
	public function logout()
	{
		if(isset($_SESSION['user']))
			$_SESSION['user'] = null;
		
		if(isset($_COOKIE['user']))
			unset($_COOKIE['user']);
		
		// Just to be sure
		session_regenerate_id();
	}
	
	
	/**
	* Puts user privileges into an array
	*
	* @param string $priv User privilegs flags 21 characters in length (0/1)
	* @return array $privileges User privileges sorted into readable array
	*/
	public function getPrivileges(&$priv)
	{		
		// Initial array with no privileges
		$privileges = array(
			"CanReply" => 0,
			"CanCreateTopics" => 0,
			"CanUsePM" => 0,
			"CanReplyReadOnlyForums" => 0,
			"CanCreateTopicsReadOnlyForums" => 0,
			"CanEditOwnPosts" => 0,
			"CanDeleteOwnPosts" => 0,
			"CanEditOthersPosts" => 0,
			"CanDeleteOthersPosts" => 0,
			"CanMoveTopics" => 0,
			"CanLockTopics" => 0,
			"CanCreateForums"= 0,
			"CanEditForums" => 0,
			"CanDeleteForums" => 0,
			"CanCreateUsers"= 0,
			"CanBanUsers"= 0,
			"CanEditUsers"= 0,
			"CanDeleteUsers"= 0,
			"CanCreateGroups"= 0,
			"CanEditGroups"= 0,
			"CanDeleteGroups"= 0
		);
		
		// Get array from privileges string
		$pr = explode('', $priv);
		
		// If the initial array has appropriate number of flags
		if(count($pr) == 21) {
			
			// Get secured values back to privileges from source array
			$this->filterPushToArray("int", $pr, $privileges);
		}
		
		return $privileges;
	}
	
	
	/**
	* Utility function to reset default values in an array with filtered data.
	* Arrays are passed by reference.
	*
	* @param string $type Content type to return. Values "html", "string", "int".
	* @param int $count The number of items to iterate through.
	* @param array $source The raw array containing unfiltered data.
	* @param array $source Sorted destination array with filtered data.
	*/
	public function filterPushToArray($type, $count, &$source, &$dest)
	{
		$type = strtolower($type);
		
		$i = 0;
		
		// Iterate through each key and insert corresponding source array value
		foreach($dest as $key => $value)
		{
			// Get filtered content based on type
			switch($type)
			{
				case "html":
					$dest[$key] = $this->getDefaultHtml($source[i], $value);
					break;
				case "string":
					$dest[$key] = $this->getDefaultString($source[i], $value);
					break;
				case "int":
					$dest[$key] = $this->getDefaultInt($source[i], $value);
					break;
			}
			$i++;
		}
	}
	
	
	
	
	/******************************************************************************
		Formatting and presentation functions
	******************************************************************************/
	
	
	/**
	* Converts topic titles into usable URLs.
	* Will be used along with IDs.
	*
	* @param string $str The raw title
	* @return string $str The cleaned up title with special characters removed
	*/
	public function titleToSlug($str)
	{
		$str = preg_replace('/[~`!\@#\$\%\^\&\*\(\)\-\_\+={}\[\]\|:;\"\'\< \>\?,.\\\/\s+]/imu', '', $str);
		return strtolower($str);
	}
	
	
	/**
	* Check if CAPS are below a percent threshold.
	* This function is for improving readability by enforcing caps limits.
	*
	* @param string $str The posted content
	* @param int $limit The threshold limit in percent
	* @return bool true If the amout of CAPS matches are below the limit, else returns false.
	*/
	public function capsCheck($str, $limit)
	{
		$percent = round(($limit / 100) * strlen($str));
		preg_match_all('/[A-Z]/', $str, $matches);
		return ((count($matches[0]) >= $percent) ? true : false);
	}
	
	
	/**
	* Truncates strings to specified limit and return with ellipse
	* This function is for improving readability by limiting titles or summary lengths.
	*
	* @param string $str The posted content
	* @param int $limit The threshold limit in number of maximum characters. Default is 100.
	* @return string $str Formatted text limited to specified length and followed by '...' .
	*/
	public function stringTrunc($str, $limit = 100)
	{
		if (strlen($str) < = $limit)
			return $str;
		
		return substr_replace($string, '...', ($limit - strlen($str)));
	}
	
	
	
	
	/******************************************************************************
		Url authentication and flood limit
	******************************************************************************/
	
	
	/**
	* Verify a certain amount of time has passed between requests
	* Prevents abuse by checking for flood/DoS or forced entry attempts.
	*
	* @deprecated This function is deprecated in favor of a purely database based approach
	*
	* @param int $limit The threshold limit in time format.
	* @return bool true If sufficient time has passed between requests or false if it has not.
	*/
	public function floodLimit($limit)
	{
		$req = $this->postDate();
		
		if(isset($_SESSION['req']) || isset($_COOKIE['req']))
		{
			$sreq = (isset($_SESSION['req']))? strtotime($_SESSION['req']) : $req;
			$creq = (isset($_COOKIE['req']))? strtotime($_COOKIE['req']) : $req;
			
			// Compare and get the most recent time
			if($sreq < $creq)
				$sreq = $creq;
			
			return ($req < ($sreq + strtotime($limit)));
		}
		
		$_SESSION['req'] = $req;
		setcookie('req', $req);
		return false;
	}
	
	/**
	* Authenticate the requested URL as originating from the site homepage
	* Prevents outside redirect attempts by comparing the source and destination URLs (site_url)
	*
	* @param string $req The raw requested URL
	* @return bool true If the source and destination URLs match or false if they dont
	*/
	public function verifyURL($req)
	{
		if(strtolower($req) == substr(0, (strlen(strtolower($props['site_url'])) - 1)))
			return true;
		
		return false;
	}
	
	
	
	
	/*****************************************************************************************************
		Sanitized input
	*****************************************************************************************************/
	
	
	/**
	* Allow only text. HTML escaped if necessary, output unicode.
	* Prevents malicious or accidental HTML tags from being posted. Formatted strings only.
	*
	* @param string $v The content to be checked.
	* @return string $v|$d Verified and formatted content as $v or default, $d, if $v is invalid or empty.
	*/ 
	public function getDefaultString($v, $d)
	{
		$v = (empty($v) && (strtolower($v) != 'false') && ($v != '0'))? $d : $v;
		return htmlentities(iconv('UTF-8', 'UTF-8', $v), ENT_QUOTES, 'UTF-8');
	}
	
	/**
	* Allow only numbers, specifically, integers. No strings and/or HTML allowed.
	* Prevents any value other than an integer from being sent.
	*
	* @param string $v The content to be checked.
	* @return int $v|$d Verified $v if it is an integer or default, $d, if $v is invalid.
	*/ 
	public function getDefaultInt($v, $d)
	{
		return (!ctype_digit($v))? $d : $v;
	}
	
	
	/**
	* Allow only safe HTML (tags, attributes and attribute values).
	* Prevents any value other than those in the presets whitelists from being sent back.
	* 
	* This function needs more work!
	*
	* @param string $v The content to be checked.
	* @return string $v|$d The filtered and formatted allowed HTML as $v or default fallback value, $d, if $v is empty or cannot be parsed.
	*/
	public function getDefaultHtml($v, $d)
	{
		// Check if the content isn't empty or we skip all the filtering
		
		$v = (empty($v) && (strtolower($v) != 'false') && ($v != '0'))? $d : $v;
		
		// Content passed empty check
		if($v != $d)
		{
			// Get disallowed attributes from presets
			$badattr = implode('|', $props['ui_format_invalid_attributes']);
			
			// Get formatting whitelist
			$allowed = implode('|', $props['ui_format_tags']);
			
			// Remove all tags not in the formatting whitelist first, but leave the content inside
			$v = preg_replace("!<\s*?(" . $allowed  . ").*?>((.*?))?!ismu", "\3", $v); 
			
			
			// Iterate through each allowed tag
			foreach($props['ui_format_tags'] as $tag)
			{
				// Get matched tags in the content
				preg_match_all("< \s*?" . $tag[0] . "([^>]+)>!ismu", $v, $matches);
				
				// Allowed tags are present
				if(count($matches[0] > 0))
				{
					// Iterate through each match and filter attributes.
					for($i=0; $i < count($matches[0]); $i++)
					{
						// Get the specified attributes for this tag (The "([^>]+)" part in the above regular expression)
						$attr = explode(' ', $matches[1][$i]);
						
						// Filtered replacement
						$repl = "";
						
						// Each allowed attribute for this specific tag
						for($j=0; $j < count($attr); $j++)
						{
							$at = strtolower($attr[$j]);
							$atm = explode(',', $tag[1]);
							
							// Iterate through each found attribute to see if it's in the allowed list for this tag
							// Ignore bad attribute values (I.E. onclick inside "style" attribute)
							for($k=0; $k < count(atm); $k++)
							{
								if(preg_match("!^" . atm[k] . "=\"?[^(" . $badattr . ")]+\"?!ismu", $at))
									$repl .= " " . $at;
							}
						}
						
						// Replace the old tag and attributes with clean ones
						$v = str_replace($matches[0][$i], "<". $tag[0] . $repl . ">", $v);
					}
				}
			}
		}
		return $v;
	}
	
	
	
	
	/*****************************************************************************************************
		Security and encryption
	*****************************************************************************************************/
	
	/**
	* Generate a random string with a specific range (for salts etc...)
	*
	* @param int $min The minimum generated string length
	* @param int $max The maximum generated string length
	* @param bool $upper Uppercase (capital) letters are required. Defaults to true.
	* @param bool $special Special characters (punctuation etc...) are required. Defaults to true.
	* @return string $random Generated random string based on specifications.
	*/
	public function genRandom($min, $max, $upper = true, $spec = true)
	{
		$charset = "0123456789abcdefghijklmnopqrstuvwxyz";
		
		if($upper)
			$charset .= "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
		
		if($spec)
			$charset .= "~!@#$%^&*()_+`-={}|\\]?[\":;'><,./";
		
		$l = mt_rand($min, $max);
		for($i = 0; $i<$l; $i++) {
			$random .= $charset[(mt_rand(0, (strlen($charset)-1)))];
		}
		
		return $random;
	}
	
	/**
	* Encrypt in Rijndael 256 with optional Base64 encoding.
	* If Base64 is used during encryption, it must be used again for decryption.
	*
	* @param string $text The content to be encrypted.
	* @param bool $base64 Optional flag for base64 encoding the the final output. Defaults to true.
	* @return string Encrypted and optionally encoded string
	*/
	public function encrypt($text, $base64=true)
	{
		if($base64)
			return trim(base64_encode(mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $salt, $text, MCRYPT_MODE_ECB, mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB), MCRYPT_RAND))));
		
		return trim(mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $salt, $text, MCRYPT_MODE_ECB, mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB), MCRYPT_RAND)));
	}
	
	/**
	* Decrypt as encrypted above in Rijndael 256 with optional Base64 decoding (Default).
	* If Base64 is used to encrypt, it must be used again for decryption.
	*
	* @param string $text The content to be decrypted.
	* @param bool $base64 Optional flag for base64 decoding the input first. Defaults to true.
	* @return string Decrypted content
	*/
	public function decrypt($text, $base64=true)
	{
		if($base64)
			return trim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $salt, base64_decode($text), MCRYPT_MODE_ECB, mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB), MCRYPT_RAND)));
		
		return trim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $salt, $text, MCRYPT_MODE_ECB, mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB), MCRYPT_RAND)));
	}
	
	
	
	
	/*****************************************************************************************************
		Software downloading and updating
	*****************************************************************************************************/
	
	/**
	* Download a specified file to specified destination.
	* Used for updating the software or getting special content from a remote server.
	* If PHP has been compiled with cURL, it will use that. If not, it will try fopen.
	* 
	* @param string $url Download source location
	* @param string $dest Download destination (must be a writable folder)
	* @return bool $ret True If everything went OK or false if something went wrong.
	*/
	public function getFile($url, $dest)
	{
		$ret = false;
		$out = '';
		try
		{
			if(function_exists('curl_init'))
			{
				// If cURL exists, we can use it
				
				$file = curl_init($url);
				curl_setopt($file, CURLOPT_CONNECTTIMEOUT, 3);
				curl_setopt($file, CURLOPT_RETURNTRANSFER, true);
				curl_setopt($file, CURLOPT_BINARYTRANSFER, true);
				
				$out = curl_exec($file);
				
				// After everything is read, close cURL
				curl_close($file);
				
				// Empty if something went wrong
				if(empty($out))
					$ret = false;
				
				$fo = fopen($dest, 'w');
				fwrite($fo, $file);
				fclose($fo);
			}
			else
			{
				// If cURL doesn't exist, we have to stick to fopen
				
				$file = fopen($url, 'rb');
				
				// Buffer the read file
				while(!feof($file))
				{
					$out .= fread($file, 8192);
				}
				
				// Empty if something went wrong
				if(empty($out))
					$ret = false;
				
				fclose($file);
				
				$fo = fopen($dest, 'w');
				fwrite($fo, $out);
				fclose($fo);
			}
			
			// If the newly created file exists, return true
			if(file_exists($dest))
				$ret = true;
		}
		catch
		{
			// If something went wrong, return false
			$ret = false;
		}
		
		return $ret;
	}
	
	/**
	* Extract downloaded files to specified directories.
	* Obviously incomplete...
	*/
	public function pkgExtract($file, $dest)
	{
		if(function_exists('zip_open'))
		{
			// Set working path
			$path = $props['site_upload_directory'] . 'tmp';
			
			// Extract the file
			$pkg = zip_open($path, $file);
			
			// Opening successful
			if(is_resource($pkg))
			{
				while($item = zip_read($pkg))
				{
					$cpath = $path . dirname(zip_entry_name($item));
					$cname = $path . zip_entry_name($item);
				}
			}
		}
	}
}

New (old) PHP forum SQL tables

While waiting to finish the rest of the code on my forum script, I decided to do some updates to the SQL tables. I’ve split up the scripts into four files; clean.sql, setup.sql, forums.sql, users.sql.

The separate files are to make the setup phase more flexible. An admin can choose to overwrite a previous installation by running clean.sql, or simply run setup.sql to create the tables if only the database exists. The remaining files, forums.sql and users.sql, are to create the default forums, users and groups. The default forums will contain one demo topic and reply. The default user will be the admin account.

The group privilege system is setup as a series of flags where 1 = has privilege and 0 = doesn’t have it. These flags are arranged in the following order:

  • Can create replies
  • Can create topics
  • Can use PM
  • Can reply in read-only forums
  • Can create topics in read-only forums
  • Can edit own posts
  • Can delete own posts
  • Can edit other users’ posts
  • Can delete other users’ posts
  • Can move topics
  • Can lock topics
  • Can create forums
  • Can edit forums
  • Can delete forums
  • Can create users
  • Can ban users
  • Can edit users
  • Can delete users
  • Can create groups
  • Can edit groups
  • Can delete groups

A user in the “Administrators” group will have : ‘1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1’. As in all the privileges.

A user in the “Moderators” group will have : ‘1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,1,0,0,0,0,0’. As in every privilege from replying to posts to locking topics. But canot affect forums, groups or users (other than banning them).

A user in the ordinary “Members” group will have : ‘1,1,1,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0’. As in the ability to create topics, replies and delete their own posts or replies, but nothing else.

I felt this is a better system than giving admins everthing, mods something and members nothing as it allows greater control. Maybe a forum owner would want different types of admins or introduce a ladder system via a plugin.

And speaking of plugins, this turned out to be the hardest part so far. It seems introducing some degree of user override without allowing a plugin to destroy the base system is a bit of a noodle scratcher for me. I thought of a simple hooks system, but this seemed is too limited. A strictly API based function override would be too rigid and cumbersome. So the only solution seems to be a hybrid of sorts. I’m still looking into this and I want to be careful where I get examples.

This is taking longer than expected because I’m trying to incorporate examples that are either in the public domain or under a very permissive license since I plan to release this with the ISC license. I would prefer to release it under only my usual disclaimer (pretty much in the Public Domain), but I don’t want to violate any other licenses in the process.

The ceanup sql script (clean.sql):

-- Existing table cleanup (This method destroys existing data)

DROP TABLE IF EXISTS `messages`;
DROP TABLE IF EXISTS `forums`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `groups`;
DROP TABLE IF EXISTS `pm`;
DROP TABLE IF EXISTS `polls`;
DROP TABLE IF EXISTS `pollvotes`;
DROP TABLE IF EXISTS `banned`;
DROP TABLE IF EXISTS `pollvotes`;
DROP TABLE IF EXISTS `confirmations`;

Note: The original six tables have already balooned into ten. This was inevitable as too much simplicity in the database will result in increased complexity code-side. In this case, when it came down to complexity, I had to pick the lesser of two evils.

The setup script (setup.sql):

-- Create all tables

-- Topics and replies are stored here
CREATE TABLE `messages` (
	`id` bigint(20) NOT NULL auto_increment,
	`parent_id` bigint(20) default 0,
	`forum_id` int(25) default 0,
	`title` varchar(255) NOT NULL default '',
	`content` text default '',
	`content_plain` text default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`user_id` bigint(20) default 0,
	`author` varchar(50) default '',
	`author_email` varchar(100) default '',
	`author_web` varchar(255) default '',
	`author_ip` varchar(100) default '',
	`reply_count` int(25) default 0,
	`view_count` int(25) default 0,
	`locked` tinyint(4) default 0,
	`sticky` tinyint(4) default 0,
	`global_sticky` tinyint(4) default 0,
	`last_id` bigint(20) NOT NULL default 0,
	`last_author` varchar(50) NOT NULL default '',
	`last_date` datetime NOT NULL default '0000-00-00 00:00:00',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- All forum data
CREATE TABLE `forums` (
	`id` int(25) NOT NULL auto_increment,
	`parent_id` int(25) default 0,
	`display_order` int(16) default 0,
	`title` varchar(255) NOT NULL default '',
	`content` text default '',
	`content_plain` text default '',
	`topic_count` int(25) default 0,
	`reply_count` int(25) default 0,
	`last_id` bigint(20) default '0',
	`last_author` varchar(50) default '',
	`last_date` datetime default '0000-00-00 00:00:00',
	`read_only` tinyint(4) default 0,
	`private` tinyint(4) default 0,
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Membership info
CREATE TABLE `users` (
	`id` bigint(20) NOT NULL auto_increment,
	`username` varchar(50) NOT NULL default '',
	`password` text NOT NULL default '',
	`password_salt` text NOT NULL default '',
	`display_name` varchar(100) default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`enabled` tinyint(4) default 1,
	`sig` text default '',
	`avatar` text default '',
	`email` varchar(100) default '',
	`web` varchar(255) default '',
	`bio` text default '',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Sorted groups
CREATE TABLE `groups` (
	`id` int(25) NOT NULL auto_increment,
	`title` varchar(255) NOT NULL default '',
	`content` text default '',
	`signup` tinyint(4) default 0,
	`privileges` text default '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- User / Group relationships
DROP TABLE IF EXISTS `usersgroups`;

CREATE TABLE `usersgroups` (
	`user_id` bigint(20) NOT NULL,
	`group_id` int(25) NOT NULL
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Private Messaging
CREATE TABLE `pm` (
	`id` bigint(20) NOT NULL auto_increment,
	`user_id` bigint(20) default 0,
	`sender_id` bigint(20) default 0,
	`title` varchar(255) NOT NULL default '',
	`content` text default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`opened` tinyint(4) default 0,
	`hide_sender` tinyint(4) default 0,
	`hide_reader` tinyint(4) default 0,
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Topic polls
CREATE TABLE `polls` (
	`id` bigint(20) NOT NULL auto_increment,
	`message_id` bigint(20) default 0,
	`question` text NOT NULL default '',
	`multiple` tinyint(4) default 0,
	`option1` varchar(255) default '',
	`option2` varchar(255) default '',
	`option3` varchar(255) default '',
	`option4` varchar(255) default '',
	`option5` varchar(255) default '',
	`option6` varchar(255) default '',
	`option7` varchar(255) default '',
	`option8` varchar(255) default '',
	`option9` varchar(255) default '',
	`option10` varchar(255) default '',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Poll votes
CREATE TABLE `pollvotes` (
	`id` bigint(20) NOT NULL auto_increment,
	`poll_id` bigint(20) NOT NULL,
	`user_id` bigint(20) NOT NULL,
	`options` varchar(25) default '0,0,0,0,0,0,0,0,0,0',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Banned emails, IPs or usernames
CREATE TABLE `banned` (
	`id` bigint(20) NOT NULL auto_increment,
	`ip` varchar(100) default '',
	`username` varchar(50) default '',
	`email` varchar(100) default '',
	`reason` varchar(255) default '',
	`ban_date` datetime NOT NULL default '0000-00-00 00:00:00',
	`expires` datetime default '0000-00-00 00:00:00',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Registration or email change confirmations
CREATE TABLE `confirmations` (
	`id` bigint(20) NOT NULL auto_increment,
	`user_id` bigint(20) NOT NULL,
	`title` varchar(255) default '',
	`type` varchar(100) NOT NULL default '',
	`created` datetime default '0000-00-00 00:00:00',
	`expires` datetime default '0000-00-00 00:00:00',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;

Note: The “groups” table has a “signup” flag. This is where all new registered users will be sorted.

The default user and group setup script (users.sql):

-- Default user (admin)

INSERT INTO users(`id`, `username`, `password`, `password_salt`, `created`, `enabled`, `email`) 
		VALUES('1', 'USERNAME', 'PASSWORD', 'PASSWORDSALT', NOW(), '1', 'EMAIL');


-- Default groups (new members will be sorted into the Members group)

INSERT INTO groups(`id`, `title`, `content`, `signup`, `privileges`) 
		VALUES('1', 'Administrators', 'Will have global administration privileges', '0', '1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1');

INSERT INTO groups(`id`, `title`, `content`, `signup`, `privileges`) 
		VALUES('2', 'Moderators', 'Will moderation privileges', '0', '1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,1,0,0,0,0,0');

INSERT INTO groups(`id`, `title`, `content`, `signup`, `privileges`) 
		VALUES('2', 'Members', 'Will be the default registration group', '1', '1,1,1,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0');


-- Setup the default user in the Administrators group

INSERT INTO usersgroups(`user_id`, `group_id`) 
		VALUES('1', '1');

Note: USERNAME, PASSWORD, PASSWORDSALT and EMAIL markers in the first INSERT command will be replaced with the appropriate data by the installation script. These will be grabbed by the submitted form data.

And lastly, the demo forum, topic and reply data (forums.sql)

-- Default entries

INSERT INTO forums (`id`, `parent_id`, `display_order`, `title`, `content`, `content_plain`, `topic_count`, `reply_count`, `last_id`, `last_author`, `last_date`, `read_only`, `private`) 
		VALUES ('1', '0', '0', 'General', '<p>This is where the chit chat happens</p>', 'This is where the chit chat happens', '1', '1', '2', 'Anon Author', NOW(), '0', '0');

INSERT INTO messages (`id`, `parent_id`, `forum_id`, `title`, `content`, `content_plain`, `created`, `author`, `reply_count`, `view_count`, `locked`, `last_id`, `last_author`, `last_date`, `sticky`, `global_sticky`) 
		VALUES ('1', '0', '1', 'Demo Topic', '<p>This is an example of a forum topic</p>', 'This is an example of a forum topic', NOW(), 'Anon Author', '1', '0', '0', '0', 'Anon Author', NOW(), '0', '0');

INSERT INTO messages (`id`, `parent_id`, `forum_id`, `title`, `content`, `content_plain`, `created`, `author`, `reply_count`, `view_count`, `locked`, `last_id`, `last_author`, `last_date`, `sticky`, `global_sticky`) 
		VALUES ('2', '1', '1', 'Demo Reply', '<p>This is an example of a topic reply</p>', 'This is an example of a topic reply', NOW(), 'Anon Author', '0', '0', '0', '0', 'Anon Author', NOW(), '0', '0');

If you spot any errors in the SQL or if you have any suggestions on improving the overall design, please comment.

Update on the new (old) PHP forum

A few weeks ago, I gave an overly enthusiastic estimate of when I’ll be completing this project. Well it all seems to have been for the best, since I decided the one-page-software scheme isn’t really going to work these days.

While that may be ideal for certain situations, aside from the most primitive of software packages, it really doesn’t seem all that practical to put everything on one page. But I am following the small size protocol in that there will only be very few code pages. So I ended up scrapping everything and starting from scratch.

I will have some form of database abstraction. Though the first release will only have a MySQL class, I’m leaving the methods open so a substitute will be trivial to write in for other databases. The critical ones to support at this point are MySQL and SQLite. The rest will hopefully come later starting with PostgreSQL and MS SQL.

I’m releasing this without a license, but will include my usual disclaimer as found on the experiments page.  So feel free to take it apart as you wish.

In addition to this, I would encourage people to keep in mind, this is being written by a PHP novice. I can’t comment on security and performance yet, since I don’t have any testable code in that area. But I did consider performance second to security. 

  • Sessions and cookies are encrypted when created.
  • Login re-directions that start with “http://&#8221; will be checked against the site_url setting which will be the base URL for all on page links.
  • Rijndael 256 with mcrypt for everything (this includes cookie encryption which does run the risk of being a bit large).
  • Passwords and password salts are encrypted before storage in the database.

I understand the choice of encryption might be a bit overkill, but I wanted to see if I could get away with it in a production environment (which I hope to see it running in soon).

I have no idea if some of the methods I used are sound when it comes to PHP standards, but I pursued what I felt were the most reasonable methods of doing things. I don’t think a different point of view could hurt. But if I am doing something horrendously wrong, I would strongly encourage everyone to let me know right away.

I won’t be making the same mistake this time by announcing a release date, so I’ll take the Debian road and say “It’ll be ready when it’s ready.”

Here’s the MySQL DB schema :

DROP TABLE IF EXISTS `messages`;

CREATE TABLE `messages` (
	`id` bigint(20) NOT NULL auto_increment,
	`parent_id` bigint(20) default 0,
	`forum_id` int(25) default 0,
	`title` varchar(255) NOT NULL default '',
	`content` text default '',
	`content_plain` text default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`user_id` bigint(20) default 0,
	`author` varchar(50) default '',
	`author_email` varchar(100) default '',
	`author_web` varchar(255) default '',
	`author_ip` varchar(100) default '',
	`reply_count` int(25) default 0,
	`view_count` int(25) default 0,
	`locked` tinyint(4) default 0,
	`sticky` tinyint(4) default 0,
	`last_id` bigint(20) NOT NULL default 0,
	`last_author` varchar(50) NOT NULL default '',
	`last_date` datetime NOT NULL default '0000-00-00 00:00:00',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


DROP TABLE IF EXISTS `forums`;

CREATE TABLE `forums` (
	`id` int(25) NOT NULL auto_increment,
	`parent_id` int(25) default 0,
	`display_order` int(16) default 0,
	`title` varchar(255) NOT NULL default '',
	`content` text default '',
	`content_plain` text default '',
	`topic_count` int(25) default 0,
	`reply_count` int(25) default 0,
	`last_id` bigint(20) default '0',
	`last_author` varchar(50) default '',
	`last_date` datetime default '0000-00-00 00:00:00',
	`read_only` tinyint(4) default 0,
	`private` tinyint(4) default 0,
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
	`id` bigint(20) NOT NULL auto_increment,
	`username` varchar(50) NOT NULL default '',
	`password` text NOT NULL default '',
	`password_salt` text NOT NULL default '',
	`display_name` varchar(100) default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`enabled` tinyint(4) default 1,
	`sig` text default '',
	`avatar` text default '',
	`email` varchar(100) default '',
	`web` varchar(255) default '',
	`bio` text default '',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


DROP TABLE IF EXISTS `pm`;

CREATE TABLE `pm` (
	`id` bigint(20) NOT NULL auto_increment,
	`user_id` bigint(20) default 0,
	`sender_id` bigint(20) default 0,
	`title` varchar(255) NOT NULL default '',
	`content` text default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`opened` tinyint(4) default 0,
	`hide_sender` tinyint(4) default 0,
	`hide_reader` tinyint(4) default 0,
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


DROP TABLE IF EXISTS `polls`;

CREATE TABLE `polls` (
	`id` bigint(20) NOT NULL auto_increment,
	`message_id` bigint(20) default 0,
	`question` text NOT NULL default '',
	`multiple` tinyint(4) default 0,
	`option1` varchar(255) default '',
	`option2` varchar(255) default '',
	`option3` varchar(255) default '',
	`option4` varchar(255) default '',
	`option5` varchar(255) default '',
	`option6` varchar(255) default '',
	`option7` varchar(255) default '',
	`option8` varchar(255) default '',
	`option9` varchar(255) default '',
	`option10` varchar(255) default '',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


DROP TABLE IF EXISTS `pollvotes`;

CREATE TABLE `pollvotes` (
	`id` bigint(20) NOT NULL auto_increment,
	`poll_id` bigint(20) NOT NULL,
	`user_id` bigint(20) NOT NULL,
	`options` varchar(20) default '',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


DROP TABLE IF EXISTS `banned`;

CREATE TABLE `banned` (
	`id` bigint(20) NOT NULL auto_increment,
	`ip` varchar(100) default '',
	`username` varchar(50) default '',
	`email` varchar(100) default '',
	`reason` varchar(255) default '',
	`ban_date` datetime NOT NULL default '0000-00-00 00:00:00',
	`expires` datetime default '0000-00-00 00:00:00',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;