I have an application that allows users to edit certain parts of text and then email that out. My question is what is the best way to store this in a Microsoft SQL Server database. Right now I have two tables, one holding the HTML data and one holding the plain text data. When the user saves the info, it replaces newlines with br's and puts it in the HTML-conntaining table and then puts the regular text in the other table. This way the text box has the newlines when they go to edit, but the table that contains the HTML data, has the BR's. This seems like a silly way to do things. What would be the best practice? Thanks.

  • 3
    Just make sure you sanitize the input to avoid a SQL injection vulnerability. – Allan Jun 25 '13 at 19:58
  • 4
    It's not just SQL injection, you need to also sanitize against Cross-site Scripting (XSS) and many other forms of external attacks. – Adrian J. Moreno Jun 25 '13 at 20:00
  • Let's say @iKnowKungFoo hits the nail on the head. Storing unsanitized input is like opening Pandora's box. – Deer Hunter Jun 25 '13 at 22:13

It's not silly at all.

Consider the following possibilities:

  1. Store just HTML. That 's silly! Once stored this way, editing it would be painful: you either should decode it, or simply forbid any edition or force users to write HTML.

  2. Store just text. It may work. Until, maybe, you find that converting text to HTML is the bottleneck¹ which slows your application down. For small scale apps, this approach is still ok and probably the easiest one.

  3. Store text and HTML. That's what you've chosen, and it has the benefits of both previous approaches: editing content is simple, and at the same time you don't slow the application down by doing the conversion every time the page is generated.

If there is a thing which is annoying, it's the fact that you are using two tables. Why not keeping this data in a single table, with one column for original text, and another column for HTML?

¹ Remember one rule: don't guess what is slowing the application down: use a profiler. Discussing which approach is faster is good for an informal talk with your friends, but not a good approach to develop a scalable application without doing unnecessary work. My example of saving HTML vs. generating it on the fly, for example, is only good in theory. In practice, (1) you'll cache the results anyway, and (2) maybe, who knows, loading the data from the database may be much slower than generating HTML.


If it were me, I would just replace the newlines with br's as the text is being used. So, store the plain text (newlines), and then when you have to use it in an email, replace the newlines with br's on the fly.


You might consider an implementation of BBCode. This will allow you to store plain text, which can be converted to HTML. No actual HTML tags would be allowed.

From Wikipedia:

[b]bolded text[/b]

would become

<b>bolded text</b>

and so on.

  • What would be the benefit of storing BBCode ? – Tulains Córdova Jun 25 '13 at 20:06
  • Please, please, please! BBCode sucks for people without technical background, because... oh, I won't even explain why. Ask any interaction designer, if you're interested. And for IT people, there is Markdown, which is highly superior to BBCode. – Arseni Mourzenko Jun 25 '13 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.