Opened 6 years ago

Closed 6 years ago

#10724 closed (wontfix)

Derived quries used in extra(tables=[]) should not be escaped

Reported by: bendavis78 Owned by: nobody
Component: Uncategorized Version: master
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

example:

subquery_earned = "(SELECT user_id, SUM(points) AS points FROM rewards_userpointearning) AS points_earned"
subquery_spent = "(SELECT user_id, SUM(points) AS points FROM rewards_order) AS points_spent"
condition = "points_earned.user_id = auth_user.id AND points_spent.user_id = auth_user.id"  #join
select = {'points_balance' : 'points_earned.points - points_spent.points'}
q = User.objects.get_query_set().extra(select=select, tables=[subquery_earned, subquery_spent], where=[condition])
q = q.order_by('-points_balance')

The resulting query is:

SELECT (points_earned.points - points_spent.points) AS `points_balance`, `auth_user`.`id` 
FROM 
  `auth_user` , 
  `(SELECT user_id, SUM(points) AS points FROM rewards_userpointearning) AS points_earned` , 
  `(SELECT user_id, SUM(points) AS points FROM rewards_order) AS points_spent` 
WHERE points_earned.user_id = auth_user.id AND points_spent.user_id = auth_user.id 
ORDER BY `points_balance` DESC

In MySQL, this results in the error:

ProgrammingError: (1103, "Incorrect table name '(SELECT user_id, SUM(points) AS points FROM rewards_userpointearning) AS points_earned'")

Django should detect whether a table that's been passed through extra()'s tables parameter is a real table or not, and only add backticks when necessary.

Change History (1)

comment:1 Changed 6 years ago by Alex

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to wontfix
  • Status changed from new to closed

Django is not bulding a SQL parser, we are not going to try to detect when you have a subquery there.

Note: See TracTickets for help on using tickets.
Back to Top