Odd Characters from MS Word (via form) Corrupt DB

PopArt

In my web application, users often copy/paste from Microsoft Word into an html form field and submit. It writes to a mysql database.

Before going to the db, I clean the entry by converting ascii (less than chr(255)) to &#number; format.

However, some of Word's strange hidden characters are making into the db, and corrupting the columns in the table.

1) Is this a common problem?
2) Is there a way I can clean all characters (preferably by just zapping anything above ascii 255)?

Thanks!
Joe

drawmack

Originally posted by PopArt
1) Is this a common problem?

yes

2) Is there a way I can clean all characters (preferably by just zapping anything above ascii 255)?

No you cannot just zap the characters because these are not hidden characters. Word use special characters for things like ' and " so it can do the neat thing where the quotes angle different ways for open and close. What you'll have to do is go into word and make a list of these special characters then copy and paste them into a form field and have php give your their ascii values. Then convert those into what they should be in standard ascii. Once you've done that you can to a little str_replace or reg ex to replace them all.

PopArt

drawmack,

Thanks for the response. I think I should clarify:

Curly quotes, trademark symbols, bullets, em & en dashes, etc. are all fine for my needs. Their characters are all below 0xFF (255) in the ISO 8859-1 character set, which I'm using. There are something like 86,000+ other code points used to create the "unwanted" characters I'm trying to get rid of.

Run this code sample to see the junk I'm trying to get rid of:

<?

for ($i=256; $i<100000; $i++) {

echo "&#$i;<br />\r";

}

?>

So when a user copy/pastes text into the form from MS Word, sometimes the formatting (e.g., colored text, table formatting, indents, etc.) becomes invisible strange characters that I don't believe I can identify with the technique you outlined.

It's one (or more) of those "gremlins" that screwed up the columns in one of my (mySQL) tables. I know I can try working with UTF-8 and using PHP's mb_ (multibyte) functions, but that's over my head, and from what I've read, it looks like it has a whole other set of headaches.

Ideally I'd love to just have a function that says "for every character in $string, if it's anything other than ascii character 0-255, delete it."

Is this doable? Is it a bad idea?

Thanks again.
Joe

Sxooter

You could use a database that support multi-byte character sets and windows encoding (PostgreSQL).

Note that if you're talking a binary format, i.e. word .doc, you'll need to encode it somehow before storing it in a text type field in any database. Or use binary large objects if your db supports them.