Solved

email Storage Issues in Mysql database

Posted on 2013-05-18
6
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 78

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 25

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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 78

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 16

Accepted Solution

by:
Gurpreet Singh Randhawa 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 16

Author Closing Comment

by:Gurpreet Singh Randhawa
ID: 39196277
Thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Complex MySQL Query 2 49
mysql db 3 96
Formating field in mysql Advance formatting 1 55
MySQL TEXT fields 11 16
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

751 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