Recently I finished (more or less) converting a Perl/CGI/MySQL website application to use UTF-8 throughout.

The CGI module and the DBI module currently have lousy character encoding support, so I created Perl packages to fix them (relatively) transparently.

Here’s how, and here’s my code:

UPDATE: I’ve just updated the code based on others’ feedback, for which, many thanks. See comments below. (Jan 2007)

Abstract

I here present slap-on fixes for two really common modules in Perl-5.8-based web development—CGI.pm and DBI.pm—to allow them to properly support character encoding within an all-UTF-8 application. Both fixes can be installed with an application, without requiring admin access to the server (so ideal for a web hosted app); both fixes operate as a thin filter layer, requiring minimal changes to your app. The CGI fix is designed to deal correctly with various web browser character encodings, and the DBI fix is designed to allow you to use pure UTF-8 in your database, even when your driver and version of MySQL don’t properly support it.

I wanted to Do The Right Thing with character encoding, and these are the cheap hacks which allowed me to do it. Please feel free to reuse in your project.

Background

ASCII no longer cuts the mustard. Your software needs to deal with

• Accented characters and funny foreign symbols: 4€, Strauß, mañana, Československo.
• Professional-level punctuation, like ‘smart quotes’—dashes—and →arrows, daggers†‡ and whatever★.
• Greek letters: 3.5Ω, α-β, 2μV
• Really foreign languages, like Japanese or Welsh.

There are other approaches, but the UTF-8 character encoding has a host of advantages (not least ASCII-compatibility). Most text-processing systems, including Perl are moving towards it. Joel Spolsky has an excellent overview of Unicode and character encodings that every programmer should read.

Perl 5.8 provides decent UTF-8 support and very good support for converting between other character encodings. (See perldoc topics perluniintro and perlunicode.)

However, support for UTF-8 in libraries is patchy. Common libraries are ‘CGI’, for parsing CGI input* to server-side script, and ‘DBI’, for database access, neither of which (at least the versions I’ve seen installed anywhere), have any character encoding support, and both of which can easily mangle your carefully-wrought UTF-8.

The libraries described below, ‘UTF8CGI’ and ‘UTF8DBI’ are designed to be post facto fixes for these two common packages.

* Some people use CGI package to write HTML. I don’t. My modifications below don’t address this. However, it will probably work fairly seamlessly.

CGI.pm

Download UTF8CGI.pm

The CGI library has been around since the dawn of time. It’s the cannonical way for a Perl script to parse CGI parameter and form input. Some people even use it to generate HTML. (In my opinion it should be replaced with something less monolithic and with better standards support, but that’s just my opinion.)

Issues with the existing module

• CGI.pm completely ignores character encodings from client.
• Many (especially older) web browsers don’t tell you what encoding their form input is in.
• The HTTP standard isn’t much help and conveniently ignore the issues. Frankly it’s all a rather sorry mess. See a rather good article on the subject

Solutions

My solution was to create a subclass of CGI which magically determines the input character encoding (or at least makes a decent stab in the dark), and converts it all into UTF-8, so that your application really doesn’t need to worry about it.

This subclass, UTF8CGI should be imported into wherever you “use CGI”. Instead of creating a “new CGI” object, just create a “new UTF8CGI” object instead. Your code might look like:


#!/usr/bin/perl -wT

use utf8; # If the script itself is written in UTF-8.
use strict; # Be strict. Note we gave ‘-wT’ flags to Perl too, for extra protection.
use CGI qw/:cgi -private_tempfiles/; # …or whatever parameters you like.
use UTF8CGI; # Import the fix.

my $query = new UTF8CGI; # Create a magically-auto-detect-and-convert-to-UTF-8-ed CGI object

Notes:

1. Should work with Perl5.8 or later, and pretty much any version of CGI. (Tested with Perl 5.8.0 and 5.8.4 and CGI 2.89 and 3.04.)
2. I’ve only tested this with object-syntax for CGI. It may work with functional syntax, but I don’t know. (Object syntax is cleaner anyway.)
3. Yes, you need to do this every time you invoke CGI. But why not do what I do, and hide it behind a further abstraction, that all of your CGI Perl scripts can invoke?

DBI.pm

Download UTF8DBI.pm

Issues with the existing module

Although most modern versions of database engines have character-encoding support, the current versions of Perl DBI don’t really.

• Queries return strings as raw bytes, even when the database columns contain correctly-formed UTF-8. To correctly use such strings, the application must ‘decode’ every string from the database, which is error-prone and tedious. I found a good summary of the issues.

Solutions

My approach was to create a subclass of DBI, which puts a lightweight wrapper around every database query, and UTF-8-decodes every string from every query result.

Note that, because of the simple(minded) approach, the wrapper processes every single field of every row of every query, even if it contains numeric data. Potentially, with large data sets, this could become a significant overhead. Suck it and see.

Also, this requires that every string returned from any queries is either ASCII or valid UTF-8. If your database contains strings encoded as Latin-1, or any other character set, they will come back mangled. (However, if your database contains a mixture of character encodings, and you’re using DBI, you already have problems.) So before using, ensure all your database string data is UTF-8.

To use:

1. Ensure that UTF8DBI is in your library path
2. Replace ‘use DBI;’ with ‘use UTF8DBI;’
3. Replace ‘DBI->connect’ with ‘UTF8DBI->connect’

For example:


#!/usr/bin/perl -wT

use utf8; # The script itself is written in UTF-8
use strict; # Be strict. Note we gave ‘-wT’ flags to Perl too, for extra protection.
use UTF8DBI; # Import the fix.

my $dbh = UTF8DBI->connect( # Create a magically-UTF-8-decoding database handle
“DBI:mysql:$dbname:$host”,
$username, $password
)
or die $UTF8DBI->errstr;

Notes:

1. Should work with Perl5.8 or later, and DBI 1.21 and later. (Tested with Perl 5.8.0 and 5.8.4 and DBI 1.32 and 1.46.)
2. If we find a string with malformed UTF-8, that string is replaced by a string “malformed-UTF8”. You could edit the code and replace this with a ‘die’, so that the code fails if the database is found to contain badly formed UTF-8, but I found the replaced string to be quite useful while debugging.

Summary

I use them, and they work.

They’re small and lightweight.

However, caveat emptor. You may need to tweak them to get them to work. I’m not providing any technical support. You have the source code, so if they blow up your computer and crash your grandmother, you’re on your own.

However, I hope they’re of use to you. If I save you from pulling your hair out as a result of pesky character-encoding issues, I’ll be very glad.

I don’t have much time to maintain these modules, but please feed back any improvements or bug fixes you make.