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/login_controller.rb, app/helpers/sql_helper.rb, app/models/address.rb, app/models/user.rb, app/views/frames/about.html.erb, app/views/items/_ajax_item_basic.html.erb
Commit date: 2015-07-14 08:56:18 +0900
Commit message: Thetis ver.2.3.0: Reinforcement of security.
Signed-off-by: MORITA Shintaro <shin@sysphonic.com>

Diff: diff --git a/app/controllers/login_controller.rb b/app/controllers/login_controller.rb
index ecc72b67..3f67091b 100644
--- a/app/controllers/login_controller.rb
+++ b/app/controllers/login_controller.rb
@@ -95,13 +95,12 @@ class LoginController < ApplicationController
   #
   def send_password
     Log.add_info(request, params.inspect)
 
     mail_addr = params[:thetisBoxEdit]
-    SqlHelper.validate_token([mail_addr])
     begin
-      users = User.where("email='#{mail_addr}'").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/helpers/sql_helper.rb b/app/helpers/sql_helper.rb
index fa6ba824..872262f0 100644
--- a/app/helpers/sql_helper.rb
+++ b/app/helpers/sql_helper.rb
@@ -28,16 +28,17 @@ module SqlHelper
     if extra_chars.nil?
       extra_chars = ''
     else
       extra_chars = Regexp.escape(extra_chars.join())
     end
-    regexp = Regexp.new("^[ ]*[a-zA-Z0-9_.@\\-#{extra_chars}]+[ ]*$")
+    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?
+      if token.to_s.match(regexp).nil? \
+          and token.to_s.match(/^[ ]*\d+-\d+-\d+[ ]*$/).nil?
         raise("[ERROR] SqlHelper.validate_token failed: #{token}")
       end
     end
   end
 
diff --git a/app/models/address.rb b/app/models/address.rb
index 18c6b158..4ea96517 100644
--- a/app/models/address.rb
+++ b/app/models/address.rb
@@ -34,16 +34,16 @@ class Address < ActiveRecord::Base
   #_book_:: Book type.
   #return:: Array of Addresses.
   #
   def self.get_by_email(mail_addr, user, book=Address::BOOK_BOTH)
 
-    SqlHelper.validate_token([mail_addr])
+    mail_quote = SqlHelper.quote(mail_addr)
 
     email_con = []
-    email_con.push("(email1='#{mail_addr}')")
-    email_con.push("(email2='#{mail_addr}')")
-    email_con.push("(email3='#{mail_addr}')")
+    email_con.push("(email1=#{mail_quote})")
+    email_con.push("(email2=#{mail_quote})")
+    email_con.push("(email3=#{mail_quote})")
     con = []
     con.push('('+email_con.join(' or ')+')')
     con.push(AddressbookHelper.get_scope_condition_for(user, book))
 
     return Address.where(con.join(' and ')).to_a
@@ -210,11 +210,15 @@ class Address < ActiveRecord::Base
   def self.parse_csv_row(row, book, idxs, user)
 
     imp_id = (idxs[0].nil? or row[idxs[0]].nil?)?(nil):(row[idxs[0]].strip)
     SqlHelper.validate_token([imp_id])
     unless imp_id.blank?
-      org_address = Address.find_by_id(imp_id)
+      begin
+        org_address = Address.find(imp_id)
+      rescue => evar
+        org_address = nil
+      end
     end
 
     if org_address.nil?
       address = Address.new
     else
diff --git a/app/models/user.rb b/app/models/user.rb
index a7b67976..a9588373 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -532,11 +532,11 @@ class User < ActiveRecord::Base
   def self.get_from_name(user_name)
 
     SqlHelper.validate_token([user_name])
 
     begin
-      user = User.where("name='#{user_name}'").first
+      user = User.where(name: user_name).first
     rescue => evar
       Log.add_error(nil, evar)
     end
 
     return user
diff --git a/app/views/frames/about.html.erb b/app/views/frames/about.html.erb
index 441f29a0..da48ba18 100644
--- a/app/views/frames/about.html.erb
+++ b/app/views/frames/about.html.erb
@@ -36,11 +36,11 @@
         <tr>
           <td>
             <table style="width:60%; margin:0px auto 0px 0px; border-spacing:0px;">
               <tr>
                 <td style="text-align:left;" nowrap>
-                  Sysphonic <B>Thetis</B><sup>&reg;</sup>  <small style="text-align:right;">ver.2.2.0</small>&nbsp;
+                  Sysphonic <B>Thetis</B><sup>&reg;</sup>  <small style="text-align:right;">ver.2.3.0</small>&nbsp;
                 </td>
               </tr>
             </table>
           </td>
         </tr>
diff --git a/app/views/items/_ajax_item_basic.html.erb b/app/views/items/_ajax_item_basic.html.erb
index 9a8e3766..896d113f 100644
--- a/app/views/items/_ajax_item_basic.html.erb
+++ b/app/views/items/_ajax_item_basic.html.erb
@@ -20,54 +20,58 @@
   <% when Item::XTYPE_WORKFLOW %>
       <%= Workflow.model_name.human %>
   <% when Item::XTYPE_RESEARCH %>
       <%= Research.model_name.human %>
   <% else   # XTYPE_INFO, XTYPE_PROJECT, XTYPE_ZEPTAIR_DIST, XTYPE_UNKNOWN %>
-      <%= radio_button('item', 'xtype', Item::XTYPE_INFO, :onClick => 'dispZeptCmdOpt(false); modifiedBasic++') %><label for="item_xtype_<%= Item::XTYPE_INFO %>"> <%= t('item.type.normal') %></label><br/>
-      <%= radio_button('item', 'xtype', Item::XTYPE_PROJECT, :onClick => 'dispZeptCmdOpt(false); modifiedBasic++') %><label for="item_xtype_<%= Item::XTYPE_PROJECT %>"> <img src="<%= THETIS_RELATIVE_URL_ROOT %>/images/folder/team_folder.gif" title="<%= t('item.project') %>" alt="<%= t('item.project') %>" /> <%= t('item.type.project') %></label><br/>
-    <% if @login_user.admin?(User::AUTH_ZEPTAIR) %>
-      <%= radio_button('item', 'xtype', Item::XTYPE_ZEPTAIR_DIST, :onClick => 'dispZeptCmdOpt(this.checked); modifiedBasic++') %><label for="item_xtype_<%= Item::XTYPE_ZEPTAIR_DIST %>"> <img src="<%= THETIS_RELATIVE_URL_ROOT %>/images/zeptair/zeptair_icon.png" title="<%= t('item.type.zeptair_dist') %>" alt="<%= t('item.type.zeptair_dist') %>" /> <%= t('item.type.zeptair_dist') %></label>
-      &nbsp;<img class="img_btn" src="<%= THETIS_RELATIVE_URL_ROOT %>/images/info.gif" onclick="msg('<%= t('item.zeptair_dist_info') %>');">
-
-      <div id="div_zept_cmd" style="padding-left:20px; display:<%= (@item.xtype == Item::XTYPE_ZEPTAIR_DIST)?'block':'none' %>;">
-        <div style="background-color:#FDFEFF; padding:2px 3px">
-          <%
-          zept_cmd = @item.zeptair_command
-          checked = (!zept_cmd.nil? and zept_cmd.enabled)?'checked':''
-          %>
-          <input name="zeptair_command[enabled]" type="hidden" value="0" />
-          <input type="checkbox" id="check_zept_cmd" name="zeptair_command[enabled]" value="1" onclick="dispZeptCmdEdit(this.checked); modifiedBasic++;" <%= checked %>><label for="check_zept_cmd"><%= t('item.zeptair_dist_cmd') %></label>
-          <div id="div_zept_cmd_edit" style="padding-left:20px; display:<%= (checked=='checked')?'block':'none' %>;">
-            <textarea id="zept_cmd" name="zeptair_command[commands]" rows="4" wrap="off" style="width:95%; background-color:black; color:white;" onChange="modifiedBasic++;"><%= (zept_cmd.nil?)?'':zept_cmd.commands %></textarea><br/>
-            <% checked = (!zept_cmd.nil? and zept_cmd.confirm_exec)?'checked':'' %>
-            <input name="zeptair_command[confirm_exec]" type="hidden" value="0" />
-            <input type="checkbox" id="check_zept_cmd_with_confirm" name="zeptair_command[confirm_exec]" value="1" onclick="modifiedBasic++;" <%= checked %>><label for="check_zept_cmd_with_confirm"><%= t('item.zeptair_dist_cmd_with_confirm') %></label>
-          </div>
-        </div>
-      </div>
-
-<script type="text/javascript">
-dispZeptCmdOpt = function(sw)
-{
-  if (sw) {
-    _z("div_zept_cmd").style.display = "block";
-  } else {
-    _z("div_zept_cmd").style.display = "none";
-  }
-}
-
-dispZeptCmdEdit = function(sw)
-{
-  if (sw) {
-    _z("div_zept_cmd_edit").style.display = "block";
-  } else {
-    _z("div_zept_cmd_edit").style.display = "none";
-  }
-}
-</script>
-
-    <% end %>
+      <%= radio_button('item', 'xtype', Item::XTYPE_INFO, :onClick => 'onItemXtypeClicked(this)') %><label for="item_xtype_<%= Item::XTYPE_INFO %>"> <%= t('item.type.normal') %></label><br/>
+      <%= radio_button('item', 'xtype', Item::XTYPE_PROJECT, :onClick => 'onItemXtypeClicked(this)') %><label for="item_xtype_<%= Item::XTYPE_PROJECT %>"> <img src="<%= THETIS_RELATIVE_URL_ROOT %>/images/folder/team_folder.gif" title="<%= t('item.project') %>" alt="<%= t('item.project') %>" /> <%= t('item.type.project') %></label><br/>
+    <%
+=begin
+#    if @login_user.admin?(User::AUTH_ZEPTAIR)
+    %>
+#      <%= radio_button('item', 'xtype', Item::XTYPE_ZEPTAIR_DIST, :onClick => 'onItemXtypeClicked(this)') %><label for="item_xtype_<%= Item::XTYPE_ZEPTAIR_DIST %>"> <img src="<%= THETIS_RELATIVE_URL_ROOT %>/images/zeptair/zeptair_icon.png" title="<%= t('item.type.zeptair_dist') %>" alt="<%= t('item.type.zeptair_dist') %>" /> <%= t('item.type.zeptair_dist') %></label>
+#      &nbsp;<img class="img_btn" src="<%= THETIS_RELATIVE_URL_ROOT %>/images/info.gif" onclick="msg('<%= t('item.zeptair_dist_info') %>');">
+#
+#      <div id="div_zept_cmd" style="padding-left:20px; display:<%= (@item.xtype == Item::XTYPE_ZEPTAIR_DIST)?'block':'none' %>;">
+#        <div style="background-color:#FDFEFF; padding:2px 3px">
+#          <%
+#          zept_cmd = @item.zeptair_command
+#          checked = (!zept_cmd.nil? and zept_cmd.enabled)?'checked':''
+#          %>
+#          <input name="zeptair_command[enabled]" type="hidden" value="0" />
+#          <input type="checkbox" id="check_zept_cmd" name="zeptair_command[enabled]" value="1" onclick="dispZeptCmdEdit(this.checked); modifiedBasic++;" <%= checked %>><label for="check_zept_cmd"><%= t('item.zeptair_dist_cmd') %></label>
+#          <div id="div_zept_cmd_edit" style="padding-left:20px; display:<%= (checked=='checked')?'block':'none' %>;">
+#            <textarea id="zept_cmd" name="zeptair_command[commands]" rows="4" wrap="off" style="width:95%; background-color:black; color:white;" onChange="modifiedBasic++;"><%= (zept_cmd.nil?)?'':zept_cmd.commands %></textarea><br/>
+#            <% checked = (!zept_cmd.nil? and zept_cmd.confirm_exec)?'checked':'' %>
+#            <input name="zeptair_command[confirm_exec]" type="hidden" value="0" />
+#            <input type="checkbox" id="check_zept_cmd_with_confirm" name="zeptair_command[confirm_exec]" value="1" onclick="modifiedBasic++;" <%= checked %>><label for="check_zept_cmd_with_confirm"><%= t('item.zeptair_dist_cmd_with_confirm') %></label>
+#          </div>
+#        </div>
+#      </div>
+#
+#<script type="text/javascript">
+#dispZeptCmdOpt = function(sw)
+#{
+#  var div_zept_cmd = _z("div_zept_cmd");
+#  if (div_zept_cmd) {
+#    div_zept_cmd.style.display = (sw)?"block":"none";
+#  }
+#}
+#
+#dispZeptCmdEdit = function(sw)
+#{
+#  var div_zept_cmd_edit = _z("div_zept_cmd_edit");
+#  if (div_zept_cmd_edit) {
+#    div_zept_cmd_edit.style.display = (sw)?"block":"none";
+#  }
+#}
+#</script>
+#
+    <%
+#    end
+=end
+    %>
   <% end %>
     </td>
   </tr>
 <% end %>
   <tr>
@@ -269,10 +273,23 @@ dispZeptCmdEdit = function(sw)
 <script type="text/javascript">
 <% unless @item.nil? %>
 document.getElementById("item_id").value = "<%= @item.id %>";
 <% end %>
 
+onItemXtypeClicked = function(elem)
+{
+  modifiedBasic++;
+
+  if (typeof(dispZeptCmdOpt) == "function") {
+    if (elem.value == "<%= Item::XTYPE_ZEPTAIR_DIST %>") {
+      dispZeptCmdOpt(elem.checked);
+    } else {
+      dispZeptCmdOpt(false);
+    }
+  }
+}
+
 function doSelectFolder(frm)
 {
   var thetisBoxProgress = prog("TOP-RIGHT");
 
   new Ajax.Updater(

Vulnerable code:     SqlHelper.validate_token([mail_addr])
      users = User.where("email='#{mail_addr}'").to_a
    regexp = Regexp.new("^[ ]*[a-zA-Z0-9_.@\\-#{extra_chars}]+[ ]*$")
      if token.to_s.match(regexp).nil?
    SqlHelper.validate_token([mail_addr])
    email_con.push("(email1='#{mail_addr}')")
    email_con.push("(email2='#{mail_addr}')")
    email_con.push("(email3='#{mail_addr}')")
      org_address = Address.find_by_id(imp_id)
      user = User.where("name='#{user_name}'").first
                  Sysphonic <B>Thetis</B><sup>&reg;</sup>  <small style="text-align:right;">ver.2.2.0</small>&nbsp;
      <%= radio_button('item', 'xtype', Item::XTYPE_INFO, :onClick => 'dispZeptCmdOpt(false); modifiedBasic++') %><label for="item_xtype_<%= Item::XTYPE_INFO %>"> <%= t('item.type.normal') %></label><br/>
      <%= radio_button('item', 'xtype', Item::XTYPE_PROJECT, :onClick => 'dispZeptCmdOpt(false); modifiedBasic++') %><label for="item_xtype_<%= Item::XTYPE_PROJECT %>"> <img src="<%= THETIS_RELATIVE_URL_ROOT %>/images/folder/team_folder.gif" title="<%= t('item.project') %>" alt="<%= t('item.project') %>" /> <%= t('item.type.project') %></label><br/>
    <% if @login_user.admin?(User::AUTH_ZEPTAIR) %>
      <%= radio_button('item', 'xtype', Item::XTYPE_ZEPTAIR_DIST, :onClick => 'dispZeptCmdOpt(this.checked); modifiedBasic++') %><label for="item_xtype_<%= Item::XTYPE_ZEPTAIR_DIST %>"> <img src="<%= THETIS_RELATIVE_URL_ROOT %>/images/zeptair/zeptair_icon.png" title="<%= t('item.type.zeptair_dist') %>" alt="<%= t('item.type.zeptair_dist') %>" /> <%= t('item.type.zeptair_dist') %></label>
      &nbsp;<img class="img_btn" src="<%= THETIS_RELATIVE_URL_ROOT %>/images/info.gif" onclick="msg('<%= t('item.zeptair_dist_info') %>');">

      <div id="div_zept_cmd" style="padding-left:20px; display:<%= (@item.xtype == Item::XTYPE_ZEPTAIR_DIST)?'block':'none' %>;">
        <div style="background-color:#FDFEFF; padding:2px 3px">
          <%
          zept_cmd = @item.zeptair_command
          checked = (!zept_cmd.nil? and zept_cmd.enabled)?'checked':''
          %>
          <input name="zeptair_command[enabled]" type="hidden" value="0" />
          <input type="checkbox" id="check_zept_cmd" name="zeptair_command[enabled]" value="1" onclick="dispZeptCmdEdit(this.checked); modifiedBasic++;" <%= checked %>><label for="check_zept_cmd"><%= t('item.zeptair_dist_cmd') %></label>
          <div id="div_zept_cmd_edit" style="padding-left:20px; display:<%= (checked=='checked')?'block':'none' %>;">
            <textarea id="zept_cmd" name="zeptair_command[commands]" rows="4" wrap="off" style="width:95%; background-color:black; color:white;" onChange="modifiedBasic++;"><%= (zept_cmd.nil?)?'':zept_cmd.commands %></textarea><br/>
            <% checked = (!zept_cmd.nil? and zept_cmd.confirm_exec)?'checked':'' %>
            <input name="zeptair_command[confirm_exec]" type="hidden" value="0" />
            <input type="checkbox" id="check_zept_cmd_with_confirm" name="zeptair_command[confirm_exec]" value="1" onclick="modifiedBasic++;" <%= checked %>><label for="check_zept_cmd_with_confirm"><%= t('item.zeptair_dist_cmd_with_confirm') %></label>
          </div>
        </div>
      </div>

<script type="text/javascript">
dispZeptCmdOpt = function(sw)
{
  if (sw) {
    _z("div_zept_cmd").style.display = "block";
  } else {
    _z("div_zept_cmd").style.display = "none";
  }
}

dispZeptCmdEdit = function(sw)
{
  if (sw) {
    _z("div_zept_cmd_edit").style.display = "block";
  } else {
    _z("div_zept_cmd_edit").style.display = "none";
  }
}
</script>

    <% end %>
Fixed code:       users = User.where(email: mail_addr).to_a
    regexp = Regexp.new("^[ ]*[a-zA-Z0-9_#{extra_chars}]+[ ]*$")
      if token.to_s.match(regexp).nil? \
          and token.to_s.match(/^[ ]*\d+-\d+-\d+[ ]*$/).nil?
    mail_quote = SqlHelper.quote(mail_addr)
    email_con.push("(email1=#{mail_quote})")
    email_con.push("(email2=#{mail_quote})")
    email_con.push("(email3=#{mail_quote})")
      begin
        org_address = Address.find(imp_id)
      rescue => evar
        org_address = nil
      end
      user = User.where(name: user_name).first
                  Sysphonic <B>Thetis</B><sup>&reg;</sup>  <small style="text-align:right;">ver.2.3.0</small>&nbsp;
      <%= radio_button('item', 'xtype', Item::XTYPE_INFO, :onClick => 'onItemXtypeClicked(this)') %><label for="item_xtype_<%= Item::XTYPE_INFO %>"> <%= t('item.type.normal') %></label><br/>
      <%= radio_button('item', 'xtype', Item::XTYPE_PROJECT, :onClick => 'onItemXtypeClicked(this)') %><label for="item_xtype_<%= Item::XTYPE_PROJECT %>"> <img src="<%= THETIS_RELATIVE_URL_ROOT %>/images/folder/team_folder.gif" title="<%= t('item.project') %>" alt="<%= t('item.project') %>" /> <%= t('item.type.project') %></label><br/>
    <%
=begin
#    if @login_user.admin?(User::AUTH_ZEPTAIR)
    %>
#      <%= radio_button('item', 'xtype', Item::XTYPE_ZEPTAIR_DIST, :onClick => 'onItemXtypeClicked(this)') %><label for="item_xtype_<%= Item::XTYPE_ZEPTAIR_DIST %>"> <img src="<%= THETIS_RELATIVE_URL_ROOT %>/images/zeptair/zeptair_icon.png" title="<%= t('item.type.zeptair_dist') %>" alt="<%= t('item.type.zeptair_dist') %>" /> <%= t('item.type.zeptair_dist') %></label>
#      &nbsp;<img class="img_btn" src="<%= THETIS_RELATIVE_URL_ROOT %>/images/info.gif" onclick="msg('<%= t('item.zeptair_dist_info') %>');">
#
#      <div id="div_zept_cmd" style="padding-left:20px; display:<%= (@item.xtype == Item::XTYPE_ZEPTAIR_DIST)?'block':'none' %>;">
#        <div style="background-color:#FDFEFF; padding:2px 3px">
#          <%
#          zept_cmd = @item.zeptair_command
#          checked = (!zept_cmd.nil? and zept_cmd.enabled)?'checked':''
#          %>
#          <input name="zeptair_command[enabled]" type="hidden" value="0" />
#          <input type="checkbox" id="check_zept_cmd" name="zeptair_command[enabled]" value="1" onclick="dispZeptCmdEdit(this.checked); modifiedBasic++;" <%= checked %>><label for="check_zept_cmd"><%= t('item.zeptair_dist_cmd') %></label>
#          <div id="div_zept_cmd_edit" style="padding-left:20px; display:<%= (checked=='checked')?'block':'none' %>;">
#            <textarea id="zept_cmd" name="zeptair_command[commands]" rows="4" wrap="off" style="width:95%; background-color:black; color:white;" onChange="modifiedBasic++;"><%= (zept_cmd.nil?)?'':zept_cmd.commands %></textarea><br/>
#            <% checked = (!zept_cmd.nil? and zept_cmd.confirm_exec)?'checked':'' %>
#            <input name="zeptair_command[confirm_exec]" type="hidden" value="0" />
#            <input type="checkbox" id="check_zept_cmd_with_confirm" name="zeptair_command[confirm_exec]" value="1" onclick="modifiedBasic++;" <%= checked %>><label for="check_zept_cmd_with_confirm"><%= t('item.zeptair_dist_cmd_with_confirm') %></label>
#          </div>
#        </div>
#      </div>
#
#<script type="text/javascript">
#dispZeptCmdOpt = function(sw)
#{
#  var div_zept_cmd = _z("div_zept_cmd");
#  if (div_zept_cmd) {
#    div_zept_cmd.style.display = (sw)?"block":"none";
#  }
#}
#
#dispZeptCmdEdit = function(sw)
#{
#  var div_zept_cmd_edit = _z("div_zept_cmd_edit");
#  if (div_zept_cmd_edit) {
#    div_zept_cmd_edit.style.display = (sw)?"block":"none";
#  }
#}
#</script>
#
    <%
#    end
=end
    %>
onItemXtypeClicked = function(elem)
{
  modifiedBasic++;

  if (typeof(dispZeptCmdOpt) == "function") {
    if (elem.value == "<%= Item::XTYPE_ZEPTAIR_DIST %>") {
      dispZeptCmdOpt(elem.checked);
    } else {
      dispZeptCmdOpt(false);
    }
  }
}