Merge pull request #186 from tablecheck/escape-formulas-improvement

escape_formulas - add settings for global, workbook, worksheet, row and cell levels

5 files changed, 81 insertions, 17 deletions

diff --git a/lib/axlsx.rb b/lib/axlsx.rb
index 2ee8a1d8..9dba9144 100644
--- a/lib/axlsx.rb
+++ b/lib/axlsx.rb
@@ -200,4 +200,19 @@ module Axlsx
   def self.trust_input=(trust_me)
     @trust_input = trust_me
   end
+
+  # Whether to treat values starting with an equals sign as formulas or as literal strings.
+  # Allowing user-generated data to be interpreted as formulas is a security risk.
+  # See https://www.owasp.org/index.php/CSV_Injection for details.
+  # @return [Boolean]
+  def self.escape_formulas
+    @escape_formulas.nil? ? false : @escape_formulas
+  end
+
+  # Sets whether to treat values starting with an equals sign as formulas or as literal strings.
+  # @param [Boolean] value The value to set.
+  def self.escape_formulas=(value)
+    Axlsx.validate_boolean(value)
+    @escape_formulas = value
+  end
 end
diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb
index e1c7fb05..71a3c375 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -57,6 +57,7 @@ module Axlsx
   require 'axlsx/workbook/worksheet/sheet_format_pr.rb'
   require 'axlsx/workbook/worksheet/pane.rb'
   require 'axlsx/workbook/worksheet/selection.rb'
+
   # The Workbook class is an xlsx workbook that manages worksheets, charts, drawings and styles.
   # The following parts of the Office Open XML spreadsheet specification are not implimented in this version.
   #
@@ -239,6 +240,7 @@ module Axlsx
       @bold_font_multiplier = BOLD_FONT_MULTIPLIER
       @font_scale_divisor = FONT_SCALE_DIVISOR
 
+      self.escape_formulas = options[:escape_formulas].nil? ? Axlsx.escape_formulas : options[:escape_formulas]
       self.date1904 = !options[:date1904].nil? && options[:date1904]
       yield self if block_given?
     end
@@ -258,6 +260,19 @@ module Axlsx
     # @return [Boolean]
     def self.date1904() @@date1904; end
 
+    # Whether to treat values starting with an equals sign as formulas or as literal strings.
+    # Allowing user-generated data to be interpreted as formulas is a security risk.
+    # See https://www.owasp.org/index.php/CSV_Injection for details.
+    # @return [Boolean]
+    attr_reader :escape_formulas
+
+    # Sets whether to treat values starting with an equals sign as formulas or as literal strings.
+    # @param [Boolean] value The value to set.
+    def escape_formulas=(value)
+      Axlsx.validate_boolean(value)
+      @escape_formulas = value
+    end
+
     # Indicates if the workbook should use autowidths or not.
     # @note This gem no longer depends on RMagick for autowidth
     #     calculation. Thus the performance benefits of turning this off are
diff --git a/lib/axlsx/workbook/worksheet/cell.rb b/lib/axlsx/workbook/worksheet/cell.rb
index 364de1e9..5e2661c3 100644
--- a/lib/axlsx/workbook/worksheet/cell.rb
+++ b/lib/axlsx/workbook/worksheet/cell.rb
@@ -28,10 +28,9 @@ module Axlsx
     # @option options [String] color an 8 letter rgb specification
     # @option options [Number] formula_value The value to cache for a formula cell.
     # @option options [Symbol] scheme must be one of :none, major, :minor
-    # @option options [Boolean] escape_formulas - Whether to treat a value starting with an equal
-    #    sign as formula (default) or as simple string.
-    #    Allowing user generated data to be interpreted as formulas can be dangerous
-    #   (see https://www.owasp.org/index.php/CSV_Injection for details).
+    # @option options [Boolean] escape_formulas Whether to treat values starting with an equals
+    #   sign as formulas or as literal strings. Allowing user-generated data to be interpreted as
+    #   formulas is a security risk. See https://www.owasp.org/index.php/CSV_Injection for details.
     def initialize(row, value = nil, options = {})
       @row = row
       # Do not use instance vars if not needed to use less RAM
@@ -40,15 +39,13 @@ module Axlsx
       type = options.delete(:type) || cell_type_from_value(value)
       self.type = type unless type == :string
 
-      escape_formulas = options[:escape_formulas]
-      self.escape_formulas = escape_formulas unless escape_formulas.nil?
-
       val = options.delete(:style)
       self.style = val unless val.nil? || val == 0
       val = options.delete(:formula_value)
       self.formula_value = val unless val.nil?
 
       parse_options(options)
+      self.escape_formulas = row.worksheet.escape_formulas if escape_formulas.nil?
 
       self.value = value
       value.cell = self if contains_rich_text?
@@ -73,6 +70,10 @@ module Axlsx
     CELL_TYPES = [:date, :time, :float, :integer, :richtext,
                   :string, :boolean, :iso_8601, :text].freeze
 
+    # Leading characters that indicate a formula.
+    # See: https://owasp.org/www-community/attacks/CSV_Injection
+    FORMULA_PREFIXES = ['-', '=', '+', '@', '%', '|', "\r", "\t"].freeze
+
     # The index of the cellXfs item to be applied to this cell.
     # @return [Integer]
     # @see Axlsx::Styles
@@ -132,16 +133,17 @@ module Axlsx
       self.value = @value unless !defined?(@value) || @value.nil?
     end
 
-    # Whether to treat a value starting with an equal
-    #    sign as formula (default) or as simple string.
-    #    Allowing user generated data to be interpreted as formulas can be dangerous
-    #   (see https://www.owasp.org/index.php/CSV_Injection for details).
+    # Whether to treat values starting with an equals sign as formulas or as literal strings.
+    # Allowing user-generated data to be interpreted as formulas is a security risk.
+    # See https://www.owasp.org/index.php/CSV_Injection for details.
     # @return [Boolean]
     attr_reader :escape_formulas
 
-    def escape_formulas=(v)
-      Axlsx.validate_boolean(v)
-      @escape_formulas = v
+    # Sets whether to treat values starting with an equals sign as formulas or as literal strings.
+    # @param [Boolean] value The value to set.
+    def escape_formulas=(value)
+      Axlsx.validate_boolean(value)
+      @escape_formulas = value
     end
 
     # The value of this cell.
@@ -170,7 +172,8 @@ module Axlsx
         !is_text_run? &&          # No inline styles
         [email protected]? &&           # Not nil
         [email protected]? &&         # Not empty
-        [email protected]_with?(?=)  # Not a formula
+        !is_formula? &&           # Not a formula
+        !is_array_formula?        # Not an array formula
     end
 
     # The inline font_name property for the cell
@@ -384,10 +387,12 @@ module Axlsx
     def is_formula?
       return false if escape_formulas
 
-      type == :string && @value.to_s.start_with?(?=)
+      type == :string && @value.to_s.start_with?(*FORMULA_PREFIXES)
     end
 
     def is_array_formula?
+      return false if escape_formulas
+
       type == :string && @value.to_s.start_with?('{=') && @value.to_s.end_with?('}')
     end
 
diff --git a/lib/axlsx/workbook/worksheet/row.rb b/lib/axlsx/workbook/worksheet/row.rb
index 97a7adab..119cd10b 100644
--- a/lib/axlsx/workbook/worksheet/row.rb
+++ b/lib/axlsx/workbook/worksheet/row.rb
@@ -23,6 +23,7 @@ module Axlsx
     # @option options [Array] values
     # @option options [Array, Symbol] types
     # @option options [Array, Integer] style
+    # @option options [Array, Boolean] escape_formulas
     # @option options [Float] height the row's height (in points)
     # @option options [Integer] offset - add empty columns before values
     # @see Row#array_to_cells
@@ -117,6 +118,15 @@ module Axlsx
       end
     end
 
+    # Sets escape_formulas for every cell in this row. This determines whether to treat
+    # values starting with an equals sign as formulas or as literal strings.
+    # @param [Array, Boolean] value The value to set.
+    def escape_formulas=(value)
+      each_with_index do |cell, index|
+        cell.escape_formulas = value.is_a?(Array) ? value[index] : value
+      end
+    end
+
     # @see height
     def height=(v)
       unless v.nil?
@@ -145,6 +155,7 @@ module Axlsx
     # @option options [Array] values
     # @option options [Array, Symbol] types
     # @option options [Array, Integer] style
+    # @option options [Array, Boolean] escape_formulas
     def array_to_cells(values, options = {})
       DataTypeValidator.validate :array_to_cells, Array, values
       types, style, formula_values, escape_formulas, offset = options.delete(:types), options.delete(:style), options.delete(:formula_values), options.delete(:escape_formulas), options.delete(:offset)
@@ -152,7 +163,7 @@ module Axlsx
       values.each_with_index do |value, index|
         options[:style] = style.is_a?(Array) ? style[index] : style if style
         options[:type] = types.is_a?(Array) ? types[index] : types if types
-        options[:escape_formulas] = escape_formulas.is_a?(Array) ? escape_formulas[index] : escape_formulas if escape_formulas
+        options[:escape_formulas] = escape_formulas.is_a?(Array) ? escape_formulas[index] : escape_formulas unless escape_formulas.nil?
         options[:formula_value] = formula_values[index] if formula_values.is_a?(Array)
 
         self[index + offset.to_i] = Cell.new(self, value, options)
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index 3333c3f4..38fb5254 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -14,11 +14,14 @@ module Axlsx
     # @option options [Hash] print_options A hash containing print options for this worksheet. @see PrintOptions
     # @option options [Hash] header_footer A hash containing header/footer options for this worksheet. @see HeaderFooter
     # @option options [Boolean] show_gridlines Whether gridlines should be shown for this sheet.
+    # @option options [Boolean] escape_formulas Whether formulas should be escaped by default. Can be overridden at a
+    #   row/cell level.
     def initialize(wb, options = {})
       self.workbook = wb
       @sheet_protection = nil
       initialize_page_options(options)
       parse_options options
+      self.escape_formulas = wb.escape_formulas if @escape_formulas.nil?
       @workbook.worksheets << self
       @sheet_id = index + 1
       yield self if block_given?
@@ -43,6 +46,20 @@ module Axlsx
       @name ||= "Sheet" + (index + 1).to_s
     end
 
+    # Whether to treat values starting with an equals sign as formulas or as literal strings.
+    # Allowing user-generated data to be interpreted as formulas is a security risk.
+    # See https://www.owasp.org/index.php/CSV_Injection for details.
+    # @return [Boolean]
+    attr_reader :escape_formulas
+
+    # Sets whether to treat values starting with an equals sign as formulas or as literal strings.
+    # @param [Boolean] value The value to set.
+    # @return [Boolean]
+    def escape_formulas=(value)
+      Axlsx.validate_boolean(value)
+      @escape_formulas = value
+    end
+
     # Specifies the visible state of this sheet. Allowed states are
     # :visible, :hidden or :very_hidden. The default value is :visible.
     #
@@ -410,6 +427,7 @@ module Axlsx
     #    Allowing user generated data to be interpreted as formulas can be dangerous
     #   (see https://www.owasp.org/index.php/CSV_Injection for details).
     def add_row(values = [], options = {})
+      options[:escape_formulas] = escape_formulas if options[:escape_formulas].nil?
       row = Row.new(self, values, options)
       update_column_info row, options.delete(:widths)
       yield row if block_given?