Incorrect string value

[Nick Way]

Hi - we've just upgraded from cf8 to cf9 and are getting a fair few of these errors - "Error Executing Database Query. Incorrect string value: '\xE2\x80\x8BWe ...' for column" as we try to insert a row in the db.

The query is inserting the following string "We are seeking a" - nothing innocuous in the text and certainly no instance of this string '\xE2\x80\x8B' (despite what the error message says).

Has anyone else run into this, any ideas what's going on?

Thanks very much for any help you can offer as I'm completely stuck.

Thank you.

Kind regards,

Nick

[BKBK]

The Hex value 0xE2 0x80 0x8B is the representation in UTF-8 of the 'zero-width space' character. You may remove it from the string - for example, by replacing it with a blank space - before the string enters the query.

Assuming that chr(8203) represents the zero-width character, you could replace it as follows:

<cfset myString = replace(myString,chr(8203),"","all")>

[Nick Way]

Thanks Dan.

Is there an inbuilt cf function I can use to strip out any weird characters like that do you happen to know? Or alternatively maybe I need to tell my database (mysql) not to be so fussy and accept those chrs?

Hope you're well - thanks for the help, much appreciated.

Nick

[BKBK]

You could filter out the characters as I suggested, before storing the string in the database.

[Nick Way]

Thanks Dan - yes that was what I was hoping to do (filter out the characters before inserting to the db). I'm just trying to find the most efficient way to remove those characters; I had been using safetext.cfm but that seems a bit out of date now.. http://www.cflib.org/udf/SafeText

It'd be great if there was an inbuilt cf function like RemoveOddCharacters() for use on strings before inserting them into dbs - maybe there is one.. anyone?

Thanks

Nick

[BKBK]

I suggested one, single line for the job: <cfset myString = replace(myString,chr(8203),"","all")>

[Nick Way]

Hi again - yes I know but that only removes that one particular special character; I believe there are quite a few that cause problems if stored in the db. I'm wondering if there is a function that removes all the various troublesome special characters in one go. Failing that I wonder if there is a list of all these characters that i can loop through (using the replace as you say)?..

[BKBK]

There are thousands of special characters, and infinitely many combinations of them. To be practical and efficient, therefore, you have to focus on removing just the ones that are troublesome.

[Nick Way]

FYR I am hoping that this How to turn this Java into something I can use in CF? will eventually provide anyone who's interested with a way to remove special characters from a string before inserting in the db

[BKBK]

Your link broke.

[Nick Way]

Thanks Dan - seems ok from my end, think that's because it's a post I've put in here (getting started forum) that hasn't yet been approved for publication by the moderators - hopefully link will work shortly..

[BKBK]

It is unauthorized.

[Nick Way]

How is it now? just got an email to say post has gone live

[BKBK]

It is now available.

[BKBK]

I have answered your question in your other thread.

[Nick Way]

turns out the issue was the charset in db was set to latin_sweedish

i changed it to utf8_general_ci and that sorted it.

there was one additional twist to this which i solved by looking here MySQL Bugs: #33475: Incorrect string value when inserting unicode codepoint \xC2\x92

the key part being at the bottom "

[9 Dec 2011 0:07] Arkadiy Kulev

This problem solves easily. Don't forget to not only set the database, table and collation to utf8, BUT THE COLUMNS ALSO!

That's what cause the problem for me. I created the table in latin, then switch to utf8, but forgot to also change the columns."

that caught me out but got there in the end.

HTH anyone else who has this.

[BKBK]

Thanks. Good to know. Please mark it as the correct answer.