How do I store a signature block, including formatting, in a Sql server table?

Question

I've been assigned the task of creating a table that stores an email signature for each username. The question is, how should I store the signature block? I could use a regular varchar type, but then how do I store the formatting metadata?

Any ideas or suggestions would be welcome. Thanks!

Answer 1

HTML, RTF, XML. The stanard choices are multiple.

Note: "email signature" is NOT "digital signature". The term digital signature has a specific meaning and means a SIGNATURE to make sure - for email - it comes from th real sender and has not been tampered with.

Answer 2

I'd suggest going with your initial thought -- varchar(max). This will allow you to store signatures that are ASCII based. This includes plaintext, RTF or HTML signatures.

If users want to embed images (i.e. not a link to an image), then you'd have to determine a way for the caller to convert those images to Base64 or other before storing and after reading from your table.

Answer 3

Based on what I'm finding, you have basically two options:

1) Convert your formatted signature data to Binary and store it as a BLOB.
2) Instead of saving the signature itself in the DB, save them as files somewhere and store a reference to that file location in the DB.

Answer 4

use varchar(max), or whatever length limit is appropriate.

otherwise, the only real concern is that you might want to make sure the html is html-encoded before you stick it in the database. (i.e., replace < with <, etc.) Not sure what you're using, but some tools have a setting so you don't have to do it manually.

other things you can do besides / in addition to html-encoding

1) restrict the formatting tags to some pre-defined set (i.e., search/replace tags you don't want before doing the insert. You can manage this in your db stored procedure, or better yet, in your front-end (if you have control over that).

2) disqualify attempts to insert data if they include certain tags (like '<script>', etc.)

Answer 5

Another idea I had was that you could design a specific email signature template, and then let people specify fields, such as Username, quote, avatar, alignment etc, and then have them modify their signature in a "signature editor". This way you could just store the "data" and not the rendering. so you could store something like follows:

<signature>
    <username>chama</username>
    <avatar href="http://url to my image"/>
    <quote>A bird in the hand is not in the nest</quote>
</signature>

and it could look something like:

---

Chama
A bird in the hand is not in the nest

---