Add option to protect against formula injection attacks (#34)

Caxlsx used to treat cell values beginning with an equal sign as formula by default.

This can be dangerous if the input data is user generated or coming from other untrusted sources (see https://www.owasp.org/index.php/CSV_Injection for details).

This commit adds a new option `escape_formulas` that can be used with `#add_row` and on instances of `Cell`. If set to true, cell values beginning with an equal sign are treated as normal strings (and will be displayed literally by Excel and co.)

6 files changed, 94 insertions, 3 deletions

diff --git a/examples/example.rb b/examples/example.rb
index 9266f830..8fcfe7d4 100755
--- a/examples/example.rb
+++ b/examples/example.rb
@@ -21,6 +21,7 @@ examples << :images
 examples << :format_dates
 examples << :mbcs
 examples << :formula
+examples << :escape_formulas
 examples << :auto_filter
 examples << :sheet_protection
 examples << :data_types
@@ -364,6 +365,21 @@ if examples.include? :formula
 end
 ##```
 
+##Escaping formulas for cells
+#```ruby
+if examples.include? :escape_formulas
+  wb.add_worksheet(:name => "Escaping Formulas") do |sheet|
+    sheet.add_row [1, 2, 3, "=SUM(A2:C2)"], escape_formulas: true
+    sheet.add_row [
+      "=IF(2+2=4,4,5)",
+      "=IF(13+13=4,4,5)",
+      "=IF(99+99=4,4,5)"
+    ], escape_formulas: [true, false, true]
+  end
+  p.serialize("escaped_formulas.xlsx")
+end
+##```
+
 ##Auto Filter
 
 #```ruby
@@ -882,4 +898,3 @@ if examples.include? :tab_color
   p.serialize 'tab_color.xlsx'
 end
 ##```
-
diff --git a/lib/axlsx/workbook/worksheet/cell.rb b/lib/axlsx/workbook/worksheet/cell.rb
index 99b3f8b0..53bea28b 100644
--- a/lib/axlsx/workbook/worksheet/cell.rb
+++ b/lib/axlsx/workbook/worksheet/cell.rb
@@ -30,6 +30,10 @@ 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).
     def initialize(row, value = nil, options = {})
       @row = row
       # Do not use instance vars if not needed to use less RAM
@@ -38,6 +42,8 @@ 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
@@ -102,6 +108,18 @@ 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).
+    # @return [Boolean]
+    attr_reader :escape_formulas
+
+    def escape_formulas=(v)
+      Axlsx.validate_boolean(v)
+      @escape_formulas = v
+    end
+
     # The value of this cell.
     # @return [String, Integer, Float, Time, Boolean] casted value based on cell's type attribute.
     attr_reader :value
@@ -324,6 +342,8 @@ module Axlsx
     end
 
     def is_formula?
+      return false if escape_formulas
+
       type == :string && @value.to_s.start_with?(?=)
     end
 
diff --git a/lib/axlsx/workbook/worksheet/row.rb b/lib/axlsx/workbook/worksheet/row.rb
index b394279f..decd27c7 100644
--- a/lib/axlsx/workbook/worksheet/row.rb
+++ b/lib/axlsx/workbook/worksheet/row.rb
@@ -147,10 +147,11 @@ module Axlsx
     # @option options [Array, Integer] style
     def array_to_cells(values, options={})
       DataTypeValidator.validate :array_to_cells, Array, values
-      types, style, formula_values = options.delete(:types), options.delete(:style), options.delete(:formula_values)
+      types, style, formula_values, escape_formulas = options.delete(:types), options.delete(:style), options.delete(:formula_values), options.delete(:escape_formulas)
       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[:formula_value] = formula_values[index] if formula_values.is_a?(Array)
 
         self[index] = Cell.new(self, value, options)
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index 9cfefa51..4fb95d4c 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -390,6 +390,12 @@ module Axlsx
     # @example - use << alias
     #     ws << [3, 4, 5], :types => [nil, :float]
     #
+    # @example - specify whether a row should escape formulas or not
+    #     ws.add_row ['=IF(2+2=4,4,5)', 2, 3], :escape_formulas=>true
+    #
+    # @example - specify whether a certain cells in a row should escape formulas or not
+    #     ws.add_row ['=IF(2+2=4,4,5)', '=IF(13+13=4,4,5)'], :escape_formulas=>[true, false]
+    #
     # @see Worksheet#column_widths
     # @return [Row]
     # @option options [Array] values
@@ -397,6 +403,10 @@ module Axlsx
     # @option options [Array, Integer] style
     # @option options [Array] widths each member of the widths array will affect how auto_fit behavies.
     # @option options [Float] height the row's height (in points)
+    # @option options [Array, 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).
     def add_row(values=[], options={})
       row = Row.new(self, values, options)
       update_column_info row, options.delete(:widths)
diff --git a/test/workbook/worksheet/tc_cell.rb b/test/workbook/worksheet/tc_cell.rb
index 11b54f44..d3ca9fe8 100644
--- a/test/workbook/worksheet/tc_cell.rb
+++ b/test/workbook/worksheet/tc_cell.rb
@@ -8,7 +8,7 @@ class TestCell < Test::Unit::TestCase
     @ws = p.workbook.add_worksheet :name=>"hmmm"
     p.workbook.styles.add_style :sz=>20
     @row = @ws.add_row
-    @c = @row.add_cell 1, :type=>:float, :style=>1
+    @c = @row.add_cell 1, :type=>:float, :style=>1, :escape_formulas=>true
     data = (0..26).map { |index| index }
     @ws.add_row data
     @cAA = @ws["AA2"]
@@ -19,6 +19,7 @@ class TestCell < Test::Unit::TestCase
     assert_equal(@c.type, :float, "type option is applied")
     assert_equal(@c.style, 1, "style option is applied")
     assert_equal(@c.value, 1.0, "type option is applied and value is casted")
+    assert_equal(@c.escape_formulas, true, "escape formulas option is applied")
   end
 
   def test_style_date_data
@@ -321,6 +322,33 @@ class TestCell < Test::Unit::TestCase
     assert(doc.xpath("//f[text()='IF(2+2=4,4,5)']").any?)
   end
 
+  def test_to_xml_string_formula_escaped
+    p = Axlsx::Package.new
+    ws = p.workbook.add_worksheet do |sheet|
+      sheet.add_row ["=IF(2+2=4,4,5)"], escape_formulas: true
+    end
+    doc = Nokogiri::XML(ws.to_xml_string)
+    doc.remove_namespaces!
+    assert(doc.xpath("//t[text()='=IF(2+2=4,4,5)']").any?)
+  end
+
+  def test_to_xml_string_formula_escape_array_parameter
+    p = Axlsx::Package.new
+    ws = p.workbook.add_worksheet do |sheet|
+      sheet.add_row [
+        "=IF(2+2=4,4,5)",
+        "=IF(13+13=4,4,5)",
+        "=IF(99+99=4,4,5)"
+      ], escape_formulas: [true, false, true]
+    end
+    doc = Nokogiri::XML(ws.to_xml_string)
+    doc.remove_namespaces!
+
+    assert(doc.xpath("//t[text()='=IF(2+2=4,4,5)']").any?)
+    assert(doc.xpath("//f[text()='IF(13+13=4,4,5)']").any?)
+    assert(doc.xpath("//t[text()='=IF(99+99=4,4,5)']").any?)
+  end
+
   def test_to_xml_string_array_formula
     p = Axlsx::Package.new
     ws = p.workbook.add_worksheet do |sheet|
diff --git a/test/workbook/worksheet/tc_row.rb b/test/workbook/worksheet/tc_row.rb
index 43e7d23f..f909129c 100644
--- a/test/workbook/worksheet/tc_row.rb
+++ b/test/workbook/worksheet/tc_row.rb
@@ -60,6 +60,23 @@ class TestRow < Test::Unit::TestCase
 
   end
 
+  def test_array_to_cells_with_escape_formulas
+    row = ['=HYPERLINK("http://www.example.com", "CSV Payload")', '=Bar']
+    @ws.add_row row, escape_formulas: true
+
+    assert_equal @ws.rows.last.cells[0].escape_formulas, true
+    assert_equal @ws.rows.last.cells[1].escape_formulas, true
+  end
+
+  def test_array_to_cells_with_escape_formulas_as_an_array
+    row = ['=HYPERLINK("http://www.example.com", "CSV Payload")', '+Foo', '-Bar']
+    @ws.add_row row, escape_formulas: [true, false, true]
+
+    assert_equal @ws.rows.last.cells.first.escape_formulas, true
+    assert_equal @ws.rows.last.cells[1].escape_formulas, false
+    assert_equal @ws.rows.last.cells[2].escape_formulas, true
+  end
+
   def test_custom_height
     @row.height = 20
     assert(@row.custom_height)