Robert Tamayo | January 13, 2019

How to Write a Script to Approve Comments Before Displaying Them

I recently added the ability to add comments to this blog. I checked in a month later, and there were of course plenty of spam comments. Some were even posing as me. But of course, that's all fixed now, because I wrote a very simple script that forces comments to be approved before displaying them on the page.

1. Update the API

First, we need to send an email to the admin whenever a comment is submitted. All the email needs is the comment and a simple link that automatically approves the comment when clicked. As an extra measure, I also included a "secret" in the email link that only the database knows. Here's a sample email link:

http://www.redcodebluecode.com/approve_comment.php?commentid=30&secret=1815626841

The fully updated script looks like this:

// generate random number
$secret = mt_rand();

// update sql statement to add random number
$sql = "INSERT INTO commentbase (comment, commentblogid, commentguestid, replyto, commentsecret, timewritten)
VALUES (:comment, $postid, $commentguestid, $replyto, $secret, NOW())";
$data = executeSQL();

// get the comment id to use in the email link
$commentid = json_decode($data, true)['lastinsertid'];

// get the comment to use in the email link
$comment = filter_var($_POST['comment'], FILTER_SANITIZE_STRING);

// send the email
$msg = <<<CITE
        Someone submitted a new comment:
        $comment
        Approve: <a href="http://www.redcodebluecode.com/approve_comment.php?commentid=$commentid&secret=$secret">Click to approve</a>
CITE;
mail("robert@redcodebluecode.com","New comment",$msg);

I also needed to update the API that returns comments for a blog post. This update was much simpler:

$sql = "SELECT commentbase.*, guestbase.guestname
        FROM commentbase
            JOIN guestbase 
            ON commentbase.commentguestid = guestbase.guestid
        WHERE commentbase.commentblogid = $postid 
        AND commentbase.replyto IS NULL 
        AND commentbase.approved = 1
        ORDER BY commentbase.timewritten
        $limit_and_offset";

2. Update the database

Next, I updated the MySQL database in the commentbase table. First, I added the "approved" attribute of type TINYINT with a default value of 0. Next, I added the "commentsecret" attribute of type INT with default value NULL. That's all that was needed for the comments table.

3. Add the new endpoint

Next, I added the new endpoint to handle the link I created earlier. Here's the full script:

require_once("config.php");
$db_server = DB_SERVER;
$db_username = DB_USERNAME;
$db_password = DB_PASSWORD;
$db_name = DB_NAME;

$conn = new PDO("mysql:host=$db_server;dbname=$db_name", $db_username, $db_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$commentid = filter_var($_GET['commentid'], FILTER_VALIDATE_INT);
settype($commentid, 'integer');
$commentsecret = filter_var($_GET['secret'], FILTER_VALIDATE_INT);
settype($commentsecret, 'integer');

$sql = "UPDATE commentbase SET approved = 1 WHERE commentid = $commentid AND commentsecret = $commentsecret";
$stmt = $conn->prepare($sql);
$stmt->execute();
exit;

It's very simple, and the only change I can imagine would be to add a redirect to the actual blog post. Since I'm the only one using it, I'm not too concerned about that, but to make that happen all I would need is another SQL statement to get the blog permalink using the blogid associated with the commentid.

That's it. 3 steps and now no one can spam my comments section!

Comments: