Dangers of CSV Injection (opens in new tab)

This comes up every single time someone mentions CSV. Without fail. The bottom line is that CSV is human readable and writable in plain text. If you start using fancy ASCII characters, then it becomes neither because our text editors don't support it.

The article kind of addresses this. There are millions of spreadsheets and applications out in the wild that use CSV to communicate.

Sure, if you're building some kind of system where you need to ingest data from one application from another application you control, then using a different interchange format like ASV is an option. But then people tend to use more powerful formats like JSON or XML.

Give me a version of every standard text editor that can let me display and edit these ASV files when I just need to quickly hack something, and sure, I'll use it. CSV is directly editable in any text editor and manipulable by standard text processing tools, that's one of its key advantages.

I don't think this necessarily addresses the security vulnerabilities in the article, which involve abusing the application reading the CSV, not the file format itself.

If Excel decides that text between Start of Text and End of Text that begins with a "=" is a formula, then you're in the same spot.

I am happy when I see I can get data via CSV over the other delivery methods people use. My local school board prints out all their data and then scans them into a PDF, ugh. I had one vendor that on purpose made the data only available in forms that would take me 600+ lines of code to clean up in mangled ASCII format.

I use CSV all the time when I am working with R. My data can come in the form of CSV, XLS, or PDF. Which would you want to work with?

I can easily look at the data. I never touch my incoming data and my output is in reports, but CSV can be the easiest way to get data into a computer.

If a dev is going to use a weirdo non-CSV data interchange format, they would just use XSLX or JSON or etc etc etc.

"ASV" is only a viable option if you then also use your time machine to go back 40 years and make everyone start using it then.

This might create some integration-related hiccups with XML, as most ASCII control characters are forbidden per the XML 1.0/1.1 specs.

I used to work in third-party logistics and a big project of mine was an automated file import process, so folks could send us their daily/hourly orders for processing and fulfillment. I'd say roughly 65-70% of the entire code base was error handling and figuring out when to kick out a file for human review and/or outright deny it and contact the customer.

The hardest ones to work with were the mom and pop shops who suddenly had some success on Amazon and came to us after fulfilling out of their garage for a year and a half. Try telling a semi-retired 60 year old electrician in the middle of Iowa that the file he sent is worthless because none of the product codes match what you have, especially when once he closes the file he doesn't have any idea where it is.

CSV is the source of so many problems. CSV has no character set, no rule for escaping double-quotation marks, commas, and newlines. There's not even a way to look at a CSV file and tell what it's "rules" are beyond heuristics and those only take you so far.

I ask for XLSX files since at least it's structured, unambiguous and documented, but even better: a minimal XLSX parser is trivial (about a page) to write.

Also: Educating users on how to specify the character set in every application that the user seems to want to use is a special kind of hell.

Excel is also well known for mangling gene names in expression data. No, SEPT2 (Septin-2) shouldn't be silently "corrected" to 2-Sep, but it is...

Zip codes also have a bad habit of getting reformatted, because leading zeroes are removed.

01101 -> 1101

Export side you obviously can't control :( But on import, if you use Data -> From Text, and on Step 3 select all the columns and make them "Text", that will prevent Excel from mangling any of the data (stripping leading zeros, evaluating strings starting with = as formula etc.)

Really quite rich people relying on Excel for economic world-scale critical stuff has ruined/cost more than a few lives. Do you blame Bill for his shitty software or do you blame the rich corporate types for not giving a shit/incompetent minimum effort IT?

A CSV importer absolutely needs to be configurable. I've seen delimiters including tabs, vertical bars, tildes, colons, and random control characters (they didn't even choose RS and US).

> CSVs to show properly in Excel we have to mess around with OS language settings.

Why? Aren't the import settings enough?

https://support.office.com/en-us/article/Text-Import-Wizard-...

> Some idiot somewhere decided that Comma Separated Values in certain locales should be based on semicolons

Semicolons are really better though, because they aren't used as a decimal separator unlike commas in most countries.

I don't know about Excel, but LibreOffice allows very easily to select which parameters to use when opening a CSV file, it works just fine.

The only good CSV dialect is the dif-named DSV (Delimiter Separated Values) where you select and support just one supported delimiter, and you require escaping of the delim character inside values. It's simple, it works. Quotes are hard to parse so don't use those. Just \escape.

http://www.catb.org/esr/writings/taoup/html/ch05s02.html

What's a good alternative non-proprietary format that all major spreadsheet software supports?

> The documented way is prefixing with a ' character. It doesn't have the length issue either.

It is suggested in comments, but the author answered

> Yes, this prevents formula expansion... once. Unfortunately Excel's own CSV exporter doesn't write the ', so if the user saves the ‘safe’ file and then loads it again all the problems are back.

:-/

Came here to say the same. Also tested it to confirm and the single quote mark inside the double quotes does indeed force interpretation as a string instead of a formula. In both Excel and Google Sheets.

Interestingly, in Excel removing the quotes entirely also causes a formula to be interpreted as a formula and text (even with spaces) as text and numbers as numbers.

In my testing, quotes are only needed when a field contains a comma to prevent it being interpreted as a delimiter.

"transfer a series of values between applications that apply different types to those values and applications that don't"

If we thought about it as an API mechanism, we would parse the strings and apply rules to sanitise or reject it.

Here is a principle for thinking about data. Distinguish internal data structures (persistence, search) from interchange structures (APIs). Codebase A should not be able to directly access the structures of Codebase B. To communicate, they must use explicit APIs.

At the moment, this principle is not mainstream. The CSV loader is not sure if it is loading an interchange format or persistence format. Another, that happens regularly: (1) developer builds a database as a storage mechanism. (2) developer decides to have other separate codebases query into that database. Is the database an application-data-structure (interal) or an API (external)? It is acting as both.

The applications that are communicating either have to agree on the types in advance or they have to use an interchange format that makes it explicit. If your applications don't both know the types in advance then you shouldn't be using CSV.

I think the common model people had of CSV was that it was an imperfect way to transfer values, but safeish from code execution, XSS or "all your Google account data gets exfiltrated" type effects.

That's just a single regular apostrophe? At least on my machine, with Mac Excel 15.38, if I have a CSV containing:

1,foo,'=SUM(A1:A10),bar

and open it, then the single apostrophe is visible in the cell.

Yes, through the "Import" feature. Excel will in that case allow you to choose what "type" each column in the CSV has (and will not parse text if given the "text" type). The problem is that a lot of users (myself included) will use muscle memory and double-click a CSV file in windows explorer rather than opening up Excel and initiating an import.

Agree it is completely absurd to allow formulas in a CSV file, let alone code.

I have never seen a way to disable a full recalculation when Excel opens a CSV file, which beyond the security implications is painful for people like me who keep their calculations on manual because I often have very heavy workbooks opened all the time.

My first thought was this dead-simple solution: just pop up a prompt when opening CSVs. "Do you want to run formulas from this CSV file?" No need for complicated import wizards, just a simple yes/no.

We recently went through an external pentest simulating a hostile actor with inside information. We had 2 weeks to prepare and successfully defended against timing attacks, DDoS attempts, identity spoofs, request modifications, script injections etc. Passed with flying colors... except for CSV/Excel injection. Everyone looked at each other with the sheepish embarrassment of being pwned by a script kiddie. This was a total blind spot indeed, even after we reviewed every other user I/O.

"Input is evil" is a pretty good maxim to follow.

There's nothing in CSV that has anything to do with presentation (nor with what the underlying values are, for that matter).

These vulnerabilities can't be blamed on CSV so much as on the desire of application vendors to treat data as code.

CSV is a format for two-dimensional text values, and nothing beyond that. It's not a poor format, it's a simple low-level format.

Except if your raw value begins with a "'". Or if it is 2017/10/10, or 10/10/2017, then it may be represented by an integer with a format of "date". Or if your raw value is 1234567890123456789, then you get a string like '1.23456789012345e18', complete with modified data. Or if it begins with an "=" which could result in basically anything as the article points out.

Excel conflates the idea of display format and data type which is the source of countless headaches. It is legacy pain in the purest form.

CSV was only ever intended to store simple text and simple numbers.

Dates are a /type/ of text; parsing dates in to machine readable formats is an /entire/ other can of spam.

It would be interesting if spreadsheets supported a sub-set of a binary interchange format like FITS or HDF5.

I noticed this in the data of some scientists I work with. Another awful thing is that when you tell them they need to format the column as text to prevent this in the future, before the data is put in the column (very important!), they'll eventually try to apply it to their existing fubar spreadsheets as well - in which case the "date-recognized" genes become ... large numbers representing the number of days since 1900, totally unrecognizable.

FWIW, I (not a biologist, though) only use LibreOffice for importing CSV these days. It allows me to look at the fields first and tell it if I want to suppress special treatment of data in a column.

EDIT: LibreOffice also allows you to tell it what encoding a file uses and what character(s) are used as separators.

Just curious, but what about non-vertebrates? I'd have expected there to be an official number/hash that identifies genes like the InChI Key for chemistry or something. IIRC, that key in particular is just a SHA-256 of a long human-readable "chemical formula".

See, one of the reasons CSV managed to get so ubiquitous is precisely because all those things are unspecified. CSV is not a popular format; CSV is the name we give 960 visually similar but very different formats that as a collective are popular.

The thing you use CSV for is not it's technical merit. You use CSV for its ubiquity. If you nailed down all those things you talk about, you would have a much, much smaller user base and there would be no reason to use CSV in the first place.

(Hey, this reminds me of a similar situation governing s/CSV/C/g...)

> No distinction between categorical data and strings. R thinks your strings are categories, and Pandas thinks your categories are strings.

I think this is more of an R-ism than a standardization issue. Strings are a pretty universal data type, where as categorical data (factors) are mostly specific to the domain of statistical modeling. IMO Python is doing the correct thing here. Personally I find factors to be more trouble than they are worth, and fortunately `data.table::fread` mimics Python in this regard.

.parquet fam, it's all about columnar data stores now

XKCD covered this: https://xkcd.com/927/

Well, it catches folks by surprise. We could abstract all computer vulns down to a few broad computing concepts, but that isn't as useful.

This one is your data turned out to be code. There are many, many books on all the various forms this takes. Memory corruption cat and mouse..... It is a long complex story that we can sweep up to that generalization. But it is important to know that high, medium, and low level of these issues. They form a gigantic tree. The medium level somewhere between is where devs need to threat model most of the time. But some of the time things are very specific and you just need to know about the specific thing and not it's various generalized forms, because the specific thing can really matter. E.g. simple programming mistakes lead to side channels, etc. We can generically understand a side channels easily. But it takes a ton of specific hard earned knowledge to avoid it.

As weird as it sounds, it might be related to your system region settings, specifically the decimal point sign and the thousands separator sign. I've been only able to open CSVs by manually importing them with Excel's 'import data from text file' function.

It does depend on using the csv file extension. Anything else brings up the import wizard.

That's bad advice.

http://blog.hackensplat.com/2013/09/never-sanitize-your-inpu...

this used to be common with txt files and IE's terrible practice of sniffing content. It would see a txt file that contained html and display the html instead, it could then pull in a secret silverlight file that was mascarading as a docx file as they are both simply zip files. Even more amusingly silverlight and docx contents don't clash so it could still be a valid docx file if you opened it, and the txt file would look like txt even though it was really rendering html with a hidden silverlight app.

As mentioned, protects by showing a wall of text with "yes" preselected at bottom; equally useless and annoying.