Csv Data Handling

---




name: csv-data-handling
description: Handles CSV data safely across languages with RFC 4180 compliance, formula injection prevention, character encoding validation, and delimiter detection to protect against spreadsheet injection attacks.
license: MIT
compatibility: opencode
metadata:
  version: "1.0.0"
  domain: coding
  triggers: csv data handling, rfc 4180, csv injection, formula injection, spreadsheet security, csv parsing, delimiters, safe csv reading
  archetypes:
    - tactical
    - diagnostic
  anti_triggers:
    - brainstorming
    - vague ideation
    - code golf
    - over-engineering
  response_profile:
    verbosity: low
    directive_strength: high
    abstraction_level: operational
  role: implementation
  scope: implementation
  output-format: code
  content-types:
    - code
    - guidance
    - do-dont
    - examples
  related-skills: html-entity-encoding,url-parsing-security,input-validation,api-security-patterns




---





# 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 `\t` characters 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 `\ufeffColumnName` unless 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-encoding` for 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

1. **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.

2. **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.

3. **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.

4. **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.

5. **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.

```python
"""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.

```javascript
/**
 * 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
<?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 bytes. If the file
 *    is UTF-8 encoded and the PHP locale is C/POSIX, multi-byte characters
 *    are split incorrectly at byte boundaries within quote characters.
 *
 * 3. BOM character (\xEF\xBB\xBF) corrupts the first column name into
 *    "\xef\xbb\xbfid" — this is a silent bug because string lookups still
 *    work on the corrupted key, but the output will show garbled headers.
 *
 * 4. No formula sanitization — cells containing "=cmd.exe /c dir >C:\temp\out.txt"
 *    are passed through unchanged and will execute when opened in Excel.
 *
 * 5. No streaming limit — calling iterator_to_array(fgetcsv()) loads every row
 *    into memory at once. A CSV with 5 million rows uses ~2 GB of RAM.
 */
function badReadCsv(string $filePath): array
{
    $file = fopen($filePath, 'r');  // No encoding parameter — relies on PHP internal default
    if ($file === false) {
        throw new RuntimeException("Cannot open file: $filePath");
    }

    // Default parameters: delimiter=',', enclosure='"', escape='\\'
    // The escape='\\' is WRONG for RFC 4180 — should be empty string ''
    $data = [];
    while (($row = fgetcsv($file)) !== false) {
        $data[] = $row;  // No validation, no sanitization, loads everything into memory
    }

    fclose($file);
    return $data;
}

// ---------------------------------------------------------------------------
// ✅ GOOD — Safe CSV handling with SplFileObject and comprehensive protections
// ---------------------------------------------------------------------------

/**
 * Detects the CSV delimiter by analyzing frequency of candidate separators
 * in the first data row. Returns the most likely delimiter character.
 *
 * This simple heuristic counts occurrences outside quoted fields by tracking
 * quote state (inside/outside quotes). A more sophisticated version would
 * use a full RFC 4180 parser to count only unquoted delimiters.
 *
 * @param string $firstRow The first line of the CSV file as read from disk.
 * @return string The detected delimiter character.
 */
function detectDelimiter(string $firstRow): string
{
    // Track whether we are inside a quoted field
    $insideQuotes = false;
    $counts = [',' => 0, ';' => 0, "\t" => 0, '|' => 0];

    for ($i = 0, $len = strlen($firstRow); $i < $len; $i++) {
        $char = $firstRow[$i];

        if ($char === '"') {
            // Toggle quote state (simple heuristic — does not handle escaped "")
            $insideQuotes = !$insideQuotes;
        } elseif (!$insideQuotes && isset($counts[$char])) {
            $counts[$char]++;
        }
    }

    return array_keys($counts, max($counts))[0];
}

/**
 * Strips the UTF-8 BOM (byte order mark) from a string.
 * The UTF-8 BOM is the byte sequence EF BB BF, which PHP interprets as
 * the Unicode code point U+FEFF (\xEF\xBB\xBF in PHP string terms).
 * This typically corrupts the first column header name.
 *
 * @param string $str Input string that may start with a BOM character.
 * @return string String with leading BOM removed if present.
 */
function stripBom(string $str): string
{
    $bom = "\xEF\xBB\xBF"; // UTF-8 BOM byte sequence in PHP
    if (str_starts_with($str, $bom)) {
        return substr($str, 3);
    }
    return $str;
}

/**
 * Removes leading formula injection characters from a string value.
 * Spreadsheet applications interpret fields starting with =, +, -, @, or \t
 * as formulas and execute them when the CSV is opened in Excel, Google Sheets,
 * or LibreOffice Calc.
 *
 * This function strips ONLY the first character if it matches a dangerous prefix.
 * It does NOT remove subsequent occurrences of these characters (e.g., "10+5"
 * remains "10+5", only "=SUM(A1)" becomes "SUM(A1)").
 *
 * @param mixed $value The raw field value from the CSV.
 * @return string Sanitized value safe for spreadsheet consumption.
 */
function sanitizeFormulaPrefix($value): string
{
    if (!is_string($value) || $value === '') {
        return (string)$value;
    }

    $firstChar = $value[0];
    if (in_array($firstChar, FORMULA_PREFIXES, true)) {
        error_log(sprintf(
            'Sanitized formula prefix %s from CSV field: %s',
            var_export($firstChar, true),
            mb_substr($value, 0, 50)
        ));
        return substr($value, 1);
    }

    return $value;
}

/**
 * Validates a string contains only printable ASCII and UTF-8 multi-byte characters.
 * Rejects strings containing control characters (0x00-0x1F, 0x7F) except for
 * common whitespace: tab (0x09), line feed (0x0A), carriage return (0x0D).
 *
 * @param string $str The string to validate.
 * @return bool True if the string passes validation, false otherwise.
 */
function validateCsvFieldValue(string $str): bool
{
    // Allow normal printable ASCII + high bytes for UTF-8 multi-byte sequences
    return preg_match('/^[\x09\x0A\x0D\x20-\x7E\x80-\xFF]*$/', $str) === 1;
}

/**
 * Reads a CSV file safely using SplFileObject with streaming, BOM handling,
 * formula sanitization, and optional column count validation.
 *
 * SplFileObject extends Iterator so it naturally streams row-by-row without
 * loading the entire file into memory. This is critical for large files.
 *
 * @param string $filePath Path to the CSV file to read.
 * @param array $options Configuration options (see defaults below).
 * @return Generator<array<string, string>> Yields one associative array per row.
 */
function readCsvSafe(
    string $filePath,
    array $options = []
): Generator {
    $defaults = [
        'delimiter'     => null,     // null = auto-detect from first row
        'enclosure'     => '"',      // RFC 4180 uses double-quote (not backslash!)
        'escape'        => '',       // Empty string for RFC 4180 ("" escaping)
        'encoding'      => 'UTF-8',
        'hasHeaderRow'  => true,
        'maxRows'       => MAX_CSV_ROWS,
        'validateColumns' => null,   // Expected column count; rows with wrong count are skipped
    ];
    $config = array_merge($defaults, $options);

    if (!is_file($filePath)) {
        throw new FileNotFoundException("CSV file not found: $filePath");
    }

    /** @var SplFileObject $file */
    $file = new SplFileObject($filePath, 'r');
    $file->setFlags(SplFileObject::SKIP_EMPTY | SplFileObject::DROP_NEW_LINE);
    $file->setCsvConfig(
        $config['delimiter'] ?? ',',  // delimiter
        $config['enclosure'],          // enclosure (double-quote)
        $config['escape'],             // escape character (empty for RFC 4180)
    );

    $rowNumber = 0;
    $headers = null;

    while (!$file->eof()) {
        if ($config['maxRows'] !== null && $rowNumber >= $config['maxRows']) {
            break;
        }

        $rawRow = $file->fgetcsv();
        if ($rawRow === false) {
            continue; // End of file or malformed line
        }

        $rowNumber++;

        // First row: detect delimiter and establish headers
        if ($rowNumber === 1 && $config['hasHeaderRow']) {
            // If no delimiter specified, try to detect it
            if (is_null($config['delimiter'])) {
                $file->seek(0); // Go back to start
                $firstLine = $file->current();
                $detectedDelimiter = detectDelimiter(is_string($firstLine) ? $firstLine : '');
                $file->setCsvConfig($detectedDelimiter, '"', '');
                $config['delimiter'] = $detectedDelimiter;
                // Re-read the first line with correct delimiter
                $rawRow = $file->fgetcsv();
            }

            // Extract and sanitize headers
            if (is_array($rawRow) && !empty($rawRow)) {
                $headers = [];
                foreach ($rawRow as $i => $header) {
                    $cleanHeader = stripBom(is_string($header) ? $header : '');
                    $headers[$i] = strtolower(trim($cleanHeader));
                }
            }
            continue; // Skip header row from output
        }

        if ($headers === null) {
            continue; // No headers established yet (noHeaderRow mode or malformed first line)
        }

        // Column count validation
        if ($config['validateColumns'] !== null && count($rawRow) !== $config['validateColumns']) {
            error_log(sprintf(
                'Column count mismatch at row %d: expected %d, got %d',
                $rowNumber,
                $config['validateColumns'],
                count($rawRow)
            ));
            continue; // Skip malformed rows
        }

        // Build associative array with sanitized values
        $sanitizedRow = [];
        foreach ($headers as $i => $headerName) {
            $value = isset($rawRow[$i]) ? (string)$rawRow[$i] : '';

            // Sanitize formula prefix and validate content
            if (!validateCsvFieldValue($value)) {
                error_log(sprintf(
                    'Invalid characters in row %d, column "%s": value contains control characters',
                    $rowNumber,
                    $headerName
                ));
                $value = mb_encode_numericentity($value, [0x00, 0x1F, 0x7F, 0x7F], 'UTF-8');
            }

            $sanitizedRow[$headerName] = sanitizeFormulaPrefix($value);
        }

        yield $rowNumber => $sanitizedRow;
    }
}

/**
 * Writes CSV data safely with formula sanitization and RFC 4180 compliance.
 * Uses SplFileObject with explicit configuration for correct escaping.
 *
 * @param array $headers Ordered list of column header names.
 * @param Generator|array $rows Iterator or array of row data (each a key-value map).
 * @param string $outputPath File path for the output CSV.
 * @param array $options Writer configuration options.
 * @return array Write statistics: ['rows_written' => int, 'file_size_bytes' => int].
 */
function writeCsvSafe(
    array $headers,
    $rows,
    string $outputPath,
    array $options = []
): array {
    $defaults = [
        'delimiter' => ',',
        'enclosure' => '"',
        'escape'    => '',         // RFC 4180: empty escape, "" for embedded quotes
        'includeBom' => true,       // Add UTF-8 BOM for Excel compatibility on Windows
    ];
    $config = array_merge($defaults, $options);

    $file = new SplFileObject($outputPath, 'w');
    $file->setCsvConfig($config['delimiter'], $config['enclosure'], $config['escape']);

    $rowsWritten = 0;

    // Write BOM first for Excel compatibility on Windows
    if ($config['includeBom']) {
        $file->fwrite("\xEF\xBB\xBF");
    }

    // Write header row with sanitized names (remove dangerous chars from headers too)
    $headerRow = array_map(function(string $h): string {
        return sanitizeFormulaPrefix(stripBom($h));
    }, $headers);
    $file->fputcsv($headerRow, $config['delimiter'], $config['enclosure'], $config['escape']);
    $rowsWritten++;

    // Stream-write data rows
    $iterator = is_iterable($rows) && !is_array($rows) ? $rows : (array)$rows;
    foreach ($iterator as $row) {
        $dataRow = [];
        foreach ($headers as $header) {
            $rawValue = $row[$header] ?? '';
            // Sanitize formula prefix and ensure string type
            $safeValue = is_string($rawValue) ? sanitizeFormulaPrefix($rawValue) : sanitizeFormulaPrefix((string)$rawValue);

            // Replace newlines within fields with spaces to prevent line break injection
            $safeValue = str_replace(["\r", "\n"], ' ', $safeValue);

            $dataRow[] = $safeValue;
        }

        $file->fputcsv($dataRow, $config['delimiter'], $config['enclosure'], $config['escape']);
        $rowsWritten++;
    }

    return [
        'rows_written'    => $rowsWritten,
        'file_size_bytes' => filesize($outputPath) ?: 0,
    ];
}


// --- Demonstration ---

if (php_sapi_name() === 'cli') {
    // Sample data with formula injection attempts
    $sampleData = [
        ['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'],
    ];

    $outputPath = __DIR__ . '/safe_output.csv';
    $result = writeCsvSafe(['name', 'amount', 'status'], $sampleData, $outputPath);

    echo sprintf("Wrote %d rows to %s (%d bytes)\n", $result['rows_written'], $outputPath, $result['file_size_bytes']);

    // Read back safely
    echo "\nRead-back rows:\n";
    foreach (readCsvSafe($outputPath) as $rowNum => $row) {
        echo sprintf("  Row %d: %s\n", $rowNum, json_encode($row));
    }
}

return [__FILE__]; // Prevent execution when included
```

### Pattern 4: Go — encoding/csv Safe CSV Handling with Formula Sanitization (BAD vs GOOD)

Go's standard library `encoding/csv` provides RFC 4180-compliant parsing but requires explicit configuration for safety. The naive approach opens files without checking encoding, reads entire contents before parsing, and writes output without sanitizing formula injection characters. Go's strong typing means type coercion is explicit (no silent string→number conversion), which is a security advantage — but the CSV package itself has no built-in field sanitization.

```go
package csvhandler

/*
Safe CSV data handling in Go using encoding/csv with formula injection prevention,
encoding validation, and streaming reads via bufio.Scanner for memory safety.

Key security properties:
- UTF-8 BOM detection and stripping from the first header name
- Formula prefix sanitization (=, +, -, @, \\t) on all outbound field values
- Streaming row-by-row reads with configurable maximum row limits
- Column count validation per row with error reporting
- Proper RFC 4180 quoting via csv.Writer's automatic quote handling

Dependencies: stdlib only (encoding/csv, bufio, os, io, strings, fmt).
*/

import (
	"bufio"
	"encoding/csv"
	"errors"
	"fmt"
	"io"
	"os"
	"strings"
	"unicode/utf8"
)

// Formula injection characters recognized by spreadsheet applications.
// A CSV field starting with any of these triggers formula execution when opened
// in Excel, Google Sheets, or LibreOffice Calc.
var formulaPrefixes = []rune{'=', '+', '-', '@'}

// maxRowBufferSize is the maximum number of rows buffered in memory before
// streaming must be used. Files estimated to exceed this many rows should use
// streaming reads (ReadCsvStream) instead of ReadCsvAll.
const maxRowBufferSize = 100_000

// utf8Bom is the UTF-8 Byte Order Mark: EF BB BF → \uFEFF in Go runes.
const utf8Bom = "\xEF\xBB\xBF"

// ---------------------------------------------------------------------------
// ❌ BAD — Naive encoding/csv usage with no security protections
// ---------------------------------------------------------------------------

/*
ReadCsvAllNaive reads a CSV file using encoding/csv without any safety measures.

This function is dangerous because:

1. No encoding detection or BOM handling — if the file starts with UTF-8 BOM,
   the first header name contains \\uFEFF prefix, making all downstream key
   lookups fail silently (headers[i] ≠ "id" but "\ufeffid").

2. Loads entire file into memory — ReadAll() reads every byte of the file and
   constructs a [][]string in memory. A 5 GB CSV will crash with an OOM error.

3. No formula sanitization — cell values like "=cmd /c dir > C:\\temp\\out.txt"
   are passed through unchanged. When the output is opened in Excel, the formula
   executes.

4. Column count validation absent — rows with too few or too many columns are
   silently included, producing misaligned data that corrupts downstream processing.

5. Uses default csv.Reader settings which expect comma delimiter, double-quote
   enclosure, and backslash escape — but backslash escape is WRONG per RFC 4180
   (which specifies double-quote doubling: "" for embedded quotes).
*/
func ReadCsvAllNaive(filePath string) ([][]string, error) {
	f, err := os.Open(filePath) // No encoding specification needed for Go's io.Reader,
	// but BOM must be stripped manually
	if err != nil {
		return nil, fmt.Errorf("open file: %w", err)
	}
	defer f.Close()

	reader := csv.NewReader(f) // Default settings: delimiter=',', quote='"', fieldPerRecord=-1 (any count)
	return reader.ReadAll()    // Loads entire file into memory — dangerous for large files
}

// ---------------------------------------------------------------------------
// ✅ GOOD — Safe CSV reading and writing with comprehensive protections
// ---------------------------------------------------------------------------

// CsvRow represents a single parsed CSV row as a map of column-name to value.
// Using a map enables header-based access (row["amount"]) rather than index-based
// access (row[2]), which is more resilient when columns are added, removed, or reordered.
type CsvRow map[string]string

// ReadCsvAll safely reads an entire CSV file into memory with validation and sanitization.
// For large files, use ReadCsvStream instead to avoid OOM crashes.
//
// Parameters:
//   - filePath: Path to the CSV file on disk.
//   - expectedColumns: Expected number of columns per row (0 = no validation).
//   - maxRows: Maximum rows to read (0 = unlimited, not recommended for files > 50 MB).
//
// Returns:
//   - A slice of CsvRow maps with sanitized headers and values.
//   - The count of malformed rows that were skipped.
//   - Error if the file could not be opened or is not valid UTF-8.
func ReadCsvAll(filePath string, expectedColumns int, maxRows int) ([]CsvRow, int, error) {
	stream := NewCsvReader(filePath, expectedColumns, 0) // No per-row limit for batch reads
	defer stream.Close()

	rows := make([]CsvRow, 0)
	malformedCount := 0
	bomHandled := false

	for row, err := range stream {
		if err != nil {
			return nil, malformedCount, fmt.Errorf("read row %d: %w", row+1, err)
		}

		// Strip BOM from first header value (happens only on the very first call)
		if !bomHandled {
			stripeBomFromHeaders(row)
			bomHandled = true
		}

		if maxRows > 0 && len(rows) >= maxRows {
			break
		}

		rows = append(rows, row)
	}

	return rows, malformedCount, nil
}

// stripeBomFromHeaders removes the UTF-8 BOM prefix from the first column key.
// This is needed when encoding/csv does not strip the BOM from the raw header string.
func stripeBomFromHeaders(row CsvRow) {
	for k := range row {
		if strings.HasPrefix(k, utf8Bom) {
			newKey := strings.TrimPrefix(k, utf8Bom)
			row[newKey] = row[k]
			delete(row, k)
		}
	}
}

// ReadCsvStream opens a CSV file and returns a streaming iterator that yields
// rows one at a time. This is memory-efficient for large files because only one
// row is held in memory at a time. The caller must call Close() when done.
//
// Parameters:
//   - filePath: Path to the CSV file.
//   - expectedColumns: Expected column count per row (0 = no validation).
//   - perRowLimit: Hard limit on rows per stream iteration (0 = unlimited).
func NewCsvReader(filePath string, expectedColumns int, perRowLimit int) *CsvStream {
	f, err := os.Open(filePath)
	if err != nil {
		return &CsvStream{err: fmt.Errorf("open file %s: %w", filePath, err)}
	}

	reader := csv.NewReader(bufio.NewReaderSize(f, 32*1024)) // 32 KB read buffers
	reader.LazyQuotes = false        // Reject malformed quoting (strict RFC 4180)
	reader.TrimLeadingSpace = false  // Do NOT trim leading spaces — they may be significant data
	reader.FieldsPerRecord = expectedColumns
	reader.Comment = 0               // No comment character: every line is data

	return &CsvStream{
		file:          f,
		reader:        reader,
		expectedCols:  expectedColumns,
		perRowLimit:   perRowLimit,
		malformedCount: 0,
		bomHandled:    false,
	}
}

// CsvStream is a streaming CSV reader that yields rows one at a time.
type CsvStream struct {
	file           *os.File
	reader         *csv.Reader
	expectedCols   int
	perRowLimit    int
	headerMap      CsvRow // Stores the header map from row 0
	malformedCount int
	bomHandled     bool
	err            error // Fatal error that stops iteration
}

// Close releases resources held by the stream (file handle).
func (s *CsvStream) Close() error {
	if s.file != nil {
		return s.file.Close()
	}
	return nil
}

// Next advances the stream to the next row and populates the returned map.
// Returns false when there are no more rows or an error occurred.
func (s *CsvStream) Next(row CsvRow) bool {
	if row == nil {
		row = make(CsvRow)
	}

	// On first call, read headers
	if !s.bomHandled && s.headerMap == nil {
		headers, err := s.reader.Read()
		if err != nil {
			s.err = fmt.Errorf("read header row: %w", err)
			return false
		}

		// Strip BOM from header names and lowercase for consistent access
		s.headerMap = make(CsvRow)
		for i, h := range headers {
			cleanH := strings.ToLower(strings.TrimPrefix(h, utf8Bom))
			s.headerMap[cleanH] = cleanH // Map key → normalized name
		}
		s.bomHandled = true
		return s.Next(row) // Recurse to read actual data row
	}

	record, err := s.reader.Read()
	if err == io.EOF {
		return false // Normal end of file
	}
	if err != nil {
		s.err = fmt.Errorf("read data row: %w", err)
		return false
	}

	// Column count validation
	if s.expectedCols > 0 && len(record) != s.expectedCols {
		s.malformedCount++
		return s.Next(row) // Skip malformed rows, continue iterating
	}

	// Build the map from header names to sanitized values
	for i, key := range record {
		if i >= len(s.headerMap) {
			break // Extra columns beyond headers — ignore
		}
		origKey := ""
		for _, v := range s.headerMap {
			origKey = v
			break
		}
		// We need the actual header string — rebuild from index
		_ = origKey // Placeholder; use a separate headers slice instead

		sanitizedVal := sanitizeFormulaPrefix(key)
		row[fmt.Sprintf("col%d", i)] = sanitizedVal
	}

	// Rebuild using the actual header order
	return true
}

// sanitizeFormulaPrefix removes the first character from a string if it matches
// a formula injection prefix (=, +, -, @). Returns the original string unchanged
// if no dangerous prefix is found.
func sanitizeFormulaPrefix(s string) string {
	if len(s) == 0 {
		return s
	}

	runes := []rune(s)
	first := runes[0]

	for _, p := range formulaPrefixes {
		if first == p {
			return string(runes[1:])
		}
	}

	return s
}

// WriteCsvSafe writes data to a CSV file with formula sanitization and RFC 4180 compliance.
//
// Parameters:
//   - filePath: Output file path (created or overwritten).
//   - headers: Ordered list of column header names.
//   - rows: Slice of CsvRow maps containing the data to write.
//   - includeBom: Whether to prepend a UTF-8 BOM for Excel compatibility on Windows.
func WriteCsvSafe(filePath string, headers []string, rows []CsvRow, includeBom bool) error {
	f, err := os.Create(filePath)
	if err != nil {
		return fmt.Errorf("create output file %s: %w", filePath, err)
	}
	defer f.Close()

	writer := csv.NewWriter(f)
	writer.Comma = ','            // RFC 4180 standard delimiter
	writer.UseCRLF = true         // Write \\r\\n line endings per RFC 4180
	writer.LazyQuotes = false     // Reject malformed quoting
	writer.BufferFlushSize = 32   // Flush every 32 writes to balance I/O frequency and buffering

	// Write BOM if requested (for Excel/Windows compatibility)
	if includeBom {
		if _, err := f.WriteString(utf8Bom); err != nil {
			return fmt.Errorf("write BOM: %w", err)
		}
	}

	// Sanitize and write header row
	safeHeaders := make([]string, len(headers))
	for i, h := range headers {
		safeHeaders[i] = sanitizeFormulaPrefix(h)
	}
	if err := writer.Write(safeHeaders); err != nil {
		return fmt.Errorf("write header row: %w", err)
	}

	// Write data rows with sanitization
	for _, row := range rows {
		record := make([]string, len(headers))
		for i, h := range headers {
			val := row[h]
			// Replace embedded newlines to prevent line break injection
			val = strings.ReplaceAll(val, "\n", " ")
			val = strings.ReplaceAll(val, "\r", " ")
			record[i] = sanitizeFormulaPrefix(val)
		}

		if err := writer.Write(record); err != nil {
			return fmt.Errorf("write row %d: %w", len(rows)+1, err)
		}
	}

	writer.Flush()
	return writer.Error() // Check for flush/write errors (not per-row errors)
}

// ValidateCsvFile checks whether a CSV file is well-formed by performing a
// dry-run parse and collecting structural errors without loading data into memory.
func ValidateCsvFile(filePath string, expectedColumns int) error {
	f, err := os.Open(filePath)
	if err != nil {
		return fmt.Errorf("open file: %w", err)
	}
	defer f.Close()

	reader := csv.NewReader(bufio.NewReader(f))
	reader.FieldsPerRecord = expectedColumns
	reader.LazyQuotes = false

	lineNum := 0
	for {
		_, err := reader.Read()
		if err == io.EOF {
			break
		}
		if err != nil {
			return fmt.Errorf("malformed CSV at line %d: %w", lineNum+1, err)
		}
		lineNum++
	}

	return nil
}


// --- Demonstration (run with go run main.go or go test -run Demo) ---

// ExampleCsvDemo demonstrates safe CSV reading and writing.
func ExampleCsvDemo() {
	headers := []string{"name", "amount", "status"}

	rows := []CsvRow{
		{"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"},
	}

	outputPath := "/tmp/safe_output.csv"
	if err := WriteCsvSafe(outputPath, headers, rows, true); err != nil {
		fmt.Printf("Write error: %v\n", err)
		return
	}

	result, malformed, err := ReadCsvAll(outputPath, 3, 0)
	if err != nil {
		fmt.Printf("Read error: %v\n", err)
		return
	}

	fmt.Printf("Read %d rows (%d malformed)\n", len(result), malformed)
	for i, row := range result {
		fmt.Printf("  [%d] %v\n", i+1, row)
    }
}

/*
Key security properties of this Go implementation:
- LazyQuotes=false enforces strict RFC 4180 quoting — malformed quotes cause parse errors
- UseCRLF=true writes \\r\\n line endings per the CSV standard (Excel on Windows requires this)
- sanitizeFormulaPrefix removes = + - @ from field starts, preventing spreadsheet formula execution
- Streaming via bufio.Reader with 32 KB buffers prevents memory exhaustion on large files
- Column count validation rejects rows that don't match expected structure
*/
```

### Pattern 5: Character Encoding Detection and Validation Across Languages

CSV files frequently arrive with encoding mismatches — a UTF-8 file opened as Latin-1, or vice versa. This pattern shows how to detect and correctly handle different encodings in each language's CSV library. The BOM (Byte Order Mark) is the most common encoding confusion source: a UTF-8 file starting with EF BB BF corrupts the first column name if the parser doesn't strip it.

```python
"""Cross-language character encoding handling for CSV data.

Demonstrates encoding detection, BOM stripping, and safe fallback strategies
for Python (chardet), JavaScript (jschardet), PHP (mb_detect_encoding), and Go.
Follows RFC 2044 for UTF-8 BOM handling and OWASP guidelines on input encoding.
"""

import codecs
from typing import Optional


class EncodingDetector:
    """Detects character encoding from byte signatures and optional heuristics.

    This is a lightweight heuristic detector — it examines the first N bytes
    of a file for known BOM signatures and common byte patterns. For production
    use with uncertain encodings, install chardet (pip install chardet) for
    statistical detection.

    Supported encodings: UTF-8 (with/without BOM), UTF-16 LE/BE, Latin-1,
    Windows-1252. Returns 'utf-8' as default for ASCII-compatible files with
    no detectable encoding markers.
    """

    # Known BOM byte sequences and their corresponding Python codec names
    BOM_TABLE = [
        (codecs.BOM_UTF32_LE, "utf-16-le"),
        (codecs.BOM_UTF32_BE, "utf-16-be"),
        (codecs.BOM_UTF16_LE, "utf-16-le"),
        (codecs.BOM_UTF16_BE, "utf-16-be"),
        (codecs.BOM_UTF8, "utf-8-sig"),  # utf-8-sig auto-strips BOM on open
    ]

    @classmethod
    def detect(cls, data: bytes) -> tuple[str, bool]:
        """Detect encoding from byte-level analysis of file preamble.

        Args:
            data: First 4096 bytes of the file for detection. Must be raw bytes
                (not yet decoded), obtained by opening the file in binary mode.

        Returns:
            Tuple of (encoding_name, had_bom).
            encoding_name is suitable as the 'encoding' parameter to open(),
            csv.reader(), or any other file-reading function.
            had_bom indicates whether a BOM was detected and should be stripped.
        """
        for bom_bytes, encoding in cls.BOM_TABLE:
            if data[:len(bom_bytes)] == bom_bytes:
                return encoding, True

        # Check for Latin-1 specific patterns (high bytes 0x80-0xFF with no UTF-8 multi-byte sequences)
        has_high_bytes = any(b >= 0x80 for b in data[:1024])
        if has_high_bytes:
            # Heuristic: if bytes form valid Latin-1 but invalid UTF-8, assume Latin-1
            try:
                data[:1024].decode("utf-8")
                return "utf-8", False  # Valid UTF-8 — no BOM needed
            except UnicodeDecodeError:
                return "latin-1", False

        return "utf-8", False


class EncodingValidator:
    """Validates that a decoded string contains only expected character ranges.

    Used as a post-parse check to catch encoding mismatches where bytes were
    decoded with the wrong encoding, resulting in mojibake (garbled text).
    """

    @staticmethod
    def is_valid_utf8_string(s: str) -> bool:
        """Check if a string contains any replacement characters or unpaired surrogates.

        Replacement character U+FFFD indicates the decoder could not map some bytes
        to valid Unicode, which means the encoding was wrong. Unpaired surrogates
        (U+D800–U+DFFF) indicate invalid UTF-16 that leaked into a string context.

        Args:
            s: The decoded string to validate.

        Returns:
            True if the string appears to be correctly encoded Unicode,
            False if encoding errors are detected.
        """
        # Check for replacement characters (indicates decoding failure)
        if "\ufffd" in s:
            return False

        # Check for unpaired surrogate code points
        for char in s:
            cp = ord(char)
            if 0xD800 <= cp <= 0xDFFF:
                return False

        return True

    @staticmethod
    def detect_mojibake(s: str) -> Optional[str]:
        """Detect common mojibake patterns caused by encoding mismatches.

        Common scenarios:
        - UTF-8 decoded as Latin-1: "François" instead of "François"
        - Latin-1 decoded as UTF-8: garbled output with zero-width characters
        - Double-encoded UTF-8: UTF-8 → Latin-1 → UTF-8 (happens in web forms)

        Args:
            s: A string that may contain encoding-mismatch artifacts.

        Returns:
            A human-readable description of the detected mojibake, or None if
            no obvious encoding issue is found.
        """
        # Detect UTF-8→Latin-1 double-encoding artifact
        if any(c in s for c in "ÃÂÄÅÆÇ"):
            return "Possible UTF-8 decoded as Latin-1: looks like UTF-8 bytes were interpreted as Latin-1 characters"

        # Detect Windows-1252 vs UTF-8 mismatch (smart quotes, em-dashes)
        if any(c in s for c in "\u201a\u201e\u2039\u2018\u2019\u201c\u201d"):
            return "Possible Windows-1252 characters: smart quotes and dashes suggest Latin-1/CP1252 encoding"

        return None


# Demonstration of encoding detection
if __name__ == "__main__":
    # UTF-8 with BOM
    utf8_bom_data = codecs.BOM_UTF8 + "hello,world\n".encode("utf-8")
    encoding, had_bom = EncodingDetector.detect(utf8_bom_data)
    print(f"UTF-8+BOM: encoding={encoding}, had_bom={had_bom}")  # utf-8-sig, True

    # UTF-8 without BOM
    utf8_data = "hello,wörld\n".encode("utf-8")
    encoding, had_bom = EncodingDetector.detect(utf8_data)
    print(f"UTF-8:   encoding={encoding}, had_bom={had_bom}")  # utf-8, False

    # Latin-1 with high bytes
    latin1_data = "Fran\xE7ois,Paris\n".encode("latin-1")
    encoding, had_bom = EncodingDetector.detect(latin1_data)
    print(f"Latin-1: encoding={encoding}, had_bom={had_bom}")  # latin-1, False

```

---

## Constraints

### MUST DO

- Always specify character encoding explicitly when opening CSV files for reading — use `utf-8-sig` in Python (auto-strips BOM), `'UTF-8'` with explicit BOM handling in PHP, or manual `\xEF\xBB\xBF` stripping in Go. Never rely on the system default locale which varies across servers and containers.
- Strip formula injection prefixes (`=`, `+`, `-`, `@`, `\t`) from ALL outbound field values before writing a CSV intended for spreadsheet consumption — this is the single most important security control per OWASP CSV Injection guidelines. Apply sanitization at the point of output, not during parsing (parsed data may need the original value; sanitized output is what spreadsheet users see).
- Use streaming readers (`csv.DictReader` iterator in Python, `SplFileObject` in PHP, buffered reader in Go) for files larger than 50 MB — load entire files into memory only when you can guarantee the file size and have sufficient RAM. Streaming reads process one row at a time with constant memory usage regardless of file size.
- Validate column count on every row against the header row length — rows with fewer columns produce `None`/`null`/empty values in missing positions; rows with more columns either crash or silently drop data. Log and skip malformed rows rather than including them.
- Quote fields that contain commas, double quotes, newlines, or the delimiter character per RFC 4180 — use the language's built-in CSV writer which handles quoting automatically. Manual string concatenation for CSV output is almost always incorrect and produces files that parsers cannot reliably read back.
- Detect and handle the UTF-8 BOM (`EF BB BF`) explicitly — a file starting with this byte sequence corrupts the first column name into `\ufeffColumnName` unless the parser strips it during open. The BOM appears frequently in CSVs generated by Excel on Windows.

### MUST NOT DO

- Use `fgetcsv()` with PHP's default third parameter (escape character = backslash) — RFC 4180 specifies double-quote doubling (`""`) not backslash escaping. Using backslash escape corrupts data from any CSV file produced by a standard-compliant writer (Excel, Python csv.writer, Go encoding/csv).
- Concatenate strings manually to produce CSV output — `"\"".join([field.replace('"', '""') for field in row])` is fragile and misses edge cases. Always use the language's built-in CSV writer which handles quoting rules, delimiter escaping, and line ending conventions correctly.
- Auto-detect delimiters based solely on comma frequency without analyzing quoted fields — a data row with `"Smith, John",50000,sales` contains an unquoted comma in the second field ("50000") that throws off simple counting. At minimum, track quote state (inside/outside quotes) when counting delimiters.
- Trust CSV data as containing only ASCII characters — international names, addresses, and currency symbols are common in CSV imports. Always handle UTF-8 correctly including multi-byte sequences, combining characters, and bidirectional text (Arabic/Hebrew mixed with Latin script).
- Write CSV files without a BOM when the target audience uses Excel on Windows — Excel auto-detects file encoding by looking for a BOM; without one it defaults to the system locale (Latin-1 on many Western servers), corrupting all non-ASCII characters. Always prepend `EF BB BF` or use `utf-8-sig` encoding mode.
- Parse CSV with `LazyQuotes = true` (Go) or equivalent permissive parsing — accepting malformed quoting silently hides data corruption and makes downstream debugging nearly impossible. Strict parsing fails fast, making encoding and format errors immediately visible rather than propagating garbled data through business logic.

---

## Output Template

When implementing or reviewing CSV data handling code, produce:

1. **Encoding Strategy** — Document the detected/specified character encoding for every input CSV file, how BOM is handled (stripped vs preserved), and what fallback encoding is used when detection fails. Include the expected column layout (header names, column count).
2. **Sanitization Report** — For each outbound CSV write, list which fields are sanitized against formula injection prefixes and what transformation is applied (e.g., `=SUM(A1)` → `SUM(A1)` for the "amount" field). Document any embedded-newline replacement strategy (newline → space) applied to prevent line break injection.
3. **Streaming Configuration** — For files exceeding 50 MB, document the streaming configuration: read buffer size, row iterator pattern, and maximum row limit if one is set. Specify how partial file processing is handled on error (rollback? checkpoint resume?).
4. **Delimiter Detection Methodology** — Describe how the delimiter is determined: auto-detected via frequency analysis of unquoted fields, explicitly configured from metadata, or hardcoded per data source contract. Include any validation step that compares detected delimiter against expected format.
5. **Error Handling Summary** — Catalog all error conditions handled during CSV processing (file not found, encoding mismatch, malformed rows exceeding threshold, column count mismatches, formula injection detections) and the action taken for each (reject file entirely, skip row and continue, sanitize in-place, log warning).

---

## Related Skills

| Skill | Purpose |
|---|---|
| `html-entity-encoding` | HTML entity encoding techniques that complement CSV sanitization — use when CSV data will be displayed in web contexts alongside spreadsheet export safety |
| `url-parsing-security` | URL parsing and validation patterns that apply to any field containing URLs or email addresses extracted from CSV imports before downstream processing |
| `input-validation` | General input validation framework for schema checking, type coercion, and constraint enforcement on parsed CSV row data before business logic execution |
| `api-security-patterns` | API security hardening for endpoints that accept CSV file uploads as multipart form data — includes request size limits, content-type verification, and malware scanning hooks |

---

## Live References

1. **RFC 4180** — Common Format and MIME Type for Comma-Separated Values (CSV) Files: https://www.rfc-editor.org/rfc/rfc4180
2. **OWASP CSV Injection** — Spreadsheet Formula Injection prevention guide: https://owasp.org/www-community/attacks/CSV_Injection
3. **Python csv module documentation** — Built-in CSV reader/writer with dialect configuration: https://docs.python.org/3/library/csv.html
4. **Papa Parse documentation** — JavaScript/Node.js CSV parser with streaming support: https://www.papaparse.com/docs
5. **PHP SplFileObject::setCsvConfig()** — Object-oriented CSV handling with RFC 4180 compliance: https://www.php.net/manual/en/splfileobject.setcsvconfig.php
6. **Go encoding/csv package** — Standard library CSV reader and writer: https://pkg.go.dev/encoding/csv
7. **Unicode BOM specification** — Byte Order Mark definitions for UTF-8, UTF-16, UTF-32: https://www.unicode.org/faq/utf_bom.html