CVE-2014-125061: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: ** UNSUPPORTED WHEN ASSIGNED ** A vulnerability was found in peel filebroker and classified as critical. Affected by this issue is the function select_transfer_status_desc of the file lib/common.rb. The manipulation leads to sql injection. The name of the patch is 91097e26a6c84d3208a351afaa52e0f62e5853ef. It is recommended to apply a patch to fix this issue. The identifier of this vulnerability is VDB-217616. NOTE: This vulnerability only affects products that are no longer supported by the maintainer.
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: 9.8
Published: 2023-01-07T13:15Z
Repository: https://github.com/peel/filebroker
Language: Ruby
Files changed: lib/common.rb, lib/filebroker.rb, public/filebroker_service.xsd
Commit date: 2014-02-18 12:23:15 +0100
Commit message: filter out transferred + sql injection fixes

Diff: diff --git a/lib/common.rb b/lib/common.rb
index b8f7379..d947400 100644
--- a/lib/common.rb
+++ b/lib/common.rb
@@ -501,16 +501,19 @@ class Database
   def select_transfer_status_desc(status_id)
     @db.exec("SELECT status_desc FROM fb_transfer_status_dict WHERE status_id=#{status_id}")[0]['status_desc']
   end
 
   def insert_file_status(transfer_id, file, status_id, status_time)
-    @db.exec("INSERT into fb_file_status (transfer_id, filename, status_id, status_time) VALUES (#{transfer_id}, '#{file}', #{status_id}, '#{status_time}')")
+    sql = "INSERT into fb_file_status (transfer_id, filename, status_id, status_time) VALUES ($1::int, $2::text, $3::int, $4::timestamp)"
+    @db.exec(sql, [transfer_id, file, status_id, status_time])
   end
 
   def update_file_status(transfer_id, file, status_id, status_time)
-    if @db.exec("SELECT * FROM fb_file_status WHERE transfer_id=#{transfer_id} AND filename='#{file}'").count > 0
-      @db.exec("UPDATE fb_file_status SET status_id=#{status_id}, status_time='#{status_time}' WHERE transfer_id=#{transfer_id} AND filename='#{file}'")
+    sql_count = "SELECT * FROM fb_file_status WHERE transfer_id=$1::int AND filename=$2::text"
+    if @db.exec(sql_count, [transfer_id, file]).count > 0
+      sql_update = "UPDATE fb_file_status SET status_id=$1::int, status_time=$2::timestamp WHERE transfer_id=$3::int AND filename=$4::text"
+      @db.exec(sql_update, [status_id, status_time, transfer_id, file])
     else
       insert_file_status(transfer_id, file, status_id, status_time)
     end
   end
 
@@ -553,10 +556,27 @@ class Database
 
   def select_running_transfers_by_source(status_id, source_id, source_path)
     @db.exec("SELECT fb_transfer.transfer_id FROM fb_transfer, fb_transfer_status WHERE fb_transfer.source_id=#{source_id} AND fb_transfer_status.status_id=#{status_id} AND fb_transfer.source_path='#{source_path}' AND fb_transfer.transfer_id=fb_transfer_status.transfer_id")
   end
 
+  def select_files_by_transfer_status(status_id, source_id, source_path, file_name)
+    sql = "
+    SELECT 
+      fb_transfer.transfer_id, 
+      fb_file_status.filename,
+      fb_file_status.status_time
+    FROM 
+        fb_transfer, fb_transfer_status, public.fb_file_status
+    WHERE fb_transfer.source_id = $1::bigint 
+      AND fb_transfer_status.status_id = $2::int
+      AND fb_transfer.source_path = $3::text
+      AND fb_transfer.transfer_id = fb_transfer_status.transfer_id
+      AND fb_file_status.filename = $4::text
+      AND fb_file_status.transfer_id = fb_transfer.transfer_id"
+    @db.exec(sql, [source_id, status_id, source_path, file_name])
+  end
+
   def select_running_transfers
     sql = "
     SELECT DISTINCT
       fb_transfer.transfer_id,
       fb_transfer.transfer_hash,
@@ -743,13 +763,13 @@ class Database
     FROM
       public.fb_file_status,
       public.fb_file_status_dict
     WHERE
       fb_file_status.status_id = fb_file_status_dict.status_id AND
-      fb_file_status.transfer_id = #{transfer_id}
+      fb_file_status.transfer_id = $1::int
     ORDER BY fb_file_status.filename ASC"
-    @db.exec(sql)
+    @db.exec(sql, [transfer_id])
   end
 
   def add_client(client)
     if @db.exec("SELECT login FROM fb_client WHERE login=E'#{client['login']}'")[0] != nil
       raise ClientAlreadyExist, "login already exist"
diff --git a/lib/filebroker.rb b/lib/filebroker.rb
index 60e02da..6340cdf 100644
--- a/lib/filebroker.rb
+++ b/lib/filebroker.rb
@@ -1226,10 +1226,11 @@ class FBService < Sinatra::Base
       req['address'] 	= soap_body.xpath("//#{prefix}:Address/text()").to_s
       req['port'] 		= soap_body.xpath("//#{prefix}:Port/text()").to_s
       req['login'] 		= soap_body.xpath("//#{prefix}:Login/text()").to_s
       req['password'] = soap_body.xpath("//#{prefix}:Password/text()").to_s
       req['path'] 		= soap_body.xpath("//#{prefix}:Path/text()").to_s
+      req['filter_out_transferred'] = soap_body.xpath("//#{prefix}:FilterOutTransferred/text()").to_s
 
       begin
         source = @db.select_account(req)
       rescue Database::AccountNotExist
         source = @db.insert_account(req)
@@ -1267,17 +1268,24 @@ class FBService < Sinatra::Base
       end
 
       # Convert time format
       list.map { |j| j['mtime'] = DateTime.parse(j['mtime']) }
 
-      # Remove currently transferred files
+      # Remove currently transferring files
       @db.select_running_transfers_by_source(FBService::TRANSFER_RUNNING, source['account_id'], source['path']).each { |i|
         @db.select_transfer_files(i['transfer_id']).each { |j|
           list.delete_if { |k| k['name'] == j['filename'] }
         }
       }
 
+      # Remove already transferred files - optional depends on FilterOutTransferred option
+      if source['filter_out_transferred'] == 'true'
+        list.delete_if { |k| 
+          @db.select_files_by_transfer_status(FBService::TRANSFER_COMPLETED_SUCCESSFULLY, source['account_id'], source['path'], k['name']).ntuples() > 0
+        }
+      end
+
       # Remove temporary files
       list.delete_if { |j| j['name'] =~ /\.partial$/ }
 
       builder(:list_response, :locals => { :list => list })
     rescue
diff --git a/public/filebroker_service.xsd b/public/filebroker_service.xsd
index a941871..ecd73c3 100644
--- a/public/filebroker_service.xsd
+++ b/public/filebroker_service.xsd
@@ -1350,10 +1350,11 @@
       <xs:element name="Address" type="xs:string" minOccurs="1" maxOccurs="1" />
       <xs:element name="Port" type="xs:int" minOccurs="1" maxOccurs="1" />
       <xs:element name="Login" type="xs:string" minOccurs="1" maxOccurs="1" />
       <xs:element name="Password" type="xs:string" minOccurs="0" maxOccurs="1" />
       <xs:element name="Path" type="xs:string" minOccurs="1" maxOccurs="1" />
+      <xs:element name="FilterOutTransferred" type="xs:boolean" minOccurs="0" maxOccurs="1" />
     </xs:sequence>
   </xs:complexType>
 
   <xs:complexType name="FileType">
     <xs:sequence>

Vulnerable code:     @db.exec("INSERT into fb_file_status (transfer_id, filename, status_id, status_time) VALUES (#{transfer_id}, '#{file}', #{status_id}, '#{status_time}')")
    if @db.exec("SELECT * FROM fb_file_status WHERE transfer_id=#{transfer_id} AND filename='#{file}'").count > 0
      @db.exec("UPDATE fb_file_status SET status_id=#{status_id}, status_time='#{status_time}' WHERE transfer_id=#{transfer_id} AND filename='#{file}'")
      fb_file_status.transfer_id = #{transfer_id}
    @db.exec(sql)
      # Remove currently transferred files
Fixed code:     sql = "INSERT into fb_file_status (transfer_id, filename, status_id, status_time) VALUES ($1::int, $2::text, $3::int, $4::timestamp)"
    @db.exec(sql, [transfer_id, file, status_id, status_time])
    sql_count = "SELECT * FROM fb_file_status WHERE transfer_id=$1::int AND filename=$2::text"
    if @db.exec(sql_count, [transfer_id, file]).count > 0
      sql_update = "UPDATE fb_file_status SET status_id=$1::int, status_time=$2::timestamp WHERE transfer_id=$3::int AND filename=$4::text"
      @db.exec(sql_update, [status_id, status_time, transfer_id, file])
  def select_files_by_transfer_status(status_id, source_id, source_path, file_name)
    sql = "
    SELECT 
      fb_transfer.transfer_id, 
      fb_file_status.filename,
      fb_file_status.status_time
    FROM 
        fb_transfer, fb_transfer_status, public.fb_file_status
    WHERE fb_transfer.source_id = $1::bigint 
      AND fb_transfer_status.status_id = $2::int
      AND fb_transfer.source_path = $3::text
      AND fb_transfer.transfer_id = fb_transfer_status.transfer_id
      AND fb_file_status.filename = $4::text
      AND fb_file_status.transfer_id = fb_transfer.transfer_id"
    @db.exec(sql, [source_id, status_id, source_path, file_name])
  end

      fb_file_status.transfer_id = $1::int
    @db.exec(sql, [transfer_id])
      req['filter_out_transferred'] = soap_body.xpath("//#{prefix}:FilterOutTransferred/text()").to_s
      # Remove currently transferring files
      # Remove already transferred files - optional depends on FilterOutTransferred option
      if source['filter_out_transferred'] == 'true'
        list.delete_if { |k| 
          @db.select_files_by_transfer_status(FBService::TRANSFER_COMPLETED_SUCCESSFULLY, source['account_id'], source['path'], k['name']).ntuples() > 0
        }
      end

      <xs:element name="FilterOutTransferred" type="xs:boolean" minOccurs="0" maxOccurs="1" />