CVE-2023-43640: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: TaxonWorks is a web-based workbench designed for taxonomists and biodiversity scientists. Prior to version 0.34.0, a SQL injection vulnerability was found in TaxonWorks that allows authenticated attackers to extract arbitrary data from the TaxonWorks database (including the users table). This issue may lead to information disclosure. Version 0.34.0 contains a fix for the issue.

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.
CVSS v3 base score: 6.5
Published: 2023-09-22T18:15Z
Repository: https://github.com/SpeciesFileGroup/taxonworks
Language: Ruby
Files changed: app/models/collection_object_observation.rb, app/models/content.rb, app/models/language.rb, app/models/topic.rb
Commit date: 2023-06-29 14:24:25 -0300
Commit message: Optimized several find_for_autocomplete by removing redundant expressions

Diff: diff --git a/app/models/collection_object_observation.rb b/app/models/collection_object_observation.rb
index 547433f395..af4db9f972 100644
--- a/app/models/collection_object_observation.rb
+++ b/app/models/collection_object_observation.rb
@@ -32,11 +32,10 @@ class CollectionObjectObservation < ApplicationRecord
   validates_presence_of :data
 
   # @param [ActionController::Parameters] params
   # @return [Scope]
   def self.find_for_autocomplete(params)
-    term = "#{params[:term]}%"
-    where('data LIKE ? OR data ILIKE ? OR data = ?', term, "#{term}%", '%term')
+    where('data ILIKE ? OR data = ?', "#{params[:term]}%", params[:term])
       .where(project_id: params[:project_id])
   end
 
 end
diff --git a/app/models/content.rb b/app/models/content.rb
index 1c6f6df4a5..eb47d76bd0 100644
--- a/app/models/content.rb
+++ b/app/models/content.rb
@@ -90,11 +90,11 @@ class Content < ApplicationRecord
   def self.for_page_layout(otu_page_layout_id)
     where('topic_id in (?)', OtuPageLayout.where(id: otu_page_layout_id).first.topics.pluck(:id))
   end
 
   def self.find_for_autocomplete(params)
-    where('text ILIKE ? OR text ILIKE ?', "#{params[:term]}%", "%#{params[:term]}%")
+    where('text ILIKE ?', "%#{params[:term]}%")
   end
 
   def self.used_recently(user_id, project_id)
     Content.touched_by(user_id).where(project_id:).order(updated_at: :desc).limit(6).to_a
   end
diff --git a/app/models/language.rb b/app/models/language.rb
index a87b5808c6..056c2200f5 100644
--- a/app/models/language.rb
+++ b/app/models/language.rb
@@ -50,12 +50,11 @@ class Language < ApplicationRecord
     d = t[:alpha_3_terminologic].matches_any(value)
     Language.where(a.or(b).or(c).or(d).to_sql)
   end
 
   def self.find_for_autocomplete(params)
-    term = "#{params[:term]}%"
-    where('english_name ILIKE ? OR english_name = ?', term, params[:term])
+    where('english_name ILIKE ? OR english_name = ?', "#{params[:term]}%", params[:term])
   end
 
   # @param klass ['Source' || 'Serial']
   def self.select_optimized(user_id, project_id, klass = 'Source')
     language_ids = case klass
diff --git a/app/models/topic.rb b/app/models/topic.rb
index b45ac11901..17276f370e 100644
--- a/app/models/topic.rb
+++ b/app/models/topic.rb
@@ -22,13 +22,14 @@ class Topic < ControlledVocabularyTerm
 
   scope :used_on_klass, -> (klass) { joins(:citations).where(citations: {citation_object_type: klass}) }
 
   # TODO: Deprecate for CVT + params (if not already done)
   def self.find_for_autocomplete(params)
-    term = "#{params[:term]}%"
-    where_string = "name LIKE '#{term}' OR name ILIKE '%#{term}' OR name = '#{term}' OR definition ILIKE '%#{term}'"
-    ControlledVocabularyTerm.where(where_string).where(project_id: params[:project_id], type: 'Topic')
+    term = params[:term]
+    ControlledVocabularyTerm
+      .where('name ILIKE ? OR name = ? OR definition ILIKE ?', "%#{term}%", term, "%#{term}%")
+      .where(project_id: params[:project_id], type: 'Topic')
   end
 
   # @param used_on [String] one of `Citation` (default) or `Content`
   # @return [Scope]
   #    the max 10 most recently used topics, as used on Content or Citation

Vulnerable code:     term = "#{params[:term]}%"
    where('data LIKE ? OR data ILIKE ? OR data = ?', term, "#{term}%", '%term')
    where('text ILIKE ? OR text ILIKE ?', "#{params[:term]}%", "%#{params[:term]}%")
    term = "#{params[:term]}%"
    where('english_name ILIKE ? OR english_name = ?', term, params[:term])
    term = "#{params[:term]}%"
    where_string = "name LIKE '#{term}' OR name ILIKE '%#{term}' OR name = '#{term}' OR definition ILIKE '%#{term}'"
    ControlledVocabularyTerm.where(where_string).where(project_id: params[:project_id], type: 'Topic')
Fixed code:     where('data ILIKE ? OR data = ?', "#{params[:term]}%", params[:term])
    where('text ILIKE ?', "%#{params[:term]}%")
    where('english_name ILIKE ? OR english_name = ?', "#{params[:term]}%", params[:term])
    term = params[:term]
    ControlledVocabularyTerm
      .where('name ILIKE ? OR name = ? OR definition ILIKE ?', "%#{term}%", term, "%#{term}%")
      .where(project_id: params[:project_id], type: 'Topic')