How to use a list in a raw django SQL query that uses `WHERE ... IN (...)`?-CodePudding

How do you inject a list parameter into a Django raw query?

Given a list of UUIDs i'd like to be able to inject them into the () in a WHERE ... IN (...) query.

list_of_uuids = [
  "<UUID_1>",
  "<UUID_2>
]

Output for SQL:

SELECT * 
FROM some_model_table 
WHERE some_model_table.uuid IN ( "<UUID_1>", "<UUID_2>" )

So I tried doing the following using raw django queries:

query_set = SomeModel.objects.raw("SELECT * FROM some_model_table where some_model_table.uuid IN %s", [list_of_uuids])

Unfortunately, the above will give the following error:

django.db.utils.ProgrammingError: syntax error at or near "ARRAY":

LINE X: WHERE uuid IN ARRAY['00123...

Which means the list is interpreted as an array and passed to the SQL query as an array, which cannot be used in an IN query.

Enclosing the injected array in () also doesn't work:

LINE X: WHERE uuid IN (ARRAY['00123...
                                                     ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Note: I'm aware this particular example can be done with Django ORM, but my actual query is more complex than this and has to be done with raw queries, and contains this WHERE ... IN (...) syntax. I just kept the irrelevant code out of the question to make it easier to parse. For those who want to use ORM, here is the code you need:

query_set = SomeModel.objects.filter(uuid__in=list_of_uuids)

I have tried building the delimited list of UUID strings myself and injecting it, unfortunately this is injected in the format WHERE uuid IN ('"some_uuid", "other_uuid"')

You could use .format(...) on the query string before handing it over to Django ORM, but this opens us up to SQL injection.

I've also looked into a way of getting SQL to interpret the Array as the input for the WHERE IN query but I haven't had much luck there.

Is there another way? Could we somehow parse the array given to the SQL into valid syntax for the WHERE ... IN (...) query?

CodePudding user response：

You can use ANY(%s), for example in your case:

query_set = SomeModel.objects.raw("SELECT * FROM some_model_table where some_model_table.uuid = ANY(%s)", [list_of_uuids])

CodePudding user response：

I believe your uuid field is not actually a UUID field. I think it's a CharField instead.

If you change the type of uuid to UUIDField the ORM approach with __in filtering you've mentioned will work as it should.