CODEWITHSUNDEEP
djangopostgresql
dasm
dasm

Reputation: 192

Django: RawQuerySet problem with passing multiple params

I've used answer from this question:

Django: making raw SQL query, passing multiple/repeated params?

but have some problems.

I have params:

params = {'film_id_string': 'core_film.parent_id', 'tags_params': 'comedy', 'order_by': 'core_film.title', 'content_type': '18', 'language_code': 'en'}

for SQL query: 

query = 'SELECT DISTINCT "core_object".*, "core_film".*  FROM "core_film"  INNER JOIN "core_object" ON ("core_film"."parent_id" = "core_object"."id")  LEFT OUTER JOIN "core_objectlocalized" ON ("core_objectlocalized"."parent_id" = %(film_id_string)s) LEFT OUTER JOIN "tagging_taggeditem" ON ("tagging_taggeditem"."object_id" = "core_objectlocalized"."id") LEFT OUTER JOIN "tagging_tag" ON ("tagging_tag"."id" = "tagging_taggeditem"."tag_id")  WHERE  "tagging_tag"."name" IN (%(tags_params)s) AND "core_objectlocalized"."LANG"=%(language_code)s AND content_type_id=%(content_type)s ORDER BY %(order_by)s'

When I tried to use RawQuerySet

films = Film.objects.raw(query, params)

I get:

SELECT DISTINCT "core_object".*, "core_film".*
FROM "core_film"
INNER JOIN "core_object" ON ("core_film"."parent_id" = "core_object"."id")
LEFT OUTER JOIN "core_objectlocalized" ON ("core_objectlocalized"."parent_id" = E\'core_film.parent_id\')
LEFT OUTER JOIN "tagging_taggeditem" ON ("tagging_taggeditem"."object_id" = "core_objectlocalized"."id")
LEFT OUTER JOIN "tagging_tag" ON ("tagging_tag"."id" = "tagging_taggeditem"."tag_id")
WHERE "tagging_tag"."name" IN (E\'comedy\')
  AND "core_objectlocalized"."LANG"=E\'en\'
  AND content_type_id=E\'18\'
ORDER BY E\'core_film.title\'

Problem is, that every place with 'E\' generate error similar to this:

DatabaseError: invalid input syntax for integer: "core_film.parent_id"
LINE 1: ...calized" ON ("core_objectlocalized"."parent_id" = E'core_fil...

How can I fix this?

Django version 1.2.3.

edit
I can't remove quotes, because I work with string:

result = self.function(result, tag, "core_film.parent_id")
def function(self, objects, tags, film_id_string):

My params for RawQuerySet look like this:

params = {'film_id_string': film_id_string}

When I try to parse this I get:

LEFT OUTER JOIN "core_objectlocalized" ON ("core_objectlocalized"."parent_id" = E\'core_film.parent_id\')

and then I have problems with

DatabaseError: invalid input syntax for integer: "core_film.parent_id"
LINE 1: ...calized" ON ("core_objectlocalized"."parent_id" = E'core_fil...

but, when I use string formatting

LEFT OUTER JOIN "core_objectlocalized" ON ("core_objectlocalized"."parent_id" = %s)' % film_id_string

it works:

LEFT OUTER JOIN "core_objectlocalized" ON ("core_objectlocalized"."parent_id" = core_film.parent_id)

I want to ommit posibility of SQL injection, so basing on Django docs I don't want to pass params with string formatting.
What can I else do?

Upvotes: 0

Views: 1625

Answers (1)

solartic
solartic

Reputation: 4319

Django prevents SQL interjection by escaping parameters. This is an SQL statement, therefore the fact that it does not work is a good thing - Django is doing it's job. Unless the users of the system will be setting the value for "film_id_string" what you did to get it to work should be ok. If not, then you would have to still used the "...%s)' % film_id_string..." method but create your own custom filter to validate that it is one of the correct allowed values.

Upvotes: 1

Related Questions