Creating a Web-based BBS, Part 1

Ready to create your own virtual community? Here's how to begin.
Creating and Viewing Threads

Since each message must belong to a thread, we will first look at how a thread is created. A thread is no more than a single row in the ATFThreads table, so our thread-creation program will be fairly simple.

The three listings referred to in this article are available for anonymous download at They are not printed here due to space considerations. (Listing 1 in the archive file) uses the contents of an HTML form to insert a new row into ATFThreads. However, it performs some additional manipulation as well, to ensure that the data will be retrievable in a useful way.

We can use either single or double quotes around text strings in SQL queries. Double quotes are used by DBI for parameters and thus exclude the possibility of using quotation marks. We therefore use single quotes around our text strings. However, this raises the issue of how to pass single quotes to the program. A simple solution is to perform a substitution on each of the text strings generated by the user. For example:

$value{"subject"} = $query->param("subject");
$value{"subject"} =~ s/\'/\'\'/g;

We can do even better by using the built-in $dbh->quote method, which quotes a text string for us. $dbh->quote decides whether to use single or double quotes and also handles special characters, such as quotation marks and question marks, with ease. We use a foreach loop to quote each of the elements:

# Get the form parameters
foreach my $element (qw(subject text author
$value{$element} =
Once we have done this, we can be sure that $value{$element} is suitable for insertion into the database.

We also perform several substitutions on the “text” HTML element, which contains the text that starts the thread. To begin, we remove all HTML tags, so as to prevent people from linking to all sorts of crazy sites. While it might be desirable to allow people to include HTML in their postings, it could also lead to chaos if formatting commands were inserted. I decided to be slightly draconian and disallowed all HTML. We do that by removing everything between < and >:

$text =~ s/<.*?>//sg;

Notice how we use Perl's non-greedy operator *? instead of * to remove the HTML tag. If we were to use * and the line had two HTML tags, Perl would remove everything from the first < through the final >. We use the /s modifier to tell Perl that . includes all characters, including new lines. Without /s, \n would not be included in ., which means a two-line tag such as

would be ignored.

We then make sure new lines are treated correctly, first removing multiple new lines and then replacing them with HTML paragraph markers:

$text =~ s/\r\n/\n/g;
$text =~ s/\r/\n/g;
$text =~ s|\n\n|</P>\n<\P>|gi;

Once it has performed all of these tasks, creates the SQL query that will insert the new thread into ATFThreads:

my $sql = "INSERT INTO ATFThreads ";
$sql .= " (subject, text, author, email, date) ";
$sql .= "VALUES ($values, NOW())";
We insert the date of the thread for future use, but also so that we can sort the threads in the order of their creation.

The program which lists threads, appropriately named (Listing 2 in the archive file), uses a SELECT query to retrieve all of the rows from ATFThreads:

my $sql =
"SELECT id,subject FROM ATFThreads ORDER BY subject";

After performing an $sth->execute, it checks to see how many rows were returned. If none were returned, we indicate that no threads have yet been created. If threads exist, we iterate through the results with $sth->fetchrow, which places the query result into @row. We can pull out the elements of @row and print a list:

if ($sth->rows)
print "<ul>\n";
while (my @row = $sth->fetchrow)
 print "<li> ";
 print "<a href=\"/cgi-bin/";
 print "$row[0]\">$row[1]</a>\n";
print "</ul>\n";
Users are presented with an alphabetical list of thread titles, each of which is a hyperlink to (Listing 3 in the archive file), described below. As you can see, the argument to is the id value of the thread, the defined primary key.