Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Just make sure you sanitize the input to avoid a SQL injection vulnerability. – Allan Jun 25 '13 at 19:58
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
up vote 10 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
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. – MainMa Jun 25 '13 at 20:07

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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