How to insert a string with special characters into MySQL?

Question

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.

Do these special characters need to be recalled at a later stage? — Lemuel Botha, Oct 28 '16 at 18:26
Not really. For example one that I just found is "Topkapı" which is a town in Turkey. You will notice the "ı". I am really at my wits end so really don't care if I lose the "ı" completely. This is just one example. The user can enter a very long string with tons of this stuff in there. I just want to get rid of it so that my insert doesn't break. — Marthinus Strydom, Oct 28 '16 at 18:35
Error Executing Database Query. Incorrect string value: '/xC4/xB1 and...' for column 'country_description' at row 1 — Marthinus Strydom, Oct 28 '16 at 18:40
That sounds very familiar. Something relating to unicode. Let me have a quick search. In the meant time, can move the full error message into the question (so it is more visible)? — Leigh, Oct 28 '16 at 19:35
Found it. For next time, do a search on the first part of the error message and it often comes right up (the "/xC4/xB1" part can vary) ie *"Error Executing Database Query. Incorrect string value:..."* http://stackoverflow.com/a/37577728/104223 .. and .. http://stackoverflow.com/questions/26788570/dealing-with-mysql-nativeerror-code-1366-and-sqlstate-hy000-in-coldfusion/26792514#26792514 — Leigh, Oct 28 '16 at 19:36

score 3 · Answer 1 · edited Aug 21 '19 at 15:35

3

Here are your options in my opinion:

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

Hope this helps.

edited Aug 21 '19 at 15:35

Community

1
1

answered Oct 28 '16 at 23:48

Nikhil Dèvrè

383
1
6

1

You may be able to improve this answer further by adding examples of how to do each of the things you have listed. – thesecretmaster Oct 29 '16 at 01:07

score 3 · Accepted Answer · answered Oct 29 '16 at 08:37

3

EncodeForHTML() does not fix this particular issue if you are actually inserting HTML from TinyMCE for example.

What fixed this was changing the Collation to utf8mb4. You can do this in Workbench by expanding the header. It's collapsed by default.

Backup your table.
Go to "Alter Table".
Click the arrows on the top right of the windows
Select utf8mb4 from the Collation dropdown.

Click "Apply"

answered Oct 29 '16 at 08:37

Marthinus Strydom

267
5
15

Nice answer, and +1 for the reminder to always backup first! – Leigh Oct 29 '16 at 18:47

How to insert a string with special characters into MySQL?

2 Answers2