Django custom for complex Func (sql function)

Question

In the process of finding a solution for Django ORM order by exact, I created a custom django Func:

from django.db.models import Func

class Position(Func):
    function = 'POSITION'
    template = "%(function)s(LOWER('%(substring)s') in LOWER(%(expressions)s))"
    template_sqlite = "instr(lower(%(expressions)s), lower('%(substring)s'))"

    def __init__(self, expression, substring):
        super(Position, self).__init__(expression, substring=substring)

    def as_sqlite(self, compiler, connection):
        return self.as_sql(compiler, connection, template=self.template_sqlite)

which works as follows:

class A(models.Model):
    title = models.CharField(max_length=30)

data = ['Port 2', 'port 1', 'A port', 'Bport', 'Endport']
for title in data:
    A.objects.create(title=title)

search = 'port'
qs = A.objects.filter(
        title__icontains=search
    ).annotate(
        pos=Position('title', search)
    ).order_by('pos').values_list('title', flat=True)
# result is
# ['Port 2', 'port 1', 'Bport', 'A port', 'Endport'] 

But as @hynekcer commented:

"It crashes easily by ') in '') from myapp_suburb; drop ... expected that the name of the app is "myapp and autocommit is enabled."

The main problem is that extra data (substring) got into the template without sqlescape which leaves the app vulnerable to SQL injection attacks.

I cannot find which is the Django way to protect from that.

---

I created a repo (djposfunc) where you can test any solution.

Answer 1

TL;DR: All examples with Func() in Django docs can be easily used to safely implement other similar SQL functions with one argument. All builtin Django database fuctions and conditional functions that are descendants of Func() are also safe by design. Application beyond this limit needs comment.

---

The class Func() is the most general part of Django Query expressions. It allows to implement almost any function or operator into Django ORM some way. It is like a Swiss Army knife, very universal, but one must be little more attentive to not cut himself, than with a specialized tool (like an electric cutter with optical barrier). It is still much more secure then to forge an own tool by hammer from piece of iron, if once an "upgraded" "secure" pocket knife does not fit into pocket.

---

Security notes

• The short documentation for Func(*expressions, **extra) with examples should be read first. (I recommend here the development docs for Django 2.0 where is recently added more security information, including Avoiding SQL injection, related exactly to your example.)

• All positional arguments in *expressions are compiled by Django, that is Value(string) are moved to parameters, where they are correctly escaped by database driver.

• Other strings are interpreted as field names F(name), then prefixed by right table_name. alias dot, eventually a join to that table is added and names are treated by quote_name() function.
• The problem is that the documentation in 1.11 is still simple, the seductive parameters **extra and **extra_context are documented vaguely. They can be used only for simple parameters that will be never "compiled" and never go through SQL params. Numbers or simple strings with safe characters without apostrophe, backslash or percent are good. It can not be a field name, because it will be not unambiguous, neither joined. It is safe for previously checked numbers and fixed strings like 'ASC'/'DESC', timezone names and other values like from a drop down list. There is still a weak point. Drop down list values must be checked at the server side. Also numbers must be verified that they are numbers, not a numeric string like '2' because all database functions silently accept an omitted numeric string instead of number. If a false "number" is passed '0) from my_app.my_table; rogue_sql; --' then the injection is over. Note that the rogue string doesn't contain any very prohibitive character in this case. User supplied numbers must be checked specifically or the value must be passed through positional expressions.
• It is safe to specify function name and arg_joiner string attributes of Func class or the same function and arg_joiner parameters of Func() call. The template parameter should never contain apostrophes around substituted parameter expressions inside parentheses: ( %(expressions)s ), because apostrophes are added by the database driver if necessary, but additional apostrophes can cause that it usually doesn't work correctly, but sometimes it could be overlooked and that would lead to another security issue.

Notes not related to security

• Many simple builtin functions with one argument do not look as simple as possible because they are derived from multi-purpose descendants of Func. For example Length is a function that can be used also as lookup Transform.

  class Length(Transform):
      """Return the number of characters in the expression."""
      function = 'LENGTH'
      output_field = fields.IntegerField()  # sometimes specified the type
      # lookup_name = 'length'  # useful for lookup not for Func usage
  

  Lookup transformation applies the same function to the left and right side of lookup.

  # I'm searching people with usernames longer than mine 
  qs = User.objects.filter(username__length__gt=my_username)
  

• The same keyword arguments that can be specified in Func.as_sql(..., function=..., template=..., arg_joiner=...) can be specified already in Func.__init__() if not overwritten in custom as_sql() or they can be set as attributes of a custom descendant class of Func.

• Many SQL database functions have a verbose syntax like POSITION(substring IN string) because it simplifies readability if named parameters are not supported like POSITION($1 IN $2) and a brief variant STRPOS(string, substring) (por postgres) or INSTR(string, substring) (for other databases) that is easier implemented by Func() and the readability is fixed by the Python wrapper with __init__(expression, substring).

• Also very complicated functions can be implemented by a combination of more nested functions with simple arguments safe way: Case(When(field_name=lookup_value, then=Value(value)), When(...),... default=Value(value)).