Page 1 of 1

Storing special characters in database

Posted: Wed Sep 23, 2009 3:38 am
by mattspriggs28
Hi all,

I've built a simple script that allows a user to create an email which is inserted into a pre-built html template to create a nice looking email.

I have created a form that the user enters the email text, uploads an image etc. However, the problem arises when I try to extract the text from the database and place it into the template. It looks fine when I preview the email with the text inserted, but once this is sent out to the mailing list it doesn't look right. £ (pound) signs are replaced with unrecognisable characters, single quotes have backslashes etc etc.

I've tried things like strip_slashes that work to some extent, but I was wondering if there is a function that can deal with all special characters (such as double/single quotes, pound signs etc) and manipulate them for storage in the database and then 'un' manipulate them when they are extracted from the database and placed in the email template?

Thanks in advance

Re: Storing special characters in database

Posted: Wed Sep 23, 2009 9:11 pm
by Robert07
You mean stripslashes, not strip_slashes, right? http://us3.php.net/manual/en/function.stripslashes.php. You shouldn't need more than that, unless you did some binary encoding or something when inserting into the db. Did you use mysql_real_escape_string() when inserting to the DB?

Re: Storing special characters in database

Posted: Wed Sep 23, 2009 11:09 pm
by Ollie Saunders
  • On the form and in the email put:

    Code: Select all

    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  • The text editor, which you use to create these files, should be set to UTF-8 and — if it's an option — not be inserting a BOM.
  • Ensure your database and — in some cases — individual database tables and fields are also set to UTF-8.
Then you should be all set to go. If you want to be really thorough, there's a bunch of other things you can do in your HTML to ensure that UTF-8 is used for all IO such as putting an accept-charset="UTF-8" attribute on the <form> tag but these shouldn't really be required unless you're likely to be working with old/buggy browsers. Google can assist you further in this matter, if necessary.

Re: Storing special characters in database

Posted: Wed Sep 23, 2009 11:14 pm
by Ollie Saunders
Regarding the slashes problem: If you can, disable magic_quotes_gpc and do all quoting manually using mysql_real_escape_string() as was suggested by my fellow contributor. If you can't disable magic quotes then Google for a strip slashes solution and wrap it in a conditional block that detects if magic quotes is on so your code will work just the same if magic quotes is on or off, namely, as if it were off.

You only need to quote data that is entering the database, stuff that comes out however, should be HTML escaped with htmlspecialchars(). A lot of people use htmlentities() but they're silly.