Comments on a Webpage using PHP & MySQL

29 October 2022

I want my website to have some discussion on some projects, so adding a system for visitors to leave comments made sense. There are a few pre-made services where you can just drop in some code like CBox. There are also some guestbook style services where you just link to another website that serves only you. Planning for this feature made me think of the HTML/CSS/PHP summer camp I attended in middle school. We made basic website that had a login feature and product listings based on MySQL databases, so I figured I could tackle this with PHP and MySQL again.

Most tutorials I saw relied on AJAX which seems to be a JavaScript framework/library. I knew from past experience that this does not require JS at all, so I eventually found a few articles and got a system working with pure PHP & MySQL.

I opted for MySQL for the database server since it seems to be the most popular. I also installed PHPMyAdmin so I could manage it from a webpage rather than just the command line. MySQL was already installed, and phpmyadmin was a simple "sudo apt install" away. On first install, a setup script runs that asks if you're running certain web-servers, but nginx is not listed. Don't select any of the options and just hit "OK".

Selecting "yes" to running "dbconfig-common" walks you through basic configuration. The password for the "phpmyadmin" user is not needed, so leaving it blank will randomly generate one. I created new users for basic administration and another for the webpage to use, so restricting root logins will result in a more secure server.

I created a symlink to the phpmyadmin install with the line below. The second file-path should be to whatever folder nginx is set up to serve from. I also recommend changing the link name to something other than "phpmyadmin" or "admin". Make it harder for the bots to find it.

sudo ln -s /usr/share/phpmyadmin /var/www/your_domain/super_secret_page_for_moderating_comments

There are some more security techniques you can apply, but I'm not making a massive commercial product here; my threat model doesn't include the Chinese government.

I wanted comments to be on every page and individual to each page, so I put all the relevant code in the footer that gets inserted onto each page. For HTML, I added a <form> section with inputs. There is also a hidden input that grabs the name of the current page and passes it to the PHP script. This is how I track what page each comment belongs to.

Normally when you enter a URL, the browser makes an HTTP GET request. When the submit button is clicked, the browser makes an HTTP POST request for the page with the variables from the form in the body of the request. The PHP script detects that a POST request is received and triggers the rest of the PHP code.

If you're curious on what the HTML looks like, hit F12 or right-click and view the page source.

The PHP is responsible for connecting to the SQL server and formatting the SQL request to insert a new comment. Both these programs (the web-server and the SQL server) are running on the same machine; making $dbhost localhost. $dbname will be set to whatever you called your database during setup. I made a MySQL user with limited permissions for the webpages to use to submit comments (the ones below are not the real ones I used). PHP runs on the server, so nobody should be able to get this code to steal credentials, but it can't hurt to have a restricted account just in case.

$dbhost = "localhost";
$dbname = "akbattenDB";
$dbchar = "utf8";
$dbuser = "SQL_user";
$dbpass = "password1234";

$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
if (!$conn) {
    die("Connection failed");
}

Now the connection to the database should be established. If it isn't, the die() function will abort and print an error message. Assuming it was successful, the below code gathers the appropriate variables, forms an SQL query, and executes it.

$name = mysqli_real_escape_string($conn, $_POST['name']);
$comment = mysqli_real_escape_string($conn, $_POST['comment']);
$page = mysqli_real_escape_string($conn, $_POST['page']);
$dateStamp = date("Y-m-d H:i:s");

$stmt = $conn->prepare("INSERT INTO comments (name, comment, timeSubmitted, pageName) VALUES (?, ?, ?, ?)");
$stmt->bind_param('ssss', $name, $comment, $dateStamp, $page);

$_POST[] is special variable that is an array/dictionary that contains all the data from the form the user filled out. The key is the same as the "name" that was set in the HTML. For the date, I just grab the current date & time.

Next I set up a "prepared statement". This stores an SQL query in the MySQL server. I'm using it to mitigate SQL injection attacks, but it's also useful if you want to make repeated queries while iterating a variable, for example. The first argument in the bind_param() function is telling it what kind of variables follow. All of mine are strings. Once the parameters are bound to it, you can set their value and execute as much as you want. Speaking of executing:

if ($stmt->execute()) {
    echo "<p>Success!</p>";
}
else {
	echo "Error submitting comment";
}

mysqli_close($conn);

If the query executed correctly, there should now be a new entry in the table. All of the above code gets put in a function and called in the appropriate place in the HTML portion of the footer.

The function that retrieves comments from the database starts the same way, with making a connection. It then formats a query & executes. It takes the results and iterates over them, populating the HTML to produce all the comments.

The query will be a SELECT instead of insert, I am only looking for comments on the current page, and I want to order them so the most recent is first. After execution, you will need to call the get_result() function to actually get the responses.

$stmt = $conn->prepare("SELECT * FROM comments WHERE pageName=? ORDER BY timeSubmitted DESC");
$stmt->bind_param('s', $page);
$stmt->execute();
$result = $stmt->get_result();

I then create an array and push each comment into it:

$comments = array();
while ($row = mysqli_fetch_object($result)) {
	array_push($comments, $row);
}

Finally, we can iterate through this array and build the HTML using the data from it. explode() extracts the date from the timestamp since that's all I want printed out. I use htmlentities() to make sure symbols are displayed properly. Otherwise, if a comment contains any HTML symbols in it, it will directly modify the page. This is sort of like an SQL injection. I tried this attack with some JS, which didn't work, but the HTML structure was modified.

foreach ($comments as $key => $comment) {
	$date = explode(" ", $comment->timeSubmitted);

	echo '<div class="comment">';
	echo '<p class="comment_title">'.htmlentities($comment->name).' @ '.$date[0].'</p>';
	echo '<p class="comment_content">'.htmlentities($comment->comment).'</p>';
	echo '</div>';
}

mysqli_close($conn);	

After all this, the only things left to modify are the .css file to make things pretty. While testing on my local machine, the SQL server is still localhost and as long as the database is set up the same, everything should work. It will just be a separate copy.

After a few weeks of having this system up, I got a few bot comments. Some seemed designed to just probe pages with HTML forms and 1 contained a link.

In the database, I could see that the page it commented on was set to some seemingly random text. The page name is actually a regular hidden field in the HTML that defaults to the name of the current page. If the bot does not check for hidden fields, it will set them like any other field. A side effect of this is that the comment will not show up anywhere since the PHP to display comments looks for comments that match the page name exactly.

I added a hidden field that if set in the POST request, the PHP function immediately drops. I also set the page name to sanitize since I now see it can be manipulated even though it's hidden. This will stop bots like the recent link one, but the other spam comments didn't set the hidden page name field. Similar bots may still slip through.