Defect #25416

"My account" broken with mysql (keyword admin should be escaped on MySQL 8.0)

Added by Michael Medin about 1 month ago. Updated 20 days ago.

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

0%

Category:Database
Target version:3.4.0
Resolution:Fixed Affected version:3.3.2

Description

Clicking "My account" when using a mysql database gives the folowing error:

Completed 500 Internal Server Error in 383ms (ActiveRecord: 12.9ms)

ActionView::Template::Error (Mysql2::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 
'admin = 1 AND id <> 4) LIMIT 1' at line 1: 
SELECT  1 AS one FROM `users` WHERE `users`.`type` IN ('User', 'AnonymousUser') AND `users`.`status` = 1 AND (admin = 1 AND id <> 4) LIMIT 1):
    3: <p><%=l(:field_login)%>: <strong><%= link_to_user(@user, :format => :username) %></strong><br />
    4: <%=l(:field_created_on)%>: <%= format_time(@user.created_on) %></p>
    5:
    6: <% if @user.own_account_deletable? %>
    7:   <p><%= link_to(l(:button_delete_my_account), {:action => 'destroy'}, :class => 'icon icon-del') %></p>
    8: <% end %>
    9:
  app/models/user.rb:687:in `own_account_deletable?'
  app/views/my/_sidebar.html.erb:6:in `_app_views_my__sidebar_html_erb___2869233383891145181_70035919334040'
  app/views/my/account.html.erb:57:in `block in _app_views_my_account_html_erb___4301926150472292574_70036033743920'
  app/views/my/account.html.erb:56:in `_app_views_my_account_html_erb___4301926150472292574_70036033743920'
  lib/redmine/sudo_mode.rb:63:in `sudo_mode'

The fix is to add escapings `` for the admin keyword in the sql query like so:

[extmcm@ux310tas001 ~]$ diff user.rb user_fixed.rb
114d113
<   validates_length_of :identity_url, maximum: 255
687c686
<       (!admin? || User.active.where("admin = ? AND id <> ?", true, id).exists?)
---
>       (!admin? || User.active.where("`admin` = ? AND id <> ?", true, id).exists?)

0001-Fixed-SQL-99-reserved-word-admin-conflict.patch Magnifier (6.82 KB) Go MAEDA, 2017-03-26 06:03

Associated revisions

Revision 16449
Added by Jean-Philippe Lang 20 days ago

Adds a User.admin scope (#25416).

History

#1 Updated by Toshi MARUYAMA about 1 month ago

  • Status changed from New to Needs feedback
  • Priority changed from Urgent to Normal

What MySQL version do you use?

#2 Updated by Michael Medin about 1 month ago

Version 8...

sudo docker run --detach \
    --name redmine-mysql \
    --env MYSQL_ROOT_PASSWORD=REPLACE_WITH_ROOT_PASSWORD \
    --env MYSQL_DATABASE=redmine \
    mysql:8
sudo docker run --detach \
    --name redmine \
    --publish 80:3000 \
    --link redmine-mysql:mysql \
    redmine

#3 Updated by Toshi MARUYAMA about 1 month ago

  • Status changed from Needs feedback to Closed
  • Resolution set to Invalid

Please contact docker provider.

#4 Updated by Michael Medin about 1 month ago

Huh?

The error was from redmine. Dokerinfo was to help easily replicate the error...
Not sure why it would be related to docker?
Or is mysql 8 not supported?

ADMIN is a reserved keyword in MYSQL 8: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

#5 Updated by Michael Medin about 1 month ago

  • Status changed from Closed to Reopened

reopening as this is still a bug in redmine as far as I am concerned...

#6 Updated by Go MAEDA about 1 month ago

  • Subject changed from "My account" broken with mysql (keyword admin should be escaped on mysql database) to "My account" broken with mysql (keyword admin should be escaped on MySQL 8.0)
  • Category changed from Accounts / authentication to Database
  • Resolution deleted (Invalid)

Michael Medin wrote:

ADMIN is a reserved keyword in MYSQL 8: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Thank you for pointing it out.

Unfortunately current versions of Redmine don't support MySQL 8.0. Please use MySQL 5.0 - 5.5 for now.
http://www.redmine.org/projects/redmine/wiki/RedmineInstall#Supported-database-back-ends

#7 Updated by udom bunma 30 days ago

  • File 20757.jpg added

#8 Updated by Toshi MARUYAMA 30 days ago

  • File deleted (20757.jpg)

#9 Updated by Go MAEDA 29 days ago

"admin" is reserved word in SQL 99 and MySQL.
Should we change column name? (e.g. "admin" to "is_admin")

#10 Updated by Go MAEDA 28 days ago

This is a patch to fix a conflict with SQL-99 reserved word by renaming the column "admin" to "is_admin".

The reason for using alias_attribute in the patch is to keep compatibility with existing plugins which call User#admin? method.

#11 Updated by Toshi MARUYAMA 28 days ago

  • Target version changed from Candidate for next major release to 3.5.0

#12 Updated by Go MAEDA 27 days ago

  • Target version changed from 3.5.0 to 3.4.0

Since this issue is critical, I think it would be better to deliver this fix as soon as possible.
Setting target version to 3.4.0.

#13 Updated by Jean-Philippe Lang 20 days ago

  • Status changed from Reopened to Closed
  • Assignee set to Jean-Philippe Lang
  • Resolution set to Fixed

I won't rename the column because `admin` is now a reserved word in mysql (BTW I don't see it in the list of SQL99 reserved words). r16449 fixes the error on my account for Mysql 8 by escaping the column name.

#14 Updated by Go MAEDA 20 days ago

Jean-Phillipe, thanks for much smarter solution!

Also available in: Atom PDF