Best practice. Do I save html tags in DB or store the html entity value?

Question

Hi Guys,

I was wondering about which way i should do the following. I am using the tiny MCE wysiwyg editor which formats the users data with the right html tags. Now, i need to save this data entered into the editor into a database table.

Should I encode the html tags to their corresponding entities when inserting into the DB, then when i get the data back from the table, not have the encode it for XSS purposes but I'd still have to use eval for the html tags to format the text.

OR

Do i save the html tags into the database, then when i get the data back from the database encode the html tags to their entities, but then as the tags will appear to the user, I'd have to use the eval function to actually format the data as it was entered.

My thoughts are with the first option, I just wondered on what you guys thought.

Answer 1

I would suggest storing the data in the database in as close to it's "natural" form as possible. Generally your database layer should not be concerned with whether a field contains HTML, Base64 Encoded Binary text, or just plain text. These are concerns for your view layer, when it decides how to render the content.

Thus, while you might want to do some preliminary screening for XSS attacks before you insert into the database, you should always screen for XSS before you send "untrusted" information to the browser.

This also has the advantage that if your XSS prevention algorithms improve in the future, you can implement it across your entire application just by changing the routines that display it, instead of having to scan your database for fields that might contain HTML, and then update them.

Answer 2

Neither. You store the HTML "as-is" so when you pull it out its ready fro rendering. You chouldnt be converting back and forth. What you put in should be what you display. What you want to do is filter the input before you put it into the DB. both tinyMCE and ck/fckEditor have facilities to limit the tags that can be used in an editor and it will strip those tags for you. Then you jsut need to perform any other necessary validation or formatting.

Answer 3

I would just check for SQL injections when inserting into the database and leave the html as is, in it's "raw" form.

I know that drupal does this and applies filters (eg. all html tags (no filter), certain tags only, xss filter, php code format, tokens, etc) on the fly. An advantage to this approach is that you don't destructively modify your input data if you want to change the filter used later.

Answer 4

When I first started my blog, I decided I would convert from BBCode to HTML (and do sanity checks) and then put it in the database. Well, a month rolls by and turns out I had a layout issue. Now that my old HTML is "fixed" in the database, I soon learned that you should always store the initial text the user uses in the database and then convert it later in a request.

This makes it so you can fix bugs with HTML and XSS and it be retroactive.

Answer 5

One possibility is to store both the sanitized version and the original version. I use this with HTMLPurifier to avoid the performance problems that are created by live sanitization, while still allowing users to edit their content in its original form.

Needless to say it will take double the storage space but usually space isn't as much of an issue as speed and control.