[Answered ]-What is the proper way to use raw sql in Django with params?

by

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']
👤willeM_ Van Onsem

Leave a comment