Repository: mholt/PapaParse
Pull request #796 (2020-05-23 12:02:29)

Implement escapeFormulae option
See #793 for discussion.


Patch:
diff --git a/docs/docs.html b/docs/docs.html
index b7785de..8d253a1 100644
--- a/docs/docs.html
+++ b/docs/docs.html
@@ -337,6 +337,14 @@
 									If <code>data</code> is an array of objects this option can be used to manually specify the keys (columns) you expect in the objects. If not set the keys of the first objects are used as column.
 								</td>
 							</tr>
+							<tr>
+								<td>
+									<code>escapeFormulae</code>
+								</td>
+								<td>
+									If <code>true</code>, field values that begin with <code>=</code>, <code>+</code>, <code>-</code>, or <code>@</code>, will be prepended with a <code>'</code> to defend against <a href="https://www.contextis.com/en/blog/comma-separated-vulnerabilities" target="_blank" rel="noopener">injection attacks</a>, because Excel and LibreOffice will automatically parse such cells as formulae.
+								</td>
+							</tr>
 						</table>
 					</div>
 					<div class="clear"></div>
diff --git a/papaparse.js b/papaparse.js
index 70dedf0..ec49373 100755
--- a/papaparse.js
+++ b/papaparse.js
@@ -282,6 +282,9 @@ License: MIT
 		/** the columns (keys) we expect when we unparse objects */
 		var _columns = null;
 
+		/** whether to prevent outputting cells that can be parsed as formulae by spreadsheet software (Excel and LibreOffice) */
+		var _escapeFormulae = false;
+
 		unpackConfig();
 
 		var quoteCharRegex = new RegExp(escapeRegExp(_quoteChar), 'g');
@@ -361,6 +364,9 @@ License: MIT
 			if (_config.escapeChar !== undefined) {
 				_escapedQuote = _config.escapeChar + _quoteChar;
 			}
+
+			if (typeof _config.escapeFormulae === 'boolean')
+				_escapeFormulae = _config.escapeFormulae;
 		}
 
 
@@ -447,6 +453,10 @@ License: MIT
 			if (str.constructor === Date)
 				return JSON.stringify(str).slice(1, 25);
 
+			if (_escapeFormulae === true && typeof str === "string" && (str.match(/^[=+\-@].*$/) !== null)) {
+				str = "'" + str;
+			}
+
 			var escapedQuoteStr = str.toString().replace(quoteCharRegex, _escapedQuote);
 
 			var needsQuotes = (typeof _quotes === 'boolean' && _quotes)


Test patch:
diff --git a/tests/test-cases.js b/tests/test-cases.js
index 18ec380..bc0b333 100644
--- a/tests/test-cases.js
+++ b/tests/test-cases.js
@@ -1842,7 +1842,36 @@ var UNPARSE_TESTS = [
 		input: [{a: 'foo', b: '"quoted"'}],
 		config: {header: false},
 		expected: 'foo,"""quoted"""'
-	}
+	},
+	{
+		description: "Escape formulae",
+		input: [{ "Col1": "=danger", "Col2": "@danger", "Col3": "safe" }, { "Col1": "safe=safe", "Col2": "+danger", "Col3": "-danger, danger" }, { "Col1": "'+safe", "Col2": "'@safe", "Col3": "safe, safe" }],
+		config: { escapeFormulae: true },
+		expected: 'Col1,Col2,Col3\r\n\'=danger,\'@danger,safe\r\nsafe=safe,\'+danger,"\'-danger, danger"\r\n\'+safe,\'@safe,"safe, safe"'
+	},
+	{
+		description: "Don't escape formulae by default",
+		input: [{ "Col1": "=danger", "Col2": "@danger", "Col3": "safe" }, { "Col1": "safe=safe", "Col2": "+danger", "Col3": "-danger, danger" }, { "Col1": "'+safe", "Col2": "'@safe", "Col3": "safe, safe" }],
+		expected: 'Col1,Col2,Col3\r\n=danger,@danger,safe\r\nsafe=safe,+danger,"-danger, danger"\r\n\'+safe,\'@safe,"safe, safe"'
+	},
+	{
+		description: "Escape formulae with forced quotes",
+		input: [{ "Col1": "=danger", "Col2": "@danger", "Col3": "safe" }, { "Col1": "safe=safe", "Col2": "+danger", "Col3": "-danger, danger" }, { "Col1": "'+safe", "Col2": "'@safe", "Col3": "safe, safe" }],
+		config: { escapeFormulae: true, quotes: true },
+		expected: '"Col1","Col2","Col3"\r\n"\'=danger","\'@danger","safe"\r\n"safe=safe","\'+danger","\'-danger, danger"\r\n"\'+safe","\'@safe","safe, safe"'
+	},
+	{
+		description: "Escape formulae with single-quote quoteChar and escapeChar",
+		input: [{ "Col1": "=danger", "Col2": "@danger", "Col3": "safe" }, { "Col1": "safe=safe", "Col2": "+danger", "Col3": "-danger, danger" }, { "Col1": "'+safe", "Col2": "'@safe", "Col3": "safe, safe" }],
+		config: { escapeFormulae: true, quoteChar: "'", escapeChar: "'" },
+		expected: 'Col1,Col2,Col3\r\n\'\'=danger,\'\'@danger,safe\r\nsafe=safe,\'\'+danger,\'\'\'-danger, danger\'\r\n\'\'+safe,\'\'@safe,\'safe, safe\''
+	},
+	{
+		description: "Escape formulae with single-quote quoteChar and escapeChar and forced quotes",
+		input: [{ "Col1": "=danger", "Col2": "@danger", "Col3": "safe" }, { "Col1": "safe=safe", "Col2": "+danger", "Col3": "-danger, danger" }, { "Col1": "'+safe", "Col2": "'@safe", "Col3": "safe, safe" }],
+		config: { escapeFormulae: true, quotes: true, quoteChar: "'", escapeChar: "'" },
+		expected: '\'Col1\',\'Col2\',\'Col3\'\r\n\'\'\'=danger\',\'\'\'@danger\',\'safe\'\r\n\'safe=safe\',\'\'\'+danger\',\'\'\'-danger, danger\'\r\n\'\'\'+safe\',\'\'\'@safe\',\'safe, safe\''
+	},
 ];
 
 describe('Unparse Tests', function() {