Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Be prepared for CSV injections in spreadsheet

by Tux (Abbot)
on Oct 18, 2017 at 11:34 UTC ( #1201572=perlmeditation: print w/replies, xml ) Need Help??

Read this article to get an idea of how dangerous it can be to blindly accept macro's in spreadsheets. Be it MS Excel or Google spreadsheets, they all suffer.

You cannot blame CSV for it. CSV is just passive data.

Once you load or open a CSV file into something dangerous as a spreadsheet program that allows formula's to be execcuted on open, all bets are off. Or are they?

The upcoming Text::CSV_XS has added a new feature to optional take actions when a field contains a leading =, which to most spreadsheet programs indicates a formula.

On both parsing and generating CSV, you will be able to specify what you want to do (where "formula" does not go beyond the fact that the field starts with a =):

  • Do nothing special (default behavior) and leave the text as-is
  • Die whenever a formula is seen
  • Croak when a formula is seen
  • Give a warning where a formula is seen
  • Replace all formulas with an empty string
  • Remove all formulas (replace with undef

Code speaks loader than words ...

$ cat formula.csv a,b,c 1,=2+3,4 6,,7,=8+9,

Parsing

$ perl -MCSV -e'dcsv (in => "formula.csv")' [ [ 'a', 'b', 'c' ], [ '1', '=2+3', '4' ], [ '6', '', '7', '=8+9', '' ] ] $ perl -MCSV -e'dcsv (in => "formula.csv", formula => "none")' [ [ 'a', 'b', 'c' ], [ '1', '=2+3', '4' ], [ '6', '', '7', '=8+9', '' ] ] $ perl -MCSV -e'dcsv (in => "formula.csv", formula => "die")' Formulas are forbidden $ perl -MCSV -e'dcsv (in => "formula.csv", formula => "croak")' Formulas are forbidden $ perl -MCSV -e'dcsv (in => "formula.csv", formula => "diag")' Field 2 in record 1 contains formula '=2+3' Field 4 in record 2 contains formula '=8+9' [ [ 'a', 'b', 'c' ], [ '1', '=2+3', '4' ], [ '6', '', '7', '=8+9', '' ] ] $ perl -MCSV -e'dcsv (in => "formula.csv", formula => "empty")' [ [ 'a', 'b', 'c' ], [ '1', '', '4' ], [ '6', '', '7', '', '' ] ] $ perl -MCSV -e'dcsv (in => "formula.csv", formula => "undef")' [ [ 'a', 'b', 'c' ], [ '1', undef, '4' ], [ '6', '', '7', undef, '' ] ]

Generating

$ perl -MCSV -e'dcsv (in => [["a","b","c"],[1,"=2+3",4],[6,"",7,"=8+9" +]], quote_empty => 1)' a,b,c 1,=2+3,4 6,"",7,=8+9 1 $ perl -MCSV -e'dcsv (in => [["a","b","c"],[1,"=2+3",4],[6,"",7,"=8+9" +]], quote_empty => 1, formula => "none")' a,b,c 1,=2+3,4 6,"",7,=8+9 1 $ perl -MCSV -e'dcsv (in => [["a","b","c"],[1,"=2+3",4],[6,"",7,"=8+9" +]], quote_empty => 1, formula => "die")' a,b,c Formulas are forbidden Exit 255 $ perl -MCSV -e'dcsv (in => [["a","b","c"],[1,"=2+3",4],[6,"",7,"=8+9" +]], quote_empty => 1, formula => "croak")' a,b,c Formulas are forbidden Exit 255 $ perl -MCSV -e'dcsv (in => [["a","b","c"],[1,"=2+3",4],[6,"",7,"=8+9" +]], quote_empty => 1, formula => "diag")' a,b,c Field 1 contains formula '=2+3' 1,=2+3,4 Field 3 contains formula '=8+9' 6,"",7,=8+9 1 $ perl -MCSV -e'dcsv (in => [["a","b","c"],[1,"=2+3",4],[6,"",7,"=8+9" +]], quote_empty => 1, formula => "empty")' a,b,c 1,"",4 6,"",7,"" 1 $ perl -MCSV -e'dcsv (in => [["a","b","c"],[1,"=2+3",4],[6,"",7,"=8+9" +]], quote_empty => 1, formula => "undef")' a,b,c 1,,4 6,"",7, 1

I'm pretty pleased with the diagnostics

$ cat formula.csv a,b,c 1,=2+3,4 6,,7,=8+9, $ perl -MCSV -e'$_ = dcsv (in => "formula.csv", bom => 1, formula => " +diag")' Field 2 (column: 'b') in record 1 contains formula '=2+3' Field 4 in record 2 contains formula '=8+9'

Expect this to be available by next week.


Enjoy, Have FUN! H.Merijn

Replies are listed 'Best First'.
Re: Be prepared for CSV injections in spreadsheet
by chacham (Prior) on Oct 18, 2017 at 16:41 UTC

    A friend who does pen-testing just mentioned this to me last week while relating some attack vectors he used at a place or two. It's amazing how many people will open up csv attachments, and how many automatically open in Excel. Ouch!

Re: Be prepared for CSV injections in spreadsheet
by RonW (Vicar) on Oct 19, 2017 at 00:45 UTC

    I had not known that Excel would accept formulae from CSV files. I wonder if LibreOffice and OpenOffice have the same "mis-feature". I do, of course, inspect CSV files in a text editor, then use a sandbox account to open them. (And I do NOT open "Office" files on my personal PC.)

    With this new feature in Text::CSV_XS, I will have a new tool to help screen CSV files. Thanks.

    Update: At work, where I do have to use documents sent to me via email, I have a work-issued, Windows PC and I don't accept documents directly from outsiders. Since all customer documents are required to be vetted by the project management team, I let them take the risk, first. Documents from inside the company, I can't avoid the risk (but it's my "office PC" that's at risk). Any others, I delete. When I do have to open questionable files on my development PC, I open them first on my office PC, then I use the sandbox account on the dev PC.

Re: Be prepared for CSV injections in spreadsheet
by Anonymous Monk on Oct 18, 2017 at 12:52 UTC
    That is an excellent new fee-chur. Thank you!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://1201572]
Front-paged by Corion
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (3)
As of 2017-11-21 05:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:













    Results (295 votes). Check out past polls.

    Notices?