Defect #14902

Potential invalid SQL error with invalid group_ids

Added by Ty You about 4 years ago. Updated about 4 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:

0%

Category:Issues
Target version:2.3.4
Resolution:Fixed Affected version:

Description

In 2.3.2-devel, when a tracker is either a parent or child record and has no assignee, non-admin users receive an Internal 500 error wafter clicking on the Subject of the tracker in the Issues list. Details of the error are as follows:

Started GET "/issues/50" for 192.168.50.63 at Fri Sep 13 14:16:44 -0500 2013
Processing by IssuesController#show as HTML
Parameters: {"id"=>"50"}
Current user:XYZPDQ (id=14)
Rendered issues/_action_menu.html.erb (3.7ms)
Rendered issues/show.html.erb within layouts/base (13.0ms)
Completed 500 Internal Server Error in 252ms

ActionView::Template::Error (Mysql::Error: 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 ')))) OR (projects.is_public = 1 AND ((issues.is_private = 0 OR issues.author_id ' at line 1: SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`is_private` AS t0_r22, `issues`.`closed_on` AS t0_r23, `projects`.`id` AS t1_r0, `projects`.`name` AS t1_r1, `projects`.`description` AS t1_r2, `projects`.`homepage` AS t1_r3, `projects`.`is_public` AS t1_r4, `projects`.`parent_id` AS t1_r5, `projects`.`created_on` AS t1_r6, `projects`.`updated_on` AS t1_r7, `projects`.`identifier` AS t1_r8, `projects`.`status` AS t1_r9, `projects`.`lft` AS t1_r10, `projects`.`rgt` AS t1_r11, `projects`.`inherit_members` AS t1_r12, `projects`.`dmsf_description` AS t1_r13 FROM `issues` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`root_id` = 56 AND (`issues`.`lft` <= 6 AND `issues`.`rgt` >= 7) AND (`issues`.id != 50) AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (7,39,38,34,41,40,53,8,9,10,11,25,26,27,37,33,13,14,12,48,47,36,35,30) AND ((issues.is_private = 0 OR issues.author_id = 14 OR issues.assigned_to_id IN (14,,)))) OR (projects.is_public = 1 AND ((issues.is_private = 0 OR issues.author_id = 14 OR issues.assigned_to_id IN (14,,)))) OR (projects.id IN (31,53,52,45,44,50,51,49,29,30) AND ((issues.is_private = 0 OR issues.author_id = 14 OR issues.assigned_to_id IN (14,,))))))) ORDER BY `issues`.`lft`):
22: <%= avatar(@issue.author, :size => "50") >
23:
24: <div class="subject">
25: <
= render_issue_subject_with_tree(@issue) >
26: </div>
27: <p class="author">
28: <
= authoring @issue.created_on, @issue.author %>.
app/helpers/issues_helper.rb:66:in `render_issue_subject_with_tree'
app/views/issues/show.html.erb:25:in `_app_views_issues_show_html_erb__407699512_73517660'
app/controllers/issues_controller.rb:123:in `show'
app/controllers/issues_controller.rb:120:in `show'

_____________________________________________
Redmine issue reported by Todd Young on 9/13/13

Associated revisions

Revision 12162
Added by Jean-Philippe Lang about 4 years ago

Prevents invalid SQL with invalid group_ids (#14902).

Revision 12213
Added by Jean-Philippe Lang about 4 years ago

Merged r12162 from trunk (#14902).

History

#1 Updated by Jean-Philippe Lang about 4 years ago

  • Assignee set to Jean-Philippe Lang

Could you try this patch?

Index: app/models/issue.rb
===================================================================
--- app/models/issue.rb    (revision 12132)
+++ app/models/issue.rb    (working copy)
@@ -108,10 +108,10 @@
         when 'all'
           nil
         when 'default'
-          user_ids = [user.id] + user.groups.map(&:id)
+          user_ids = [user.id] + user.groups.map(&:id).compact
           "(#{table_name}.is_private = #{connection.quoted_false} OR #{table_name}.author_id = #{user.id} OR #{table_name}.assigned_to_id IN (#{user_ids.join(',')}))" 
         when 'own'
-          user_ids = [user.id] + user.groups.map(&:id)
+          user_ids = [user.id] + user.groups.map(&:id).compact
           "(#{table_name}.author_id = #{user.id} OR #{table_name}.assigned_to_id IN (#{user_ids.join(',')}))" 
         else
           '1=0'

#2 Updated by Andreas Hupfau about 4 years ago

I'm not the author, but I had the same issue and can confirm the patch is helping!

#3 Updated by Etienne Massip about 4 years ago

  • Target version set to Candidate for next minor release

#4 Updated by Jean-Philippe Lang about 4 years ago

  • Subject changed from Trackers without Assignees get Internal 500/MySQL error when parents or children to Potential invalid SQL error with invalid group_ids
  • Target version changed from Candidate for next minor release to 2.3.4

Patch applied in r12162, thanks for the feedback.

#5 Updated by Jean-Philippe Lang about 4 years ago

  • Status changed from New to Closed
  • Resolution set to Fixed

Merged.

Also available in: Atom PDF