paulpas CSV Data Handler
Reads, writes, validates, and sanitizes CSV data across Python, JavaScript/Node.js, PHP, and Go with RFC 4180 compliance, formula injection prevention, character encoding validation, and safe delimiter detection. Treat every CSV file — whether downloaded from a user, generated by an external service, or received as a webhook payload — as potentially malicious. The CSV format's simplicity hides critical security risks: spreadsheet applications interpret fields starting with =, +, -, @, and \t as formulas, enabling remote code execution in the spreadsheet process itself. Validate encoding at the byte level before parsing, enforce size limits during streaming reads, sanitize all outbound data before writing, and never trust a CSV reader library's auto-detected delimiter to be correct for your data.
TL;DR Checklist
- Always specify character encoding explicitly (UTF-8 preferred) when opening CSV files — never rely on the system default locale which varies across environments
- Sanitize every field value before writing it into a CSV that will be opened in Excel, Google Sheets, or LibreOffice — strip leading
=,+,-,@, and\tcharacters from all text fields - Use streaming readers for files larger than 50 MB — load the entire file into memory with csv.DictReader or read line-by-line with a buffered reader to avoid OOM crashes
- Validate delimiter selection against actual data — commas inside unquoted fields are the #1 cause of column misalignment, and semicolon vs comma differences break across regional Excel configurations
- Detect and handle BOM (Byte Order Mark) explicitly when reading UTF-8 CSV files — a leading EF BB BF byte corrupts the first header name into
\ufeffColumnNameunless stripped during open - Set maximum row count limits on all CSV readers to prevent memory exhaustion from maliciously large uploads
When to Use
Use this skill when:
- Building data import pipelines that accept CSV files uploaded by users or consumers (user-generated exports, bank statements, CRM data dumps)
- Generating CSV exports for downstream consumption by spreadsheet applications where formula injection is a real attack vector
- Parsing CSV feeds from third-party APIs, government portals, or partner integrations where you cannot control the producer's output quality
- Migrating legacy systems between formats (CSV → database, database → CSV) and need bidirectional safety guarantees
- Implementing data validation layers that enforce schema correctness on tabular imports — column count validation, type checking, null handling
- Building ETL/ELT pipelines where CSV is the intermediary format between ingestion and transformation stages
- Debugging CSV parsing errors caused by encoding mismatches, delimiter confusion, or RFC 4180 quoting violations
When NOT to Use
Avoid this skill for:
- JSON or XML data processing — use
data-encodingfor JSON serialization, XML parsing, and other structured formats instead - Binary file handling (Excel
.xlsx, Parquet, Avro) — these require specialized libraries (openpyxl, pyarrow) and have different security considerations than plain-text CSV - Real-time database queries or OLTP workloads — CSV is a batch interchange format; use parameterized SQL for database operations instead
- Generating reports intended for human visual inspection in terminals — CSV has no styling capabilities; use HTML tables or PDF for formatted reports
Core Workflow
Inspect the Raw Bytes — Before parsing, examine the first 4096 bytes of the file to determine: character encoding (UTF-8 BOM detection at EF BB BF, UTF-16 LE/BE signatures at FF FE / FE FF), line ending style (CRLF vs LF vs CR only), delimiter character (first data row scanned for field separators), and whether a header row is present. Checkpoint: Record the detected encoding, delimiter, and BOM presence before any parsing begins — these decisions affect every subsequent operation.
Open the File with Explicit Configuration — Configure the file handle with the correct encoding, newline handling, buffer size, and error recovery strategy (surrogateescape for Python, strict/ignore for other languages). For streaming reads, use chunked or line-by-line reading rather than
read()to load the entire file into memory. Checkpoint: If you are opening a 2 GB file with no streaming, you will crash — always use iterators or generators for files over 50 MB.Parse with Validation — Read rows while enforcing: column count matches header row length (reject rows with too few or too many fields), type checking on numeric and date columns, required field validation, and duplicate key detection. Track and report parse errors with line numbers rather than failing silently. Checkpoint: After parsing the first 100 rows, verify that all expected columns are present and contain plausible data before committing to a full file load.
Sanitize for Outbound CSV — Before writing any data to CSV, ensure every field value is safe: strip leading formula characters (
=,+,-,@,\t), escape embedded double-quotes by doubling them ("→""per RFC 4180), quote all fields containing commas, newlines, or the delimiter character, and validate string length limits to prevent malformed output. Checkpoint: Write a sample row and verify it parses back correctly using an independent parser — outbound CSV must be round-trip safe.Validate Output Integrity — After writing, verify the file has no truncation (expected row count matches written row count), no encoding corruption (re-read first 1024 bytes as UTF-8 without errors), and correct structure (header row present, consistent column counts). For large files, sample rows at intervals (every 10,000th row) to detect mid-file corruption. Checkpoint: File size should be within expected range; a CSV that is 90% of its normal size likely has truncated rows due to encoding errors or write failures.
Implementation Patterns / Reference Guide
Pattern 1: Python — Safe CSV Reading with Encoding Detection and Formula Sanitization (BAD vs GOOD)
Python's csv module handles RFC 4180 parsing natively but provides zero security protections. The naive approach leaves applications vulnerable to encoding crashes, BOM corruption on headers, formula injection in output files, and memory exhaustion from loading large files entirely into RAM. Use explicit encoding specification, streaming iteration, header sanitization, and field-level formula prevention.
"""Safe CSV data handling with RFC 4180 compliance, encoding detection,
formula injection prevention, and streaming for large files.
This module demonstrates production-grade CSV handling in Python using the
built-in csv module combined with chardet-style encoding detection, explicit
BOM stripping, formula sanitization for spreadsheet safety, and memory-efficient
streaming reads. Follows OWASP guidance on CSV injection mitigation.
Key security properties:
- UTF-8 BOM (EF BB BF) detected and stripped to prevent header corruption
- Leading formula characters (=, +, -, @, \\t) sanitized from all outbound fields
- Streaming iteration prevents loading entire files into memory
- Explicit encoding specification avoids locale-dependent defaults
- Column count validation rejects malformed rows immediately
"""
import codecs
import csv
import io
import logging
from dataclasses import dataclass, field
from pathlib import Path
from typing import Any, Generator, Iterator
logger = logging.getLogger(__name__)
# RFC 4180: Fields containing commas, double quotes, or line breaks MUST be quoted.
# Double quotes within a field are escaped by doubling them ("").
# Standard delimiter is comma; CRLF is the standard line ending.
# Formula injection characters recognized by Excel, Google Sheets, and LibreOffice Calc.
# A field starting with ANY of these characters triggers formula execution when opened
# in a spreadsheet application — enabling arbitrary calculations in the spreadsheet process.
FORMULA_PREFIXES = ("=", "+", "-", "@", "\t")
# Maximum CSV file size to load entirely into memory (100 MB). Files above this threshold
# must be processed via streaming iteration to prevent OOM crashes.
MAX_MEMORY_CSV_BYTES = 100_000_000
@dataclass
class CsvParseResult:
"""Structured result from parsing a CSV file safely."""
headers: list[str]
row_count: int
encoding_detected: str
delimiter: str
had_bom: bool
malformed_rows: list[dict[str, Any]] = field(default_factory=list)
def detect_encoding_and_delimiter(
raw_bytes: bytes, sample_size: int = 4096
) -> tuple[str, str]:
"""Detect CSV file encoding and delimiter from the first N bytes.
Analyzes the byte signature to determine encoding (UTF-8 with BOM, UTF-16,
ASCII-compatible) and scans the first row to infer the delimiter character.
Args:
raw_bytes: First 4096 bytes of the CSV file for analysis.
sample_size: Number of bytes to analyze (default: 4096).
Returns:
Tuple of (encoding_string, delimiter_char).
Encoding will be 'utf-8', 'utf-16-le', 'utf-16-be', 'latin-1', etc.
Delimiter is typically ',', ';', or '\\t'.
Raises:
ValueError: If no printable ASCII characters are found in the sample,
indicating binary data that cannot be parsed as CSV.
"""
if not raw_bytes:
raise ValueError("Empty file — nothing to detect encoding for.")
# Step 1: Detect BOM and encoding from byte signature
had_bom = False
encoding = "utf-8"
if raw_bytes[:3] == b"\xef\xbb\xbf":
encoding = "utf-8-sig" # UTF-8 with BOM — Python handles stripping automatically
had_bom = True
elif raw_bytes[:2] == b"\xff\xfe":
encoding = "utf-16-le"
had_bom = True
elif raw_bytes[:2] == b"\xfe\xff":
encoding = "utf-16-be"
had_bom = True
# Step 2: Scan first row for delimiter by counting field separators
sample_text = raw_bytes[:sample_size].decode(encoding, errors="replace")
first_line = sample_text.split("\n")[0] if "\n" in sample_text else sample_text
# Count candidate delimiters (exclude quoted field occurrences)
candidates = {
",": first_line.count(","),
";": first_line.count(";"),
"\t": first_line.count("\t"),
"|": first_line.count("|"),
}
delimiter = max(candidates, key=candidates.get)
if candidates[delimiter] == 0:
raise ValueError(
"No delimiter characters found in the file sample. "
"This may be a binary file or single-column data."
)
return encoding, delimiter
def sanitize_formula_field(value: str) -> str:
"""Remove leading formula injection characters from a CSV field value.
Spreadsheet applications (Excel, Google Sheets, LibreOffice Calc) interpret
any cell whose content begins with =, +, -, @, or \\t as a formula. This
function strips the prefix character and returns the raw text, preventing
formula execution when the CSV is opened in a spreadsheet app.
This follows OWASP guidance on CSV Injection prevention: sanitize outbound
data by neutralizing dangerous prefixes rather than trying to detect and
block specific formula syntaxes (which is an impossible game of whack-a-mole).
Examples:
"=SUM(A1:A10)" → "SUM(A1:A10)" (prefix = stripped)
"+2+3" → "2+3" (prefix + stripped)
"-5*6" → "5*6" (prefix - stripped)
"@now()" → "now()" (prefix @ stripped)
"\\tformula" → "formula" (tab prefix stripped)
Args:
value: The raw string value to sanitize for CSV output.
Returns:
Sanitized string with leading formula prefix removed if present,
or the original string unchanged if no dangerous prefix detected.
"""
if not value:
return value
if value[0] in FORMULA_PREFIXES:
logger.warning(
"Sanitized formula injection character '%s' from CSV field: %s",
repr(value[0]),
value[:50],
)
return value[1:]
return value
def sanitize_header(header: str) -> str:
"""Clean CSV header names that may be corrupted by BOM or whitespace.
UTF-8 BOM (EF BB BF) at the start of a file corrupts the first header field
into '\\ufeffColumnName' unless the encoding is set to 'utf-8-sig'. This
function strips BOM remnants and normalizes all header names by stripping
whitespace and lowercasing for consistent internal representation.
Args:
header: Raw header field name as parsed from the CSV first row.
Returns:
Cleaned header name with BOM characters, surrounding whitespace, and
consecutive spaces removed. The returned value is safe to use as a
dictionary key or column identifier.
"""
# Strip BOM character if it survived into the header (encoding mismatch)
cleaned = header.replace("\ufeff", "")
# Normalize whitespace: strip edges, collapse internal runs to single space
cleaned = " ".join(cleaned.strip().split())
return cleaned.lower()
def read_csv_streaming(
file_path: Path,
max_rows: int | None = None,
expected_columns: int | None = None,
) -> Generator[dict[str, str], None, None]:
"""Stream-parse a CSV file row-by-row without loading it entirely into memory.
Uses Python's csv.DictReader with an open file iterator to yield one dict
per row. This is the only safe approach for files larger than 50 MB because
DictReader internally iterates lazily and never holds all rows in a list.
Applies encoding detection, BOM stripping, formula sanitization on values,
and column count validation with malformed row tracking.
Args:
file_path: Path to the CSV file to read.
max_rows: Optional hard limit on rows to yield. Prevents processing of
maliciously large files by stopping iteration at this count.
expected_columns: If provided, validates each row has exactly this many
columns. Rows with mismatched column counts are logged and skipped.
Yields:
Dictionary mapping sanitized header names to sanitized field values
for each valid row in the CSV file.
Raises:
FileNotFoundError: If file_path does not exist.
ValueError: If the file cannot be opened or parsed as valid UTF-8.
"""
if not file_path.exists():
raise FileNotFoundError(f"CSV file not found: {file_path}")
# Detect encoding from first 4096 bytes before opening
with open(file_path, "rb") as f_raw:
raw_sample = f_raw.read(4096)
encoding, delimiter = detect_encoding_and_delimiter(raw_sample)
logger.info(
"Detected CSV encoding=%s, delimiter=%r, had_bom=%s for %s",
encoding,
delimiter,
"\ufeff" in raw_sample[:3].decode(encoding, errors="replace"),
file_path,
)
malformed = []
row_count = 0
headers_validated = False
with open(file_path, "r", encoding=encoding, newline="", errors="surrogateescape") as f:
reader = csv.DictReader(f, delimiter=delimiter)
# Validate and sanitize headers on first read
if reader.fieldnames:
sanitized_headers = [sanitize_header(h) for h in reader.fieldnames]
reader.fieldnames = sanitized_headers
if expected_columns is not None:
if len(sanitized_headers) != expected_columns:
logger.error(
"Expected %d columns but found %d in header row",
expected_columns,
len(sanitized_headers),
)
headers_validated = True
for row_dict in reader:
if max_rows is not None and row_count >= max_rows:
logger.info("Reached maximum row limit (%d) — stopping iteration.", max_rows)
break
# Validate column count on first non-header row (after headers are validated)
if expected_columns is not None and reader.fieldnames:
actual_keys = [k for k in row_dict.keys() if k is not None]
if len(actual_keys) != expected_columns:
malformed.append({
"row_num": row_count + 1,
"expected_columns": expected_columns,
"actual_columns": len(actual_keys),
"data_sample": {str(k): str(v)[:100] for k, v in list(row_dict.items()) if k is not None},
})
continue
# Sanitize all values: strip formula injection prefixes
sanitized_row = {}
for key, value in (row_dict or {}).items():
if key is not None:
sanitized_key = sanitize_header(key)
sanitized_value = sanitize_formula_field(str(value)) if value else ""
sanitized_row[sanitized_key] = sanitized_value
yield sanitized_row
row_count += 1
if malformed:
logger.warning("Parsed %d rows with %d malformed entries", row_count, len(malformed))
# ---------------------------------------------------------------------------
# ❌ BAD — Naive CSV reading with no security protections
# ---------------------------------------------------------------------------
def bad_read_csv(file_path: str) -> list[dict[str, str]]:
"""Read a CSV file using csv.DictReader without any safety measures.
This function is dangerous in multiple ways:
1. No encoding specified — relies on system default locale. On a server with
LANG=C the parser may misinterpret UTF-8 bytes as Latin-1, corrupting
every international character (e.g., "Fran\\u00e7ois" becomes "François").
2. BOM handling absent — if the file starts with UTF-8 BOM (EF BB BF), the
first header name becomes '\\ufeffid' instead of 'id', breaking all
downstream dictionary lookups by key. This is silent corruption: the
code doesn't crash, it just produces wrong results.
3. Formula injection not prevented — if the CSV contains "=SUM(A1:A10)" in
any cell and a user opens the file in Excel, the formula executes in the
spreadsheet process. This can lead to data exfiltration (via named range
tricks) or DoS via volatile function exhaustion.
4. Entire file loaded into memory — csv.DictReader.read() collects ALL rows
into a list before returning. A 2 GB CSV with 10 million rows will crash
the process with MemoryError on a machine with only 8 GB RAM (because
Python's dict overhead makes the in-memory representation ~3x larger than
the raw file).
5. No column count validation — malformed rows with missing fields silently
produce None values that propagate through business logic as unexpected
nulls, causing subtle bugs.
Args:
file_path: Path to the CSV file (string, not Path object).
Returns:
List of dictionaries mapping raw header names to raw field values.
Headers may contain BOM characters; values may contain formula prefixes;
None values appear for rows with missing columns.
"""
with open(file_path, newline="") as f:
reader = csv.DictReader(f)
return list(reader) # Loads entire file into memory — dangerous
# ---------------------------------------------------------------------------
# ✅ GOOD — Production-safe CSV reading and writing
# ---------------------------------------------------------------------------
def write_csv_safe(
rows: Iterator[dict[str, Any]],
output_path: Path,
headers: list[str],
delimiter: str = ",",
max_rows: int | None = None,
) -> dict[str, int]:
"""Write CSV data safely with formula injection prevention and RFC 4180 compliance.
Uses csv.writer with explicit quoting strategy to produce RFC 4180-compliant
output. Every field value is sanitized against formula injection characters
before being written. The quoting module ensures proper escaping: commas,
newlines, and the delimiter within fields are automatically quoted, and
embedded double-quotes are escaped by doubling them.
Implements streaming writes — rows are yielded from an iterator (Generator,
database cursor, or other lazy source) and written one at a time to avoid
memory accumulation. A max_rows limit provides a hard safety cap.
Args:
rows: Iterator of dictionaries mapping header names to field values.
Each dict should contain all keys from the headers list. Missing
keys are written as empty strings. Non-string values are coerced
via str().
output_path: File path where the CSV will be written. The parent directory
must exist; the file is created or overwritten atomically.
headers: Ordered list of column names for the CSV header row. These must
match the keys in every row dictionary (minus any None-keyed entries).
delimiter: Field separator character (default: comma per RFC 4180).
Semicolon is common in European Excel exports due to regional settings.
Tab is used for TSV format.
max_rows: Optional hard limit on rows to write. Prevents runaway writes
from infinite generators or unbounded data sources.
Returns:
Dictionary with written row count and estimated file size in bytes:
{"rows_written": int, "file_size_bytes": int}
Raises:
IOError: If the output file cannot be created or written to.
"""
rows_written = 0
# Open with explicit UTF-8 encoding and BOM for spreadsheet compatibility.
# Excel on Windows requires the BOM (EF BB BF) to correctly recognize UTF-8
# files; without it, Excel assumes Latin-1 and corrupts non-ASCII characters.
# Using 'w' mode with newline="" is critical: Python's csv.writer writes '\r\n'
# line endings per RFC 4180, and the newline="" parameter prevents double-wrapping
# by Python's universal newline translation layer.
with open(output_path, "w", encoding="utf-8-sig", newline="", errors="replace") as f:
writer = csv.writer(
f,
delimiter=delimiter,
quotechar='"',
quoting=csv.QUOTE_MINIMAL, # Quote only when necessary (RFC 4180 compliant)
lineterminator="\r\n", # RFC 4180 standard line ending
)
# Write header row
writer.writerow(headers)
rows_written += 1 # Count header as first "row" for the return dict
# Stream-write data rows one at a time
for row in rows:
if max_rows is not None and rows_written - 1 >= max_rows:
logger.info("Reached maximum row limit during write (%d).", max_rows)
break
# Build sanitized data row with formula prefix removal
data_row = []
for header in headers:
raw_value = row.get(header, "")
str_value = str(raw_value) if raw_value is not None else ""
safe_value = sanitize_formula_field(str_value)
data_row.append(safe_value)
writer.writerow(data_row)
rows_written += 1
file_size = output_path.stat().st_size
logger.info("Wrote %d rows (including header) to %s (%d bytes)", rows_written, output_path, file_size)
return {
"rows_written": rows_written,
"file_size_bytes": file_size,
}
# Demonstration usage
if __name__ == "__main__":
import tempfile
sample_rows = [
{"name": "Alice", "amount": "=SUM(B1:B10)", "status": "active"},
{"name": "Bob", "amount": "+2+3", "status": "inactive"},
{"name": "Charlie O'Brien", "amount": "-5", "status": "pending, reviewed"}, # Comma in value needs quoting
]
with tempfile.TemporaryDirectory() as tmpdir:
output_file = Path(tmpdir) / "sanitized_output.csv"
# Write sanitized CSV (formula prefixes removed, commas properly quoted)
result = write_csv_safe(
rows=iter(sample_rows),
output_path=output_file,
headers=["name", "amount", "status"],
)
print(f"Wrote {result['rows_written']} rows to {output_file}")
# Read it back safely
print("\nRead-back rows:")
for row in read_csv_streaming(output_file):
print(f" {row}")
print("\n=== CSV Security Properties ===")
print("- Formula prefixes (=, +, -, @, \\t) are stripped from all field values")
print("- UTF-8 BOM detected and handled transparently via utf-8-sig encoding")
print("- Streaming reads prevent OOM on large files (uses csv.DictReader iterator)")
print("- RFC 4180 quoting: commas in values are auto-quoted, embedded \"\" escaped by doubling")
Pattern 2: JavaScript/Node.js — Papa Parse Safe CSV Parsing with Formula Sanitization (BAD vs GOOD)
Node.js has no built-in CSV parser. The industry-standard solution is Papa Parse for reading and a simple manual writer for controlled output. The naive approach uses Papa Parse defaults which are insecure: it assumes UTF-8 without checking for BOM, loads entire files into memory unless explicitly streaming, does not sanitize formula injection characters in parsed values, and auto-detects delimiters with heuristics that fail on data containing commas within unquoted fields.
/**
* Safe CSV handling in Node.js using Papa Parse (reading) and a custom
* RFC 4180-compliant writer (writing).
*
* Security features:
* - BOM detection and stripping from header names
* - Formula injection character sanitization (=, +, -, @, \\t prefixes)
* - Configurable chunked/streaming reads to avoid memory exhaustion
* - Explicit delimiter selection with fallback validation
* - Column count validation per row with error reporting
*
* Dependencies: npm install papaparse
*/
const fs = require("fs");
const path = require("path");
const Papa = require("papaparse");
// Formula injection prefixes recognized by spreadsheet applications.
const FORMULA_PREFIXES = ["=", "+", "-", "@", "\t"];
/** Maximum number of rows to process in memory before yielding results.
* For streaming: set to a small batch size (e.g., 1000) and process each
* batch independently. Default is null for no limit (caller responsibility). */
const DEFAULT_MAX_BATCH_ROWS = 5000;
/** Maximum file size in bytes before switching to streaming mode automatically.
* Files above this threshold must be read as a stream, not with readFile + Papa.parse(). */
const STREAMING_THRESHOLD_BYTES = 50_000_000; // 50 MB
// ---------------------------------------------------------------------------
// ❌ BAD — Naive Papa Parse usage with no security protections
// ---------------------------------------------------------------------------
/**
* Reads a CSV file using Papa Parse defaults. No encoding checks, no formula
* sanitization, no streaming for large files. This is how most Node.js apps
* handle CSV imports — and it leaves them vulnerable to:
*
* 1. BOM corruption of the first column header ("\\ufeffid" vs "id")
* 2. Formula injection in cells like "=EXECUTE(cmd)" opening in Excel
* 3. OOM crashes on large files because Papa.parse() loads everything into memory
* 4. Silent data loss when rows have mismatched column counts (skipEmptyLines)
*/
function badReadCsv(filePath) {
const rawData = fs.readFileSync(filePath, "utf8"); // No encoding detection, no BOM handling
const result = Papa.parse(rawData, {
header: true, // Assumes first row is headers — but what if it's not?
skipEmptyLines: true, // Silently drops empty rows — could hide data issues
// No dynamicStep, no chunk processing — entire file in memory
// No formula sanitization on values
});
if (result.errors.length > 0) {
console.warn("Papa Parse reported errors:", result.errors);
// Errors are logged but parsing continues with corrupted data
}
return result.data; // Raw, unsanitized, potentially dangerous data
}
// ---------------------------------------------------------------------------
// ✅ GOOD — Safe CSV reading with Papa Parse streaming and sanitization
// ---------------------------------------------------------------------------
/**
* Detects the first 4096 bytes of a file for BOM encoding signature.
* Returns { encoding, bomPresent, encodingName }.
*
* UTF-8 BOM: EF BB BF → requires 'utf-8-sig' handling (strip \\ufeff from headers)
* UTF-16 LE: FF FE → rare in CSV exchanges, but possible
* No BOM: normal ASCII-compatible UTF-8
*/
function detectBom(filePath) {
const buffer = Buffer.alloc(3);
const fd = fs.openSync(filePath, "r");
try {
fs.readSync(fd, buffer, 0, 3, 0);
fs.closeSync(fd);
if (buffer[0] === 0xef && buffer[1] === 0xbb && buffer[2] === 0xbf) {
return { encoding: "utf-8-sig", bomPresent: true, encodingName: "UTF-8 with BOM" };
}
} catch (err) {
// File too small or unreadable — fall through to default UTF-8
}
return { encoding: "utf8", bomPresent: false, encodingName: "UTF-8" };
}
/**
* Strips formula injection characters from the beginning of a string.
* Spreadsheet apps interpret leading = + - @ \\t as formula initiators.
*
* This is NOT regex-based stripping (which would remove ALL occurrences).
* It only removes the first character IF it matches a dangerous prefix,
* preserving legitimate data that happens to start with those characters
* in other positions (e.g., "the = sign" → "the = sign", unchanged).
*/
function sanitizeFormulaPrefix(value) {
if (typeof value !== "string" || !value.length) return value;
const firstChar = value.charAt(0);
if (FORMULA_PREFIXES.includes(firstChar)) {
console.warn(`Sanitized formula prefix '${firstChar}' from value: ${value.substring(0, 40)}`);
return value.substring(1);
}
return value;
}
/**
* Strips BOM character (\\ufeff) from a string, typically found in the
* first header column when a UTF-8 BOM file is not opened with utf-8-sig.
*/
function stripBom(str) {
if (typeof str !== "string") return str;
return str.replace(/^\uFEFF/, "").trim();
}
/**
* Reads and sanitizes a CSV file safely using Papa Parse with configurable
* options for encoding, streaming, and validation.
*
* Supports both buffered reads (small files) and streaming reads (large files).
* Returns parsed rows as an array of sanitized objects.
*
* @param {string} filePath - Absolute or relative path to the CSV file.
* @param {Object} [options] - Configuration options.
* @param {number} [options.maxRows=null] - Hard limit on rows to read. Null = unlimited.
* @param {number} [options.batchSize=5000] - Number of rows per processing batch (for streaming).
* @param {string} [options.delimiter=null] - Force delimiter; null = auto-detect.
* @returns {Promise<Array<Object>>} Array of sanitized row objects with clean headers.
*/
function readCsvSafe(filePath, options = {}) {
const { maxRows = null, batchSize = DEFAULT_MAX_BATCH_ROWS, delimiter = null } = options;
if (!fs.existsSync(filePath)) {
throw new Error(`CSV file not found: ${filePath}`);
}
const stat = fs.statSync(filePath);
const bomInfo = detectBom(filePath);
return new Promise((resolve, reject) => {
// For small files (< 50 MB), use buffered read for simplicity.
// For large files, switch to streaming mode below.
if (stat.size < STREAMING_THRESHOLD_BYTES && !delimiter === null) {
_readCsvBuffered(filePath, bomInfo, options, resolve, reject);
} else {
_readCsvStreaming(filePath, bomInfo, options, resolve, reject);
}
});
}
/** Buffered read path — loads file into memory, parses with Papa Parse. */
function _readCsvBuffered(filePath, bomInfo, options, resolve, reject) {
const rawData = fs.readFileSync(filePath, { encoding: bomInfo.encoding });
const parsed = Papa.parse(rawData, {
header: true,
skipEmptyLines: false,
dynamicTyping: false, // Keep all values as strings — validate types explicitly later
delimiter: options.delimiter || undefined,
quoteChar: '"',
escapeChar: '"',
});
if (parsed.errors.length > 0) {
const errors = parsed.errors.slice(0, 10); // Log first 10 only
console.warn(`CSV parsing reported ${parsed.errors.length} errors (showing first 10):`, errors.map(e => e.message));
}
// Sanitize headers and rows
const sanitizedRows = parsed.data
.filter(row => row !== null && typeof row === "object" && Object.keys(row).length > 0) // Skip empty rows
.slice(0, options.maxRows) // Apply max rows limit
.map((row, index) => {
const sanitizedRow = {};
for (const [key, value] of Object.entries(row)) {
if (key === null || key === undefined) continue;
const cleanKey = stripBom(key);
const cleanValue = sanitizeFormulaPrefix(typeof value === "string" ? value : String(value ?? ""));
sanitizedRow[cleanKey] = cleanValue;
}
return sanitizedRow;
});
resolve(sanitizedRows);
}
/** Streaming read path — processes file in chunks to bound memory usage. */
function _readCsvStreaming(filePath, bomInfo, options, resolve, reject) {
const allRows = [];
let rowCounter = 0;
const maxRows = options.maxRows;
const stream = fs.createReadStream(filePath, {
encoding: bomInfo.encoding,
highWaterMark: 64 * 1024, // 64 KB read buffers — balances I/O efficiency with memory
});
Papa.parse(stream, {
header: true,
dynamicTyping: false,
delimiter: options.delimiter || undefined,
chunk: (results, parser) => {
// Process each chunk of rows
const rows = results.data;
if (!Array.isArray(rows)) return;
for (const row of rows) {
if (maxRows !== null && rowCounter >= maxRows) {
parser.abort();
break;
}
if (row === null || typeof row !== "object" || Object.keys(row).length === 0) continue;
const sanitizedRow = {};
for (const [key, value] of Object.entries(row)) {
if (key == null) continue;
const cleanKey = stripBom(String(key));
const cleanValue = sanitizeFormulaPrefix(typeof value === "string" ? value : String(value ?? ""));
sanitizedRow[cleanKey] = cleanValue;
}
allRows.push(sanitizedRow);
rowCounter++;
}
},
error: (err) => {
console.error(`CSV stream parse error: ${err.message}`);
reject(new Error(`CSV parsing failed: ${err.message}`));
},
complete: () => {
console.log(`Streamed ${allRows.length} rows from ${filePath}`);
resolve(allRows);
},
});
}
/**
* Writes data to CSV safely with formula sanitization and RFC 4180 compliance.
* Uses Papa's unparse for output generation, then post-processes the result
* to ensure proper BOM inclusion and line ending consistency.
*
* @param {Array<Object>} rows - Array of row objects to write.
* @param {string} headers - Ordered header column names (e.g., "id,name,email").
* @param {string} outputPath - File path for the output CSV.
* @returns {Object} Write statistics: { rowsWritten, fileSizeBytes }.
*/
function writeCsvSafe(rows, headers, outputPath) {
const csvOutput = Papa.unparse({
fields: headers,
data: rows.map(row => {
const safeRow = [];
for (const header of headers) {
const rawValue = row[header] ?? "";
// Sanitize formula prefix from every value before writing
safeRow.push(sanitizeFormulaPrefix(String(rawValue)));
}
return safeRow;
}),
quotes: true, // Quote every field for safety (prevents delimiter issues)
quotedString: true, // Always quote string values
});
// Prepend UTF-8 BOM for Excel/Google Sheets compatibility on Windows
const bomBuffer = Buffer.from([0xEF, 0xBB, 0xBF]);
const contentBuffer = Buffer.from(csvOutput, "utf8");
const finalBuffer = Buffer.concat([bomBuffer, contentBuffer]);
fs.writeFileSync(outputPath, finalBuffer);
const stat = fs.statSync(outputPath);
return { rowsWritten: rows.length, fileSizeBytes: stat.size };
}
// Demonstration
if (require.main === module) {
// Sample data with formula injection attempts
const sampleRows = [
{ name: "Alice", amount: "=SUM(A1:A10)", status: "active" },
{ name: "Bob", amount: "+2+3*4", status: "inactive" },
{ name: "Charlie O'Brien", amount: "-5.99", status: "pending, reviewed" },
];
const outputPath = path.join(__dirname, "safe_output.csv");
const result = writeCsvSafe(sampleRows, ["name", "amount", "status"], outputPath);
console.log(`Wrote ${result.rowsWritten} rows to ${outputPath} (${result.fileSizeBytes} bytes)`);
}
module.exports = { readCsvSafe, writeCsvSafe, sanitizeFormulaPrefix };
Pattern 3: PHP — fgetcsv and SplFileObject Safe CSV Handling (BAD vs GOOD)
PHP provides two built-in CSV parsers: fgetcsv() (procedural, line-by-line) and SplFileObject (object-oriented, implements Iterator). Both handle RFC 4180 quoting correctly but have different security implications. The naive approach uses fgetcsv() with default parameters — which assume comma delimiter, double-quote enclosure, and backslash escape — matching neither RFC 4180 nor many real-world CSV exports that use semicolons (European regional Excel settings).
<?php
/**
* Safe CSV data handling in PHP using SplFileObject with formula injection
* prevention, encoding validation, delimiter detection, and streaming.
*
* Security features:
* - Explicit character encoding handling (UTF-8 with BOM stripping)
* - Formula injection sanitization for spreadsheet safety
* - Streaming reads via SplFileObject iterator — no full file load in memory
* - Delimiter auto-detection from first row content analysis
* - Column count validation per row
*
* PHP version: 8.0+ required for named arguments and match expressions.
*/
/** Formula injection prefixes recognized by spreadsheet applications.
* A field starting with ANY of these characters triggers formula execution. */
const FORMULA_PREFIXES = ['=', '+', '-', '@', "\t"];
/** Maximum rows to process before stopping (prevents infinite malicious files). */
const MAX_CSV_ROWS = 1_000_000;
/** Stream chunk size in bytes for reading large files. Default is 8 KB. */
const STREAM_BUFFER_SIZE = 8192;
// ---------------------------------------------------------------------------
// ❌ BAD — Naive fgetcsv() with no security protections
// ---------------------------------------------------------------------------
/**
* Reads CSV using PHP's fgetcsv() with default parameters. This is the most
* common PHP CSV reading pattern found in tutorials and legacy code, but it
* is dangerous because:
*
* 1. Uses backslash as escape character by default (third parameter).
* RFC 4180 specifies double-quote escaping ("" for embedded quotes).
* A field containing "hello""world" will be parsed as hello\"world instead
* of the correct hello"world, corrupting all data.
*
* 2. No encoding handling — PHP's fgetcsv() works on raw b
…(truncated)