Skip to main content

MySQL: Encoding Hell

Every once in a while, I end up in this weird place called Encoding Hell, and it takes me about a day to get out of it. Usually it's related to MySQLdb, the MySQL driver for Python; however, this time it was related to URLs.

I was trying to do a MySQL import. I kept getting a lot of warnings, which I usually try to fix. I couldn't even figure out what the warnings were because I was using the mysqlimport tool. After a while, I figured out that if you do the import from within the MySQL shell, you can run "SHOW WARNINGS;".

Anyway, I got a warning like "Incorrect string value: '\xF1os' for column 'category' at row 76997". I traced it back to a URL like "". That's an ASCII URL, so I couldn't figure out what the problem was.

I had some code that was splitting the URL into two other parts. It used a regex to pull out the parts I wanted, and then it unurlencoded the parts. It turns out that once you unurlencode 'southside%20sure%F1as', you are left with 'southside sure\xf1as'. I tried to .decode('UTF-8') it, but it didn't work. I finally figured out, thanks to Vim's automatic encoding detection, that I needed to .decode('Latin-1') it, and I ended up with 'southside sureñas' (whatever that is).

What's interesting is that I started off with a perfectly fine ASCII URL and ended up with some Latin-1 that I wasn't expecting. That's a good reminder that user-submitted data is pervasively dangerous--those could have been control characters or something.


rgz said…
In Spanish sureñas is female plural for "southern". Spanish adjectives don't need a filler noun. (for instance "little" by itself functions like "little one") so without context its safe to assume "women" for a regional adjective like "sureñas"

"southside southern women"

I further guess its latin women.
flow said…
there is a related thingie that i have noted even some web frameworks get wrong—the ambiguity in encoding of URLs as sent by the browser. firefox2, for one, likes to send out URLs in the system encoding (or so i guess) where possible and to switch to UTF-8 when not possible.

this is why typingä will give me in the browser address bar, but typingäЖ will give me where ä is %C3%A4. neat, eyh? means you have to have at least one fallback encoding scheme ready for when UTF-8 fails.

the nasty thing here is that (1) there is to my knowledge no way for the client to include information about URL encoding used; (2) you can only have one fallback encoding scheme that is activated by UTF-8 decoding failure—schemes like Latin-1 do not have a big chance to throw an exception on string.decode; (3) what fallback scheme to use will depend on the geolocation of the majority of your clients; (4) since URLs are typically rather short pieces of text, a heuristic encoding detection (such as the ones browser employ for web pages) should be difficult to impossible to implement; (5) if the secondary URL encoding is really governed by system settings it may prove exceedingly difficult to testdrive whatever setup you choose.

all told, it's sort of a shame that in 2009 we still have technology that shows erratic %4D%75%6D%62%6F%4A%75%6D%62%6F instead of readable text. this should have been in the past by now.
jjinux said…
Wow, awesome comments! Thanks, guys.

Popular posts from this blog

Drawing Sierpinski's Triangle in Minecraft Using Python

In his keynote at PyCon, Eben Upton, the Executive Director of the Rasberry Pi Foundation, mentioned that not only has Minecraft been ported to the Rasberry Pi, but you can even control it with Python. Since four of my kids are avid Minecraft fans, I figured this might be a good time to teach them to program using Python. So I started yesterday with the goal of programming something cool for Minecraft and then showing it off at the San Francisco Python Meetup in the evening.

The first problem that I faced was that I didn't have a Rasberry Pi. You can't hack Minecraft by just installing the Minecraft client. Speaking of which, I didn't have the Minecraft client installed either ;) My kids always play it on their Nexus 7s. I found an open source Minecraft server called Bukkit that "provides the means to extend the popular Minecraft multiplayer server." Then I found a plugin called RaspberryJuice that implements a subset of the Minecraft Pi modding API for Bukkit s…

Apple: iPad and Emacs

Someone asked my boss's buddy Art Medlar if he was going to buy an iPad. He said, "I figure as soon as it runs Emacs, that will be the sign to buy." I think he was just trying to be funny, but his statement is actually fairly profound.

It's well known that submitting iPhone and iPad applications for sale on Apple's store is a huge pain--even if they're free and open source. Apple is acting as a gatekeeper for what is and isn't allowed on your device. I heard that Apple would never allow a scripting language to be installed on your iPad because it would allow end users to run code that they hadn't verified. (I don't have a reference for this, but if you do, please post it below.) Emacs is mostly written in Emacs Lisp. Per Apple's policy, I don't think it'll ever be possible to run Emacs on the iPad.

Emacs was written by Richard Stallman, and it practically defines the Free Software movement (in a manner of speaking at least). Stal…

Creating Windows 10 Boot Media for a Lenovo Thinkpad T410 Using Only a Mac and a Linux Machine

TL;DR: Giovanni and I struggled trying to get Windows 10 installed on the Lenovo Thinkpad T410. We struggled a lot trying to create the installation media because we only had a Mac and a Linux machine to work with. Everytime we tried to boot the USB thumb drive, it just showed us a blinking cursor. At the end, we finally realized that Windows 10 wasn't supported on this laptop :-/I've heard that it took Thomas Edison 100 tries to figure out the right material to use as a lightbulb filament. Well, I'm no Thomas Edison, but I thought it might be noteworthy to document our attempts at getting it to boot off a USB thumb drive:Download the ISO. Attempt 1: Use Etcher. Etcher says it doesn't work for Windows. Attempt 2: Use Boot Camp Assistant. It doesn't have that feature anymore. Attempt 3: Use Disk Utility on a Mac. Erase a USB thumb drive: Format: ExFAT Scheme: GUID Partition Map Mount the ISO. Copy everything from the I…