diff options
| -rwxr-xr-x | examples/example.rb | 17 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/cell.rb | 20 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/row.rb | 3 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/worksheet.rb | 10 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_cell.rb | 30 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_row.rb | 17 |
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) |