DRF dynamic filtering

Question

I need dynamic filtering in DRF that should allow using parenthesis for defining operations precedence and use any combination of the available fields in model. Operations are: and, or, eq (equal), ne (not equal), gt (greater than), lt (less than)

example: "(date eq '2016-05-01') AND ((number_of_calories gt 20) OR (number_of_calories lt 10))"

How can I achieve this? what is best way?

Currently I have below solution but it's not good approach as it's vulnerable to SQL Injection:

utils.py

mappings = {
    ' eq ': ' = ',
    ' ne ': ' != ',
    ' gt ': ' > ',
    ' lt ': ' < ',
    ' gte ': ' >= ',
    ' lte ': ' <= ',
}

def convert_string(query: str) -> Optional[str]:
    if query and isinstance(query, str):
        pattern_drop = re.compile(r"drop\s+table\s*\w*")
        pattern_alter = re.compile(r"alter\s+table\s+\w+")
        pattern_delete = re.compile(r"delete\s+from\s+\w+")
        pattern_update = re.compile(r"update\s+\w+\s+set\s+\w+")
        pattern_insert = re.compile(r"insert\s+into\s+\w+")
        pattern_select = re.compile(r"select\s+\w+\s+from\s+")
        query_lower = query.lower()
        if '--' in query_lower or '/*' in query_lower or \
                pattern_drop.match(query_lower) or pattern_alter.match(query_lower) or \
                pattern_update.match(query_lower) or pattern_insert.match(query_lower) or \
                pattern_delete.match(query_lower) or pattern_select.match(query_lower):
            return None
        for expression, operation in mappings.items():
            query = query.replace(expression, operation)

    return query

views.py

def get_queryset(self):
    q_string = self.request.data['query']
    # q_string = "(date eq '2016-05-01') AND ((number_of_calories gt 20) OR (number_of_calories lt 10))"

    query = convert_string(q_string)
    # just replace 'eq' with '=', 'ne' with '!=', and so on ...
    # query = "(date = '2016-05-01') AND ((number_of_calories > 20) OR (number_of_calories < 10))"

    users = Users.objects.raw('SELECT * FROM Users WHERE ' + query)
    return users

Answer 1

For parsing a query string like:

string = "((num_of_pages gt 20) OR (num_of_pages lt 10)) AND (date gt '2016-05-01')"

you can use the pyparsing package (not an expert but very powerful library) with django Q objects:

1. parsing code:

import pyparsing as pp
import operator as op

from django.db.models import Q

word = pp.Word(pp.alphas, pp.alphanums + "_-*'")
operator = pp.oneOf('lt gt eq').setResultsName('operator')
number = pp.pyparsing_common.number()
quoted = pp.quotedString().setParseAction(pp.removeQuotes)
term = (word | number | quoted)

key = term.setResultsName('key')
value = term.setResultsName('value')

group = pp.Group(key + operator + value)

def q_item(item):
    """Helper for create django Q() object"""
    k = f'{item.key}__{item.operator}'
    v = item.value
    return Q(**{k: v})


class BaseBinary:

    def __init__(self, tokens):
        self.args = tokens[0][0::2]

    def __repr__(self):
        return f'{self.__class__.__name__}({self.symbol}):{self.args}'

    def evaluate(self):
        a = q_item(self.args[0]) if not isinstance(self.args[0], BaseBinary) else self.args[0].evaluate()
        b = q_item(self.args[1]) if not isinstance(self.args[1], BaseBinary) else self.args[1].evaluate()
        return self.op(a, b)


class BoolNotOp(BaseBinary):
    symbol = 'NOT'
    op = op.not_

    def __init__(self, tokens):
        super().__init__(tokens)
        self.args = tokens[0][1]

    def evaluate(self):
        a = q_item(self.args) if not isinstance(self.args, BaseBinary) else self.args.evaluate()
        return ~a


class BoolAndOp(BaseBinary):
    symbol = 'AND'
    op = op.and_


class BoolOrOp(BaseBinary):
    symbol = 'OR'
    op = op.or_


expr = pp.infixNotation(group,
                        [('NOT', 1, pp.opAssoc.RIGHT, BoolNotOp),
                         ('AND', 2, pp.opAssoc.LEFT, BoolAndOp),
                         ('OR', 2, pp.opAssoc.LEFT, BoolOrOp)])

Now given a string like:

string = "(date gt '2016-05-01') AND ((num_of_pages gt 20) OR (num_of_pages lt 10))"

to the parser:

parser = expr.parseString(string)[0]
print(parser.evaluate())

give us our Q objects:

(AND: ('date__gt', '2016-05-01'), (OR: ('num_of_pages__gt', 20), ('num_of_pages__lt', 10)))

ready to be filtered

class Book(models.Model):
    title = models.CharField(max_length=200)
    counter = models.PositiveIntegerField(default=0)
    date = models.DateField(auto_now=True)
    num_of_pages = models.PositiveIntegerField(default=0)
    
qs = Book.objects.filter(parser.evaluate())
print(qs.query)

SELECT "core_book"."id", "core_book"."title", "core_book"."counter", "core_book"."date", "core_book"."num_of_pages" FROM "core_book" WHERE ("core_book"."date" > 2016-05-01 AND ("core_book"."num_of_pages" > 20 OR "core_book"."num_of_pages" < 10))

P.S not fully tested.