CVE-2015-2972: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: Multiple SQL injection vulnerabilities in Sysphonic Thetis before 2.3.0 allow remote attackers to execute arbitrary SQL commands via unspecified vectors.
CWE description: 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.
Published: 2015-07-19T15:59Z
Repository: https://github.com/sysphonic/thetis
Language: Ruby
Files changed: app/controllers/desktop_controller.rb, app/controllers/equipment_controller.rb, app/controllers/folders_controller.rb, app/controllers/items_controller.rb, app/controllers/locations_controller.rb, app/controllers/login_controller.rb, app/controllers/researches_controller.rb, app/controllers/schedules_controller.rb, app/controllers/send_mails_controller.rb, app/controllers/timecards_controller.rb, app/controllers/users_controller.rb, app/controllers/zeptair_dist_controller.rb, app/helpers/sql_helper.rb, db/migrate/20110524100000_changes_for_ver110.rb
Commit date: 2015-07-05 22:30:39 +0900
Commit message: Reinforcement of security.
Signed-off-by: MORITA Shintaro <shin@sysphonic.com>

Diff: diff --git a/app/controllers/desktop_controller.rb b/app/controllers/desktop_controller.rb
index 2b50babf..bc828910 100644
--- a/app/controllers/desktop_controller.rb
+++ b/app/controllers/desktop_controller.rb
@@ -496,13 +496,14 @@ class DesktopController < ApplicationController
     Log.add_info(request, params.inspect)
 
     @group_id = nil
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
-    elsif !params[:group_id].nil? and !params[:group_id].empty?
+    elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
 
     submit_url = url_for(:controller => 'desktop', :action => 'get_group_users')
     render(:partial => 'common/select_users', :layout => false, :locals => {:target_attr => :id, :submit_url => submit_url})
   end
 
diff --git a/app/controllers/equipment_controller.rb b/app/controllers/equipment_controller.rb
index dc219665..e63d4006 100644
--- a/app/controllers/equipment_controller.rb
+++ b/app/controllers/equipment_controller.rb
@@ -137,10 +137,12 @@ class EquipmentController < ApplicationController
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
     elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
+
     unless @group_id.nil?
       if @group_id == '0'
         con << "((groups like '%|0|%') or (groups is null))"
       else
         con << SqlHelper.get_sql_like([:groups], "|#{@group_id}|")
diff --git a/app/controllers/folders_controller.rb b/app/controllers/folders_controller.rb
index 6bb33b55..8c1ef685 100644
--- a/app/controllers/folders_controller.rb
+++ b/app/controllers/folders_controller.rb
@@ -33,13 +33,14 @@ class FoldersController < ApplicationController
     if !@login_user.nil? and @login_user.admin?(User::AUTH_FOLDER)
 
       @group_id = nil
       if !params[:thetisBoxSelKeeper].nil?
         @group_id = params[:thetisBoxSelKeeper].split(':').last
-      elsif !params[:group_id].nil? and !params[:group_id].empty?
+      elsif !params[:group_id].blank?
         @group_id = params[:group_id]
       end
+      SqlHelper.validate_token([@group_id])
 
       @folder_tree = Folder.get_tree_by_group_for_admin(@group_id || '0')
     else
       @folder_tree = Folder.get_tree_for(@login_user)
     end
@@ -504,24 +505,26 @@ class FoldersController < ApplicationController
   #Gets Users in specified Group.
   #
   def get_group_users
     Log.add_info(request, params.inspect)
 
+    SqlHelper.validate_token([params[:id]])
     begin
       @folder = Folder.find(params[:id])
     rescue => evar
       Log.add_error(request, evar)
     end
 
     @group_id = nil
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
-    elsif !params[:group_id].nil? and !params[:group_id].empty?
+    elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
 
-    @users = Group.get_users @group_id
+    @users = Group.get_users(@group_id)
 
     render(:partial => 'ajax_select_users', :layout => false)
   end
 
   #=== set_auth_users
diff --git a/app/controllers/items_controller.rb b/app/controllers/items_controller.rb
index f00dd4a2..ddfd1504 100644
--- a/app/controllers/items_controller.rb
+++ b/app/controllers/items_controller.rb
@@ -1215,15 +1215,16 @@ class ItemsController < ApplicationController
     Log.add_info(request, params.inspect)
 
     @group_id = nil
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
-    elsif !params[:group_id].nil? and !params[:group_id].empty?
+    elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
 
-    @users = Group.get_users @group_id
+    @users = Group.get_users(@group_id)
 
     render(:partial => 'ajax_select_users', :layout => false)
   end
 
   #=== wf_issue
@@ -1232,11 +1233,11 @@ class ItemsController < ApplicationController
   #Issues specified Workflow.
   #
   def wf_issue
     Log.add_info(request, params.inspect)
 
-    begin
+   begin
       @item = Item.find(params[:id])
       @workflow = @item.workflow
     rescue => evar
       Log.add_error(request, evar)
     end
@@ -1256,11 +1257,11 @@ class ItemsController < ApplicationController
   #
   def team_organize
     Log.add_info(request, params.inspect)
 
     team_id = params[:team_id]
-    unless team_id.nil? or team_id.empty?
+    unless team_id.blank?
       begin
         @team = Team.find(team_id)
       rescue
         @team = nil
       ensure
@@ -1278,20 +1279,20 @@ class ItemsController < ApplicationController
     created = false
     modified = false
 
     if team_members.nil? or team_members.empty?
 
-      unless team_id.nil? or team_id.empty?
+      unless team_id.blank?
         # @team must not be nil.
         @team.save if modified = @team.clear_users
       end
 
     else
 
       if team_members != users
 
-        if team_id.nil? or team_id.empty?
+        if team_id.blank?
 
           item = Item.find(params[:id])
 
           created = true
           @team = Team.new
@@ -1357,10 +1358,12 @@ class ItemsController < ApplicationController
   #Changes status of the Team.
   #
   def change_team_status
     Log.add_info(request, params.inspect)
 
+    SqlHelper.validate_token([params[:status]])
+
     team_id = params[:team_id]
     begin
       team = Team.find(team_id)
       team.update_status(params[:status])
 
@@ -1380,11 +1383,11 @@ class ItemsController < ApplicationController
   #=== check_owner
   #
   #Filter method to check if the current User is owner of the specified Item.
   #
   def check_owner
-    return if params[:id].nil? or params[:id].empty? or @login_user.nil?
+    return if params[:id].blank? or @login_user.nil?
 
     begin
       owner_id = Item.find(params[:id]).user_id
     rescue
       owner_id = -1
diff --git a/app/controllers/locations_controller.rb b/app/controllers/locations_controller.rb
index 351e91af..fff8e1d4 100644
--- a/app/controllers/locations_controller.rb
+++ b/app/controllers/locations_controller.rb
@@ -36,10 +36,11 @@ class LocationsController < ApplicationController
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
     elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
 
     unless params[:keyword].blank?
       con_prim = []
       con_second = []
       key_array = params[:keyword].split(nil)
@@ -140,10 +141,11 @@ class LocationsController < ApplicationController
   #
   def update_map
     Log.add_info(request, params.inspect)
 
     group_id = params[:group_id]
+    SqlHelper.validate_token([group_id])
 
     @office_map = OfficeMap.get_for_group(group_id, true)
 
     params[:office_map].delete(:group_id)
 
@@ -161,10 +163,11 @@ class LocationsController < ApplicationController
   #
   def delete_map
     Log.add_info(request, params.inspect)
 
     group_id = params[:group_id]
+    SqlHelper.validate_token([group_id])
 
     @office_map = OfficeMap.get_for_group(group_id, false)
 
     begin
       @office_map.destroy
diff --git a/app/controllers/login_controller.rb b/app/controllers/login_controller.rb
index f5f2280b..65c73acb 100644
--- a/app/controllers/login_controller.rb
+++ b/app/controllers/login_controller.rb
@@ -94,12 +94,14 @@ class LoginController < ApplicationController
   #Sends User account information by E-mail.
   #
   def send_password
     Log.add_info(request, params.inspect)
 
+    mail_addr = params[:thetisBoxEdit]
+    SqlHelper.validate_token([mail_addr], ['@-'])
     begin
-      users = User.where("email='#{params[:thetisBoxEdit]}'").to_a
+      users = User.where("email='#{mail_addr}'").to_a
     rescue => evar
     end
 
     if users.nil? or users.empty?
       Log.add_error(request, evar)
diff --git a/app/controllers/researches_controller.rb b/app/controllers/researches_controller.rb
index 89b1d7dd..d2104e71 100644
--- a/app/controllers/researches_controller.rb
+++ b/app/controllers/researches_controller.rb
@@ -432,12 +432,13 @@ class ResearchesController < ApplicationController
       end
     end
 
     # Specifying page
     @page = '01'
-    unless params[:page].nil? or params[:page].empty?
+    unless params[:page].blank?
       @page = params[:page]
+      SqlHelper.validate_token([@page])
     end
   end
 
   #=== save_page
   #
@@ -464,16 +465,18 @@ class ResearchesController < ApplicationController
           end
         end
       end
     end
 
-    if params[:research_id].nil? or params[:research_id].empty?
+    research_id = params[:research_id]
+    SqlHelper.validate_token([research_id])
+    if research_id.blank?
       @research = Research.new(params.require(:research).permit(Research::PERMIT_BASE))
       @research.status = Research::U_STATUS_IN_ACTON
       @research.update_attribute(:user_id, @login_user.id)
     else
-      @research = Research.find(params[:research_id])
+      @research = Research.find(research_id)
       @research.update_attributes(params.require(:research).permit(Research::PERMIT_BASE))
     end
 
     if pave_val <= page_num
 
@@ -550,10 +553,12 @@ class ResearchesController < ApplicationController
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
     elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
+
     unless @group_id.nil?
       con << SqlHelper.get_sql_like([:groups], "|#{@group_id}|")
     end
 
     include_research = false
@@ -688,10 +693,11 @@ class ResearchesController < ApplicationController
 
     where = ' where Research.status=' + Research::U_STATUS_COMMITTED.to_s
 
     unless params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
+      SqlHelper.validate_token([@group_id])
 
       group_cons = []
 
       if @group_id != '0'
         group_cons << SqlHelper.get_sql_like(['User.groups'], "|#{@group_id}|")
diff --git a/app/controllers/schedules_controller.rb b/app/controllers/schedules_controller.rb
index 5c38e9da..c2d4a7e7 100644
--- a/app/controllers/schedules_controller.rb
+++ b/app/controllers/schedules_controller.rb
@@ -676,13 +676,14 @@ class SchedulesController < ApplicationController
     Log.add_info(request, params.inspect)
 
     @group_id = nil
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
-    elsif !params[:group_id].nil? and !params[:group_id].empty?
+    elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
 
     submit_url = url_for(:controller => 'schedules', :action => 'get_group_users')
     render(:partial => 'common/select_users', :layout => false, :locals => {:target_attr => :id, :submit_url => submit_url})
   end
 
@@ -706,13 +707,14 @@ class SchedulesController < ApplicationController
     Log.add_info(request, params.inspect)
 
     @group_id = nil
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
-    elsif !params[:group_id].nil? and !params[:group_id].empty?
+    elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
 
     submit_url = url_for(:controller => 'schedules', :action => 'get_group_equipment')
     render(:partial => 'common/select_equipment', :layout => false, :locals => {:target_attr => :id, :submit_url => submit_url})
   end
 
diff --git a/app/controllers/send_mails_controller.rb b/app/controllers/send_mails_controller.rb
index ef2c2cdd..bb6cbfcb 100644
--- a/app/controllers/send_mails_controller.rb
+++ b/app/controllers/send_mails_controller.rb
@@ -361,13 +361,14 @@ class SendMailsController < ApplicationController
     Log.add_info(request, params.inspect)
 
     @group_id = nil
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
-    elsif !params[:group_id].nil? and !params[:group_id].empty?
+    elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
 
     submit_url = url_for(:controller => 'send_mails', :action => 'get_group_users')
     render(:partial => 'common/select_users', :layout => false, :locals => {:target_attr => :email, :submit_url => submit_url})
   end
 
@@ -376,11 +377,11 @@ class SendMailsController < ApplicationController
   #
   #Filter method to check if current User is owner of specified Item.
   #
   def check_owner
 
-    return if params[:id].nil? or params[:id].empty? or @login_user.nil?
+    return if params[:id].blank? or @login_user.nil?
 
     email = Email.find(params[:id])
 
     if !@login_user.admin?(User::AUTH_MAIL) and email.user_id != @login_user.id
       Log.add_check(request, '[check_owner]'+request.to_s)
diff --git a/app/controllers/timecards_controller.rb b/app/controllers/timecards_controller.rb
index cc6c92c1..1b9b5ff2 100644
--- a/app/controllers/timecards_controller.rb
+++ b/app/controllers/timecards_controller.rb
@@ -425,10 +425,12 @@ class TimecardsController < ApplicationController
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
     elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
+
     unless @group_id.nil?
       if @group_id == '0'
         con << "((groups like '%|0|%') or (groups is null))"
       else
         con << SqlHelper.get_sql_like([:groups], "|#{@group_id}|")
diff --git a/app/controllers/users_controller.rb b/app/controllers/users_controller.rb
index 988f9e72..b12a3f94 100644
--- a/app/controllers/users_controller.rb
+++ b/app/controllers/users_controller.rb
@@ -160,10 +160,11 @@ class UsersController < ApplicationController
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
     elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
 
 # Copy to FEATURE_PAGING_IN_TREE >>>
     con = ['User.id > 0']
 
     unless @group_id.nil?
@@ -267,10 +268,11 @@ class UsersController < ApplicationController
     @user = User.find(params[:user_id])
 
     unless params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
     end
+    SqlHelper.validate_token([@group_id])
 
     if @group_id.nil?
       @official_titles = OfficialTitle.get_for('0', false, true)
     else
       @official_titles = OfficialTitle.get_for(@group_id, false, true)
@@ -349,10 +351,12 @@ class UsersController < ApplicationController
   #Updates User's authority.
   #
   def update_auth
     Log.add_info(request, params.inspect)
 
+    return unless request.post?
+
     auth = nil
 
     if params[:check_auth_all] == '1'
 
       auth = User::AUTH_ALL
diff --git a/app/controllers/zeptair_dist_controller.rb b/app/controllers/zeptair_dist_controller.rb
index 6f2ed4f4..c3db5635 100644
--- a/app/controllers/zeptair_dist_controller.rb
+++ b/app/controllers/zeptair_dist_controller.rb
@@ -46,10 +46,12 @@ class ZeptairDistController < ApplicationController
     if !params[:thetisBoxSelKeeper].nil?
       @group_id = params[:thetisBoxSelKeeper].split(':').last
     elsif !params[:group_id].blank?
       @group_id = params[:group_id]
     end
+    SqlHelper.validate_token([@group_id])
+
     unless @group_id.nil?
       con << SqlHelper.get_sql_like([:groups], "|#{@group_id}|")
     end
 
     include_comment = false
diff --git a/app/helpers/sql_helper.rb b/app/helpers/sql_helper.rb
index e5e129a7..7a6c4c62 100644
--- a/app/helpers/sql_helper.rb
+++ b/app/helpers/sql_helper.rb
@@ -23,12 +23,12 @@ module SqlHelper
   #_extra_chars_:: Extra characters.
   #return:: Query condition.
   #
   def self.validate_token(tokens, extra_chars=nil)
 
-    extra_chars ||= []
-    regexp = Regexp.new("^\s*[a-zA-Z0-9_.#{extra_chars.join()}]+\s*$")
+    extra_chars = Regexp.escape((extra_chars || []).join())
+    regexp = Regexp.new("^[ ]*[a-zA-Z0-9_.#{extra_chars}]+[ ]*$")
 
     [tokens].flatten.each do |token|
       next if token.blank?
 
       if token.to_s.match(regexp).nil?
diff --git a/db/migrate/20110524100000_changes_for_ver110.rb b/db/migrate/20110524100000_changes_for_ver110.rb
index d5d412df..a8bad1d5 100644
--- a/db/migrate/20110524100000_changes_for_ver110.rb
+++ b/db/migrate/20110524100000_changes_for_ver110.rb
@@ -13,23 +13,20 @@ class ChangesForVer110 < ActiveRecord::Migration
     add_column :teams, :req_to_del_at, :datetime
     change_table :teams do |t|
       t.timestamps
     end
 
-    teams = Team.all
-    unless teams.nil?
-      teams.each do |team|
-        begin
-          item = Item.find(team.item_id)
-        rescue
-        end
-        next if item.nil?
-
-        attrs = ActionController::Parameters.new({created_at: item.created_at, updated_at: item.updated_at})
-        attrs.permit!
-        team.update_attributes(attrs)
+    Team.find_each do |team|
+      begin
+        item = Item.find(team.item_id)
+      rescue
       end
+      next if item.nil?
+
+      attrs = ActionController::Parameters.new({created_at: item.created_at, updated_at: item.updated_at})
+      attrs.permit!
+      team.update_attributes(attrs)
     end
   end
 
   def self.down
     remove_column :items, :source_id

Vulnerable code:     elsif !params[:group_id].nil? and !params[:group_id].empty?
      elsif !params[:group_id].nil? and !params[:group_id].empty?
    elsif !params[:group_id].nil? and !params[:group_id].empty?
    @users = Group.get_users @group_id
    elsif !params[:group_id].nil? and !params[:group_id].empty?
    @users = Group.get_users @group_id
    begin
    unless team_id.nil? or team_id.empty?
      unless team_id.nil? or team_id.empty?
        if team_id.nil? or team_id.empty?
    return if params[:id].nil? or params[:id].empty? or @login_user.nil?
      users = User.where("email='#{params[:thetisBoxEdit]}'").to_a
    unless params[:page].nil? or params[:page].empty?
    if params[:research_id].nil? or params[:research_id].empty?
      @research = Research.find(params[:research_id])
    elsif !params[:group_id].nil? and !params[:group_id].empty?
    elsif !params[:group_id].nil? and !params[:group_id].empty?
    elsif !params[:group_id].nil? and !params[:group_id].empty?
    return if params[:id].nil? or params[:id].empty? or @login_user.nil?
    extra_chars ||= []
    regexp = Regexp.new("^\s*[a-zA-Z0-9_.#{extra_chars.join()}]+\s*$")
    teams = Team.all
    unless teams.nil?
      teams.each do |team|
        begin
          item = Item.find(team.item_id)
        rescue
        end
        next if item.nil?

        attrs = ActionController::Parameters.new({created_at: item.created_at, updated_at: item.updated_at})
        attrs.permit!
        team.update_attributes(attrs)
Fixed code:     elsif !params[:group_id].blank?
    SqlHelper.validate_token([@group_id])
    SqlHelper.validate_token([@group_id])

      elsif !params[:group_id].blank?
      SqlHelper.validate_token([@group_id])
    SqlHelper.validate_token([params[:id]])
    elsif !params[:group_id].blank?
    SqlHelper.validate_token([@group_id])
    @users = Group.get_users(@group_id)
    elsif !params[:group_id].blank?
    SqlHelper.validate_token([@group_id])
    @users = Group.get_users(@group_id)
   begin
    unless team_id.blank?
      unless team_id.blank?
        if team_id.blank?
    SqlHelper.validate_token([params[:status]])

    return if params[:id].blank? or @login_user.nil?
    SqlHelper.validate_token([@group_id])
    SqlHelper.validate_token([group_id])
    SqlHelper.validate_token([group_id])
    mail_addr = params[:thetisBoxEdit]
    SqlHelper.validate_token([mail_addr], ['@-'])
      users = User.where("email='#{mail_addr}'").to_a
    unless params[:page].blank?
      SqlHelper.validate_token([@page])
    research_id = params[:research_id]
    SqlHelper.validate_token([research_id])
    if research_id.blank?
      @research = Research.find(research_id)
    SqlHelper.validate_token([@group_id])

      SqlHelper.validate_token([@group_id])
    elsif !params[:group_id].blank?
    SqlHelper.validate_token([@group_id])
    elsif !params[:group_id].blank?
    SqlHelper.validate_token([@group_id])
    elsif !params[:group_id].blank?
    SqlHelper.validate_token([@group_id])
    return if params[:id].blank? or @login_user.nil?
    SqlHelper.validate_token([@group_id])

    SqlHelper.validate_token([@group_id])
    SqlHelper.validate_token([@group_id])
    return unless request.post?

    SqlHelper.validate_token([@group_id])

    extra_chars = Regexp.escape((extra_chars || []).join())
    regexp = Regexp.new("^[ ]*[a-zA-Z0-9_.#{extra_chars}]+[ ]*$")
    Team.find_each do |team|
      begin
        item = Item.find(team.item_id)
      rescue
      next if item.nil?

      attrs = ActionController::Parameters.new({created_at: item.created_at, updated_at: item.updated_at})
      attrs.permit!
      team.update_attributes(attrs)