Proper way of storing HTML in MySQL database and also returning it on a page and also via json?

[mds1256]

Hi

 

Just wanting to know how exactly I should be doing the following:

 

I am looking to store HTML in a MySQL database, this will be added via a WYSIWYG editor on a webpage and posted via php to a MySQL database.

 

This HTML is used to display news articles on a website, so what is the correct way of storing and returning this?

 

I don't want to remove HTML so I guess it's a straight insert via pdo bind parameters and then just select and display on the webpage when returning? Or should I be using any functions to encode/ make safe the HTML code.

 

Also as part of this I also need to return the news articles for a mobile app in a JSON api, I will use json_encode function on the returned data but should I be running any make safe functions on that too?

 

Thanks

[kicken]

There's nothing special that needs to be done to store it or retrieve it from mysql, just use your typical queries with bound parameters.

 

However before you display it you will want to run it through some kind of filtering to guard against scripting attacks. HTMLPurifier is one such library you could use to do the filtering.

 

Using json_encode for your API will be fine.

[Destramic]

why would you want store html in a database?

 

what you need to do it store necessary data, such as article name, article, user_id, timestamp etc.

 

what parts are you struggling with so we can help you further?...creating a database table...using PDO?

[Jacques1]

Storing and displaying dynamic HTML is inherently dangerous and should be avoided at all costs. If the WYSIWYG editor can export a simplified format like Markdown, that's definitely preferrable. Raw HTML only makes sense if the markup is so complex that it cannot be represented in any other way.

 

The workflow is as follows:

• The WYSIWYG editor exports the document in a certain format (the simpler, the better).
• You store this document in the database using a prepared statement.
• To display the document, you convert it to HTML (if necessary), filter it with the already mentioned HTMLPurifier and then include it on your page.
• Additionally, you send a Content Security Policy header to reduce the risk of cross-site scripting attacks.

[mds1256]

There's nothing special that needs to be done to store it or retrieve it from mysql, just use your typical queries with bound parameters.

 

However before you display it you will want to run it through some kind of filtering to guard against scripting attacks. HTMLPurifier is one such library you could use to do the filtering.

 

Using json_encode for your API will be fine.

 

Thats what I was thinking, just want to make sure I do it correctly to save any problems in the future, I did look into the HTMLPurifier and it does look like a good idea. because I am using PDO and prepared statements then I should be ok with any sql injections. The one issue I seen was that single quotes seem to be converted to a slash and a number when json_encode is used? Is this normal as the html then isn't formed correctly in the json output?

 

 

 

 

why would you want store html in a database?

 

what you need to do it store necessary data, such as article name, article, user_id, timestamp etc.

 

what parts are you struggling with so we can help you further?...creating a database table...using PDO?

 

The news articles contain markup from the WYSIWYG editor, e.g. headings, paragraph, styes etc. the metadata of the news article is stored in different fields in the database but the content itself it stored in html due to the formatting from the WYSIWYG editor.

 

 

 

 

Storing and displaying dynamic HTML is inherently dangerous and should be avoided at all costs. If the WYSIWYG editor can export a simplified format like Markdown, that's definitely preferrable. Raw HTML only makes sense if the markup is so complex that it cannot be represented in any other way.

 

The workflow is as follows:

• The WYSIWYG editor exports the document in a certain format (the simpler, the better).
• You store this document in the database using a prepared statement.
• To display the document, you convert it to HTML (if necessary), filter it with the already mentioned HTMLPurifier and then include it on your page.
• Additionally, you send a Content Security Policy header to reduce the risk of cross-site scripting attacks.

 

 

 

Never really done much with markdown, but it looks like a good way to go so I will look into this - any chance you could give me the basics of how markdown works? I guess we store the markdown in the database but how is it converted back to html when retrieved?

Edited January 17, 2017 by mds1256

[Jacques1]

There are Markdown parsers which can convert the markup to HTML.

 

Markdown itself is very simple to compared to HTML (and you don't have to care about it anway as it's processed automatically).