Repository: jazzband/tablib
Pull request #540 (2023-03-01 14:39:40)

Escape formulae on export
There is a potential security flaw where XLSX files can be exported with active formulae (see #257).

This PR adds an optional 'escape' kwarg to the export method which will strip leading '=' characters and disable formulae.

Tests included.

codecov[bot]: # [Codecov](https://codecov.io/gh/jazzband/tablib/pull/540?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband) Report
> Merging [#540](https://codecov.io/gh/jazzband/tablib/pull/540?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband) (667bc24) into [master](https://codecov.io/gh/jazzband/tablib/commit/4363c435f5f7c99c5adb5d98ba0263f6fecd78e3?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband) (4363c43) will **increase** coverage by `0.15%`.
> The diff coverage is `100.00%`.

```diff
@@            Coverage Diff             @@
##           master     #540      +/-   ##
==========================================
+ Coverage   91.15%   91.30%   +0.15%     
==========================================
  Files          28       28              
  Lines        2680     2727      +47     
==========================================
+ Hits         2443     2490      +47     
  Misses        237      237              
```


| [Impacted Files](https://codecov.io/gh/jazzband/tablib/pull/540?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband) | Coverage Δ | |
|---|---|---|
| [src/tablib/formats/\_xlsx.py](https://codecov.io/gh/jazzband/tablib/pull/540?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband#diff-c3JjL3RhYmxpYi9mb3JtYXRzL194bHN4LnB5) | `97.02% <100.00%> (+0.06%)` | :arrow_up: |
| [tests/test\_tablib.py](https://codecov.io/gh/jazzband/tablib/pull/540?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband#diff-dGVzdHMvdGVzdF90YWJsaWIucHk=) | `98.73% <100.00%> (+0.06%)` | :arrow_up: |

:mega: We’re building smart automated test selection to slash your CI/CD build times. [Learn more](https://about.codecov.io/iterative-testing/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband)

claudep: Thanks for the patch! Could you please also complete the docs in `formats.rst`?
matthewhegarty: > Thanks for the patch! Could you please also complete the docs in `formats.rst`?

Done.  To avoid duplication I added links to the source docstrings.

Also updated requirements.txt to the latest version of 'Sphinx'
claudep: @hugovk, I let you do the merge if you find it OK.

Patch:
diff --git a/docs/formats.rst b/docs/formats.rst
index c3620da..268c3c8 100644
--- a/docs/formats.rst
+++ b/docs/formats.rst
@@ -247,6 +247,10 @@ data.
 
     Reads cell values instead of formulas.
 
+You can export data to xlsx format by calling :meth:`export('xlsx') <.export>`.
+There are optional parameters to control the export.
+For available parameters, see :meth:`tablib.formats._xlsx.XLSXFormat.export_set`.
+
 .. admonition:: Binary Warning
 
     The ``xlsx`` file format is binary, so make sure to write in binary mode::
diff --git a/docs/requirements.txt b/docs/requirements.txt
index 498f43d..553b0c0 100644
--- a/docs/requirements.txt
+++ b/docs/requirements.txt
@@ -1,1 +1,1 @@
-sphinx==4.2.0
+sphinx==6.1.3
diff --git a/src/tablib/formats/_xlsx.py b/src/tablib/formats/_xlsx.py
index c84ad54..974e14b 100644
--- a/src/tablib/formats/_xlsx.py
+++ b/src/tablib/formats/_xlsx.py
@@ -35,12 +35,18 @@ class XLSXFormat:
             return False
 
     @classmethod
-    def export_set(cls, dataset, freeze_panes=True, invalid_char_subst="-"):
+    def export_set(cls, dataset, freeze_panes=True, invalid_char_subst="-", escape=False):
         """Returns XLSX representation of Dataset.
 
-        If dataset.title contains characters which are considered invalid for an XLSX file
+        If ``freeze_panes`` is True, Export will freeze panes only after first line.
+
+        If ``dataset.title`` contains characters which are considered invalid for an XLSX file
         sheet name (http://www.excelcodex.com/2012/06/worksheets-naming-conventions/), they will
-        be replaced with `invalid_char_subst`.
+        be replaced with ``invalid_char_subst``.
+
+        If ``escape`` is True, formulae will have the leading '=' character removed.
+        This is a security measure to prevent formulae from executing by default
+        in exported XLSX files.
         """
         wb = Workbook()
         ws = wb.worksheets[0]
@@ -50,19 +56,16 @@ class XLSXFormat:
             if dataset.title else 'Tablib Dataset'
         )
 
-        cls.dset_sheet(dataset, ws, freeze_panes=freeze_panes)
+        cls.dset_sheet(dataset, ws, freeze_panes=freeze_panes, escape=escape)
 
         stream = BytesIO()
         wb.save(stream)
         return stream.getvalue()
 
     @classmethod
-    def export_book(cls, databook, freeze_panes=True, invalid_char_subst="-"):
+    def export_book(cls, databook, freeze_panes=True, invalid_char_subst="-", escape=False):
         """Returns XLSX representation of DataBook.
-
-        If dataset.title contains characters which are considered invalid for an XLSX file
-        sheet name (http://www.excelcodex.com/2012/06/worksheets-naming-conventions/), they will
-        be replaced with `invalid_char_subst`.
+        See export_set().
         """
 
         wb = Workbook()
@@ -75,7 +78,7 @@ class XLSXFormat:
                 if dset.title else 'Sheet%s' % (i)
             )
 
-            cls.dset_sheet(dset, ws, freeze_panes=freeze_panes)
+            cls.dset_sheet(dset, ws, freeze_panes=freeze_panes, escape=escape)
 
         stream = BytesIO()
         wb.save(stream)
@@ -125,7 +128,7 @@ class XLSXFormat:
             dbook.add_sheet(data)
 
     @classmethod
-    def dset_sheet(cls, dataset, ws, freeze_panes=True):
+    def dset_sheet(cls, dataset, ws, freeze_panes=True, escape=False):
         """Completes given worksheet from given Dataset."""
         _package = dataset._package(dicts=False)
 
@@ -166,3 +169,6 @@ class XLSXFormat:
                     cell.value = col
                 except (ValueError, TypeError):
                     cell.value = str(col)
+
+                if escape and cell.data_type == 'f' and cell.value.startswith('='):
+                    cell.value = cell.value.replace("=", "")


Test patch:
diff --git a/tests/test_tablib.py b/tests/test_tablib.py
index 29772f3..971e3d1 100755
--- a/tests/test_tablib.py
+++ b/tests/test_tablib.py
@@ -14,6 +14,7 @@ from pathlib import Path
 from uuid import uuid4
 
 from MarkupPy import markup
+from openpyxl.reader.excel import load_workbook
 
 import tablib
 from tablib.core import Row, detect_format
@@ -1117,6 +1118,60 @@ class XLSXTests(BaseTestCase):
             data = tablib.Dataset().load(fh)
         self.assertEqual(data.headers[0], 'Hello World')
 
+    def test_xlsx_export_set_escape_formulae(self):
+        """
+        Test that formulae are sanitised on export.
+        """
+        data.append(('=SUM(1+1)',))
+        _xlsx = data.export('xlsx')
+
+        # read back using openpyxl because tablib reads formulae as values
+        wb = load_workbook(filename=BytesIO(_xlsx))
+        self.assertEqual('=SUM(1+1)', wb.active['A1'].value)
+
+        _xlsx = data.export('xlsx', escape=True)
+        wb = load_workbook(filename=BytesIO(_xlsx))
+        self.assertEqual('SUM(1+1)', wb.active['A1'].value)
+
+    def test_xlsx_export_book_escape_formulae(self):
+        """
+        Test that formulae are sanitised on export.
+        """
+        data.append(('=SUM(1+1)',))
+        _book = tablib.Databook()
+        _book.add_sheet(data)
+        _xlsx = _book.export('xlsx')
+
+        # read back using openpyxl because tablib reads formulae as values
+        wb = load_workbook(filename=BytesIO(_xlsx))
+        self.assertEqual('=SUM(1+1)', wb.active['A1'].value)
+
+        _xlsx = _book.export('xlsx', escape=True)
+        wb = load_workbook(filename=BytesIO(_xlsx))
+        self.assertEqual('SUM(1+1)', wb.active['A1'].value)
+
+    def test_xlsx_export_set_escape_formulae_in_header(self):
+        data.headers = ('=SUM(1+1)',)
+        _xlsx = data.export('xlsx')
+        wb = load_workbook(filename=BytesIO(_xlsx))
+        self.assertEqual('=SUM(1+1)', wb.active['A1'].value)
+
+        _xlsx = data.export('xlsx', escape=True)
+        wb = load_workbook(filename=BytesIO(_xlsx))
+        self.assertEqual('SUM(1+1)', wb.active['A1'].value)
+
+    def test_xlsx_export_book_escape_formulae_in_header(self):
+        data.headers = ('=SUM(1+1)',)
+        _book = tablib.Databook()
+        _book.add_sheet(data)
+        _xlsx = _book.export('xlsx')
+        wb = load_workbook(filename=BytesIO(_xlsx))
+        self.assertEqual('=SUM(1+1)', wb.active['A1'].value)
+
+        _xlsx = _book.export('xlsx', escape=True)
+        wb = load_workbook(filename=BytesIO(_xlsx))
+        self.assertEqual('SUM(1+1)', wb.active['A1'].value)
+
     def test_xlsx_bad_dimensions(self):
         """Test loading file with bad dimension.  Must be done with
         read_only=False."""