74 lines
2.9 KiB
Python
74 lines
2.9 KiB
Python
"""
|
|
utils/excel_safety.py
|
|
=====================
|
|
Protection against spreadsheet formula injection in exports.
|
|
|
|
Text that reaches an export can come from the public check-in page (address,
|
|
device, selected location), from imported time-clock files, or from the legacy
|
|
database. In an .xlsx file a cell whose value starts with "=" is stored as a
|
|
formula; in a CSV a value starting with = + - @ tab or CR is evaluated when the
|
|
file is opened in Excel. These helpers keep such text inert while the formulas
|
|
the exports generate on purpose (map HYPERLINKs, SUM / SUMIF totals) keep working.
|
|
"""
|
|
import re
|
|
|
|
# Formulas the exports generate themselves. Any other cell value starting with
|
|
# "=" is written as plain text. HYPERLINKs are accepted only with a Google Maps
|
|
# target and correctly escaped string arguments ("" for a quote), so user text
|
|
# inside them can never close the string and append another function.
|
|
_ALLOWED_FORMULA_PATTERNS = (
|
|
re.compile(
|
|
r'^=HYPERLINK\("(?:https://www\.google\.com/maps/place/|http://maps\.google\.com/maps\?q=)'
|
|
r'(?:[^"]|"")*","(?:[^"]|"")*"\)$'
|
|
),
|
|
re.compile(r'^=SUM\(\$?[A-Z]{1,3}\$?\d+:\$?[A-Z]{1,3}\$?\d+\)$'),
|
|
re.compile(
|
|
r'^=SUMIF\(\$?[A-Z]{1,3}\$?\d+:\$?[A-Z]{1,3}\$?\d+,'
|
|
r'\$?[A-Z]{1,3}\$?\d+,'
|
|
r'\$?[A-Z]{1,3}\$?\d+:\$?[A-Z]{1,3}\$?\d+\)$'
|
|
),
|
|
)
|
|
|
|
_CSV_FORMULA_PREFIXES = ('=', '+', '-', '@', '\t', '\r')
|
|
|
|
|
|
def excel_escape_string(text):
|
|
"""Escape text for use inside a double-quoted string argument of an Excel formula."""
|
|
value = '' if text is None else str(text)
|
|
return value.replace('"', '""').replace('\r', ' ').replace('\n', ' ')
|
|
|
|
|
|
def excel_hyperlink(url, display_text):
|
|
"""=HYPERLINK("url","display text") with both arguments safely quoted."""
|
|
return f'=HYPERLINK("{excel_escape_string(url)}","{excel_escape_string(display_text)}")'
|
|
|
|
|
|
def is_allowed_formula(value):
|
|
"""True for the formula shapes the exports generate on purpose."""
|
|
return any(pattern.match(value) for pattern in _ALLOWED_FORMULA_PATTERNS)
|
|
|
|
|
|
def neutralize_unexpected_formulas(workbook):
|
|
"""
|
|
Store every formula the app did not generate itself as plain text.
|
|
|
|
Call right before saving an export workbook. Returns the number of cells
|
|
changed. The cell keeps its visible text; it is just never evaluated.
|
|
"""
|
|
changed = 0
|
|
for worksheet in workbook.worksheets:
|
|
for row in worksheet.iter_rows():
|
|
for cell in row:
|
|
value = cell.value
|
|
if isinstance(value, str) and value.startswith('=') and not is_allowed_formula(value):
|
|
cell.data_type = 's' # written as a string, not as <f>
|
|
changed += 1
|
|
return changed
|
|
|
|
|
|
def csv_safe(value):
|
|
"""A CSV value that Excel will not evaluate as a formula (leading apostrophe)."""
|
|
if isinstance(value, str) and value.startswith(_CSV_FORMULA_PREFIXES):
|
|
return "'" + value
|
|
return value
|