python - Django filter(icontains).extra 产生错误的 SQL 语法(无引号)

Question

我正在尝试构建自定义查询，并希望在filter()之后应用.extra()。该语句如下所示:

V.objects.filter(v_id__product__icontains=name)

现在它会生成有效的 SQL，但是名称周围没有引号:

WHERE `v_id`.`product` LIKE %xxx%

但是当我添加 .extra() 语句时:

V.objects.filter(id__product__icontains=name).extra(where=[concat_str],params=[version,'%','%'])

，查询无效，因为 %xxx% 周围没有引号:

WHERE `v_id`.`product` LIKE %xxx% AND 'yyy' LIKE CONCAT('%',version,'%')

我在这里需要的只是在 %xxx% 周围添加单引号，以使其有效:

WHERE `vulnerabilities_cpeid`.`product` LIKE '%xxx%' AND 'yyy' LIKE CONCAT('%',version,'%')

但是，我只是不知道如何强制 Django 在使用 icontains 时将 %xxx% 放入单引号中。任何帮助表示赞赏。预先感谢您。

完整回溯:

INFO 2014-07-21 11:33:55,515 views: SELECT `vulnerabilities_vulnerability`.`identifier` FROM `vulnerabilities_vulnerability` INNER JOIN `vulnerabilities_vulnerability_cpe_id` ON (`vulnerabilities_vulnerability`.`id` = `vulnerabilities_vulnerability_cpe_id`.`vulnerability_id`) INNER JOIN `vulnerabilities_cpeid` ON (`vulnerabilities_vulnerability_cpe_id`.`cpeid_id` = `vulnerabilities_cpeid`.`id`) WHERE (`vulnerabilities_cpeid`.`product` LIKE %accountsservice%  AND '0.6.15-2ubuntu9.7' LIKE CONCAT('%',version,'%'))
ERROR 2014-07-21 11:33:55,517 django.request: Internal Server Error: /vulndb/inventory/
Traceback (most recent call last):
  File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/core/handlers/base.py", line 111, in get_response
    response = callback(request, *callback_args, **callback_kwargs)
  File "/home/sapegin/vulndb_mercurial/vulndb/vulndb/vulnerabilities/views.py", line 1650, in inventory
    if ((vulnerabilities is not None) and (vulnerabilities.count() > 0)):
  File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/models/query.py", line 351, in count
    return self.query.get_count(using=self.db)
  File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/models/sql/query.py", line 418, in get_count
    number = obj.get_aggregation(using=using)[None]
  File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/models/sql/query.py", line 384, in get_aggregation
    result = query.get_compiler(using).execute_sql(SINGLE)
  File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/models/sql/compiler.py", line 818, in execute_sql
    cursor.execute(sql, params)
  File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/backends/util.py", line 40, in execute
    return self.cursor.execute(sql, params)
  File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/django/db/backends/mysql/base.py", line 119, in execute
    return self.cursor.execute(query, args)
  File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/MySQL_python-1.2.4-py2.6-linux-x86_64.egg/MySQLdb/cursors.py", line 201, in execute
    self.errorhandler(self, exc, value)
  File "/home/sapegin/vulndb_mercurial/vulndb/HANA/PYTHON/Python/lib/python2.6/site-packages/MySQL_python-1.2.4-py2.6-linux-x86_64.egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
DatabaseError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0.6.15-2ubuntu9.7'' LIKE CONCAT(''%'',version,''%''))' at line 1")
ERROR 2014-07-21 11:33:55,517 django.request: Internal Server Error: /vulndb/inventory/

Answer 1

我敢打赌，错误是您的 .extra 调用参数中有额外的引号。删除 %s 周围的引号，看看是否可以解决问题。