Why doesn't ruby detect an invalid encoding while mysql does?

Question

I'm pulling some RSS feeds in from YouTube which have invalid UTF8. I can create a similar ruby string using

bad_utf8 = "\u{61B36}"
bad_utf8.encoding # => #<Encoding:UTF-8>
bad_utf8.valid_encoding? # => true

Ruby thinks this is a valid UTF-8 encoding and I'm pretty sure it isn't.

When talking to Mysql I get an error like so

require 'mysql2'
client = Mysql2::Client.new(:host => "localhost", :username => "root")
client.query("use test");

bad_utf8 = "\u{61B36}"
client.query("INSERT INTO utf8 VALUES ('#{moo}')")

# Incorrect string value: '\xF1\xA1\xAC\xB6' for column 'string' at row 1 (Mysql2::Error)

How can I detect or fix up these invalid types of encodings before I send them off to MySQL?

So I think the problem might be that MySQL only supports the Basic MultiLingual Plane where as ruby supports everything. — johnf, May 09 '11 at 03:10

score 2 · Answer 1 · edited Dec 02 '16 at 00:22

I don't rely on Ruby's built-in String.valid_encoding?, because the following is also possible:

irb
1.9.3-p125 :001 > bad_utf8 = "\u{0}"
 => "\u0000" 
1.9.3-p125 :002 > bad_utf8.valid_encoding?
 => true 
1.9.3-p125 :003 > bad_utf8.encoding
 => #<Encoding:UTF-8>

This is valid UTF-8 (Reference: https://en.wikipedia.org/wiki/Utf8), but I found the presence of the NULL character in a string is often a hint to a previous conversion error (e.g. when transcoding from invalid encoding informations found in html pages).

I created my own validation function for "Modified UTF-8", which can take a :bmp_only option for restricting validation to the Basic Multilingual Plane (0x1-0xffff). This should be enough for most modern languages (Reference: https://en.wikipedia.org/wiki/Unicode_plane).

Find the validator here: https://gist.github.com/2295531

\u0000 is valid. It's exclusion is within the context of "Modified UTF-8" used by Java DataInput/DataOutput http://docs.oracle.com/javase/6/docs/api/java/io/DataInput.html — nessence, Aug 16 '12 at 20:34
That's correct. The above gist makes usage of Modified UTF-8, I will make a note there to make it clearer. — lumpidu, Sep 10 '12 at 19:56

score 1 · Accepted Answer · answered May 09 '11 at 06:44

possibly because the code point doesn't lie in the basic multilingual plane which is the only characters that MySQL allows in its "utf8" character set.

Newer versions of mysql have another character set called "utf8mb4" which supports unicode characters outside the BMP.

But you probably don't want to be using that. Consider your use-cases carefully. Few real human languages (if any) use characters outside the BMP.

Why doesn't ruby detect an invalid encoding while mysql does?

2 Answers2