复杂功能的Django自定义（SQL函数）

本文介绍了复杂功能的Django自定义（SQL函数）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 正在寻找 Django ORM精确排序，我创建了一个自定义django函数：In the process of finding a solution for Django ORM order by exact, I created a custom django Func:from django.db.models import Funcclass 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) ：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']但正如@hynekcer所说：But as @hynekcer commented:主要问题是没有sqlescape的额外数据（子字符串）进入模板，这会使应用程序容易受到SQL注入攻击。The main problem is that extra data (substring) got into the template without sqlescape which leaves the app vulnerable to SQL injection attacks.我找不到Django的保护方法。I cannot find which is the Django way to protect from that.我创建了 repo（djposfunc），您可以在其中测试任何解决方案。I created a repo (djposfunc) where you can test any solution.推荐答案 TL; DR： Func（） 可以轻松地用于通过一个参数安全地实现其他类似的SQL函数。 所有内置的Django 数据库功能和条件函数，它们是 Func（ ）在设计上也很安全。超出此限制的应用程序需要注释。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.类 Func（） 是Django Query表达式中最通用的部分。它允许以某种方式在Django ORM中实现几乎所有函数或运算符。它非常像瑞士军刀，非常通用，但是与专用工具（例如带有光学屏障的电动切割机）相比，一定要更加注意不割伤自己。如果一旦升级后的安全小刀无法放入口袋，则用铁锤锻造自己的工具仍然更加安全。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.安全说明 Func（* expressions，** extra） 和示例应首先阅读。 （我在这里推荐Django 2.0的开发文档，最近在其中添加了更多安全性信息，包括避免SQL注入，与您的示例完全相关。）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.) *表达式 由Django 编译，即 Value（string）移至与安全无关的注释 Length 是可以用作查找 转换 。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 mineqs = User.objects.filter(username__length__gt=my_username) 可以在 Func.as_sql（...，function = ...，template = ...，arg_joiner = ...）中指定的关键字参数可以是如果已在 Func .__ init __（）中指定不能在自定义as_sql（）中覆盖，也可以将它们设置为 Func 的自定义后代类的属性。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.许多SQL数据库函数都具有 POSITION（substring IN string）这样的冗长语法，因为如果不支持诸如 POSITION（ $ 1 IN $ 2）和一个简短的变体 STRPOS（string，substring）（por postgres）或 INSTR（string，substring） ）（对于其他数据库），通过 Func（）更容易实现，并且可读性由Python包装器使用固定。 __init __（表达式，子字符串）。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).也可以通过将更多嵌套函数与简单参数组合使用来实现非常复杂的函数方式： Case（When（field_name = lookup_value，then = Value（value）），When（...），... default = Value（value））。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)). 这篇关于复杂功能的Django自定义（SQL函数）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！