Django extra where: how to escape identifiers-CodePudding

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)

CodePudding user response：

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.

CodePudding user response：

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 "".