Code

Opened 5 years ago

Closed 5 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.

Attachments (0)

Change History (1)

comment:1 Changed 5 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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.