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/desktop_controller.rb
app/controllers/items_controller.rb
app/controllers/login_controller.rb
app/controllers/mail_folders_controller.rb
app/controllers/timecards_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/address.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_folder.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/common/_edit_scope.html.erb
app/views/schedules/_edit_members.html.erb
app/views/send_mails/edit_send_to.html.erb
app/views/timecards/group.html.erb
Commit message: Reinforcement of security.
Signed-off-by: MORITA Shintaro <shin@sysphonic.com>


Vulnerable code:
    if date_s.nil? or date_s.empty?
    end 
        @team.add_users team_members
        @team.remove_application team_members
    SqlHelper.validate_token([mail_addr], ['@-'])
      SqlHelper.validate_token([account_xtype])
    if date_s.nil? or date_s.empty?
    SqlHelper.validate_token([params[:user_id], params[:zeptair_id], params[:group_id]])
    unless params[:user_id].blank?
      target_user = User.find(params[:user_id])
    unless params[:zeptair_id].blank?
      zeptair_id = params[:zeptair_id]
      if params[:group_id].blank?
        group_ids = [params[:group_id]]
          group_ids += Group.get_childs(params[:group_id], true, false)
        group_ids.each do |group_id|
          groups_con << SqlHelper.get_sql_like(['User.groups'], "|#{@group_id}|")
    unless params[:user_id].blank?
      if @login_user.admin?(User::AUTH_ZEPTAIR) or @login_user.id.to_s == params[:user_id].to_s
        target_user = User.find(params[:user_id])
    unless params[:zeptair_id].blank?
      target_user = User.where("zeptair_id=#{params[:zeptair_id]}").first
      if params[:attachment_id].blank?
        attach = Attachment.find(params[:attachment_id])
    return '#' + sprintf('%03d', rev+1)
    extra_chars = Regexp.escape((extra_chars || []).join())
    regexp = Regexp.new("^[ ]*[a-zA-Z0-9_.#{extra_chars}]+[ ]*$")

    account_ary = MailAccount.find_by_sql('select * from mail_accounts ' + where + ' order by xorder ASC, title ASC')
    if account_ary.nil? or account_ary.empty?
      return account_ary.first
      PaidHoliday.destroy_all(['user_id=? and year=?', user_id, fiscal_year])

  #_users_:: Array of User-IDs.
  def remove_application(users)
    return if users.nil? or users.empty?
    array = ["(xtype='#{Comment::XTYPE_APPLY}')"]
    array << "(item_id=#{self.item_id})"
    users.each do |user_id|
    array << '(' + user_con_a.join(' or ') + ')'
    Comment.destroy_all(array.join(' and '))

  #_date_:: Target Date.
  def self.get_for(user_id, date)
      con = "(user_id=#{user_id}) and (date='#{date}')"
    con = "(user_id=#{user_id}) and (date >= '#{start_date}') and (date <= '#{end_date}')"
    SqlHelper.validate_token([user_id, start_date, end_date])
    sql = "SELECT COUNT(*) FROM timecards WHERE user_id = #{user_id} AND date >= '#{start_date}' AND date <= '#{end_date}'"
    return Folder.where("(owner_id=#{user_id}) and (xtype='#{Folder::XTYPE_USER}')").first
<table cellspacing="0" cellpadding="0" style="width:100%;">
        <table cellspacing="0" cellpadding="0" style="width:100%; padding:5px 10px;">
              <table cellspacing="0" cellpadding="0" style="width:100%;">
        <table cellspacing="0" cellpadding="0" style="width:100%; padding:5px 10px;">
              <table cellspacing="0" cellpadding="0" style="width:100%;">
        <table cellspacing="0" cellpadding="0" style="width:100%; padding:5px 10px;">
        <table cellspacing="0" cellpadding="0" style="width:100%; padding:5px 10px;">
              <table cellspacing="0" cellpadding="0" style="width:100%;">
        <table cellspacing="0" cellpadding="0" style="width:100%; padding:5px 10px;">
              <table cellspacing="0" cellpadding="0" style="width:100%;">
                  user = User.where("email='#{mail_addr}'").first

<% date_s = @date.strftime(Schedule::SYS_DATE_FORM) unless @date.nil? %>
    timecard = Timecard.get_for(user.id, @date)

Fixed code:
    if date_s.blank?
    end
    SqlHelper.validate_token([team_members])
        @team.add_users(team_members)
        @team.remove_application(team_members)
    SqlHelper.validate_token([mail_addr])
    SqlHelper.validate_token([account_xtype])
    if date_s.blank?
    user_id = params[:user_id]
    zeptair_id = params[:zeptair_id]
    group_id = params[:group_id]
    SqlHelper.validate_token([user_id, zeptair_id, group_id])
    unless user_id.blank?
      target_user = User.find(user_id)
    unless zeptair_id.blank?
      if group_id.blank?
        group_ids = [group_id]
          group_ids += Group.get_childs(group_id, true, false)
        group_ids.each do |grp_id|
          groups_con << SqlHelper.get_sql_like(['User.groups'], "|#{grp_id}|")
    user_id = params[:user_id]
    zeptair_id = params[:zeptair_id]
    attachment_id = params[:attachment_id]
    SqlHelper.validate_token([user_id, zeptair_id, attachment_id])

    unless user_id.blank?
      if @login_user.admin?(User::AUTH_ZEPTAIR) or @login_user.id.to_s == user_id.to_s
        target_user = User.find(user_id)
    unless zeptair_id.blank?
      target_user = User.where("zeptair_id=#{zeptair_id}").first
      if attachment_id.blank?
        attach = Attachment.find(attachment_id)

    SqlHelper.validate_token([user_id, source_id])

    return ('#' + sprintf('%03d', rev+1))
    if extra_chars.nil?
      extra_chars = ''
    else
      extra_chars = Regexp.escape(extra_chars.join())
    end
    regexp = Regexp.new("^[ ]*[a-zA-Z0-9_.@\\-#{extra_chars}]+[ ]*$")
    SqlHelper.validate_token([name])

    SqlHelper.validate_token([node_id])

    SqlHelper.validate_token([node_id])
    SqlHelper.validate_token([item_id, user_id])
    SqlHelper.validate_token([item_id])
    SqlHelper.validate_token([item_id])
    SqlHelper.validate_token([mail_addr])


    SqlHelper.validate_token([user_id, mail_account_id])

    SqlHelper.validate_token([user_id])

    SqlHelper.validate_token([folder_id])
    SqlHelper.validate_token([group_id])
    SqlHelper.validate_token([group_id])
    SqlHelper.validate_token([user_id])

    mail_accounts = MailAccount.find_by_sql('select * from mail_accounts ' + where + ' order by xorder ASC, title ASC')
    if mail_accounts.nil? or mail_accounts.empty?
      return mail_accounts.first
    SqlHelper.validate_token([user_id, mail_account_id, xtype])

    SqlHelper.validate_token([user_id])


    SqlHelper.validate_token([user_id, fiscal_year])

    SqlHelper.validate_token([user_id, fiscal_year])

      con = []
      con << "(user_id=#{user_id})"
      con << "(year=#{fiscal_year})"
      PaidHoliday.destroy_all(con.join(' and '))
    SqlHelper.validate_token([user_id, year])

    SqlHelper.validate_token([user_id])

    SqlHelper.validate_token([user_id, category])

    SqlHelper.validate_token([user_id, category, key])

    SqlHelper.validate_token([user_id, category, key])

    SqlHelper.validate_token([group_id, category])

    SqlHelper.validate_token([group_id, category, key])

    SqlHelper.validate_token([group_id, category, key])

    SqlHelper.validate_token([team_id])
  #_user_ids_:: Array of User-IDs.
  def remove_application(user_ids)
    return if user_ids.nil? or user_ids.empty?
    SqlHelper.validate_token([user_ids])

    con = ["(xtype='#{Comment::XTYPE_APPLY}')"]
    con << "(item_id=#{self.item_id})"
    user_ids.each do |user_id|
    con << '(' + user_con_a.join(' or ') + ')'
    Comment.destroy_all(con.join(' and '))
  #_date_s_:: Target Date string.
  def self.get_for(user_id, date_s)

    SqlHelper.validate_token([user_id, date_s])
      con = "(user_id=#{user_id}) and (date='#{date_s}')"

    SqlHelper.validate_token([user_id])

    start_s = start_date.strftime(Schedule::SYS_DATE_FORM)
    end_s = end_date.strftime(Schedule::SYS_DATE_FORM)

    con = "(user_id=#{user_id}) and (date >= '#{start_s}') and (date <= '#{end_s}')"
    SqlHelper.validate_token([user_id])

    start_s = start_date.strftime(Schedule::SYS_DATE_FORM)
    end_s = end_date.strftime(Schedule::SYS_DATE_FORM)
    sql = "SELECT COUNT(*) FROM timecards WHERE user_id = #{user_id} AND date >= '#{start_s}' AND date <= '#{end_s}'"
    SqlHelper.validate_token([xtype, target_id])

    SqlHelper.validate_token([name])

    SqlHelper.validate_token([user_name])

   SqlHelper.validate_token([user_id])
   return Folder.where("(owner_id=#{user_id}) and (xtype='#{Folder::XTYPE_USER}')").first
    SqlHelper.validate_token([order])
    SqlHelper.validate_token([item_id])

<table style="width:100%; border-spacing:0px;">
        <table style="width:100%; padding:5px 10px; border-spacing:0px;">
              <table style="width:100%; border-spacing:0px;">
        <table style="width:100%; padding:5px 10px; border-spacing:0px;">
              <table style="width:100%; border-spacing:0px;">
        <table style="width:100%; padding:5px 10px; border-spacing:0px;">
        <table style="width:100%; padding:5px 10px; border-spacing:0px;">
              <table style="width:100%; border-spacing:0px;">
        <table style="width:100%; padding:5px 10px; border-spacing:0px;">
              <table style="width:100%; border-spacing:0px;">
                  user = User.where(email: mail_addr).first
<%
date_s = @date.strftime(Schedule::SYS_DATE_FORM) unless @date.nil?
%>
    timecard = Timecard.get_for(user.id, date_s)