Django: SQL Injection - Use subquery in raw query

Question

I'm getting an error when I try to pass a non-executed query string into a raw query, using the params argument that Django recommends, to use as a subquery:

from apps.bikeshare.models import Station
qs = Station.objects.filter(...)

subquery_string = qs.values('id').order_by().query
raw = Station.objects.raw('SELECT * FROM bikeshare_station WHERE id IN (%s)', [subquery_string])

The query prints the correct SQL (WHERE clause omitted):

<RawQuerySet: SELECT * FROM bikeshare_station WHERE id IN (SELECT "bikeshare_station"."id" FROM "bikeshare_station" WHERE ...)>

However, executing the raw query (raw[0]) gives error:

Traceback (most recent call last):
  File "/home/gbrown/Envs/bikeshare-dev/lib/python3.5/site-packages/IPython/core/interactiveshell.py", line 2847, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-4-6eada3e31b4e>", line 1, in <module>
    raw[0]
  File "/home/gbrown/Envs/bikeshare-dev/lib/python3.5/site-packages/django/db/models/query.py", line 1329, in __getitem__
    return list(self)[k]
  File "/home/gbrown/Envs/bikeshare-dev/lib/python3.5/site-packages/django/db/models/query.py", line 1299, in __iter__
    query = iter(self.query)
  File "/home/gbrown/Envs/bikeshare-dev/lib/python3.5/site-packages/django/db/models/sql/query.py", line 93, in __iter__
    self._execute_query()
  File "/home/gbrown/Envs/bikeshare-dev/lib/python3.5/site-packages/django/db/models/sql/query.py", line 127, in _execute_query
    self.cursor.execute(self.sql, params)
  File "/home/gbrown/Envs/bikeshare-dev/lib/python3.5/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/gbrown/Envs/bikeshare-dev/lib/python3.5/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/gbrown/Envs/bikeshare-dev/lib/python3.5/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/gbrown/Envs/bikeshare-dev/lib/python3.5/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/home/gbrown/Envs/bikeshare-dev/lib/python3.5/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/gbrown/Envs/bikeshare-dev/lib/python3.5/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: can't adapt type 'Query'

I'm aware that this is dangerous as it is SQL injection. However, I would like to support this, to create a QuerySet method (which happens to require raw query) that can be used on the end of a queryset chain where the filtered queryset is passed into the raw query as a subquery.

Would the params parameter mitigate any risk even if the error didn't happen? Or shall I just allow the risk and use basic python string formatting?

Any reason you can't use query_set.filter(id__in=your_list_of_ids) somewhere? — Jon Clements
– Jon Clements, Commented Apr 16, 2018 at 16:16
@JonClements thanks, just realised I had another issue too, see my answer. — Greg Brown
– Greg Brown, Commented Apr 16, 2018 at 16:43

Greg Brown · Accepted Answer · 2018-04-16 16:41:45Z

0

I've just released my method doesn't work properly anyway, since the query method doesn't return valid SQL, i.e.

qs = Station.objects.filter(name='Albert Gate, Hyde Park').values('id').order_by()
print(qs.query)

[out] SELECT "bikeshare_station"."id" FROM "bikeshare_station" WHERE "bikeshare_station"."name" = Albert Gate, Hyde Park

blows up because the name string is not quoted.

It seems the only way then is, as Jon Clements points out in his comment, to execute the query and pass in a list of ids which can be achieved:

qs = Station.objects.filter(name='Albert Gate, Hyde Park')
ids = list(qs.values_list('id', flat=True))

You could then pass the list of ids to the raw query rather than a string.

answered Apr 16, 2018 at 16:41

Greg Brown

1,3782 gold badges18 silver badges34 bronze badges

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

2 Comments

Jon Clements Over a year ago

Out of curiosity - I'm still not sure what you were trying to do here? Query names that were also in a subset of IDs, or get a list of IDs matching a name and then get a raw ORM'd generated SQL from it or... ? Just wondering where the .raw stuff comes into this is all...

Greg Brown Over a year ago

@JonClements see my answer to another question. I was trying to terminate a queryset chain ending in a raw query. I wanted to pass the query "as is" as a subquery in the raw query to give maximum flexibility. However, since the SQL returned by query isn't valid I can't rely on that anyway.

Collectives™ on Stack Overflow

Django: SQL Injection - Use subquery in raw query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related