web app breakage with utf-8

[elmo]

Hello, after two days of failed efforts and googling, I thought I had
better seek advice or observations from the experts. I would be grateful
for any input.

We have various small internal web applications that use utf-8 pages for
storing, searching and retrieving user input. They have worked fine for
years with non ASCII values, including Russian, Greek and lots of accented
characters. They still do on an old version of python (2.2.1), and there's
nothing in the code to decode/encode the input, it's *just worked*.

Recently however, while testing on a dev machine, I notice that any
characters outside ASCII are causing SQL statement usage to break with
UnicodeDecodeError exceptions with newer versions of python (2.3 and 2.4).
There are a number of threads online, suggesting converting to unicode
types, and similar themes, but I'm having no success. I am probably
completely misunderstaning something fundamental. :-(

My first question is did something change for normal byte stream usage
making it more strict? I'm surprised there aren't more problems
like this online.

Is there a correct way to handle text input from a <FORM> when the page is
utf-8 and that input is going to be used in SQL statements? I've tried
things like (with no success):
sql = u"select * from blah where col='%s'" % input

Doing sql = sql.decode('latin1') prior to execution prevents the
some UnicodeDecodeError exceptions, but the data retrieved from the tables
is no longer usable, causing breakage when being used to create the output
for the browser.


I really am at a loss for what is going wrong, when everything works fine
on crusty old 2.2.1. What are others doing for caputre, store, and output
for web utf-8?


Rgds,
Jason

 

[Stefan Behnel]

Hello, after two days of failed efforts and googling, I thought I had
better seek advice or observations from the experts. I would be grateful
for any input.

We have various small internal web applications that use utf-8 pages for
storing, searching and retrieving user input. They have worked fine for
years with non ASCII values, including Russian, Greek and lots of accented
characters. They still do on an old version of python (2.2.1), and there's
nothing in the code to decode/encode the input, it's *just worked*.

Recently however, while testing on a dev machine, I notice that any
characters outside ASCII are causing SQL statement usage to break with
UnicodeDecodeError exceptions with newer versions of python (2.3 and 2.4).
There are a number of threads online, suggesting converting to unicode
types, and similar themes, but I'm having no success. I am probably
completely misunderstaning something fundamental. :-(

My first question is did something change for normal byte stream usage
making it more strict? I'm surprised there aren't more problems
like this online.

Is there a correct way to handle text input from a <FORM> when the page is
utf-8 and that input is going to be used in SQL statements? I've tried
things like (with no success):
sql = u"select * from blah where col='%s'" % input

What about " ... % unicode(input, "UTF-8")" ?

Doing sql = sql.decode('latin1') prior to execution prevents the
some UnicodeDecodeError exceptions, but the data retrieved from the tables
is no longer usable, causing breakage when being used to create the output
for the browser.

I really am at a loss for what is going wrong, when everything works fine
on crusty old 2.2.1. What are others doing for caputre, store, and output
for web utf-8?

You didn't tell us what database you are using, which encoding your database
uses, which Python-DB interface library you deploy, and lots of other things
that might be helpful to solve your problem.

Stefan

 

[elmo]

What about " ... % unicode(input, "UTF-8")" ?

I guess it's similar, I've had partial success with input.decode('utf-8')
before DB usage, and then output.encode('utf-8') for output. But although
this stores and displays newly added utf-8 texts correctly, it
causes other problems when displaying the existing texts. I think
they're suffering from a double encoding issue. It seems rather
strange the encode/decode appears to be required now, and not before.
Is this how it should be done?

You didn't tell us what database you are using, which encoding your
database uses, which Python-DB interface library you deploy, and lots of
other things that might be helpful to solve your problem.

That would be MySQLdb with latin1, but I've tried various methods to make
it utf-8 (lots of guidance online). But this was only after I discovered
the breakage with the newer python. I.e. it has worked for years on both
machines and various python versions. I omitted that info because I can
paste the SQL into mysql's shell, it does the expected thing with no
errors, so I assumed the DB itself isn't the cause. I guess it could
be a new MySQLdb issue causing breakage.


I feel I can see part of the light, but if I'm close to what I think
is needed, it's not practical to change everything to handle encode/decode
site wide, especially as some of the data gets moved to Oracle for other
applications (most is written in Perl).



I'm thinking I need to do this now, is this the norm?:

get user input from web
text.encode('utf-8')
store or use as search in DB
text.decode('utf-8')
display page etc

The encode/decode stages have never been required before :-(

 

[elmo]

I guess it could be a new MySQLdb issue causing breakage.

Replying to self, this is *very* close to the problem:


http://sourceforge.net/tracker/index.php?func=detail&aid=1438913&group_id=22307&atid=374932

 

[Dennis Lee Bieber]

Is there a correct way to handle text input from a <FORM> when the page is
utf-8 and that input is going to be used in SQL statements? I've tried
things like (with no success):
sql = u"select * from blah where col='%s'" % input

Well... First step to consider is... don't do that...

sql = "select * from blah where col=%s"
cursor.execute(sql, input)

If you get lucky -- the database adapter will figure out how to
properly handle the encoding of "input" (I've not crawled through the
high-level Python code of MySQLdb to see if it handles such conditions)
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Justin Ezequiel]

replacing connection.character_set_name instance method seems to work
but is this the correct/preferred way?

import MySQLdb
MySQLdb.get_client_info() '4.1.8'
import sys
sys.version '2.3.4 (#53, May 25 2004, 21:17:02) [MSC v.1200 32 bit (Intel)]'
sys.platform 'win32'
cred = {'passwd': 'secret', 'host': 'myhost', 'db': 'mydb', 'user': 'justin'}
insert = 'insert into unicodetest2 (foo) values (%s)'
alpha = u'\N{GREEK SMALL LETTER ALPHA}'
alpha u'\u03b1'
conn.close()
conn = MySQLdb.connect(**cred)
cur = conn.cursor()
cur.execute(insert, 'a') 1L
conn.commit()
conn.close()
conn = MySQLdb.connect(**cred)
cur = conn.cursor()
cur.execute(select) 1L
cur.fetchall() ((9L, 'a'),)
conn.close()
conn = MySQLdb.connect(**cred)
cur = conn.cursor()
cur.execute(insert, alpha)

Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "E:\Python23\Lib\site-packages\MySQLdb\cursors.py", line 95, in
execute
return self._execute(query, args)
File "E:\Python23\Lib\site-packages\MySQLdb\cursors.py", line 114, in
_execute
self.errorhandler(self, exc, value)
File "E:\Python23\Lib\site-packages\MySQLdb\connections.py", line 33,
in defaulterrorhandler
raise errorclass, errorvalue
LookupError: unknown encoding: latin1_swedish_ci

 

[Dennis Lee Bieber]

replacing connection.character_set_name instance method seems to work
but is this the correct/preferred way?

That's gotten beyond my ability... but there is some "SET OPTION"
command that might be just as effective... or...
Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "E:\Python24\Lib\site-packages\MySQLdb\cursors.py", line 137, in
execute
self.errorhandler(self, exc, value)
File "E:\Python24\Lib\site-packages\MySQLdb\connections.py", line 33,
in defaulterrorhandler
raise errorclass, errorvalue
UnicodeEncodeError: 'latin-1' codec can't encode character u'\u03b1' in
position 0: ordinal not in range(256)
Well... It went IN, but is it correct?

Nope... Query browser shows a "?"

Altering the table definition to use utf8 for the text field, and
restarting the MySQLdb connection results in:
.... for c in r:
.... print c, repr(c)
.... print
....
1 1L
a 'a'

2 2L
? '?'

3 3L
? '?'

4 4L
α '\xce\xb1'

Well, something went in that was multiple bytes -- but interpreting
it is another matter. The table definition looks like:

DROP TABLE IF EXISTS `test`.`uni`;
CREATE TABLE `test`.`uni` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Text` char(20) character set utf8 default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Test of Unicode stuff';

--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/