This project is archived and is in readonly mode.

#69 ✓resolved
Psycopg website

PQescapeByteaConn does not escape backslashes on postgres 9.1 server

Reported by Psycopg website | September 8th, 2011 @ 11:47 AM

Submitted by: Jonathan Slenders

Still not entirely sure whether this is a bug in psycopg2, but I think it may be.

I have a Django project, with PostGIS setup, and it works perfect on a PostgreSQL 8.4 machine. When I change the settings, in order to connect to a postgres 9.1 server, it doesn't work anymore.

The problem appears when using queries; polygons are encoded using binary_escape, which calls PQescapeByteaConn. Somehow, this function returns single backslashes on my postgres 9.1 server, but double backslahses on postgres 8.4.

The generated query looks like this:

ST_GeomFromEWKB(E'\001\003\000\000 \346\020\000\000\001\000\000\000\005\000\000\000\F...')

Obviously \000 conflicts with zero terminated strings.

Thanks,
Jonathan

Comments and changes to this ticket

  • Federico Di Gregorio

    Federico Di Gregorio September 8th, 2011 @ 01:12 PM

    Can you provide us with a minimalist python program that expose the problem?

  • Daniele Varrazzo

    Daniele Varrazzo September 8th, 2011 @ 01:46 PM

    The difference in the output of PQescapeByteaConn is due to the standard_conforming_string setting: it is on by default in PG 9.1.

    The difference is taken into account by binary_escape, which adds an E when the "double backslash" are used. The result should be thus:

    # on pg 9.0 with SCS = off
    print cur.mogrify("select %s", [psycopg2.Binary(chr(0))])
    select E'\\x00'::bytea
    
    # on pg 9.0 with SCS = on
    print cur.mogrify("select %s", [psycopg2.Binary(chr(0))])
    select '\x00'::bytea
    

    by default, pg 9.1 should send '\x00'::bytea or maybe '\000'::bytea (probably depending on the version on the client libpq). The E'\000' (the E with single backslash) is dodgy.

    Can you send the version of the libpq you are using and the output for

    show standard_conforming_strings;
    show bytea_output;
    

    in the 9.1 server? Also the output of cur.mogrify("select %s", [psycopg2.Binary(chr(0))]) could be useful to see if I am on the right track. Thanks!

    Meanwhile I will try to reproduce the bug with the latest 9.1 and a libpq 8.4 on the client.

  • Jonathan Slenders

    Jonathan Slenders September 9th, 2011 @ 10:09 AM

    Indeed, somehow, I got the E with single backslashes...

    . >>> import psycopg2

    . >>> conn_string = "local_postgres8.4 database... ..."

    . >>> conn = psycopg2.connect(conn_string)

    . >>> cursor=conn.cursor()

    . >>> cursor.mogrify("select %s", [psycopg2.Binary(chr(0))])

    "select E'\\000'::bytea"

    . >>> conn_string = "remote postgres 9.1 database... ..."

    . >>> conn = psycopg2.connect(conn_string)

    . >>> cursor=conn.cursor()

    . >>> cursor.mogrify("select %s", [psycopg2.Binary(chr(0))])

    "select '\x00'::bytea"

    For the 9.1 database, I have:
    standard_conforming_stings: on
    bytea_output: hex

    For the 8.4 server, I have:
    standard_conforming_stings: off
    bytea_output: (does not exist???)

    Thanks!

  • Daniele Varrazzo

    Daniele Varrazzo September 9th, 2011 @ 10:31 AM

    From the samples you posted, it looks psycopg is behaving as expected. What is in between your django program and psycopg?

    Are you using django's PostGIS adapter? This looks a bug to me: https://code.djangoproject.com/browser/django/trunk/django/contrib/...

    That is not the way to write the adapter: I shall provide them a patch.

  • Jonathan Slenders

    Jonathan Slenders September 9th, 2011 @ 10:53 AM

    Thanks a lot, Daniele! I am indeed using the PostGIS adaptor, so that explains the issue.
    Hopefully, they will merge your patch asap into their trunk.

  • Daniele Varrazzo

    Daniele Varrazzo September 9th, 2011 @ 10:54 AM

    Try with this patch. Please try with postgres servers using both parameters for standard_conforming_strings. If we are happy with it we can pass it to the django guys. Thanks.

  • Daniele Varrazzo

    Daniele Varrazzo September 9th, 2011 @ 11:01 AM

    (please see for update to the above comment: I've submitted it before finishing)

  • Jonathan Slenders

    Jonathan Slenders September 9th, 2011 @ 11:08 AM

    Thanks, that was quick. I'll test it right now.

  • Jonathan Slenders

    Jonathan Slenders September 9th, 2011 @ 11:34 AM

    I can confirm this patach to be working.

  • Daniele Varrazzo

    Daniele Varrazzo September 9th, 2011 @ 11:51 AM

    • State changed from “new” to “resolved”

    Brilliant, thanks for testing.

    I've open ticket #16778 on Django: https://code.djangoproject.com/ticket/16778

    Closing this bug as psycopg is doing ok here. Bye!

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

<b>WARNING:</b> the informations in this tracker are archived. Please submit new tickets or comments to <a href="https://github.com/psycopg/psycopg2/issues">the new tracker</a>.
<br/>
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

Shared Ticket Bins

Attachments

Referenced by

Pages