[Pypgsql-devel] [ pypgsql-Bugs-3563300 ] Quoting doesn't work with PostgreSQL 9

[SourceForge.net <no...@so...>]

Bugs item #3563300, was opened at 2012-08-30 06:06
Message generated for change (Tracker Item Submitted) made by pai1ripe
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=3563300&group_id=16528

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Andriy Pylypenko (pai1ripe)
Assigned to: Nobody/Anonymous (nobody)
Summary: Quoting doesn't work with PostgreSQL 9

Initial Comment:
Hello,

I'm using the pyPgSQL for quite a while, but recently I faced a trouble after migration to PostgreSQL 9. I found out that newly stored strings in the database are corrupted. After investigation I've created a patch which fixes the issue.

Let me explain the problem. The pyPgSQL library uses backslash escaped strings when using parameter binding. Lets use as an example the string: 'Here is a newline: \n'.

The PostgreSQL server has a special notation for backslash escaped strings, so the above string should be written with the E prefix: E'Here is a newline: \n'. If the E prefix is omitted then the PostgreSQL 9 sees it as 'Here is a newline: \\n' string, so the string actually two caracters - backslash and 'n' instead of a single newline character.

Before the version 9 the PostgreSQL just warned about use of an incorrect notation and processed the string as a backslash escaped string anyway. The PostgreSQL 9 has changed this behavior and the E prefix is now mandatory.

Taking into account that in pyPgSQL all the strings are forcibly casted to backslash escaped format during parameter binding, it is safe to always use the E prefix. This fixes the bug when working with PostgreSQL 9. It is also safe for earlier versions of the PostgreSQL, as this notation is recognized by them.

Please see the attached patch.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=3563300&group_id=16528