[egenix-users] Unicode problem

[M.-A. Lemburg]

Moof wrote:
> On 12/29/05, Andrew Veitch <andrew at logicalware.com> wrote:
>> I'm in the process of adding Microsoft SQL Server support to
>> MailManager. Currently it supports PostgreSQL (psycopg) and MySQL.
>> I've bought a copy of mxODBC Zope DA.
>>
>> Unfortunately I keep getting a "TypeError: command must be a string".
>> Doing some quick Googling suggested this was a Unicode problem and
>> when I changed the input to ordinary strings it works fine.
>>
>> Obviously we could get MailManager to check if it is using the mxODBC
>> and then avoid Unicode if it is but that would be quite a lot of work
>> and something I'm keen to avoid if there is a work around.
> 
> 
> It's a bit of an ambiguous error message.
> 
> The command must be a string, this seems to be a limitation of mxODBC.
> However, the values in a prepared statement can be unicode if necessary, and
> mXODBC handles that situation correctly.
> 
> This is wrong and raises the above error:
> 
> cursor.execute(u"INSERT INTO table1 (value1) VALUES 'áéíóú'")
> 
> This works:
> 
> cursor.execute('INSERT INTO table1 (value1) VALUES ?', (u'áéíóú',))
> 
> ...and also gives you the advantage of doing all your escaping for you, all
> the better to avoid SQL injection attacks, as well as caching execution
> paths for complex queries.

Indeed. Unfortunately, the Zope database architecture currently
doesn't support passing parameters to the SQL command - it
insists on inlining everything into the command string which has
several limitations: not only the problem of having to do the
escaping and the risk of SQL injection, but also the rather
simple problem of the command string length being limited by
several database backends.

In Zope, the only work-around is to do queries using external
methods which can then use the bound parameter strategy. Note
that this is supported by the mxODBC Zope DA (please see
the documentation).

It is true that the command string currently cannot be
Unicode. The reason for this is that the ODBC API for
passing in the command string does not support Unicode.

There is a special Unicode API in ODBC for this, but
support among drivers is only starting to get to a state
where this API would be a viable choice for mxODBC.

> Yes, it's annoying, as most ORM packages these days seem to generate unicode
> strings, which makes mxODBC of limited use with them. I'm not sure if this
> is a limitation of ODBC itself, but if it isn't, is there any chance of
> getting mxODBC working with these strings?

The next release of the mxODBC Zope DA will support encodings
to work around this problem. You will be able to specify
the database encoding to use and the Zope DA will then
automatically convert Unicode to this encoding prior
to passing it to the database driver.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Dec 29 2005)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::