Bear Brown Bear Brown - 3 years ago 160
Python Question

Django custom for complex Func (sql function)

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:

search = 'port'
qs = A.objects.filter(
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 (
) 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 Source

basis on the John Moutafis ideas, final function is (inside the __init__ method we use Values for safety result.)

from django.db.models import Func, F, Value
from django.db.models.functions import Lower

class Instr(Func):
    function = 'INSTR'

    def __init__(self, string, substring, insensitive=False, **extra):
        if not substring:
            raise ValueError('Empty substring not allowed')
        if not insensitive:
            expressions = F(string), Value(substring)
            expressions = Lower(string), Lower(Value(substring))
        super(Instr, self).__init__(*expressions)

    def as_postgresql(self, compiler, connection):
        return self.as_sql(compiler, connection, function='STRPOS')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download