Error debugging GeoDjango queries

[rodrigoolmo]

When debugging queries that use GeoDjango they are parsed like normal strings.
Example:

params = [u"ST_GeomFromEWKB('\\x0101000020e6100000555458045a9d16c010470d5cd47c4440'::bytea)"]
sql = 'SELECT (ST_distance_sphere("location"."point",%s)) AS "distance" FROM "location"'

What it should execute:

SELECT (ST_distance_sphere("location"."point", ST_GeomFromEWKB('\x0101000020e6100000555458045a9d16c010470d5cd47c4440'::bytea))) AS "distance" FROM "location"

What it really executes:

SELECT (ST_distance_sphere("location"."point", 'ST_GeomFromEWKB(''\x0101000020e6100000555458045a9d16c010470d5cd47c4440''::bytea)')) AS "distance" FROM "location"

I am using django-debug-toolbar version 0.9.4, Django 1.4 and PostgreSQL with PostGIS.

[aaugustin]

I'm not sure where the problem you're describing occurs. Is it in the EXPLAIN feature?

[rodrigoolmo]

Yes, in the views sql_select and sql_explain.
It raises an InternalError in cursor.execute(sql, params) and cursor.execute("EXPLAIN %s" % (sql,), params) respectively.

[aaugustin]

I looked at this in detail today I don't understand your example at all. If it worked, it would be an SQL injection. Parameters you pass to the database are escaped. It's incorrect to pass a parameter such as "ST_GeomFromEWKB('\\x0101000020e6100000555458045a9d16c010470d5cd47c4440'::bytea)".

Here's how I have reproduced your example in a project of mine, slightly adapted because I'm using geography while you appear to be using geometry.

The following query works:

% ./manage.py dbshell
psql (9.1.4)
Type "help" for help.

test_db=> SELECT (
test_db(>     ST_Distance(
test_db(>         "generic_position"."coords"::geometry,
test_db(>         ST_GeomFromEWKB('\x0101000020e6100000555458045a9d16c010470d5cd47c4440'::bytea)
test_db(>     )
test_db(> ) AS "distance"
test_db-> FROM "generic_position"
test_db-> LIMIT 1;
     distance     
------------------
 11.1782329195295
(1 row)

The following code in a view crashes:

from django.db import connection
sql = """
SELECT (
    ST_Distance(
        "generic_position"."coords"::geometry,
        %s
    )
) AS "distance"
FROM "generic_position"
LIMIT 1;
"""
params = ["ST_GeomFromEWKB('\\x0101000020e6100000555458045a9d16c010470d5cd47c4440'::bytea)"]
cursor = connection.cursor()
cursor.execute(sql, params)

DatabaseError at /
parse error - invalid geometry
LINE 5:             'ST_GeomFromEWKB(''\x0101000020e6100000555458045...
                    ^
HINT:  You must specify a valid OGC WKT geometry type such as POINT, LINESTRING or POLYGON

The crash happens even before the code reaches the toolbar.

There might be a bug in this area, but you haven't provided enough information for me to reproduce it.

Please remember that I do not have access to your brain and your computer when reporting bugs.

[jperelli]

I have the same problem, using django==2.1.4 and django-debug-toolbar==1.11

[tim-schilling]

Reopend as per #1547

[jperelli]

I was impacted by this issue here #423 (comment), used this today and I was very surprised that just worked fine!

I couldn't find it in the changelog but it seems you landed the fix in #1426

I think you can close this one @tim-schilling / @aaugustin

Thank you very much guys!!

[tim-schilling]

@jperelli that change was reverted as it mutates data going into your database. You should upgrade versions of the toolbar.

[jperelli]

@tim-schilling You are right, sorry abot that. I can see it now in a query that uses a geom as a parameter



👍