<?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]&nbsp;</th>
    <td><textarea class='inputtext commenttext' name='text' rows=2 cols=50></textarea>
    </td></tr><tr><th class='prompt' align='right' valign='top'>$[Author]&nbsp;</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 . " &mdash; ";

      $result = $result .  "<a name='comment-" . $this->id . "'></a>[-" .
                strftime($CdbDateTimeFmt,strtotime($this->date));

      $result = $result ." &mdash; [[". $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'");
}


?>