unicode merge broke PostgreSQL support

[sfrost@…]

Changeset 3024 ('Merged sandbox/unicode in trunk.') broke PostgreSQL support by dropping the proper quoting when doing lookups into the database. I noticed this at least in trac/attachment.py, line 171, which gave me this error: ERROR: column "wikistart" does not exist SELECT filename,description,size,time,author,ipnr FROM attachment WHERE type='wiki' AND id=WikiStart ORDER BY time

Changing line 171 in attachment.py to again include the 'str()' fixed the problem, ie: 169: cursor.execute("SELECT filename,description,size,time,author,ipnr " 170: "FROM attachment WHERE type=%s AND id=%s ORDER BY time", 171: (parent_type, str(unicode(parent_id))))

Note sure what other or how many other places something similar is needed…

Thanks!

Stephen

[Matthew Good]

What Python PostgreSQL modules do you have installed? If your DB module isn't escaping unicode strings, it's a bug with that module. If it needs worked around it will need done specifically for that module.

[anonymous]

I've got these installed under Debian/unstable: python2.3-pgsql 2.4.0-7 python2.3-psycopg 1.1.21-4

Another, similar, error is in wiki/model.py, around line 51. In the calls to cursor.execute, name needs to be quoted, this worked for me: util.unicode_quote(name)

Additionally, above, it should probably be 'unicode_quote(parent_id)' instead of 'str(unicode(parent_id))'

[Matthew Good]

Um, no. The docstring for unicode_quote is "A unicode aware version of urllib.quote", meaning it's made for quoting characters for use in URLs, it is not meant for quoting values for using in DB queries.

The quoting of SQL parameters should be handled by the DB module, so if a particular module doesn't handle unicode values correctly it will need worked around in "trac.db", not throughout the application code.

[pacopablo@…]

I'm a wonderful victim of this too.

Packages:

• trac: r3026
• python: 2.4.2
• psycopg: 1.1.21
• postgresql: 8.0.4

Running on gentoo.

So, is this a psycopg1 deal?

[pacopablo@…]

Just rebuilt postgresql with the "nls" USE flag and rebuilt psycopg, but that didn't fix it.

Installed psycopg2 and all is kosher.

Looks like it's an issue with psycopg1. Unfortunately, I not sure what needs to be done to special case it.

[sfrost@…]

I removed the python2.3-psycopg package, which I'm guessing forced trac to use the 'pythong2.3-pgsql' packages and removed my changes and things appear to be working still. Thanks for the help.

[Christian Boos]

Added a note on DatabaseBackend about this.

Should we comment out the support for psycopg1 in trunk?

[Christian Boos]

(forgot to set the relevant keywords)

[Jonas Borgström]

I've removed psycopg1 support in r3452.

I looked into adding a work-around similar to sqlite's UnicodeCursor but it didn't seem worth it since two other postgresql drivers can be used instead.

I'm closing this ticket and will be updating the documentation as well.