CODEWITHSUNDEEP
djangopostgresqldjango-querysetpostgresql-9.1
tchaymore
tchaymore

Reputation: 3726

Django queryset not escaping text in a where...in query

I'm trying to construct a fairly straightforward queryset that returns a list of values based on a set of criteria in a related table. Based on values from URL params, an array of values is passed to an in filter on the queryset.

The problem is that Django passes those values as text without quotes into the SQL query and thus the query fails.

The focal table contains information on companies ("Entity") and the related table information on offices ("Office") of those companies.

This is the array of values being passed to queryset:

locations = [u'San Francisco', u'New York']

Here's the query:

companies = Entity.objects.values("name","summary").filter(office__city__in=locations)

Here's the query that actually hits SQL:

SELECT "entities_entity"."name", "entities_entity"."summary" FROM "entities_entity" INNER JOIN "entities_office" ON ("entities_entity"."id" = "entities_office"."entity_id") WHERE "entities_office"."city" IN (San Francisco, New York)

If I run the SQL query manually, but surround each of the statements in the IN clause with single quotes (not double quotes), it works fine.

Any thoughts on why this is happening and how I can fix it?

For what it's worth, here are the pertinent parts of the models

class Entity(models.Model):
    name = models.CharField(max_length=450)
    summary = models.TextField(blank=True,null=True)

class Office(models.Model):
    entity = models.ForeignKey(Entity)
    city = models.CharField(max_length=250,blank=True,null=True)

Upvotes: 1

Views: 1441

Answers (2)

Cerin
Cerin

Reputation: 64789

Unfortunately, that's a known limitation of the Django queryset query attribute. The Django devs only provide that for debugging, and have refused to modify it to reflect the actual database backend, so it won't properly escape certain datatypes correctly.

Upvotes: 0

jap1968
jap1968

Reputation: 7763

Seems to be an odd behavior when Django replaces contents to apply the query filter.

Maybe it worths trying this:

locations = [u'\'San Francisco\'', u'\'New York\'']

Upvotes: 2

Related Questions