<?php if (!defined('PmWiki')) exit();
/*
commentdb.php
Version: 1.1beta
Copyright 2006, Konstantin Tretyakov.
Copyright 2008, Ivar Snaaijer. (added spamfiltering)
A remake of commentboxstyled.php by Hans Bracker.
- Stores comments in the database, and paginates them on display.
- Latest entries appear at top, underneat the commentbox,
i.e. in reverse chronological order.
- Provides action=comment-rss, that produces an RSS-feed for latest comments on the page (see known bugs)
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published
by the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
Usage:
1) You need to set up a mysql database. Read the corresponding documentation on pmwiki.org.
In short, you need a database with a single table for basic operation, two with spamfilter:
-- Start of SQL code
-- normal table
CREATE TABLE IF NOT EXSISTS `comments` (
`id` int(10) unsigned NOT NULL auto_increment,
`page` text NOT NULL,
`author` varchar(255) default NULL,
`text` text,
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`ip` varchar(20) default NULL,
`score` smallint(6) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=utf8;
-- table for spam filtering
CREATE TABLE IF NOT EXSISTS `nasties` (
`stringcode` varchar(25) NOT NULL default '',
`score` smallint(6) NOT NULL default '1'
) ENGINE=MyISAM CHARSET=utf8;
-- example content, every occurence of stringcode will be multiplied with score
-- score value may be negative for white listing
INSERT INTO `nasties` VALUES('http', 1);
INSERT INTO `nasties` VALUES('a href=', 4);
INSERT INTO `nasties` VALUES('htm', 1);
INSERT INTO `nasties` VALUES('.com', 1);
INSERT INTO `nasties` VALUES('.cn', 1);
INSERT INTO `nasties` VALUES('.ru', 2);
INSERT INTO `nasties` VALUES('.info', 1);
INSERT INTO `nasties` VALUES('[url=', 2);
INSERT INTO `nasties` VALUES('viagra', 4);
INSERT INTO `nasties` VALUES('cialis', 4);
INSERT INTO `nasties` VALUES('tramadol', 4);
INSERT INTO `nasties` VALUES('sex', 3);
INSERT INTO `nasties` VALUES('porn', 2);
INSERT INTO `nasties` VALUES('fuck', 3);
INSERT INTO `nasties` VALUES('gay', 2);
INSERT INTO `nasties` VALUES('cumshot', 4);
INSERT INTO `nasties` VALUES('poker', 2);
INSERT INTO `nasties` VALUES('casino', 1);
INSERT INTO `nasties` VALUES('anime', 1);
INSERT INTO `nasties` VALUES('ticket', 1);
INSERT INTO `nasties` VALUES('bargain', 1);
INSERT INTO `nasties` VALUES('discount', 1);
INSERT INTO `nasties` VALUES('cheap', 1);
INSERT INTO `nasties` VALUES('free', 1);
INSERT INTO `nasties` VALUES('sell', 1);
-- end of SQL code
2) Add "include_once("cookbook/commentdb.php");" to your local/config.php
3) Add (:commentdb:) markup to the page where you wish the comment input box and the comments to be shown (this may be a group footer).
4) Create the page Site.AllRecentComments and also place the (:commentdb:) markup
Known issues:
- Currently only supports UTF-8 encoding. There's a lame attempt at making it configurable but its not tested at all.
- RSS feed does not work properly.
- The thing was used in a small friendly community, so it's not
guaranteed to be secure and durable for use in the wild.
- In particular, it does not respect page protection
(anyone can post comments even to a password-protected page,
of course, posting comments does not modify the page in any way)
- spam filtering is now avalable, but you need to pick the spam words carefully
- The CSS styling was only tested in a single context, so it most probably requires further tuning for general use.
- In general PmWiki markup seems to be supported within the comments. At least one exception is with line breaks.
Somehow they don't produce paragraphs, probably because < and > are alway's filtered out.
- Some lame attempts at making it internationalizable/localizable, but these are not tested.
- If your MySQL server breaks you most probably won't get very informative error messages. At least it wasn't tested.
- Comments can only be deleted via database, no nice interface for that (should not at all be hard to make, anyone?)
*/
// -----------------------------------------------------
// Configuration Settings
// You SHOULD modify these
// (It's better to do it by leaving this file intact and setting the values in local/config.php
// before or after include_once(commentdb.php)
// you may wish to put the style declarations into pub/css/local.css
// and set $CdbCommentStyles = 0; or delete $HTMLStylesFmt definitions below.
SDV($CdbCommentDbStyles, 1);
// Comments per-page
SDV($CdbCommentsPerPage, 15);
// Score needed to make entry disapear completely (/dev/null !).
SDV($CdbSpamScoreDiscard, 10);
// Score needed to put the entry on hold (only visible from Site.AllRecentComments)
SDV($CdbSpamScoreHold, 3);
// Set this value to 1 to use the 'nasties' table, set to 0 to turn it off
SDV($CdbSpamScoreTable, 1);
// MySQL connection settings
SDV($CdbMySQLHost, "localhost");
SDV($CdbMySQLDb, "database_needs_to_be_configured");
SDV($CdbMySQLUser, "username_needs_to_be_configured");
SDV($CdbMySQLPass, "password_needs_to_be_configured");
SDV($CdbEncoding, "utf8"); // This string is used in three places:
// a) "set names '<charset>'" in mysql
// b) "<?xml ... encoding='<charset>'" in RSS feed
// c) "Content-Type: text/xml; charset='<charset>'" in RSS feed HTTP header
// Haven't really tested what happens if you change it
// to something else than utf8
// ------------------------------------------------------------
// Output formatting
// You MAY modify these
// [ihs] it looks like it is not used ...
SDV($CdbCommentBoxFmt,"<div id='message'><form name='cboxform' action='\$PageUrl' method='post'>
<input type='hidden' name='n' value='\$FullName' />
<input type='hidden' name='action' value='commentdb' />
<table width='90%'><tr>
<th class='prompt' align='right' valign='top'>$[Comment] </th>
<td><textarea class='inputtext commenttext' name='text' rows=2 cols=50></textarea>
</td></tr><tr><th class='prompt' align='right' valign='top'>$[Author] </th>
<td><input class='inputbox commentauthorbox' type='text' name='author' value='\$Author' size='32' />
<input class='inputbutton commentbutton' type='submit' name='post' value=' $[Post] ' />
<input class='inputbutton commentbutton' type='reset' value='$[Reset]' /></td></tr></table><br /></form></div>");
SDV($CdbPageListFmt, "<div style='text-align: center'>$[Pages]: \$PageList</div>"); // $PageList is substituted with a list of links to pages
SDV($CdbDateTimeFmt,'%e %B %Y, %R');
// ---------------------------------------------------------------
// Some minor internal setup
// You MAY modify these but it's not recommented
if($CdbCommentDbStyles == 1) {
$HTMLStylesFmt[commentdb] = "
/* Shapes */
.messagehead {
margin:0;
padding:0 0 0 3px;
border:1px solid #999;
}
.messagehead h5 {
padding: 0 0 0 0;
margin: 0 0 0 0;
}
.messageitem {
margin:0 0 5px 0;
padding:4px;
border-left:1px solid #999;
border-right:1px solid #999;
border-bottom:1px solid #999;
}
.commentbutton { margin:0 0 0 5px;
padding:0 3px }
.commenttext { width:100%;
margin:0 0 3px 0
}
/* Coloring */
.messagehead { background:#e5e5ff; }
.messageitem { background:#eef; }
";
}
// -------------------------------------------------------------
// ONLY modify things below this line if you're into hacking things.
// This is the main interface between the outside world and this module.
// Delete these statements and comments won't work.
// There is some code here to allow for feedback, but it is not implemented completely yet
if ($action == 'commentdb')
SDV($HandleActions['commentdb'],'CdbHandleCommentPost');
else if ($action == 'comment-rss')
SDV($HandleActions['comment-rss'], 'CdbHandleCommentRss');
else if ($action == 'cdbApprove')
SDV($HandleActions['cdbApprove'], 'CdbHandleCommentCheck');
else if ($action=='print' || $action=='publish')
// On actions "print" or "publish" the comment box is hidden
Markup('cdbbox','fulltext','/\(:commentdb:\)/','');
else {
// Otherwise all the work is done by the CdbCommentBox function
Markup('cdbbox','fulltext','/\(:commentdb:\)/e', "CdbCommentBox(\$pagename)");
}
// ------------------------------------------------------------ //
// Main interface functions
// Output the comment input box, followed by a pageful of comments
// Followed by a list of pages
function CdbCommentBox($pagename) {
global $CdbCommentBoxFmt, $CdbPageListFmt;
// Comment box input
$result = Keep(FmtPageName($CdbCommentBoxFmt,$pagename));
// Comments
CdbDbConnect();
// See which page of comments should we show
$commentspage = intval($_REQUEST["comments-page"]);
if ($commentspage == 0) $commentspage = 1;
$num_comment_pages = CdbNumCommentPages($pagename);
if ($commentspage > $num_comment_pages) $commentspage = $num_comment_pages;
$all_comments = CdbLoadComments($pagename, $commentspage);
mysql_close();
// Append the comments one-by-one
foreach($all_comments as $com) {
$result = $result . $com->format_pmwiki();
}
// Show the list of comment pages
if ($num_comment_pages > 1) {
$pagelist = "";
for ($i = 1; $i <= $num_comment_pages; $i++) {
if ($i == $commentspage)
$pagelist = $pagelist . "$i ";
else
$pagelist = $pagelist . "[[{\$FullName}?comments-page=$i | $i ]] ";
}
$pagelist = str_replace("\$PageList", $pagelist, $CdbPageListFmt);
$result = $result . "\n" . $pagelist;
}
return $result;
}
// Outputs the RSS feed with latest comments for a particular page
function CdbHandleCommentRss($pagename) {
global $CdbEncoding;
header("Content-Type: application/xhtml+xml; charset=$CdbEncoding");
print "<?xml version=\"1.0\" encoding=\"$CdbEncoding\" ?>\n";
print "<rss version=\"2.0\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\">";
print "<channel><title>Latest comments from ".$PageUrl."</title>";
print "<link>".$PageUrl."</link>";
print "<description>Latest comments from ".$PageUrl."</description>";
// Generate "items"
CdbDbConnect();
$all_comments = CdbLoadComments($pagename, 1); //$pagename
mysql_close();
foreach($all_comments as $com) {
print $com->format_rss2();
}
print "</channel></rss>";
}
function CdbScoreCommentPost($ScoreMe) {
global $CdbSpamScoreDiscard;
// use the default connector
CdbDbConnect();
// get the data from the table
$sqltresult = mysql_query('select score, stringcode from nasties');
// iterate every row
while($row = mysql_fetch_array($sqltresult))
{
$score = $row['score'];
$stringcode = $row['stringcode'];
// multiple every occurrence of the nasty word in the string with the score
$itemscore += ($score * substr_count($ScoreMe, $stringcode));
// no need to look further when we will discard anyhow
if ($itemscore > $CdbSpamScoreDiscard) break;
}
// close the connection
mysql_close();
return $itemscore;
}
// Inserts a newly posted comment into database
// TODO: no authorization checks
function CdbHandleCommentPost($pagename) {
global $_POST, $Author, $CdbSpamScoreDiscard, $CdbSpamScoreTable;
// as < and > can messup the parser these are alway's removed, they serve no real purpose anyhow.
$nasties = array("<", ">");
$author = str_replace($nasties, '', @$_POST['author'] );
if ($author == '') $author=$Author;
// an URL as an author name is highly depreciated.
$itemscore = substr_count($author, 'http') * 2;
if ($author == '') $author='Anonymous';
$text = str_replace($nasties, '', @$_POST['text']);
// the table lookup can be turned off, if it's off we will only count the urls in the text
if ($CdbSpamScoreTable == 1)
$itemscore = $itemscore + CdbScoreCommentPost($text);
else
$itemscore = $itemscore + substr_count($text, 'http');
if ($itemscore < $CdbSpamScoreDiscard)
{
$date = date("Y-m-d H:i:s");
$ip = CdbGetIp();
$comm = new CdbComment($pagename, $author, $text, $date, $ip, $itemscore);
$comm->insert();
Redirect($pagename); // TODO: HandleBrowse() or something similar might be
// more correct but I don't know how to make it work
}
else
{
// when the post scores so high it will be discarded, we need to inform the user
// this also discorages spammers, and saves bandwidth
header("HTTP/1.1 403 Forbidden");
die("<html><title>403.".$itemscore." Forbidden</title><body>The server can not process your request. Your comment has been discarded.</body></html>");
}
exit;
}
// -------------------------------------------------------------- //
// Database communication
// Structure holding a single comment //
// Encapsulates the formatting functions and database insert procedure //
class CdbComment {
var $id;
var $page;
var $author;
var $date;
var $text;
var $ip;
var $score;
function CdbComment($page, $author, $text, $date, $ip, $score) {
$this->author = $author;
$this->text = $text;
$this->date = $date;
$this->ip = $ip;
$this->page = $page;
$this->score= $score;
}
// Returns a string that should be pasted into a page
// Assumes the string will be further converted (block/inline markups)
function format_pmwiki() {
global $CdbDateTimeFmt;
//setlocale(LC_TIME, "ru_RU.UTF8");
$result = "<div class='messagehead'>\n!!!!!" . $this->author . " — ";
$result = $result . "<a name='comment-" . $this->id . "'></a>[-" .
strftime($CdbDateTimeFmt,strtotime($this->date));
$result = $result ." — [[". $this->page ."]] <!-- from ". $this->ip ." score ". $this->score ." --> ";
$result = $result . "-]\n</div>";
$result = $result . "<div class='messageitem'>\n" . $this->text . "\n</div>\n";
return $result;
}
// Returns an RSS 2.0 <item> tag
function format_rss2() {
$result = "<item>";
$result = $result . "<title>" . trim(substr($this->text, 0, 25)) . "...</title>";
$result = $result . "<dc:contributor>" . $this->author . "</dc:contributor>";
$result = $result . "<guid>$PageUrl#comment-" . $this->id . "</guid>";
$result = $result . "<link>$PageUrl#comment-" . $this->id . "</link>";
$result = $result . "<description>" . $this->text . "</description>";
$result = $result . "<pubDate>" . date("r", strtotime($this->date)) . "</pubDate>";
$result = $result . "</item>";
return $result;
}
// Inserts a comment into database, does connection/disconnection by itself.
function insert() {
CdbDbConnect();
mysql_query("insert into comments (page, author, text, date, ip, score) values (" . CdbQuoteSmart($this->page) . "," . CdbQuoteSmart($this->author) . "," . CdbQuoteSmart($this->text) . "," . CdbQuoteSmart($this->date) . "," . CdbQuoteSmart($this->ip) . "," . $this->score . ")");
mysql_close();
}
}
// ----------------------------------------
// Comment load procedure & friends
// Returns the number of pages required to display all comments
// Assumes database is already connected
function CdbNumCommentPages($pagename) {
global $CdbCommentsPerPage;
$result = mysql_query("select count(*) from comments where page = " . CdbQuoteSmart($pagename));
$count = mysql_result($result, 0, 0);
return intval($count/$CdbCommentsPerPage) + 1;
}
// Loads all comments from the database
// returns an array of CdbComment structures, sorted in reverse chronological order (latest first). Assumes that database is already connected.
// Params:
// $pagename - the full name of the page for which the comments are to be loaded
// $comments_page_num - if it's, say, 2, then comments 26-50 will be loaded.
// (assuming 25 comments per page)
function CdbLoadComments($pagename, $comments_page_num) {
global $CdbCommentsPerPage, $CdbSpamScoreHold;
$comment_from = ($comments_page_num - 1) * $CdbCommentsPerPage;
$query = "select id, page, author, text, date, ip, score from comments ";
if ($pagename != 'Site.AllRecentComments')
$query = $query . " where score<".$CdbSpamScoreHold." and page=" . CdbQuoteSmart($pagename);
$query = $query . " order by date desc";
$query = $query . " limit $comment_from,$CdbCommentsPerPage";
$result = mysql_query($query);
$numrows = mysql_num_rows($result);
$return_value = array();
for ($i = 0; $i < $numrows; $i++) {
$com = new CdbComment(mysql_result($result, $i, "page"),
mysql_result($result, $i, "author"),
mysql_result($result, $i, "text"),
mysql_result($result, $i, "date"),
mysql_result($result, $i, "ip"),
mysql_result($result, $i, "score"));
$com->id = mysql_result($result, $i, "id");
$return_value[] = $com;
}
return $return_value;
}
// --------------------------------------------------------------------------------------------------
// Convenience routines. Most probably some of these are reimplemented somewhere in PmWiki but I'm too lazy to look
//
// Returns the IP of the client.
function CdbGetIp() {
if (getenv("HTTP_CLIENT_IP") && strcasecmp(getenv("HTTP_CLIENT_IP"), "unknown"))
$ip = getenv("HTTP_CLIENT_IP");
else if (getenv("HTTP_X_FORWARDED_FOR") && strcasecmp(getenv("HTTP_X_FORWARDED_FOR"), "unknown"))
$ip = getenv("HTTP_X_FORWARDED_FOR");
else if (getenv("REMOTE_ADDR") && strcasecmp(getenv("REMOTE_ADDR"), "unknown"))
$ip = getenv("REMOTE_ADDR");
else if (isset($_SERVER['REMOTE_ADDR']) && $_SERVER['REMOTE_ADDR'] && strcasecmp($_SERVER['REMOTE_ADDR'], "unknown"))
$ip = $_SERVER['REMOTE_ADDR'];
else
$ip = "unknown";
return($ip);
}
// Convenience function - Quote variable to make safe
function CdbQuoteSmart($value)
{
// Stripslashes
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
// Quote if not integer
if (!is_numeric($value)) {
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}
// Convenience function - connects to database. Disconnection is to be done via mysql_close();
function CdbDbConnect() {
global $CdbMySQLHost, $CdbMySQLUser, $CdbMySQLDb, $CdbMySQLPass;
global $CdbCommentsPerPage, $CdbEncoding;
@mysql_connect($CdbMySQLHost, $CdbMySQLUser, $CdbMySQLPass) or die("Failed to connect to MySQL, sorry :(");
@mysql_select_db($CdbMySQLDb) or die("There's something wrong with the database, sorry. (" . mysql_error() .")");
@mysql_query("set names '$CdbEncoding'");
}
?>