What type should I use for html text in my database?

Question

I have a text area connected using tinyMCE that will contain the HTML code that I want to save to the database.

I want to save this html code as it is, so that it can be printed at any time and even written to a file (using fopen("filename","w"); )

Since I am setting up the table that I will use, I do not know what type of record should be assigned to this HTML text in my database.

Should I..:

• use addslashes/stripslashes when i save / stamp html text?
• or htmlencodechars/decodechars ?
• or nothing at all?

Answer 1

Use HTMLPurifier to remove any malicious XSS code from HTML. TinyMCE tries to do this, but it can be circumvented by sending directly to your script.

When saving data, use a parameterized query / prepared statement instead of escaping to prevent SQL Injection. PDO or MySQLi can do this. This is a good PDO tutorial , especially if you came from mysql_ * native library.

As for the data type, any type of string is fine, it depends on how long your content can be.

Answer 2

Using a large varchar like varchar (5000) means you can perform full text indexing where you wouldn’t do this in the TEXT column.

If it would be much safer to use something like BBcode markup, but if you need to use HTML (I hope not allowing guests to enter it) there are plugins like http://htmlpurifier.org/ that perform HTML sanitization well.

Obviously, you can encode and decode HTML using standard PHP functions, but there is always a risk of letting something go.

Hope this helps.

Answer 3

Use the text type for the column in the database and some level of database abstraction that uses the destination for you, such as PDO.

htmlencodechars() and addslashes() not suitable. It is not important to do some encoding, but to do the right type of encoding for a given purpose.

Answer 4

Use the TEXT data type for storing HTML and parameterized queries using PDO or mysqli instead of addslashes .