CODEWITHSUNDEEP
pythonsqldjangopostgresqlsql-injection
Michal Charemza
Michal Charemza

Reputation: 27022

Django extra + where: how to escape identifiers

I have an extra filter in Django with a where clause, but the table name is dynamic.

filtered_queryset = queryset.extra(
    where=[
        f'({table_name}.modified_on, {table_name}.id) > (%s, %s)',
    ],
    params=(after_ts, after_id),
)

How can I best avoid the f-string to make really sure it's not open to SQL injection?

I would like the SQL to have the tuple comparison, rather than multiple > and >= with AND. From previous testing, it seemed more likely to use multi-column indexes.

(This is part of some custom pagination code, where the cursor is essentially a tuple of a datetime and id)

Upvotes: 0

Views: 419

Answers (3)

Michal Charemza
Michal Charemza

Reputation: 27022

As in this answer, extra can be avoided by using annotate, and the table name is taken from the Queryset's model internally, which avoids having to worry about escaping any identifiers:

from django.db.models import F, Func, TextField

col_a_col_b = Func(F('col_a'), F('col_b'), function='ROW', output_type=TextField())
col_a_col_b_from = Func(col_a_value, col_b_value, function='ROW')

filtered_queryset = queryset
    .annotate(col_a_col_b=col_a_col_b)
    .filter(col_a_col_b__gt=col_a_col_b_from)
    .order_by('col_a', 'col_b')

(Apparently in Django 3.2+ alias can be used instead of extra, and the output_field hack avoided)

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562631

First, validate your table_name variable against a list of known table names.

Also, use double-quotes as identifier delimiters, which allows table_name to be an SQL reserved keyword, or contain spaces or punctuation. These are all legal in SQL, as long as you use identifier delimiters.

    f'("{table_name}".modified_on, "{table_name}".id) > (%s, %s)',

See https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

However, if your table name contains a literal " character, this could cause trouble, because the " symbol would be interpreted as the terminating identifer delimiter. So it's up to you to filter your table_name, replacing a literal " with "".

Upvotes: 1

physicalattraction
physicalattraction

Reputation: 6858

Why don't you write it as a filter?

filtered_queryset = queryset.filter(
    Q(modified_on__gt=after_ts) |
    Q(Q(modified_on__gte=after_ts) & Q(id__gt=after_id))
)

PS: It's a bit unclear what your query is trying to do, I think it's this, but maybe you want to filter on something else.

Upvotes: 2

Related Questions