Invalid XML Character preventing foreign orders - ADVICE?

[Jeff_Mash]

I know this has been discussed before, scattered amongst many topics, but I don't know if I ever got a concrete solution for it.

Problem: When a customer with a foreign character in their name/address tries to place an order, it gets declined by FirstData/Linkpoint processor. The exact error is: Response Code: SGS-020003: Invalid XML

For example, if it has a character like these (ñ, í, é, etc), then the transaction declines.

Suspected Reasons Why: I think this has SOMETHING to do with mixed up COLLATION in the database. Either an entire TABLE collation is wrong, or perhaps individual fields within a table have incorrect collection.

WHAT I NEED TO KNOW:

- Can someone tell me straightforwardly, in order to avoid this Invalid XML message with customers who have foreign characters in their addresses, WHAT TABLES would effect this, and WHAT COLLATION should they be?

For example, perhaps you could say something like "The only tables which would effect this kind of error would be TABLE-X and TABLE-Y, and those table should be COLLATION-Z to avoid that problem."

Here are some of my current settings (on the tables *I THINK* would be the focus of the problem):

- zen_address_book (my collation is utf8_unicode_ci)
- zen_address_format (my collation is utf8_unicode_ci)
- zen_customers (my collation is latin1_swedish_ci)
- zen_linkpoint_api (my collation is latin1_swedish_ci)
- zen_orders (my collation is utf8_unicode_ci)

To further confuse things, some of my tables have MIXED collation inside of them. For example, my zen_customers table has a global collation of latin1_swedish_ci. And yet, if I look at the structure of each field INSIDE of that table, the fields themselves have the utf8_unicode_ci collation:

Attachment 13977

Attachment 13978

As you can see, my GUESS is that this XML error happens because of some database misconfiguration on my part, and I would just need to know what collation to change (which field, or which table, etc).

[DrByte]

Simplest answer: ideally all your tables (and all the fields inside each of those tables) should have the same collation, and that collation would be directly related to the DB_CHARSET in your configure.php files. And of course DB_CHARSET should be in the same family as the CHARSET set in your language files.

Now, if you've upgraded from v1.2/1.3 to v1.5 then your database content was probably iso-8859-1 (latin1) because that's what ZC used in v1.2/v1.3.

And, while upgrading to v1.5 the general rule of thumb is to REPLACE the v1.5 default of utf8 with whatever your site was using before the upgrade.
But if you didn't do that, then any tables you've created since the upgrade will be created using utf8, AND all the data ZC is storing into ALL your table will be using utf8 characters, even if your tables are latin1. So that means you've got utf8 characters inside latin1 fields, or you've got latin1 characters inside utf8 tables.

And yes, that could cause problems such as invalid characters, even displaying of characters on-screen as upside-down question marks, and so on.

Converting data inside tables is not simply a matter of telling the database to treat the field as a different collation ... because the database WON'T clean up the data and convert the data itself. First you need to convert all data to its binary equivalent, then change the collation, then put the data back. Else you'll lose any non-ascii characters that don't have direct translations. And then you're in the same mess again with invalid characters, just inverted.

As far as your firstdata transmission, have you looked at the logs and seen where the invalid characters are? Are they always in the "name" fields? Or are they currency symbols or product descriptions, or address details? Knowing that information helps isolate to which tables might need to be targeted first.

As always, when manipulating database data, especially as low-level as this, make sure you have solid reliable backups!

[Jeff_Mash]

Dr Byte - To answer some of your questions, the data almost ALWAYS seems to be in the ADDRESS field, and it's only with LinkPoint/Credit Card purchases. PayPal works just fine.

I am not so much interested in cleaning up the old data in the database......but I am mainly looking for any solution to fix this going forward (with new customer addresses).

I've been a long time ZC user from 2004, so I'm positive that a lot of these database collation issues are due from the old versions mixing/carrying over to the newest stuff.

I'm just wondering.......if I change all my tables (and fields) to match my DB_CHARSET, will that fix it "going forward?" I understand that this may not "convert" pre-existing data that's already stored in the database.

My only fear is that I don't want to CORRUPT (or break) any old data already stored in there. In other words, I don't want to fix the problem for new customers, but then find that all 10,000 of my preexisting customers can no longer checkout of our store because something got corrupted in the database, know what I mean?

Fortunately, this "invalid character" only happens when a customer registers an address with a foreign symbol, but I am trying to figure out some way to prevent that from happening (because the error message that the customer sees is simply that their credit card is declined......it doesn't tell them that it's due to an invalid character).

So I guess another solution would be to find a way to spit out the exact error to the customer (Invalid Character in your address.....please try again without the foreign character), but I don't know how to configure that.

Like I said, if manually changing the collation on the database won't SCREW UP the pre-existing records, then I will be fine with that. I am not trying to "fix" what is in there. I simply want to "not screw up" what's already in there, but eliminate this error from happening on NEW RECORDS which are created.

Hope that makes sense. If you have any other advice, please post it here for me.

[DrByte]

I wouldn't go changing the database to fix this.

The main thing to "fix" is what's being transmitted.

ZC v1.5.1 already converts strange characters to their corresponding html entities for transmission:

    $myorder["name"]     = htmlentities($_POST['cc_owner'], ENT_QUOTES, 'UTF-8'); //$order->billing['firstname'] . ' ' . $order->billing['lastname']);
    $myorder["company"]  = htmlentities($order->billing['company'], ENT_QUOTES, 'UTF-8');
    $myorder["address1"] = htmlentities($order->billing['street_address'], ENT_QUOTES, 'UTF-8');
    $myorder["address2"] = htmlentities($order->billing['suburb'], ENT_QUOTES, 'UTF-8');

What version are you using?