CVE-2015-2972 (Ruby, published 2015-07-19T15:59Z)
Description: value: Multiple SQL injection vulnerabilities in Sysphonic Thetis before 2.3.0 allow remote attackers to execute arbitrary SQL commands via unspecified vectors.
Severity: HIGH (CVSS v3 base score: )
Weakness: CWE-89 \u2014 Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
Repository: https://github.com/sysphonic/thetis
Files changed:
app/controllers/items_controller.rb
app/controllers/locations_controller.rb
app/controllers/templates_controller.rb
app/controllers/zeptair_post_controller.rb
app/helpers/items_helper.rb
app/helpers/sql_helper.rb
app/helpers/templates_helper.rb
app/helpers/tree_element.rb
app/helpers/zeptair_dist_helper.rb
app/models/email.rb
app/models/folder.rb
app/models/group.rb
app/models/location.rb
app/models/mail_account.rb
app/models/mail_filter.rb
app/models/mail_folder.rb
app/models/office_map.rb
app/models/official_title.rb
app/models/paid_holiday.rb
app/models/research.rb
app/models/setting.rb
app/models/team.rb
app/models/timecard.rb
app/models/toy.rb
app/models/user.rb
app/views/folders/show_tree.html.erb
app/views/frames/_footer.html.erb
app/views/groups/show_tree.html.erb
app/views/mail_filters/list.html.erb
app/views/mail_folders/show_tree.html.erb
config/locales/de.yml
config/locales/en.yml
config/locales/ja.yml
Commit message: Reinforcement of security.
Signed-off-by: MORITA Shintaro <shin@sysphonic.com>


Vulnerable code:
      params.delete(:controller)
      params.delete(:action)
      params.delete(:id)
      params[:action] = params[:from_action]
      redirect_to(params)
        key_quot = ActiveRecord::Base.connection.quote(key)
    return unless request.post?

      target_user = User.where("zeptair_id=#{zeptair_id}").first
      target_user = User.where("zeptair_id=#{zeptair_id}").first
    copied_items = Item.where("user_id=#{user_id} and source_id=#{source_id}").order('created_at DESC').to_a
      con = "(parent_id=#{my_folder.id}) and (name='#{Item.copies_folder}')"
      con = "(parent_id=#{my_folder.id}) and (name='#{Item.copies_folder}')"
    key = ActiveRecord::Base.connection.quote("%#{SqlHelper.escape_for_like(keyword)}%")
      tmpl_folder = Folder.where("folders.name='#{TMPL_ROOT}'").first
    tmpl_folder = Folder.where("folders.name='#{TMPL_ROOT}'").first
    tmpl_folder = Folder.where("folders.name='#{TMPL_ROOT}'").first
      con = "(parent_id=#{tmpl_folder.id}) and (name='#{name}')"
      nodes = klass.where("parent_id=#{node_id}").order('xorder ASC, id ASC').to_a
    con << "(parent_id=#{node_id})"
      comment = Comment.where("(user_id=#{user_id}) and (item_id=#{item_id}) and (xtype='#{Comment::XTYPE_DIST_ACK}')").first
    return Comment.where("(item_id=#{item_id}) and (xtype='#{Comment::XTYPE_DIST_ACK}')").count
    return Comment.where("(item_id=#{item_id}) and (xtype='#{Comment::XTYPE_DIST_ACK}') and (message='#{ack_msg}')").count
      count = Email.where("mail_account_id=#{mail_account_id}").count
          emails += Email.where("mail_account_id=#{mail_account_id}").order('updated_at ASC').limit(over_num).to_a
#        emails = Email.where("mail_account_id=#{mail_account_id}").order('updated_at ASC').to_a
    con = "user_id=#{user_id}"
      where = " where (parent_id = #{tree_id})"
      where << " and ((xtype is null) or not(xtype = '#{XTYPE_GROUP}' or xtype = '#{XTYPE_USER}'))"
      where = " where (Folder.parent_id = #{tree_id})"
      where <<      "(Folder.xtype = '#{XTYPE_GROUP}' and Folder.owner_id = #{group_id})"
      where <<      "(Folder.xtype = '#{XTYPE_USER}' and  Folder.owner_id = User.id and #{SqlHelper.get_sql_like(['User.groups'], "|#{group_id}|")})"
    con << "parent_id=#{tree_id}"
      con << "parent_id=#{folder_id}"
      return Folder.where("(owner_id=#{group_id}) and (xtype='#{Folder::XTYPE_GROUP}')").first
      con = "group_id=#{group_id}"
    con << "(mail_account_id=#{mail_account_id})"
    con = "user_id=#{user_id}"
    con << "(user_id=#{user_id})"
    con << "(mail_account_id=#{mail_account_id})"
    con << "(user_id=#{user_id})"
    con << "(mail_account_id=#{mail_account_id})"
    con << "(user_id=#{user_id})"
        office_map = OfficeMap.where("group_id=#{group_id}").first
        sql << " where group_id=#{group_id}"
      con << "(group_id=#{group_id})"
      con << "(user_id=#{user_id})"
        con << "(year=#{fiscal_year})"
      con << "(user_id=#{user_id})"
      con << "(year=#{fiscal_year})"
      con = "(user_id=#{user_id}) and (year < #{year})"
    return Research.where("user_id=#{user_id}").first
#Copyright:: Copyright (c) 2007-2011 MORITA Shintaro, Sysphonic. All rights reserved.
    con << "(user_id=#{user_id})"
    con << "(user_id=#{user_id})"
    con << "(user_id=#{user_id})"
    con << "(group_id=#{group_id})"
    con << "(group_id=#{group_id})"
    con << "(group_id=#{group_id})"
      return Folder.where("(owner_id=#{team_id}) and (xtype='#{Folder::XTYPE_TEAM}')").first
      user_con_a << "(user_id=#{user_id})"
      con = "(user_id=#{user_id}) and (date='#{date_s}')"
    con = "(user_id=#{user_id}) and (date >= '#{start_s}') and (date <= '#{end_s}')"
    con = "(user_id=#{user.id}) and (xtype='#{xtype}') and (target_id=#{target_id})"
   return Folder.where("(owner_id=#{user_id}) and (xtype='#{Folder::XTYPE_USER}')").first
    User.update_all("xorder=#{order}", con)
            <input type="button" value="<%= t('btn.move') %>" onclick="doMove();" style="width:80px;" />
function doMove()
  thetisBox.progress = true;
  thetisBox.setAdditionalParams(new Array("id="+folder_id, "authenticity_token=<%= form_authenticity_token %>"));
  thetisBox.show("CENTER", "", "TREE", "<%= url_for(:controller => 'folders', :action => 'move') %>", "<%= t('folder.select_dest')%>", "");
  doUpdateView("get", "<%= url_for(:controller => 'folders', :action => 'set_auth_users') %>", addParams);
  doUpdateView("get", "<%= url_for(:controller => 'folders', :action => 'set_auth_groups') %>", addParams);
  doUpdateView("get", "<%= url_for(:controller => 'folders', :action => 'set_auth_teams') %>", addParams);
    <td style="text-align:center; color:white; font-size:8px; line-height:1.0;">
            <input type="button" value="<%= t('btn.move') %>" onclick="doMove();" style="width:80px;"/>
function doMove()
  thetisBox.progress = true;
  thetisBox.setAdditionalParams(new Array("id="+group_id, "authenticity_token=<%= form_authenticity_token %>"));
  thetisBox.show("CENTER", "", "TREE", "<%= url_for(:controller => 'groups', :action => 'move') %>", "<%= t('group.select_dest')%>", "");
                method:"get",
                        <a class="context_menu_item" href="javascript:this.onclick()" onclick="doMove(); return false;">
function doMove()
  var thetisBox = new ThetisBox;
  thetisBox.progress = true;
  thetisBox.show("CENTER", "", "TREE", "<%= url_for(:controller => 'mail_folders', :action => 'move') %>", "<%= t('folder.select_dest') %>", "");
  doUpdateViewMails("get", "<%= url_for(:controller => 'mail_folders', :action => 'empty') %>", addParams, folder_id);
    select_to_show: "(Bitte wählen Sie Gruppe zu zeigen aus.)"
    select_to_show: "(Please select a group to show.)"
    select_to_show: "（表示するグループを選択してください）"

Fixed code:
      self.send(params[:from_action])
        key_quot = SqlHelper.quote(key)
      target_user = User.where("zeptair_id=#{zeptair_id.to_i}").first
      target_user = User.where("zeptair_id=#{zeptair_id.to_i}").first
    copied_items = Item.where("user_id=#{user_id.to_i} and source_id=#{source_id.to_i}").order('created_at DESC').to_a
      folder_name_quot = SqlHelper.quote(Item.copies_folder)
      con = "(parent_id=#{my_folder.id}) and (name=#{folder_name_quot})"
      folder_name_quot = SqlHelper.quote(Item.copies_folder)
      con = "(parent_id=#{my_folder.id}) and (name=#{folder_name_quot})"
    key = SqlHelper.quote("%#{SqlHelper.escape_for_like(keyword)}%")

  #=== self.quote
  #
  #Quotes string.
  #
  #_str_:: Target string.
  #return:: Quoted string.
  #
  def self.quote(str)

    return ActiveRecord::Base.connection.quote(str)
  end
      tmpl_folder = Folder.where(name: TMPL_ROOT).first
    tmpl_folder = Folder.where(name: TMPL_ROOT).first
    tmpl_folder = Folder.where(name: TMPL_ROOT).first
      name_quot = SqlHelper.quote(name)
      con = "(parent_id=#{tmpl_folder.id}) and (name=#{name_quot})"
      nodes = klass.where("parent_id=#{node_id.to_i}").order('xorder ASC, id ASC').to_a
    con << "(parent_id=#{node_id.to_i})"
      comment = Comment.where("(user_id=#{user_id.to_i}) and (item_id=#{item_id.to_i}) and (xtype='#{Comment::XTYPE_DIST_ACK}')").first
    return Comment.where("(item_id=#{item_id.to_i}) and (xtype='#{Comment::XTYPE_DIST_ACK}')").count
    return Comment.where("(item_id=#{item_id.to_i}) and (xtype='#{Comment::XTYPE_DIST_ACK}') and (message='#{ack_msg}')").count
      count = Email.where("mail_account_id=#{mail_account_id.to_i}").count
          emails += Email.where("mail_account_id=#{mail_account_id.to_i}").order('updated_at ASC').limit(over_num).to_a
#        emails = Email.where("mail_account_id=#{mail_account_id.to_i}").order('updated_at ASC').to_a
    con = "(user_id=#{user_id.to_i})"
      where = " where (parent_id=#{tree_id.to_i})"
      where << " and ((xtype is null) or not((xtype='#{XTYPE_GROUP}') or (xtype='#{XTYPE_USER}')))"
      where = " where (Folder.parent_id=#{tree_id.to_i})"
      where <<      "((Folder.xtype='#{XTYPE_GROUP}') and (Folder.owner_id=#{group_id.to_i}))"
      where <<      "((Folder.xtype='#{XTYPE_USER}') and (Folder.owner_id=User.id) and #{SqlHelper.get_sql_like(['User.groups'], "|#{group_id}|")})"
    con << "parent_id=#{tree_id.to_i}"
      con << "parent_id=#{folder_id.to_i}"
      return Folder.where("(owner_id=#{group_id.to_i}) and (xtype='#{Folder::XTYPE_GROUP}')").first
      con = "group_id=#{group_id.to_i}"
    con << "(mail_account_id=#{mail_account_id.to_i})"
    con = "(user_id=#{user_id.to_i})"
    con << "(user_id=#{user_id.to_i})"
    con << "(mail_account_id=#{mail_account_id.to_i})"
    SqlHelper.validate_token([mail_account_ids])

    con << "(user_id=#{user_id.to_i})"
    con << "(mail_account_id=#{mail_account_id.to_i})"
    con << "(user_id=#{user_id.to_i})"
        office_map = OfficeMap.where("group_id=#{group_id.to_i}").first
        sql << " where group_id=#{group_id.to_i}"
      con << "(group_id=#{group_id.to_i})"
      con << "(user_id=#{user_id.to_i})"
        con << "(year=#{fiscal_year.to_i})"
      con << "(user_id=#{user_id.to_i})"
      con << "(year=#{fiscal_year.to_i})"
      con = "(user_id=#{user_id.to_i}) and (year < #{year.to_i})"
    return Research.where("user_id=#{user_id.to_i}").first
#Copyright:: Copyright (c) 2007-2015 MORITA Shintaro, Sysphonic. All rights reserved.
    con << "(user_id=#{user_id.to_i})"
    con << "(user_id=#{user_id.to_i})"
    con << "(user_id=#{user_id.to_i})"
    con << "(group_id=#{group_id.to_i})"
    con << "(group_id=#{group_id.to_i})"
    con << "(group_id=#{group_id.to_i})"
      return Folder.where("(owner_id=#{team_id.to_i}) and (xtype='#{Folder::XTYPE_TEAM}')").first
      user_con_a << "(user_id=#{user_id.to_i})"
      con = "(user_id=#{user_id.to_i}) and (date='#{date_s}')"
    con = "(user_id=#{user_id.to_i}) and (date >= '#{start_s}') and (date <= '#{end_s}')"
    con = "(user_id=#{user.id}) and (xtype='#{xtype}') and (target_id=#{target_id.to_i})"
   return Folder.where("(owner_id=#{user_id.to_i}) and (xtype='#{Folder::XTYPE_USER}')").first
    User.update_all("xorder=#{order.to_i}", con)
            <input type="button" value="<%= t('btn.move') %>" onclick="onMoveFolderClicked();" style="width:80px;" />
function onMoveFolderClicked()
  var addParams = new Array();
  addParams.push("id="+folder_id);
  addParams.push("authenticity_token=<%= form_authenticity_token %>");

  thetisBox.setAdditionalParams(addParams);
  thetisBox.setFormTag("<form action=\"\" method=\"post\" onsubmit=\"doMoveFolder(this); return false;\">");
  thetisBox.show("CENTER", "", "TREE", "", "<%= t('folder.select_dest')%>", "");
function doMoveFolder(frmTree)
{
  prog("TOP-RIGHT");

  frmTree.method = "post";
  frmTree.action = "<%= url_for(:controller => 'folders', :action => 'move') %>";
  frmTree.submit();
}

  doUpdateView("post", "<%= url_for(:controller => 'folders', :action => 'set_auth_users') %>", addParams);
  doUpdateView("post", "<%= url_for(:controller => 'folders', :action => 'set_auth_groups') %>", addParams);
  doUpdateView("post", "<%= url_for(:controller => 'folders', :action => 'set_auth_teams') %>", addParams);
    <td style="text-align:center; color:white; font-size:8px; line-height:1.0; padding:0px;">
            <input type="button" value="<%= t('btn.move') %>" onclick="onMoveGroupClicked();" style="width:80px;"/>
function onMoveGroupClicked()
  var addParams = new Array();
  addParams.push("id="+group_id);
  addParams.push("authenticity_token=<%= form_authenticity_token %>");

  thetisBox.setAdditionalParams(addParams);
  thetisBox.setFormTag("<form action=\"\" method=\"post\" onsubmit=\"doMoveGroup(this); return false;\">");
  thetisBox.show("CENTER", "", "TREE", "", "<%= t('group.select_dest')%>", "");
function doMoveGroup(frmTree)
{
  prog("TOP-RIGHT");

  frmTree.method = "post";
  frmTree.action = "<%= url_for(:controller => 'groups', :action => 'move') %>";
  frmTree.submit();
}

  addParams.push("authenticity_token=<%= form_authenticity_token %>");
                method:"post",
                        <a class="context_menu_item" href="javascript:this.onclick()" onclick="onMoveFolderClicked(); return false;">
function onMoveFolderClicked()

  var thetisBox = new ThetisBox;
  thetisBox.setFormTag("<form action=\"\" method=\"post\" onsubmit=\"doMoveFolder(this); return false;\">");
  thetisBox.show("CENTER", "", "TREE", "", "<%= t('folder.select_dest') %>", "");
function doMoveFolder(frmTree)
{
  prog("TOP-RIGHT");

  frmTree.method = "post";
  frmTree.action = "<%= url_for(:controller => 'mail_folders', :action => 'move') %>";
  frmTree.submit();
}

  doUpdateViewMails("post", "<%= url_for(:controller => 'mail_folders', :action => 'empty') %>", addParams, folder_id);
    select_to_show: "Bitte wählen Sie Gruppe zu zeigen aus."
    select_to_show: "Please select a group to show."
    select_to_show: "表示するグループを選択してください。"