Solved

email Storage Issues in Mysql database

Posted on 2013-05-18
6
224 Views
Last Modified: 2013-05-25
My Email is storing in database as:

<p><a class="mediumimage " href="http://www.website.com/cw4/admin/js/tinyMCE/UserFiles/Images/aliciasilverstone01_1024x768.jpg" target="_blank"><img src="http://www.website.com/cw4/admin/js/tinyMCE/UserFiles/_middle/Images/aliciasilverstone01_1024x768.jpg" alt="" /></a></p>
<p> </p>
<table style="width: 730px; height: 66px;" border="0">
<tbody>
<tr>
<td>Welcome</td>
<td>Jungle</td>
</tr>
<tr>
<td>Dirty</td>
<td>Stars</td>
</tr>
</tbody>
</table>

Open in new window


when email is sent, no matter if its type is HTML, it shows HTML tags there, i am missing something on mysql part i think, anyone can throw some light here

table column Message collation is: utf8_unicode_ci

Table Dump

CREATE TABLE IF NOT EXISTS `mailinglist_messages` (
  `MessageID` bigint(255) unsigned NOT NULL AUTO_INCREMENT,
  `Message` longtext COLLATE utf8_unicode_ci,
  `SentToGroup` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SentOn` datetime DEFAULT NULL,
  `SentBy` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `InstanceName` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`MessageID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

Open in new window

0
Comment
Question by:myselfrandhawa
6 Comments
 
LVL 76

Assisted Solution

by:arnold
arnold earned 114 total points
ID: 39177899
It is unwise as you see to store email directly into a database.  An alternative might be to parse and extract/strip HTML storing only data.
It is best to store emails within the filesystem

Do you parse email?
0
 
LVL 24

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 113 total points
ID: 39178334
Hi!

You might want to store the email-message as LONGBLOB instead of LONGTEXT.
By doing that you are storing the message "as is" and will get it back exactly as you stored it.

Regards,
    Tomas Helgi
0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 113 total points
ID: 39179891
arnold is right - storing this type of data in a database field is inefficient, at best.  The file system is a much better alternative, with just a pointer to the file in the database.

As far as the formatting, there is something else in your code escaping the content before its insertion into the database.  MySQL does no translation like that.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 76

Expert Comment

by:arnold
ID: 39179910
Mysql_real_escape_string this is the php code that should be used to avoid sql injection issues with data from forms/external sources.
Not sure what process the incomng emails
0
 
LVL 15

Accepted Solution

by:
myselfrandhawa earned 0 total points
ID: 39181047
Thanks for theupdate guys, but nothing worked, i actually had to convert the htmlenteties to html through cffunction and it stored the details in the db without any issue, something else might have been causing an issue
0
 
LVL 15

Author Closing Comment

by:myselfrandhawa
ID: 39196277
Thanks
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now