• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

email Storage Issues in Mysql database

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">

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`)

Open in new window

Gurpreet Singh Randhawa
Gurpreet Singh Randhawa
4 Solutions
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?
Tomas Helgi JohannssonCommented:

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.

    Tomas Helgi
Steve BinkCommented:
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.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now