Store html entities in database? Or convert when retrieved?

Question

Quick question, is it a better idea to call htmlentities() (or htmlspecialchars()) before or after inserting data into the database?

Before: The new longer string will cause me to have to change the database to hold longer values in the field. (maxlength="800" could change to a 804 char string)

After: This will require a lot more server processing, and hundreds of calls to htmlspecialchars() could be made on every page load or AJAX load.

SOOO. Will converting when results are retrieved slow my code significantly? Should I change the DB?

Answer 1

I'd recommend storing the most raw form of the data in the database. That gives you the most flexibility when choosing how and where to output that data.

If you find that performance is a problem, you could cache the HTML-formatted version of this data somehow. Remember that premature optimization is a bad thing.

Answer 2

I have no experience of php but generally I always convert or escape nearest to output. You don't know when your output requirements will change, for example you may want to spit out data as XML, or JSON arrays and so escaping for HTML and then storing means you're limited to using the data as HTML alone.

Answer 3

In a php/MySQL web app, data flows in two ways

Database -> scripting language (php) -> HTML output -> browser ->screen and Keyboard-> browser-> $_POST -> php -> SQL statement -> database.

Data is defined as everything provided by the user.

ALWAYS ALWAYS ALWAYS....

A) process data through mysql_real_escape_string as you move it into an SQL statement, and

B) process data through htmlspecialchars as you move it into the HTML output.

This will protect you from sql injection attacks, and enable html characters and entities to display properly (unless you manage to forget one place, and then you have opened up a security hole).

Did I mention that this has to be done for every single piece of data any user could ever have touched, altered or provided via a script?

p.s. For performance reasons, use UTF-8 encoding everywhere.

Answer 4

It's best to store text as raw and encode it as needed, to be honest, you always need to htmlencode your data anyways when you're outputting it to the wbe page to prevent XSS hacking.

You shouldn't encode your data before you put it in the database. The main reason are:

1. If such data is near the column size limit, say 32 chars, if the title was "Steve & Fred blah blah" then you might go over that column limit because a 1 char & becomes a 5 char & amp;
2. You are assuming the data will always be displayed in a web page, in the future you never know where you'll be looking at the data and you might not want it encoded, now you have to decode it and it's possible you might not have access to PHP's decode function

Answer 5

It is the way of the craftsman to "measure twice, optimize once".

Answer 6

If you don't need high performance for your website, store it as raw data and when you output it do what you want.
If you need performance then consider storing it twice: raw data to do what you want with it and another field with the filtered data. It could be seen as redundant, but CPU is expensive, while data storage is really cheap.

Answer 7

The easiest way is store the data "as is" and then convert to htmlentities wherever it is needed.

The safest solution is to filter the data before it goes in into the Database as this prevents possible attacks on your server and database from the lack of security implementation, and then convert it however you need when needed. Also if you are using PDO this will happen automatically for you using prepared statements.

http://php.net/PDO

Answer 8

We had this debate at work recently. We decided to store the escaped values in the database, because before (when we were storing it unescaped) there were corner cases where data was being displayed without being escaped. This can lead to XSS. So we decided to store it escaped to be safe, and if you want it unescaped you have to do the work yourself.

Edit: So to everyone who disagrees, let me add some backstory for my case. Let's say you're working in a team of 50+ people... and data from the database is not guaranteed to be HTML-Encoded on the way out - there's no built-in mechanism for it so the developer has to write the code to do it. And this data is shown all over the place so it's not going through 1 developer's code it's going through 30's - most of whom have no clue about this data (or that it could even contain angle brackets which is rare) and merely want to get it shown on the page, move on, and forget about it.

Do you still think it's better to put the data, in HTML, into the database and rely on random people who are not-you to do things properly? Because frankly, while it certainly may not seem warm-fuzzy-best-practicey, I prefer to fail closed (meaning when the data comes through in a Word Doc it looks like Value&lt;Stock rather than Value<Stock) rather than open (so the Word Doc looks right with no work, but some corner of the platform may/likely-is vulnerable to XSS). You can't have both.