What is the proper way to use raw sql in Django with params?

Question

Consider that I have a "working" PostgreSQL query -

SELECT sum((cart->> 'total_price')::int) as total_price FROM core_foo;

I want to use the raw query within Django, and I used the below code to get the result-

from django.db import connection

with connection.cursor() as cursor:
    query = """SELECT sum((cart->> 'total_price')::int) as total_price FROM core_foo;"""
    cursor.execute(query, [])
    row = cursor.fetchone()
    print(row)

But, I need to make this hard-coded query into a dynamic one with params( maybe, to prevent SQL injections). So, I converted the Django query into -

from django.db import connection

with connection.cursor() as cursor:
    query = 'SELECT sum((%(field)s->> %(key)s::int)) as foo FROM core_foo;'
    kwargs = {
        'field': 'cart',
        'key': 'total_price',
    }
    cursor.execute(query, kwargs)
    row = cursor.fetchone()
    print(row)

Unfortunately, I'm getting the following error -

DataError: invalid input syntax for type integer: "total_price"
LINE 1: SELECT sum(('cart'->> 'total_price'::int)) as foo FROM core_...

Note that; the field ( here the value is cart) input gets an additional quote symbol during the execution, which doesn't match the syntax.

---

Question

What is the proper way to pass kwargs to the cursor.execute(...)

1. with single/double quotes?
2. without single/double quotes?

Answer 1

You should not pass field names through the parameters. The parameters only escape values.

You thus work with:

with connection.cursor() as cursor:
    field = 'cart'
    query = f'SELECT sum(({field}->> %(key)s::int)) as foo FROM core_foo;'
    kwargs = {
        'key': 'total_price',
    }
    cursor.execute(query, kwargs)
    row = cursor.fetchone()
    print(row)

This will of course not escape the field so if the field contains raw SQL, it is vulnerable to SQL injection.

This is one of the very many reasons not to use raw SQL queries.

If cart is a JSONField ^[Django-doc], you can query the JSONField ^[Django-doc]:

from django.db import models
from django.db.models import Sum
from django.db.models.functions import Cast

field = 'cart'
key = 'total_price'
Foo.objects.aggregate(
    total=Sum(Cast(f'{field}__{key}', output_field=models.IntegerField()))
)['total']