I am not good at computer

Non-techies, you can safely ignore this post and go on with your day. But, tech people, if you’re still reading… a little help for an ignorant soul, please? 😅

I’m planning to add a comment section to the main part of my website. The problem is, of course, that i’ve barely ever touched PHP and Sql before, let alone tried to make something like a comment system, and as such, i have no fucking idea what i’m doing.

I’ve got a design figured out — see above — and a rough idea of what the database will look like:

postId: Integer, generated by adding some random digits to the end of a Unix timestamp
timestamp: Integer, just a Unix timestamp of when the comment was submitted
commentLocation: Unicode string, max. 32 chars?, indicating on which comments section the comment was posted
displayName: Unicode, max. 128 chars?, is what it says on the can
emailAddress: Unicode, max. 128 chars?, used to generate the avatar via Gravatar and maybe filter spam if it comes to that
website: Unicode, max. 128 chars?, used to… link to the commenter’s website
commentText: Unicode, max. 4096 chars?, the actual text of the comment; will be processed as a subset of Markdown
planet: Unicode, max. 16 chars?, any comment for which the response isn't earth will get thrown out

So, erm… any suggestions? Improvements? Ways of not getting my site hacked? Polite ways of telling me that this was a terrible idea? are welcome in the comments below.

Mathemattack says…

Hello [Xanthe],

I'm sure you've already gotten some responses that will probably have given the advice I have below but I thought I would send it just in case.

Hopefully none of it comes across as rude.

Since I'm not really sure exactly how your backend is structured so I've made some assumptions to come up with my advice, however if the assumptions are wrong then the advice might not make sense.

Some databases don't handle capitalization of fields well or at least make it a little more annoying to handle so I might avoid camelCase and use snake_case instead. It really shouldn't be a big problem on modern databases, so it would probably just be a minor annoyance at worst.

For postId, I'd just use an integer with whatever functionality your database uses for automatically incrementing IDs, like MySQL's AUTO_INCREMENT: https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

- Sidenote: Make sure to indicate that this field is the primary key when running the CREATE TABLE statement. You can use an ALTER TABLE statement to edit the table if you've already created it.

If you're using the title of the blog post to identify it in the commentLocation field, you'll have to watch out if you edit the title of a post at any point. The previous comments won't be displayed.

- If you want to avoid that you can create a separate table with a record for each post, and then reference it in your comment records, however if you're fairly confident you won't modify any titles then this is probably fine.

For timestamp, most SQL databases have a native datetime type, so use that instead of Integer.

Make sure to use prepared statements to not get SQL injected and have a "Bobby Tables" situation: https://xkcd.com/327/.

- The following is about prepared statements with MySQL, but libraries for any popular SQL database should have some similar functionality: https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

Do you need to store the planet text? If you're just checking that it's "Earth" and don't seem to be using it for anything else, I'd just check it in the PHP and not use up space in your database.

Not sure when you're converting the markdown to HTML, but it's probably best to store it as the markdown you received it rather than the converted HTML. If you change how it's converts to HTML later then you would be stuck with HTML that was converted the old way if you stored the resulting HTML rather than markdown.

- You might have already been storing it as the markdown and knew that, but I thought I'd mention it just in case.

You're probably aware, but there's no authentication which means any commenter can impersonate any other commenter.

Since you can't identify the user, they'll have to enter in their display name, email address, and website every time they make a comment which could be a little annoying. Since it's a personal blog, that's probably fine.

- I just noticed that your current feedback can store that information in a cookie so I'm guessing that might be your plan to address this and make it easier.

The comments won't be editable without being able to authorize the user.

- That might be what you intended, but I thought I would mention it.

A crawler probably wouldn't identify how to spam these comments, however it would be very easy to make a targeted spam attack on all of your comments sections. You might not be concerned about it, but it probably wouldn't hurt to put a captcha on the comment submissions.

- If you're using a service that charges based on storage usage, I would more seriously consider a captcha so a spammer doesn't blow up your database and cause your bill to skyrocket. - I also just noticed that you use Akismet to filter out spam. I don't know much about that service but I suppose if it's worked alright for you so far then that might be alright. I'd still keep a close eye on it if the amount stored is tied to any sort of bill, especially since a lot of cloud services don't have a cap for how much they charge if it gets out of control.

Hope that helps!

Mathemattack

2 comments

Mathemattack says…

Ah, I thought you were talking about comments on the blog, and that this was a feedback form since I assumed that the comments hadn't been implemented yet. That's why I wrote it out like a letter like a grandparent on Facebook comments on a post.

Anyways, if you're talking about for your main site https://marijn.uk/, then that might be a little tricky, depending on how you want to handle it, since the structure of your main site it somewhat varied depending on the section. I'll have to think about it a bit.

Did you want just one comment section/location per section of the site?

2022 Apr 8, 07.36
Mathemattack says…
Hello [Xanthe],

I'm sure you've already gotten some responses that will probably have given the advice I have below but I thought I would send it just in case.

Hopefully none of it comes across as rude.

Since I'm not really sure exactly how your backend is structured so I've made some assumptions to come up with my advice, however if the assumptions are wrong then the advice might not make sense.
- Some databases don't handle capitalization of fields well or at least make it a little more annoying to handle so I might avoid camelCase and use snake_case instead. It really shouldn't be a big problem on modern databases, so it would probably just be a minor annoyance at worst.
- For postId, I'd just use an integer with whatever functionality your database uses for automatically incrementing IDs, like MySQL's AUTO_INCREMENT: https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html
- Sidenote: Make sure to indicate that this field is the primary key when running the CREATE TABLE statement. You can use an ALTER TABLE statement to edit the table if you've already created it.
- If you're using the title of the blog post to identify it in the commentLocation field, you'll have to watch out if you edit the title of a post at any point. The previous comments won't be displayed.
- If you want to avoid that you can create a separate table with a record for each post, and then reference it in your comment records, however if you're fairly confident you won't modify any titles then this is probably fine.
- For timestamp, most SQL databases have a native datetime type, so use that instead of Integer.
- Make sure to use prepared statements to not get SQL injected and have a "Bobby Tables" situation: https://xkcd.com/327/.
- The following is about prepared statements with MySQL, but libraries for any popular SQL database should have some similar functionality: https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php
- Do you need to store the planet text? If you're just checking that it's "Earth" and don't seem to be using it for anything else, I'd just check it in the PHP and not use up space in your database.
- Not sure when you're converting the markdown to HTML, but it's probably best to store it as the markdown you received it rather than the converted HTML. If you change how it's converts to HTML later then you would be stuck with HTML that was converted the old way if you stored the resulting HTML rather than markdown.
- You might have already been storing it as the markdown and knew that, but I thought I'd mention it just in case.
- You're probably aware, but there's no authentication which means any commenter can impersonate any other commenter.
- Since you can't identify the user, they'll have to enter in their display name, email address, and website every time they make a comment which could be a little annoying. Since it's a personal blog, that's probably fine.
- I just noticed that your current feedback can store that information in a cookie so I'm guessing that might be your plan to address this and make it easier.
- The comments won't be editable without being able to authorize the user.
- That might be what you intended, but I thought I would mention it.
- A crawler probably wouldn't identify how to spam these comments, however it would be very easy to make a targeted spam attack on all of your comments sections. You might not be concerned about it, but it probably wouldn't hurt to put a captcha on the comment submissions.
- If you're using a service that charges based on storage usage, I would more seriously consider a captcha so a spammer doesn't blow up your database and cause your bill to skyrocket. - I also just noticed that you use Akismet to filter out spam. I don't know much about that service but I suppose if it's worked alright for you so far then that might be alright. I'd still keep a close eye on it if the amount stored is tied to any sort of bill, especially since a lot of cloud services don't have a cap for how much they charge if it gets out of control.

Hope that helps!
- Mathemattack
2022 Apr 8, 07.26