Database Updates

As in Part 1, we will begin with the database. This time we’ll take it a step further. As we add new features, we’re going to be making lots of little (and maybe not-so-little) changes to our database. Looking ahead, we’re going to want a single script to create the entire database. And rather than making tons of alter table queries, each time we make a change we’ll wipe the old tables and create new ones in their place. This way we can have a single script to do a complete creation/re-creation of our data structure.

DROP TABLE IF EXISTS `Posts`;
DROP TABLE IF EXISTS `Topics`;

CREATE TABLE `Posts`
(
  postID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  body TEXT NOT NULL,
  poster VARCHAR(50) NOT NULL,
  topicID INT NOT NULL,
  postedDate DATETIME NULL,
  posterIP VARCHAR(15) NOT NULL
);

CREATE TABLE `Topics`
(
  topicID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  title VARCHAR(255) NOT NULL,
  poster VARCHAR(50) NOT NULL,
  postedDate DATETIME NULL,
  posterIP VARCHAR(15) NOT NULL
);

CREATE TRIGGER `Posts_postedDate_init` BEFORE INSERT ON `Posts` FOR EACH ROW SET NEW.`postedDate` = NOW();
CREATE TRIGGER `Topics_postedDate_init` BEFORE INSERT ON `Topics` FOR EACH ROW SET NEW.`postedDate` = NOW();

The big changes, you see, is the addition of the new Topics table, the new field topicID in the Posts table, and the new trigger which will auto-populate the postedDate field in the Topics table. Also at the beginning of the script are DROP TABLE IF EXISTS statements which will clear out any old tables (losing all data). For now we do not care about our data. Later on we may take it into consideration but for now… we have freedom!

Foreign Keys

Notice how we have not created any foreign keys just yet. Technically there should be a foreign key from `Topics`.`topicID` to `Posts`.`topicID`. I am leaving this out intentionally, but with the note that these keys should be created later. During development it is convenient to leave these out as it can cause a lot of headache for features and fields which are likely to change throughout the development process. For the purposes of data integrity, these will be added when the project is nearing a “release”.

Updates

Mirroring the DB changes, our data.php will change accordingly.

<?php
namespace data;

mysql_connect(
	\settings\config\db\SERVER,
	\settings\config\db\USERNAME,
	\settings\config\db\PASSWORD) or die(mysql_error());
mysql_select_db(\settings\config\db\SCHEMA) or die(mysql_error());

class db
{
	/* POSTS */
	function posts_list($topicID)
	{
		$sql = 'SELECT * FROM `Posts` WHERE `topiciD` = %d';
		return q($sql, $topicID);
	}
	function posts_add($body, $poster, $topicID)
	{
		$posterIP = $_SERVER['REMOTE_ADDR'];
		$sql = 'INSERT INTO `Posts` (body, poster, posterIP, topicID) VALUES ("%s","%s","%s", %d)';
		q($sql, $body, $poster, $posterIP, $topicID);
		return mysql_insert_id();
	}

	/* TOPICS */
	function topics_list()
	{
		$sql = 'SELECT * FROM `Topics`';
		return q($sql);
	}
	function topics_single($topicID)
	{
		$sql = 'SELECT * FROM `Topics` WHERE `topicID` = %d';
		return q($sql, $topicID);
	}
	function topics_add($title, $poster)
	{
		$posterIP = $_SERVER['REMOTE_ADDR'];
		$sql = 'INSERT INTO `Topics` (title, poster, posterIP) VALUES ("%s","%s","%s")';
		q($sql, $title, $poster, $posterIP);
		return mysql_insert_id();
	}
}
?>

New Pages

Our original guestbook had a single page. Keeping track of postbacks, includes, and so forth was a simple matter. Our new feature neccessitates a handful of new pages.

• index.php – Used to be a list of posts, now will be a list of topics
• newtopic.php – Where we can create new topics
• topic.php – Where we can view all the posts in a topic and post replies.

This requires a bit of refactoring. index.php used to hold all of our front-facing code. It was simply a list of messages. But with this new topics feature, each topic has a list of messages, and the list of topics is the logical front page. So we will rename index.php to topic.php, and create a new index.php which will display that list with links to topic.php. Additionally, we’ll need a form to create new topics. To be fancy, we’ll place that on a third page, newtopic.php.

<?php
date_default_timezone_set('UTC');
require_once 'scripts/settings/config.php';
require_once 'scripts/data/db.php';
require_once 'scripts/core/helpers/sanitationHelper.php';
require_once 'scripts/core/helpers/formHelper.php';
require_once 'scripts/core/util/validator.php';
require_once 'scripts/ui/validation.php';
$result = \data\db::topics_list();
$numResults = mysql_num_rows($result);

?>
<!doctype html>
<html lang="en">
<head>
	<title>Simple Forum - Topics</title>
</head>
<body>
<h1>A Simple Forum</h1>
<p><a href="newtopic.php">Post New Topic</a></p>
<?php
if($numResults == 0)
{
	echo '<p>No topics yet.</p>';
}
else
{
	echo '<table>';
	for($i=0;$i<$numResults;$i++)
	{
		$r = mysql_fetch_assoc($result);
		echo '<tr>';
		echo '<td><a href="',settings\config\url\ROOT,'topic.php?t=',h($r['topicID']),'">',h($r['title']),'</a></td>';
		echo '<td>Posted by ',h($r['poster']),'</td>';
		echo '<td>Posted ',h($r['postedDate']),'</td>';
		echo '</tr>';
	}
	echo '</table>';
}
?>
</body>
</html>

Notice how similar this is to our original index page which contained the list of topics. This page simply echoes off a table of topics with links to each one. Additionally there is a link to the newtopic page.

Next is our second new page, which will allow us to create topics. Again, notice how similar it is to our original index page. It contains a simple form with a few validation rules.

<?php
date_default_timezone_set('UTC');
require_once 'scripts/settings/config.php';
require_once 'scripts/data/db.php';
require_once 'scripts/core/helpers/sanitationHelper.php';
require_once 'scripts/core/helpers/formHelper.php';
require_once 'scripts/core/util/validator.php';
require_once 'scripts/ui/validation.php';

$validate = new \core\util\Validator;

/* Action Handler */
if(isset($_GET['a']))
{
	$action = get('a');
	if($action == 'newtopic')
	{
		$title = post('title');
		$poster = post('poster');
		$body = post('body');

		$validate->minLength('title', $title, 4, 'You must provide a title at least 4 characters long.');
		$validate->maxLength('title', $title, 200, 'Please keep the title under 200 characters.');

		$validate->required('poster', $poster, 'You must provide a name');
		$validate->maxLength('poster', $poster, 30, 'Your name cannot be over 30 characters long');

		$validate->minLength('body', $body, 10, 'You must provide a message at least 10 characters long.');
		$validate->maxLength('body', $body, 500, 'Please keep the message under 500 characters.');

		//do our action
		if(!$validate->hasErrors())
		{
			$topicID = \data\db::topics_add($title, $poster);
			\data\db::posts_add($body, $poster, $topicID);

			header('Location: index.php');
			exit();
		}
	}
}
?>
<!doctype html>
<html lang="en">
<head>
	<title>Simple Forum - New Topic</title>
</head>
<body>
	<h1>New Topic</h1>
	<form action="newtopic.php?a=newtopic" method="post">
		<p>
			<label for="poster">Poster: </label><br />
			<input id="poster" name="poster" type="text" value="<?php echo post('poster') ?>" />
			<?php ui\validation\FieldErrors1($validate->getErrors('poster')); ?>
		</p>
		<p>
			<label for="title">Title: </label><br />
			<input id="title" name="title" type="text" value="<?php echo post('title') ?>" />
			<?php ui\validation\FieldErrors1($validate->getErrors('title')); ?>
		</p>
		<p>
			<label for="body">Message: </label><br />
			<textarea id="body" name="body" cols="30" rows="5"><?php echo post('body') ?></textarea><?php ui\validation\FieldErrors1($validate->getErrors('body')); ?>
		</p>
		<p>
			<button type="submit">Submit</button>
		</p>
	</form>
</body>
</html>

Finally, our revised list of posts. Now we cannot simply list all posts, since posts are divided into topics. This page requires a “t” url parameter to work right. Besides that, this page will work just like the old one. Notice line #12-26 where we verify we have a topicID. If no topicID is provided, or no rows are return from the DB, then we redirect the user back to the index page. For now this is sufficient. In the future we will want to give the user an error message or a 404 to let them know what went wrong.

On lines #61 and 64, we now display the title of the topic in the titlebar and as a heading.

Also notice in line #40 where we now add the topicID to the form so that our message ends up in the right place.

<?php
date_default_timezone_set('UTC');
require_once 'scripts/settings/config.php';
require_once 'scripts/data/db.php';
require_once 'scripts/core/helpers/sanitationHelper.php';
require_once 'scripts/core/helpers/formHelper.php';
require_once 'scripts/core/util/validator.php';
require_once 'scripts/ui/validation.php';

$action = get('a');
//must have topic set
$topicID = get('t');
if($topicID == NULL)
{
	//no topic set; return to topic list
	header('Location: index.php');
	exit();
}
$topics = \data\db::topics_single($topicID);

if(mysql_num_rows($topics) == 0)
{
	//topic does not exist
	header('Location: index.php');
	exit();
}
$topic = hr($topics);
$validate = new \core\util\Validator;

/* Action Handler */
if($action != NULL)
{
	if($action == 'newpost')
	{
		$body = post('body');
		$poster = post('poster');

		$validate->minLength('body', $body, 10, 'You must provide a message at least 10 characters long.');
		$validate->maxLength('body', $body, 500, 'Please keep the message under 500 characters.');

		$validate->required('poster', $poster, 'You must provide a name');
		$validate->maxLength('poster', $poster, 30, 'Your name cannot be over 30 characters long');

		//do our action
		if(!$validate->hasErrors())
		{
			\data\db::posts_add($body, $poster, $topic['topicID']);
			header('Location: topic.php?t='.$topicID);
			exit();
		}
	}
}

$result = \data\db::posts_list($topicID);
$numResults = mysql_num_rows($result);

?>
<!doctype html>
<html lang="en">
<head>
	<title>Simple Forum - <?php echo $topic['title'] ?></title>
</head>
<body>
<h1><?php echo $topic['title'] ?></h1>
<?php
if($numResults == 0)
{
	echo 'No messages';
}
else
{
	for($i=0;$i<$numResults;$i++)
	{
		$r = mysql_fetch_assoc($result);
		echo '<b>',h($r['poster']),'</b> at ',h($r['postedDate']);
		echo '<p>',h($r['body']),'</p><hr />';
	}
}
?>
	<form action="/forum/topic.php?a=newpost&t=<?php echo $topicID ?>" method="post">
		<p>
			<label for="poster">Poster: </label><br />
			<input id="poster" name="poster" type="text" value="<?php echo post('poster') ?>" /><?php ui\validation\FieldErrors1($validate->getErrors('poster')); ?>
		</p>
		<p>
			<label for="body">Message: </label><br />
			<textarea id="body" name="body" cols="30" rows="5"><?php echo post('body') ?></textarea><?php ui\validation\FieldErrors1($validate->getErrors('body')); ?>
		</p>
		<p>
			<button type="submit">Submit</button>
		</p>
	</form>
</body>
</html>

Wrap-Up

Done! Our Forum is now one step closer to actually being a forum, but there still so far to go.

Get the the complete code here:
forum-0.1.2-topics.zip (6.15 KB)

399 lines of code
10 files

Next: Having moved from a single page to multiple pages, it is now apparent we need better organization to keep the pages simple and powerful. Even the three incredibly simple pages we have are becoming lengthy blobs. To combat this as our forum grows, I will show you how to set up a simple way separate our site into a sort of MVC (Model-View-Controller) pattern.

The form we created lacked any validation on the back-end. Users could put anything into the form and it would be accepted by the system, possibly displaying a user un-friendly error to them or much worse. Here are some of the fun things that users could do:

1. Post HTML and JavaScript which is displayed the page, effectively allowing them to control the page.
   
2. Perform SQL injection attacks, making unauthorized queries on the database.
   
3. Post empty fields, making blank entries.
   
4. Post fields too long (triggering an error).
   
5. Post repeatedly as fast as they like, rapidly spamming.
   

The most serious of these are #1 and #2. Either of these can cause severe problems on your site and in this day and age are downright unacceptable.

For these problems we will create “helper” functions. For organizational purposes, these functions will reside within the “core/helpers” folder.

We’ll start with the most important.

In and Out Sanitation

In Sanitation

To prevent SQL injection, all we have to do is run input parameters through PHP’s mysql_real_escape_string() function. But that is a mouthful, and we may want to add onto it later. So let’s create a short-hand alias for this.

require_once('db.php');
function mres($value)
{
	return mysql_real_escape_string($value);
}

Can’t be simpler.

Now this gets a little complicated. Using this function we can create a single-point replacement for the mysql_query function which will incorporate our mres() function into all queries.

require_once('sanitation.php');
function query($query)
{
	$result = mysql_query($query) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
	return $result;
}
function q()
{
    $args = func_get_args();
    $var  = array_shift($args);
	array_walk($args,'mres');
    return query(vsprintf($var, $args));
}

The first function, query(), is a replacement for mysql_query which has error-handling built into it.

The second function, q(), was inspired from this StackOverflow question and it applies the principle “Write Once, Run Anywhere”. It allow us to replace the mysql_query() and sprintf functions when dealing with the db. Queries with multiple inputs (updates/inserts) will be sanitized. Ones without will be handled normally. Beautiful and simple. Now a single letter can handle so much.

Our db class now looks like this:

class db{
	/* POSTS */
	function posts_list()
	{
		$sql = 'SELECT * FROM `Posts`';
		return q($sql);
	}
	function posts_add($body, $poster)
	{
		$posterIP = $_SERVER['REMOTE_ADDR'];
		$sql = 'INSERT INTO `Posts` (body, poster, posterIP) VALUES ("%s","%s","%s")';
		return q($sql, $body, $poster, $posterIP);
	}
}

It’s actually shorter while performing more actions!

Out Sanitation

In order to deal with #3 all user-facing data from the DB should be run through PHP’s htmlentities() function or htmlspecialchars(). It is a matter of preference whether or not to do this before it enters the database or before it is displayed on the page. For this project I will use the before-it-is-displayed-on-the-page route.

It is important to know that running these functions with their default parameters is not always enough. And so for this we will create an alias of sorts. A function which can be used with the parameters of htmlentities() filled in, and maybe a little shorter.

Some will fight me on this, but for the most commonly used functions in PHP, I like to award single-character function names. In this case we will use h(), (short for htmlentities).

htmlentities has a few parameters we must consider.

$flags – Defaults to ENT_COMPAT which is vulnerable to single-quote JavaScript insertion. We use ENT_QUOTES instead.

$charset – Defaults to ISO-8859-1 which for our purposes is fine. The default charset for MySQL is Latin-1 (a nicer name for ISO-8859-1), so unless you changed it while creating your table, this will work beautifully. Without getting into a huge discussion about character sets, localization, and language compatibility… and instead to but it briefly: MySQL’s standard Latin-1 charset works for most latin (read: western) languages. MySQL support unicode however for simplicity’s sake, we will use the standard Latin-1. If you plan to take your forum internationally, use utf8_general_ci or utf8_unicode_ci. Wow, that turned out to be not-so-brief.

$double_encode – Defaults to true. Depending on what you’re doing, you may want to set this to false. If you are worried about your form being re-submitted with already-encoded data, use false. If not, use true. In the case of true, submitting & once will show as &. Submitting that will return &, and so forth. Whereas if the setting is true, it will only ever return &. & will return simply &. For our sake, we’ll use the default, true.

For our new outward-facing html sanitation, we’ll create a new file called “sanitation.php” in /scripts/core/helpers/.

<?php
function h($value)
{
	$value = htmlentities($value,ENT_QUOTES,'ISO-8859-1', true);
	return str_replace(":", "&#58;", $value);
}
function hr($mysql_row)
{
	return array_walk(r($mysql_row),'h');
}
?>

Now h() is our “out” sanitization function. It run output through htmlentities() first, then encode colons, which are not included in htmlentities, which could theoretically let some javascript through within attributes.

The second function, hr() will sanitize an entire row of values from the db. Again, now we have a couple letters performing a powerful actions. This is exactly what how we want to design our programs. A single letter performing perhaps dozens of actions for us. The easier we make our life the better.

Input Validation

Our last three issues fall under the umbrella of checking input for expected types of values. For example, if are expecting a number from a user, give them a nice friendly error message if they give any letters. If you expect 2-20 letter of input, give them a nice error if they give less than 2 or more than 20 characters. The trick in this is linking problems with friendly error messages.

Now bear with me, as we’ll be creating a class called validator and several helper functions to accompany it. We’ll then use these functions to error-check our user-input and then display friendly errors back to our user describing what is wrong.

<?php
namespace core\util;
class Validator
{
	private $errors = array();

	/* VALIDATION CHECKS */
	public function required($field, $value, $errorMessage)
	{
		if(empty($value))
		{
			$this->errors[$field][] = $errorMessage;
			return false;
		}
		return true;
	}
	function minLength($field, $value, $length, $errorMessage)
	{
		if(strlen($value)<$length)
		{
			$this->errors[$field][] = sprintf($errorMessage,$length,strlen($field));
			return false;
		}
		return true;
	}
	public function maxLength($field, $value, $length, $errorMessage)
	{
		if(strlen($value)>$length)
		{
			$this->errors[$field][] = sprintf($errorMessage,$length,strlen($field));
			return false;
		}
		return true;
	}

	/* UTIL */
	public function hasErrors()
	{
		if(func_num_args()==0)
			return (count($this->errors, COUNT_RECURSIVE) > 0);
		return isset($this->errors[func_get_arg(0)]);
	}
	public function getErrors()
	{
		if(func_num_args()==0)
			return $this->errors;
		if(!isset($this->errors[func_get_arg(0)]))
			return array();
		return $this->errors[func_get_arg(0)];
	}
	public function numErrors()
	{
		if(func_num_args()==0)
			return count($this->errors, COUNT_RECURSIVE)-count($this->errors, COUNT_NORMAL);
		if(!isset($this->errors[func_get_arg(0)]))
			return 0;
		return count($this->errors[func_get_arg(0)]);
	}
}

?>

This new class contains 3 functions used to validate inputs… that they must not be empty, that they have at least so many characters, that they must not have too many characters. If it does not pass the test, an error message is stored in an associative array binding it with the field name.

This class has another 3 functions used for checking if any errors have been logged, how many errors, retrieving those errors. I use func_num_args() and func_get_arg() to overload these functions to work when looking for all errors or just errors for a specific field.

Next, knowing that I’ll be working with $_GET[] and $_POST[] values a lot more in the future, I started a file for helpers for this.

<?php
function get($name){
	if(isset($_GET[$name]))
		return $_GET[$name];
	return NULL;
}
function post($name){
	if(isset($_POST[$name]))
		return $_POST[$name];
	return NULL;
}
?>

Now we’ll add in some validation rules to start of our index file. We also have a bunch more includes to require. Soon we may break these off into another file, but for now we’ll deal.

<?php
date_default_timezone_set('UTC');
require_once 'scripts/settings/config.php';
require_once 'scripts/data/db.php';
require_once 'scripts/core/helpers/sanitationHelper.php';
require_once 'scripts/core/helpers/formHelper.php';
require_once 'scripts/core/util/validator.php';
require_once 'scripts/ui/validation.php';

$validate = new \core\util\Validator;

/* Action Handler */
if(isset($_GET['a']))
{
	$action = $_GET['a'];
	if($action == 'newpost')
	{
		$body = post('body');
		$poster = post('poster');

		$validate->minLength('body', $body, 10, 'You must provide a message at least 10 characters long.');
		$validate->maxLength('body', $body, 500, 'Please keep the message under 500 characters.');

		$validate->required('poster', $poster, 'You must provide a name');
		$validate->maxLength('poster', $poster, 30, 'Your name cannot be over 30 characters long');

		//do our action
		if(!$validate->hasErrors())
		{
			echo print_r($validate->getErrors());
			\data\db::posts_add($body, $poster);
			header('Location: index.php');
			exit();
		}
	}
}

Notice how easy it is to add errors now. It’s basically just a list of definitions.

If you try to submit invald values now, it will not add them to the Guestbook… but neither will it alert the user. Let’s do that now.

Again, knowing we’ll likely be doing this repeatedly now, and using it a great deal more in the future, I’m going to create a UI file and namespace to help us out. But just a single UI file would get bloated as it is a very broad area. Our needs fall under UI/Validation.

namespace ui\validation;
function FieldErrors1($errors)
{
	if(count($errors)>0)
	{
		echo '<ul class="errors">';
		foreach($errors as $error)
		{
			echo '<li>',$error,'</li>';
		}
		echo '</ul>';
	}
}

All the UI namespace and files will do is handle formatting. Every segment of the webpage is repeated and self-contained should be placed there to allow use to sort of “widgetize” our site… make many pieces of it inter-changable and easy usable across multiple pages. Soon we will add more functions to this area, but for now FieldErrors1 is all.

Now we can update the index.php

<!doctype html>
<html lang="en">
<head>
	<title>GuestBook Test</title>
</head>
<body>
<?php
if($numResults == 0)
{
	echo 'No messages';
}
else
{
	for($i=0;$i<$numResults;$i++)
	{
		$r = mysql_fetch_assoc($result);
		echo '<b>',h($r['poster']),'</b> at ',h($r['postedDate']);
		echo '<p>',h($r['body']),'</p><hr />';
	}
}
?>
	<form action="index.php?a=newpost" method="post">
		<p>
			<label for="poster">Poster: </label><br />
			<input id="poster" name="poster" type="text" value="<?php echo post('poster') ?>" /><?php ui\validation\FieldErrors1($validate->getErrors('poster')); ?>
		</p>
		<p>
			<label for="body">Message: </label><br />
			<textarea id="body" name="body" cols="30" rows="5"><?php echo post('body') ?></textarea><?php ui\validation\FieldErrors1($validate->getErrors('body')); ?>
		</p>
		<p>
			<button type="submit">Submit</button>
		</p>
	</form>
</body>
</html>

Done! Now test it out! See how it gives pretty errors whenever invalid input is given, and submits a message when the input looks good.

Wrap-Up

It doesn’t look like much. We haven’t even created a stylesheet yet. But that will come in due time. There is little point in creating one at this stage as we still have tons of features to add which will drastically change the page. So for now we’ll stick to functionality over style.

You may have noticed we left out problem/exploit #5–spamming. Don’t worry, I haven’t forgotten about it. However, we’ll leave that for another time as its not really critical just yet.

Get the complete code here:
forum-0.1.1-sanivali.zip (5.48 KB)

243 lines of code
8 files

Next: In Part 3 of Creating a PHP Forum: A Guided Tour, we will add in “threads”… separate areas or conversations to post in, adding just a little more complexity to the program/website.

I’ll be guiding you through my process as each post will represent a separate component that builds on those before it. This first post will be a simple guestbook not requiring any authentication or threading. It is the first and most fundamental piece of a forum.

To get started you will need a PHP and MySQL setup. I am using a WAMP setup on my home machine, which is ideal for development as it is the shortest path between development and testing, allowing you to test quickly and easily and facilitating debugging. Setting such an environment is beyond the scope of this article, but I trust you know how to use google if you do not have a setup available.

Guestbook

By the time this post is over, we will have a working guestbook, where visitors will be able to leave a post consisting of their name and a message. We will also log their IP address and date of posting to make it easy to see if the same person has left multiple messages.

Since we already have a clear idea of this feature, I will begin with the database back-end. It consists of a single table which will containing a couple fields.

Setting up the database

Before we can create the table, we need to set up a database and user for the database. For this I am using MySQL Administrator. Never use the database root/admin user as the user for development/deployment.

1. Create a new Schema

I’ll name mine “sd_forum”.

2. Create a new user.

I’ll name mine “sd_forum_user”.

3. Give the user privileges to the database.

I’ll give the user full privileges except for “grant” for now. If this were to be deployed, I would remove any unnecessary privileges, such as the ability to drop tables.

Create the table

Now I will switch to my new “sd_forum_user” user.

“Users” -> “userID”
“Topics” -> “topicID”
“CrazyUkranians” -> “crazyUkranianID”

The table we are creating is named “Posts”.

CREATE TABLE Posts
(
postID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
body TEXT NOT NULL,
poster VARCHAR(50) NOT NULL,
postedDate DATETIME NULL,
posterIP VARCHAR(15) NOT NULL
);

Now, the reason we allow postedDate to be null is to be fancy, we’ll make the default value of postedDate to be NOW(). An inconvenient limitation of MySQL is its inability to set a default for DateTime fields to NOW(). Instead we’ll throw in a trigger to do this for us.

CREATE TRIGGER Posted_postedDate_init BEFORE INSERT ON Posts FOR EACH ROW SET NEW.PostedDate = NOW();

Now we can try inserting a value into our brand-spankin’-new table.

INSERT INTO Posts (body, poster, posterIP) VALUES ('Hello world!', 'Myself', '127.0.0.1');

And then see what we have in our table.

SELECT * FROM Posts;

There’s our first row, pre-populated with an ID and a postedDate! Well done.

The PHP

Now down to Brass Tacks. Lettus (I’ve always wanted to use that horrible pun… why is it funny, again?) start with a relatively simple, but well defined structure.

This entire project will be help in a folder called “forum”, away from the http-server root. Within that I’ll create a index.php file, and a /scripts folder. Within that, create a data and a settings folder. The settings folder will hold another php file, config.php. The data folder will hold db.php.

We’ll begin working on the config file. Here we’ll store all of our settings in nice, clearly-define namespaces (thank you PHP 5.3.5).

config.php

For convention’s sake, all constants are in all-caps. I use namespaces and classes for purely organizational purposes. You could easily this and almost anything else in PHP without them. PHP is truely a scripting language and as a result OOP principals are in many ways optional, however they are still useful.

For now our config files simply holds values for our MySQL connection.

<?php
namespace settings\config\db;
const SERVER = 'localhost';
const SCHEMA = 'sd_forum';
const USERNAME = 'sd_forum_user';
const PASSWORD = '**your_password**';
?>

db.php

The namespaces, if you haven’t figured it out, are the same as the folder path of the file.

First we’ll create a MySQL connection. We are creating a connection regardless of whether or not we actually use it. Every page load will make a connection if this file is included. For our purposes that is fine as pretty much every page will use the DB.

Start with two simple functions; one for insertion and one for retrieval of our posts. There is little data validation or sanitation here outside of using sprintf. That will come in a later module.

<?php
namespace data;

mysql_connect(
  \settings\config\db\SERVER,
  \settings\config\db\USERNAME,
  \settings\config\db\PASSWORD) or die(mysql_error());
mysql_select_db(\settings\config\db\SCHEMA) or die(mysql_error());

class db{
  /* POSTS */
  function posts_list(){
    $sql = 'SELECT * FROM `Posts`';
    $result = mysql_query($sql) or die(mysql_error() . '<br /><b>SQL:</b> ' . $sql);
    return $result;
  }
  function posts_add($body, $poster){
    $posterIP = $_SERVER['REMOTE_ADDR'];
    $sql = 'INSERT INTO `Posts` (body, poster, posterIP) VALUES ("%s","%s","%s")';
    $sql = sprintf($sql, $body, $poster, $posterIP);
    mysql_query($sql) or die(mysql_error() . '<br /><b>SQL:</b> ' . $sql);
  }
}
?>

index.php

Finally, we’ll set up our index.php to list out all the posts in our database.

<?php
  date_default_timezone_set('UTC');
  require 'scripts/settings/config.php';
  require 'scripts/data/db.php';
  $result = \data\db::posts_list();
  $numResults = mysql_num_rows($result);
?>
<!doctype html>
<html lang="en">
<head>
	<title>GuestBook Test</title>
</head>
<body>
<?php
if($numResults == 0)
{
  echo 'No messages';
}
else
{
  for($i=0;$i&lt;$numResults;$i++)
  {
    $r = mysql_fetch_assoc($result);
    echo '<b>',$r['poster'],'</b> at ',$r['postedDate'];
    echo '<p>',$r['body'],'</p><hr />';
  }
}
?>
</body>
</html>

If you followed the instructions so far, and inserted an entry into the table, you’ll see that post now. If not, you’ll see “No Messages”. We’ll always test retrieval first because it’s almost always easier and it allows it to immediately see the results when we make “insert” functionality.

Creating a Form

Now to make our own posts on this page, we need two things: a form, for the user, and a script to handle the user’s input.

Since our table is simple, so is our form.

	<form action="index.php?a=newpost" method="post">
		<p>
			<label for="poster">Poster: </label><br />
			<input id="poster" name="poster" type="text" />
		</p>
		<p>
			<label for="body">Message: </label><br />
			<textarea id="body" name="body" cols="30" rows="5"></textarea>
		</p>
		<p>
			<button type="submit">Submit</button>
		</p>
	</form>

Only two fields and a submit button. You may notice that the form submits back to the same page, with a “?a=newpost” parameter. We will be using the get value “a” to let our program know what is expected of it. This way any page can have multiple actions without getting confusing.

Next we’ll write a script to handle the data the user posts to our page. The following script will appear on the index.php after the requires and before it calls posts_list().

/* Action Handler */
if(isset($_GET['a']))
{
  $action = $_GET['a'];
  if($action == 'newpost')
  {
    $body = $_POST['body'];
    $poster = $_POST['poster'];
    \data\db::posts_add($body, $poster);
    header('Location: index.php');
    exit();
  }
}

If action (“a”) is not set, our actions are skipped over. When the action is “newpost” our insert script is performed and then the page redirects to itself to avoid any messy browser refresh re-posts. There is no validation here, so the user could put almost anything into those fields and they would come visible to everyone. We will fix that in the future, but for now this is plenty.

We’re done! Try it out. Create some posts and see as they are added to the list of posts displayed on the page. Step 1 complete!

Get the completed code here: forum-0.1.0-guestbook.zip (1.77 KB)

92 lines of code
3 files

Next: Part 2 will show you all the security holes our guestbook has and how we can seal and spackle those holes.

Creating a custom Theme/Skin for YAF

Skinning YetAnotherForum is kind of self evident. Just copy an existing theme folder and XML file from the themes folder. All there is to it is a theme.css file in the folder (and a bunch of images) and the settings in the XML file. Done.

Modifying the Layout of YAF

For as easy as changing the theme of YAF is, changing the actual layout is a time-consuming and involved process. And there are a few things which are not as evident from quick google searches or examining the code. So take a lesson from me and save yourself hours of time.

1. You cannot easily remove elements by commenting out the C#

Almost any main <YAF:Element> you try to comment out will trigger errors when you try to compile due to dependent blocks elsewhere. And the source of the errors may not always be apparent. You may be able to fix those errors, but chances are it will not be worth your effort. This is a pain because depending on what you want to do you may want to eliminate some elements from showing. This may be typical for ASP.NET, but coming from PHP it prevented a learning curve. Many things you may want to remove can be removed by changing BoardSettings. (See next tip)

2. YafContext.Current.BoardSettings are stored in the database

Once you start investigating the depths of the page and class files of YAF, you will eventually start noticing how many part of the board are in hide/show if blocks based on a value of YafContext.Current.BoardSettings.[something]. Other parts of the forum look to these settings for things.

I can’t tell you how many searches I made trying to find what file BoardSettings are stored in. I still don’t know if there is a file with defaults. I didn’t find it despite much searching.

These settings can be changed in the database under the Registry table. Easy peasy? I got thrown off because many of the settings are not in the table. If they are not in the table, you will have to insert a row. The “value” field is a string, so numbers and boolean values are stored just as their string equivalent. i.e. a true value can be stored as the string “true”.

3. The logical structure of the code is as follows

This may seem elementary, but I’m new to ASP.NET and working with modifying larger(ish) packages. It would have been helpful to me to have a diagram or even just a quick explanation of the layout of what calls what.
As you can see, any single page may reference a dozen or two files. This can be a serious pain, as those .cs files in the YAF.Controls.dll are in my experience, very tedious to edit. I didn’t realize I neede the SRC version until I realized I could not access TopicLine.cs, which is necessary to change how the single line each topic is displayed as when you look at a single forum.

And to be clear: There are many more pages, controls, and elements within Controls.dll that aren’t shown here. Also, there may be some exceptions to the hierarchy shown here.

4. CSS Class names do not always describe what they refer to

With class names like .header1, .header1Title, and .rightItem, it is not always easy to tell what element these CSS classes are referring to. This can be particularly challenging when you want to edit what they are referred to and you can’t locate which source file the element it is referring to spawns in. This is more of a heads up than an actual tip.

Enjoy, and Contribute

That’s all the tips I have for you today about YetAnotherForum. In my one-day’s experience with them, I found it to be a little frustrating, however now that I am more familiar with it I feel more confident in the likelihood I would be able to switch things up more rapidly. I hope these tips help other newbies trying to do what I was doing.

If you have any tips/tricks of your own you’d like to contribute, please feel free to share and comment.

The full MSDN article is here. I will summarize the multi-page verbose article with the two important points that you need to make the connection.

1. Modifying your web.config

Replace <connectionStrings /> with one of the following <add />’s:


<connectionStrings>
  <add name="MyDbConn1"
       connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/>
  <add name="MyDbConn2"
      connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;"/>
</connectionStrings>
</pre>

And obviously, change the MyServer to your server’s name (i.e. localhost or 172.0.0.1 or whatever address.), change MyDb to the name of the database you want to use in the server. Simple!

2. Open the connection in your ASP script

Use the following code wherever in your script.

At the top of your page, you need to make the include to the framework API:

<%@ Import Namespace="System.Data.SqlClient" %>

And then you’re free to use the following to connect when you need to. This is in C#.

string connStr = ConfigurationManager.ConnectionStrings["MyDbConn1"].ToString();
SqlConnection conn = new SqlConnection(connStr);

And walah! You should be connected.

More short articles will follow further explaining performing queries and outputting data.

A few days ago I launch, for the first time, ZombieAttackPlans.com. The site is for users to submit their plans for surviving the zombie apocalypse. If you have some idea of what you want to do, come post it! If you don’t, come and read others’ plans. Leave feedback, rate plans, vote in the Poll of the Week, and read the Tip of the Day (updated daily, as per the name).

Please check it out and leave me some feedback on the site. I want to know how I can make it better. What do you like? And more importantly, what don’t you like?

What doesn’t work

1. strip_tags

Strip tags is bad for a few reasons. First, it ends up removing content, and sometimes more than you would think. That is not the goal of [textfield] sanitation. Sanitation is meant to prevent mischief while preserving the integrity of the user’s message. Secondly, and more importantly:

This function does not modify any attributes on the tags that you allow using allowable_tags, including the style and onmouseover attributes that a mischievous user may abuse when posting text that will be shown to other users.

That is bad for you, bad for me.

2. preg_replace (or any other regular expression function)

Regular expression is great for some things, but trying to create regex algorithms that will accurately do what you want here is not likely, and even if it does, it will be overly-complicated, time consuming, and not quickly modifiable  if you want to allow more tags. Basically, it’s overkill.

So what is a good way to let certain HTML tags through?

The Right Way

The right way is simple and maintains the integrity of the original input by first making a clean sweep, and then going back and choosing what to let through. Here is the code.

function sanitize_out($input){
  htmlentities($input);
  $c_p_open=0;
  $c_p_close=0;
  $c_b_open=0;
  $c_b_close=0;
  $input = str_replace('&lt;br&gt;','<br />',$input);
  $input = str_replace('&lt;hr&gt;','<hr />',$input);
  $input = str_replace('&lt;p&gt;','<p>',$input,&$c_p_open);
  $input = str_replace('&lt;/p&gt;','</p>',$input,&$c_p_close);
  $input = str_replace('&lt;b&gt;','<b>',$input,&$c_b_open);
  $input = str_replace('&lt;/b&gt;','</b>',$input,&$c_b_close);
  while($c_p_open > $c_p_close){
    $input .= '</p>';
    ++$c_p_close;
  }
  while($c_b_open > $c_b_close){
    $input .= '</b>';
    ++$c_b_close;
  }
  return $input;
}

Now the code here could be cleaner, but it gets the job done.

What is it doing?

1. Convert all brackets and other XML-important characters to their corresponding entities with htmlentities().
2. Back-convert specific entities into their HTML counterparts. In this case; <p> </p> <b> </b> <br> and <hr>.
3. Closes all hanging tags, so that there will be a </b> for every <b> and a </p> for every <p>.

Next time you want to do this type of input formatting, do it this way, rather than using overly-complicated eregs or some other expression.

that's cool – Original input
that\'s cool – First sensitization (single apostrophe escaped)
that\\\'s cool – Second sanitization (single apostrophe and backslash both escaped)

It only gets worse from here.

Doing input sensitization on a per-line basis is sloppy and inefficient. It is asking for you to slip up and forget to sanitize. After all, you are only human. So why not save yourself the trouble and do a universal sensitization of all user input at the beginning of your code? Use this follow PHP code at the very beginning of your script to save yourself a lot of trouble.

$_GET = sanitize($_GET);
$_POST = sanitize($_POST);
$_COOKIE = sanitize($_COOKIE);
$_REQUEST = sanitize($_REQUEST);

function sanitize($input){
  if(is_array($input)){
    foreach($input as $key => $value)
     $input[$key]=sanitize($value);
    return $input;
  }
  else
    return mysql_real_escape_string(stripslashes(trim($input)));
}

First, this script automatically trims your input, preventing usernames with spaces before or after, for example ‘Admin ‘, which to the user would look identical to ‘Admin’.

Second, it strips any backslashes out from previous sanitizations (plus typically you don’t want backslashes in input)

Third, it escapes any sensative characters (‘, “, and \n, for example).

Essentially it leaves you with ALL input safely cleared of any possibility of SQL injection.

Learn how to do this in two minutes following my near-light speed tutorial.

Breaking Out Your Grid

To get started, take any image with transparency. GIFs are a poor choice for this, because while they have transparency it is either on or off. You want to use PNG-24 images with alpha transparency.

Choose an image. In most cases, it’s not worth it to make your own since what you want has a great chance of already being made and free. Check out some of these posts from Smashing Magazine (I’m a huge fan) to take an eye to free icon and image sets made with developers like you in mind: 50 Free High-Quality Icon Sets and 35 (Really) Incredible Free Icon Sets

For this demonstration, I’ve selected this banana image from KlukeArt.

Position Relative

Now, it’s a pretty spectacular image by itself, but it’s within the lines of this post and doesn’t “pop” out of the page.

position: relative;
left: -150px;

With these two lines of code, the banana now pops over the border to the left, having moved 150 pixels left from where it was supposed to lay… unfortunately text does not wrap around it, so it looks sort of rubbish.

Next let’s see what happens when you float it to the left…

position: relative;
left: -150px;
float: left;

This is better… the text will wrap, but now the issue is that it still pushes the text out to form a margin where the image used to be, not where it is now. We will remedy this by adjusting the margins manually to match the left-shift.

position: relative;
left: -150px;
float: left;
margin-right: -140px;

Ahh! Much better! Now you have your image shifted over, breaking the linearity of your page and instantly making it more interesting and less boxy!

Position Absolute

We still have one more property at our disposal however. Up until now we have been using position: relative;. Another method is using position: absolute;. Absolute position allows you to position your element wherever you want within it’s parent box. It’s parent box will be either the element it is in with position:relative, or the entire page window itself.

For example:

<div class="relative">
  <img class="absolute" />
</div>

.relative{
  position: relative;
  background-color: #f99;
  width: 400px;
  height: 300px;
}
.absolute{
 position: absolute;
 bottom: -100px;
 right: 20px;

Pretty nifty. Oh no! There is a banana is on top of my text! Oh no! There is a banana is on top of my text! Oh no! There is a banana is on top of my text! Oh no! There is a banana is on top of my text! Oh no! There is a banana is on top of my text! Oh no! There is a banana is on top of my text!

The down side to absolute positioning is it is impossible to margin text around it effectively. So ideally you want to use it in areas where you don’t need to do so.

Comment!

I hope you enjoyed this quick tutorial. This technique works in Firefox, Chrome, IE 7+, and Opera… and probably more.

Input validation has long been a highly debated field. No one disputes some sort of validation is appropriate, but the question how much is subject of great debate. And more: server-side only enough, or are modern websites expected to have client-side too? Google “email validation,” or more specifically, “php email validation” and you’ll find hundreds of scripts all of which go about it different ways.

Programmers have spent years of man-hours working on the same sets of problems when it comes to validation. Username, password, email address, name, phone number, and so on. These fields are common in thousands of registration pages. Most have different ways of handling it. I will admit I’ve reinvented the wheel a few times. Perhaps this is because there is no definitive set of functions cross-language cross-platform that we’ve all agreed upon as the way to validate.

7 Tips for Validation

1. Validate to help your users

On the surface, the reason for validation is to make sure it doesn’t screw up your database, isn’t it? Wrong! That should just be a by-product. The real goal is to help your users—That is the entire goal of your site isn’t it? Users hate being wrong and they hate going back to fix their mistakes. The less frequently you can make them wrong and have your system work the better. The main goal of validation is to make them realize when they accidentally provide wrong information, as in accidental typos, not information your database doesn’t like.

2. Avoid over-validation

Over-validation is when you take over-examine the field you are validating, thinking strict validation will somehow help you or your user. The fact is people are not exact, and won’t like it if you try to force them to be. Try to compensate by other means (see tip#4). Furthermore, no amount of validation will prevent people from giving false names and e-mail addresses. If the reason for your rigorous screening for e-mail addresses is that, then you should reconsider.

“No amount of validation will prevent people from giving false names and e-mail addresses.”

Example

E-mail addresses are notoriously annoying for validation. What is the right way to validate? Read the follow two ways of validation (in plain English) and tell me which you think is better:

Method One: Two or more characters followed by an @ symbol followed by 2 or more characters followed by a valid extension (.com, .org, .uk, .ca, etc.). The strings before and after the @ symbol can have a-z, A-Z, 0-9, hyphens, underscores, and periods, but it must begin and end with a letter or number, and cannot have two symbols adjacent to each other. The domain name should be a valid domain (this could be checked). The entire address should not be longer than 64 characters.

Method Two: a-z, A-Z, 0-9, _, -, and ., followed by an @, followed by more of the same, followed by a period followed by a-z or A-Z only. The entire address should not be longer than 64 characters.

The first one obviously is stronger in that it required an e-mail address that is one hundred percent realistic. But, it does more than it needs to and generally will only stop people who are giving a fake e-mail from giving one that is quite so fake. Instead they’ll give a more realistic fake one. Why bother? Save yourself the time.