Best practices when storing multimedia posts SQL DB

I have searched StackOverflow for an answer to this question, and I’ve been surprised to find very little information for what seems to be a very common task

Let’s say I have an app that allows users to make posts. These posts can contain text, of course, but I also want the users to be able to insert images, and possibly videos.

So here’s the dilemma. The first idea that comes to mind for storing these posts would be making a table like this:

CREATE TABLE posts(id INTEGER PRIMARY KEY AUTO_INCREMENT, owner VARCHAR(36) NOT NULL, message VARCHAR(MAX), _timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

• id is an identifier for the post itself.
• owner is an identifier for the person who created the post.
• message contains the message, as text.
• _timestamp represents the time created.

However, since SQL wasn’t really made for storing images and other files, the images are being stored off-database. For sake of example, let’s say they’re stored using a product similar to Google Cloud Storage.

So, the question is, how should the message be formatted in such a way that they contain data (for example, a link) that would point to the images, without having to do too much work on the frontend code? (And without letting the user know that they’re doing anything other than inserting an image).

From experience with GitHub and StackOverflow, Markdown is obviously nice, but not as user-friendly as I’d want, and doesn’t work with images exactly the way I want.

I’ve thought about using HTML to format the message, but that brings up to main problems:

1. How should I store HTML in such a way that prevents XSS (Cross-site Scripting)? Should I just escape everything in such a way that it can still be read as HTML on the frontend?
2. Let’s say this app is a mobile app. This means I would either have to make my own HTML parser or find an existing library for it.

So what is the best practice for this?

I see this type of functionality all the time, so what are those people (such as Facebook, Google, etc.) using?

Not only have I encountered this problem, but I feel like there should be a good answer for this on StackOverflow for others who encounter this problem.

Specifically, I want to know whether HTML is a good option, or if I should consider something else. As far as right now, I’m planning to use plain HTML, and make public URIs for Cloud Storage objects

Answer

Not speaking about specific implementation I would say you never want to insert the image/video data into the post.

These should always be either an attachment or a link.

So either you let the user to insert links into the post or you let them add attachments which are then uploaded to the server and link to them is placed into the post.

Let’s say you have a situation where a user drops the image/video/audio/whatever data into the post. In that case you would fire an event that uploads the data to your storage and places the link into the post when it’s done. That’s what happens when you CTRL-C CTRL-V an image into GitHub message for example.

Regarding XSS, you should strip the inserted data off any javascript and stuff that you don’t like and you should be fine. There are many libraries that can do this for you.