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.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…