Django: RawQuerySet problem with passing multiple params

Question

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?

Try removing the single quotes from the values in the dictionary. That how it is in the old older post, and it seem that the database it trying to escape them which is what is causing the E\...\. — solartic
– solartic, Commented May 24, 2011 at 1:30
@solartic, thanks for suggestion. Partially I've removed quotes, but problem still exist. I've also added extra informations to my question. — dasm
– dasm, Commented May 24, 2011 at 12:48
@dasm Honestly that extra information is confusing me, but anyway the error to me is saying that an integer value is expected for "core_objectlocalized"."parent_id", and you are passing a string? In your last code block you use it as a variable and not as a sting which I guess works for you. — solartic
– solartic, Commented May 24, 2011 at 22:55
@solartic This function have to catch variable from different tables. I can't set up permanent table, because sometimes it is "core_film.parent_id", but other times it is "core_rating.film_id" or "core_filmranking.film_id". Every value is integer, but I only want to pass string, which is address in db for proper integer. — dasm
– dasm, Commented May 25, 2011 at 7:09
@dasm Django prevents SQL interjection by escaping parameters. This is an SQL statement, therefore the fact that it does not work is good - Django is doing it's job. Unless the users 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. — solartic
– solartic, Commented May 25, 2011 at 12:38

solartic · Accepted Answer · 2011-05-25 16:48:42Z

1

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.

answered May 25, 2011 at 16:48

solartic

4,3393 gold badges27 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django: RawQuerySet problem with passing multiple params

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related