Marthinus Strydom Marthinus Strydom - 1 month ago 7
MySQL Question

How to insert a string with special characters into MySQL?

I am going crazy. Please help.

I have a form with a text input. This is inserted into My SQL text column. Sometime a user will enter some unknown character that breaks the insert.

For example one that I just found is

Topkap─▒
, which is a town in Turkey. You will notice the last character
─▒
. On insert, this causes a database error:


Error Executing Database Query. Incorrect string value: '/xC4/xB1
and...' for column 'country_description' at row 1


Is there a simple method to either remove these characters or escape them? I am using cfqueryparam and tried HTMLEditFormat, CFSavecontent etc to no avail.

Answer

Here are your options in my opinion:

  1. If you're using ColdFusion 10 or above, try using EncodeForHTML().
  2. Validate your UI to accept only US and UK English characters, numbers etc.
  3. Change the column type in MySQL to VARCHAR(n) CHARSET utf8.

Hope this helps.

Comments