Defect #25416

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

Added by Michael Medin 8 months ago. Updated 5 months 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 8 months ago

Adds a User.admin scope (#25416).

History

#1 Updated by Toshi MARUYAMA 8 months 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 8 months 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 8 months ago

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

Please contact docker provider.

#4 Updated by Michael Medin 8 months 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 8 months 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 8 months 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 8 months ago

  • File 20757.jpg added

#8 Updated by Toshi MARUYAMA 8 months ago

  • File deleted (20757.jpg)

#9 Updated by Go MAEDA 8 months 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 8 months 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 8 months ago

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

#12 Updated by Go MAEDA 8 months ago

  • Target version changed from 4.1.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 8 months 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 8 months ago

Jean-Phillipe, thanks for much smarter solution!

#15 Updated by Jean-Philippe Lang 5 months ago

  • Subject changed from "My account" broken with mysql (keyword admin should be escaped on MySQL 8.0) to "My account" broken with MySQL 8.0 (keyword admin should be escaped)

Also available in: Atom PDF