Safe CSV exports and formula injection

A customer types =2+5 as the subject of a support ticket, and the nightly export copies it into tickets.csv unchanged. A colleague opens the file in a spreadsheet and the cell shows 7: the spreadsheet ran a formula that a stranger wrote, and a formula can do far more than add.

You will see why spreadsheets do this and who is at risk, neutralise the risky cells of an export with a check that fails before the fix and passes after it, keep number columns intact, publish a second export for programs, and validate the rows that travel the other way, from an upload into your database.

A spreadsheet runs any cell that starts like a formula

CSV has no types, so the program that opens a file decides what each cell is. A spreadsheet reads a cell whose text starts with = as a formula, and OWASP lists the other first characters that do the same in common spreadsheets: +, -, @, a tab, a carriage return, a line feed, and full-width forms such as ＝, ＋, － and ＠, which some locales also treat as formulas. Papa Parse’s documentation gives the same reason for its option that guards against them: Excel and LibreOffice parse such cells as formulae. Step through one ticket subject on its way to a spreadsheet:

This is formula injectionformula injection: A weakness of programs that export outside text to CSV: a cell that starts with =, +, - or @, or a tab or a line break, is run as a formula by the spreadsheet that opens the file. Also called CSV injection; an export prevents it by neutralising such cells., also called CSV injection. The program that wrote the export is never harmed: the formula runs in the spreadsheet of whoever opens the file, often a colleague in finance or support who trusts it because it came from your own system. Even harmless arithmetic does damage, since the report now shows 7 where the customer wrote =2+5, and OWASP describes how crafted formulas are used against the people who open such files.

Any export that copies text from outside is exposed: customer names, addresses, ticket subjects, comments, product titles from a supplier’s feed. The defence belongs in the export, the one place that knows which cells hold untrusted text, and it needs a test, because nothing in the file looks wrong to the program that wrote it.

Neutralise the first character, after a real CSV writer has quoted the field

A cell is neutralised when a spreadsheet can no longer read it as a formula. The common way, which OWASP describes, is to put a single quote in front of the value: Microsoft documents that Excel treats an entry starting with an apostrophe as text. That alone is not enough if the file is built by joining values with commas, because a comma inside a value starts a new cell, and the new cell can start with anything. This program writes five tickets both ways, then runs a check over each file: every cell a spreadsheet would read as a formula, and every record of the wrong length, is a problem. The JavaScript tab writes the same safe file:

"""A ticket export written two ways, and a check that fails on the first and passes on the second."""

import csv
import io
import re

COLUMNS = ["id", "customer", "subject", "refund"]
NUMERIC = {"refund"}
TICKETS = [
    {"id": "T-101", "customer": "Asha Iyer", "subject": "=2+5", "refund": -250},
    {"id": "T-102", "customer": "Rao, Vikram", "subject": "@mention in the invoice", "refund": 0},
    {"id": "T-103", "customer": "Meena", "subject": "Call back,+91 98765 43210", "refund": 1200},
    {"id": "T-104", "customer": "Kiran", "subject": "\tpasted with a tab", "refund": -99},
    {"id": "T-105", "customer": "Ravi", "subject": "\uff1dSUM(1,2)", "refund": 0},
]
# The first characters that make a spreadsheet read a cell as a formula, full-width forms included.
RISKY = ("=", "+", "-", "@", "\t", "\r", "\n", "\uff1d", "\uff0b", "\uff0d", "\uff20")
NUMBER = re.compile(r"-?\d+(?:\.\d+)?")


def weak_export(rows):
    """Joins the values with commas, as a quick script does."""
    lines = [",".join(COLUMNS)] + [",".join(str(row[c]) for c in COLUMNS) for row in rows]
    return "\r\n".join(lines) + "\r\n"


def neutralise(text):
    """A text cell that a spreadsheet would run as a formula gets an apostrophe in front of it."""
    return "'" + text if text.startswith(RISKY) else text


def safe_export(rows):
    """csv.writer quotes what needs quotes; text is neutralised, declared numbers are written as numbers."""
    out = io.StringIO()
    writer = csv.writer(out)
    writer.writerow(COLUMNS)
    for row in rows:
        writer.writerow([row[c] if c in NUMERIC else neutralise(str(row[c])) for c in COLUMNS])
    return out.getvalue()


def risky_cells(text):
    """The check: every cell a spreadsheet would read as a formula, and every record of the wrong length."""
    reader = csv.reader(io.StringIO(text, newline=""))
    header = next(reader)
    problems = []
    for fields in reader:
        if len(fields) != len(header):
            problems.append(f"line {reader.line_num}: {len(fields)} fields, the header has {len(header)}")
        for i, value in enumerate(fields):
            column = header[i] if i < len(header) else f"field {i + 1}"
            if column in NUMERIC and NUMBER.fullmatch(value):
                continue
            if value.startswith(RISKY):
                problems.append(f"line {reader.line_num}, {column}: {value!r}")
    return problems


for name, export in [("weak", weak_export(TICKETS)), ("safe", safe_export(TICKETS))]:
    problems = risky_cells(export)
    print(f"{name} export: {'FAIL' if problems else 'PASS'}")
    for problem in problems:
        print("  " + problem)

print()
print(safe_export(TICKETS).replace("\t", "\\t").replace("\r\n", "\\r\\n\n"), end="")

weak export: FAIL
  line 2, subject: '=2+5'
  line 3: 5 fields, the header has 4
  line 3, refund: '@mention in the invoice'
  line 4: 5 fields, the header has 4
  line 4, refund: '+91 98765 43210'
  line 5, subject: '\tpasted with a tab'
  line 6: 5 fields, the header has 4
  line 6, subject: '＝SUM(1'
safe export: PASS

id,customer,subject,refund\r\n
T-101,Asha Iyer,'=2+5,-250\r\n
T-102,"Rao, Vikram",'@mention in the invoice,0\r\n
T-103,Meena,"Call back,+91 98765 43210",1200\r\n
T-104,Kiran,'\tpasted with a tab,-99\r\n
T-105,Ravi,"'＝SUM(1,2)",0\r\n

// The safe export in JavaScript: neutralise the text cells, then quote each field as RFC 4180 asks.
const COLUMNS = ["id", "customer", "subject", "refund"];
const NUMERIC = new Set(["refund"]);
const tickets = [
  { id: "T-101", customer: "Asha Iyer", subject: "=2+5", refund: -250 },
  { id: "T-102", customer: "Rao, Vikram", subject: "@mention in the invoice", refund: 0 },
  { id: "T-103", customer: "Meena", subject: "Call back,+91 98765 43210", refund: 1200 },
  { id: "T-104", customer: "Kiran", subject: "\tpasted with a tab", refund: -99 },
  { id: "T-105", customer: "Ravi", subject: "\uff1dSUM(1,2)", refund: 0 },
];

// The first characters that make a spreadsheet read a cell as a formula, full-width forms included.
const RISKY = /^[=+\-@\t\r\n\uff1d\uff0b\uff0d\uff20]/;
const neutralise = (text) => (RISKY.test(text) ? `'${text}` : text);
const field = (value) => (/[",\r\n]/.test(value) ? `"${value.replaceAll('"', '""')}"` : value);

const lines = [COLUMNS, ...tickets.map((t) => COLUMNS.map((c) => (NUMERIC.has(c) ? String(t[c]) : neutralise(String(t[c])))))];
const text = lines.map((cells) => cells.map(field).join(",") + "\r\n").join("");
console.log(text.replaceAll("\t", "\\t").replaceAll("\r\n", "\\r\\n\n").replace(/\n$/, ""));

id,customer,subject,refund\r\n
T-101,Asha Iyer,'=2+5,-250\r\n
T-102,"Rao, Vikram",'@mention in the invoice,0\r\n
T-103,Meena,"Call back,+91 98765 43210",1200\r\n
T-104,Kiran,'\tpasted with a tab,-99\r\n
T-105,Ravi,"'＝SUM(1,2)",0\r\n

The weak export fails in eight places. The formula =2+5 and the pasted tab are there as written, and the commas in Rao, Vikram, Call back,+91 98765 43210 and ＝SUM(1,2) made extra cells: one of them, +91 98765 43210, now sits in the refund column and starts with +. The safe export passes. csv.writer quoted the three values with commas, so they stay one cell each, and neutralise() put an apostrophe in front of the four subjects that start like a formula, the full-width ＝ included. The refunds -250 and -99 are untouched: the refund column is declared as a number, and a number is written from the program’s own value, never from outside text.

So the rule needs to look at one character per cell, and it must see every cell, which only a real writer and a check of the finished file guarantee.

OWASP adds a warning: once someone saves the file in Excel and opens it again, the quoting and escaping that protected a cell may be gone, and a neutralised cell can become a formula once more. No escaping keeps a CSV file safe in every program and through every save, which is why the next section gives programs a file of their own.

Two audiences need two exports

The apostrophe protects people and corrupts data for programs: a billing import that reads '=2+5 gets a character that the customer never typed, and one that reads '-damaged box gets a note that nobody wrote. When both read your data, write two files from the same rows, one for each. This program writes refunds both ways and reads each file back with the types its documentation gives:

"""The same refunds as two files, one for people with a spreadsheet and one for programs, and a round trip of each."""

import csv
import io
from decimal import Decimal

COLUMNS = ["id", "customer", "note", "refund"]
REFUNDS = [
    {"id": "R-1", "customer": "Asha Iyer", "note": "=2+5", "refund": Decimal("-250.00")},
    {"id": "R-2", "customer": "Meena", "note": "-damaged box", "refund": Decimal("1200.50")},
    {"id": "R-3", "customer": "Ravi", "note": "चाय", "refund": Decimal("0.00")},
]
RISKY = ("=", "+", "-", "@", "\t", "\r", "\n", "＝", "＋", "－", "＠")


def write(rows, cell):
    out = io.StringIO()
    writer = csv.writer(out)
    writer.writerow(COLUMNS)
    for row in rows:
        writer.writerow([cell(c, row[c]) for c in COLUMNS])
    return out.getvalue()


def for_spreadsheets(rows):
    """UTF-8 with a BOM; text that would run as a formula gets an apostrophe; the refund stays a number."""
    def cell(column, value):
        if column == "refund":
            return str(value)
        return "'" + value if value.startswith(RISKY) else value
    return "\ufeff" + write(rows, cell)


def for_programs(rows):
    """UTF-8 without a BOM, every value exactly as it is."""
    return write(rows, lambda column, value: str(value))


def read_back(text):
    """The rows of an export, with the refund read as a Decimal, as the documentation of the file says."""
    rows = list(csv.DictReader(io.StringIO(text.removeprefix("\ufeff"), newline="")))
    return [{**row, "refund": Decimal(row["refund"])} for row in rows]


sheet, machine = for_spreadsheets(REFUNDS), for_programs(REFUNDS)
print("for spreadsheets, first bytes:", sheet.encode("utf-8")[:4].hex(" "))
print("for programs, first bytes:    ", machine.encode("utf-8")[:4].hex(" "))
print("for programs, round trip equal:", read_back(machine) == REFUNDS)
for before, after in zip(REFUNDS, read_back(sheet)):
    for column in COLUMNS:
        if before[column] != after[column]:
            print(f"for spreadsheets, {before['id']} {column}: {before[column]!r} became {after[column]!r}")
print("refunds in both files:", [str(r["refund"]) for r in read_back(sheet)] == [str(r["refund"]) for r in REFUNDS])

for spreadsheets, first bytes: ef bb bf 69
for programs, first bytes:     69 64 2c 63
for programs, round trip equal: True
for spreadsheets, R-1 note: '=2+5' became "'=2+5"
for spreadsheets, R-2 note: '-damaged box' became "'-damaged box"
refunds in both files: True

The spreadsheet file starts with ef bb bf, a byte-order mark: Microsoft’s support pages say that Excel reads a CSV file as UTF-8 without any import steps when it carries this mark, so the note चाय shows as written. The program file starts straight with the header and reads back equal to the rows it came from. The spreadsheet file differs in exactly the two neutralised notes, and the refunds are the same in both, because the refund column was never touched.

Imported CSV is untrusted input, so validate rows before you load them

The same caution applies to files that come in. An upload from a customer or a partner is untrusted input, and the OWASP Input Validation Cheat Sheet’s advice fits CSV well: define what each field may hold, an allowlist of forms with a length limit, and refuse everything else, instead of hunting for bad strings. Values then reach the database through a parameterised queryparameterised query: An SQL statement whose values are passed apart from its text, through placeholders such as ? or :name, so a value is never read as part of the SQL. Python's sqlite3 module calls this parameter substitution., which OWASP names as the first defence against SQL injection and Python’s sqlite3 documentation recommends over building SQL with string operations. This program loads one upload both ways and checks the result:

"""Imported CSV is untrusted input: an import that builds SQL from the text, one that validates rows and binds
their values, and a check that fails on the first and passes on the second."""

import csv
import io
import re
import sqlite3
import unicodedata

UPLOAD = (
    "name,mobile,amount\r\n"
    "Asha Iyer,9876543210,250.00\r\n"
    "Ravi,9900011122,12e3\r\n"
    "Meena,98123,99.00\r\n"
    "Joel D'Souza,9812345678,1200.50\r\n"
    "राधा कृष्णन,9123456789,75.25\r\n"
)


def is_name(text):
    """Letters of any script with their combining marks, and spaces, apostrophes, dots and hyphens between them."""
    if not 0 < len(text) <= 80 or not text[0].isalpha() or text != text.strip():
        return False
    return all(c.isalpha() or unicodedata.category(c).startswith("M") or c in " '.-" for c in text)


# What each column may hold: an allowlist of forms, each with its length.
RULES = {
    "name": is_name,
    "mobile": lambda text: re.fullmatch(r"\d{10}", text) is not None,
    "amount": lambda text: re.fullmatch(r"\d{1,9}\.\d{2}", text) is not None,
}


def new_table():
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE payment (name TEXT NOT NULL, mobile TEXT NOT NULL, amount_paise INTEGER NOT NULL)")
    return db


def weak_import(text):
    """Builds each INSERT from the text of the row, and skips a row whose INSERT fails."""
    db, refused = new_table(), []
    reader = csv.DictReader(io.StringIO(text, newline=""))
    for row in reader:
        amount = row["amount"].replace(".", "")
        try:
            db.execute(f"INSERT INTO payment VALUES ('{row['name']}', '{row['mobile']}', {amount})")
        except sqlite3.Error as e:
            refused.append(f"line {reader.line_num}: {e}")
    return db, refused


def safe_import(text):
    """Checks every row against RULES, then binds the values of the rows that pass with placeholders."""
    db, refused = new_table(), []
    reader = csv.DictReader(io.StringIO(text, newline=""))
    for row in reader:
        bad = [f"{column} {row[column]!r}" for column, rule in RULES.items() if not rule(row[column] or "")]
        if bad:
            refused.append(f"line {reader.line_num}: {', '.join(bad)}")
            continue
        paise = int(row["amount"].replace(".", ""))
        db.execute("INSERT INTO payment VALUES (?, ?, ?)", (row["name"], row["mobile"], paise))
    return db, refused


def check(importer):
    """Valid rows load with their names exact, and the two invalid rows are refused with their lines."""
    db, refused = importer(UPLOAD)
    problems = []
    names = [n for (n,) in db.execute("SELECT name FROM payment ORDER BY rowid")]
    if names != ["Asha Iyer", "Joel D'Souza", "राधा कृष्णन"]:
        problems.append(f"loaded {names}")
    if [r.split(":")[0] for r in refused] != ["line 3", "line 4"]:
        problems.append(f"refused {refused}")
    return problems


for importer in (weak_import, safe_import):
    problems = check(importer)
    print(f"{importer.__name__}: {'FAIL' if problems else 'PASS'}")
    for problem in problems:
        print("  " + problem)

db, refused = safe_import(UPLOAD)
print("safe_import refused:", refused)
print("a rule built on \\w:", re.fullmatch(r"\w+(?: \w+)*", "राधा कृष्णन") is not None, "| is_name():", is_name("राधा कृष्णन"))

weak_import: FAIL
  loaded ['Asha Iyer', 'Ravi', 'Meena', 'राधा कृष्णन']
  refused ['line 5: near "Souza": syntax error']
safe_import: PASS
safe_import refused: ["line 3: amount '12e3'", "line 4: mobile '98123'"]
a rule built on \w: False | is_name(): True

The weak import loaded the wrong rows and refused a right one. It stored Ravi’s amount 12e3, which is not money in any export, and Meena’s mobile number of five digits, and then the apostrophe in Joel D'Souza ended the SQL string early: near "Souza": syntax error. A harmless name broke the statement; the same flaw lets a value written on purpose change what the statement does. The safe import checked every row against its rules, refused lines 3 and 4 with the reason, bound the values of the other three with ? placeholders, and stored Joel D'Souza and राधा कृष्णन exactly as they write their names.

Write the rule for names with care. Python’s \w does not match the vowel signs of Devanagari or Tamil, so a regular expression built from it refuses most Hindi and Tamil names, as the last line of the output shows. The program’s is_name() checks each character with str.isalpha() and accepts the combining marks that letters carry.

Practise: two exports from one set of rows

The exercise builds both files of this lesson: a spreadsheet export with a byte-order mark and every risky first character neutralised, full-width forms included, which refuses text in a declared number column, and a program export that keeps every value exactly:

import csv
import io
from decimal import Decimal


def to_spreadsheet_csv(rows, numeric):
    """CSV text for people who open it in a spreadsheet: a BOM, and formulas neutralised."""
    return ",".join(rows[0]) + "\r\n" + "".join(",".join(str(v) for v in r.values()) + "\r\n" for r in rows)


def to_machine_csv(rows):
    """CSV text for programs: every value exactly as it is."""
    return ",".join(rows[0]) + "\r\n" + "".join(",".join(str(v) for v in r.values()) + "\r\n" for r in rows)

import csv
import io
from decimal import Decimal

from exports import to_machine_csv, to_spreadsheet_csv

ROWS = [
    {"id": "R-1", "note": "=2+5", "amount": Decimal("-250.00")},
    {"id": "R-2", "note": "Call back,+91 98765 43210", "amount": 1200},
    {"id": "R-3", "note": "@mention", "amount": Decimal("0.00")},
]


def cells(text):
    """The records of a CSV text, without its BOM."""
    return list(csv.reader(io.StringIO(text.removeprefix("\ufeff"), newline="")))


def test_spreadsheet_neutralises_formulas():
    """puts an apostrophe before text that starts like a formula"""
    rows = cells(to_spreadsheet_csv(ROWS, {"amount"}))
    assert rows[0] == ["id", "note", "amount"]
    assert [r[1] for r in rows[1:]] == ["'=2+5", "Call back,+91 98765 43210", "'@mention"]


def test_every_risky_first_character():
    """neutralises all eleven first characters, full-width ones too"""
    risky = ["=a", "+a", "-a", "@a", "\ta", "\ra", "\na", "＝a", "＋a", "－a", "＠a"]
    rows = [{"note": v} for v in risky + ["a=b", "safe"]]
    notes = [r[0] for r in cells(to_spreadsheet_csv(rows, set()))[1:]]
    assert notes == ["'" + v for v in risky] + ["a=b", "safe"]


def test_numbers_stay_numbers():
    """writes declared numbers as they are, negative ones too"""
    rows = cells(to_spreadsheet_csv(ROWS, {"amount"}))
    assert [r[2] for r in rows[1:]] == ["-250.00", "1200", "0.00"]


def test_bom_only_for_spreadsheets():
    """starts the spreadsheet file with a BOM and the machine file without one"""
    assert to_spreadsheet_csv(ROWS, {"amount"}).startswith("\ufeffid,note,amount\r\n")
    assert to_machine_csv(ROWS).startswith("id,note,amount\r\n")


def test_machine_export_unchanged():
    """keeps every value of the machine file exactly"""
    text = to_machine_csv(ROWS + [{"id": "R-4", "note": None, "amount": -5}])
    assert cells(text)[1:] == [["R-1", "=2+5", "-250.00"], ["R-2", "Call back,+91 98765 43210", "1200"],
                               ["R-3", "@mention", "0.00"], ["R-4", "", "-5"]]
    assert '"Call back,+91 98765 43210"' in text


def test_text_in_a_numeric_column():
    """refuses text in a numeric column, naming the row and the column"""
    rows = [{"id": "R-1", "amount": 5}, {"id": "R-2", "amount": "-5+3"}]
    try:
        to_spreadsheet_csv(rows, {"amount"})
    except ValueError as e:
        assert str(e) == "row 2, amount: not a number"
    else:
        assert False, "no ValueError"

Then answer the questions on how formulas get in, how the fix works, the two exports and safe imports. They come one at a time, beginning where your answers so far are weakest, and each explanation names the slip behind a wrong one:

Interview questions

Warm-up (fresher to 2–5 years): what is formula injection, also called CSV injection, and who is at risk?

It is a weakness of programs that export data to CSV. A value that came from outside, such as a name or a comment, is written into a cell unchanged, and when someone opens the file in a spreadsheet, a cell that starts with =, +, - or @, or with a tab, a line break or a full-width form of those signs, is read as a formula and run. The person at risk is the one who opens the file, often a colleague who trusts it because it came from your own system, not the program that wrote it. Even simple arithmetic changes the data on screen. The fix belongs in the export: write the file with a real CSV writer, put an apostrophe in front of every text cell that starts like a formula, keep declared numbers as numbers, and give programs a separate, unchanged export.

Two harder questions follow, about a security report on a support export and about a safe export that broke a finance import. Decide what you would check first, then open each first step:

Key takeaways

• Spreadsheets treat text beginning with =, +, - or @, with their full-width forms, or with a tab, CR or LF, as a formula, so an export that copies outside text unchanged lets a stranger’s formula run for whoever opens the file.
• Write the file with a real CSV writer, so commas stay inside their quotes, then put an apostrophe in front of every text cell that starts like a formula; declared numbers are written as numbers, and a check over the finished file proves it.
• Neutralised text is for people: give programs a second export with every value unchanged, UTF-8 without a byte-order mark, and document which file is for whom.
• Treat uploaded CSV as untrusted input: check every field against an allowlist with a length limit, refuse rows with their line and reason, and load the rest with placeholders, never with SQL built from the text.