How to check formula injection in a csv export

Scan every exported field and flag any that begins with =, +, - or @, counting a leading tab, CR or LF as a skip. Here node scan.mjs injection.csv flagged 9 fields, and LibreOffice Calc displayed 2 in the cell whose bytes on disk were =1+1.

Why check this

Run this at review of any export feature, and in regression after a change to the writer.

A CSV file is text and a spreadsheet is an interpreter. The same bytes are inert in one reader and executed by the other, and nothing marks which cells the second will run.

The failure it prevents: a note a customer typed into a support form is exported for the finance team, and the cell they read shows a computed number instead of the text stored.

Prerequisites

• Node 22 for the three programs below. No dependencies.
• LibreOffice headless as the reader: LibreOffice Calc 24.2.3.2 under a Ukrainian UI locale, LCID 1058, which sets the argument separator and the error text.
• Port 8933 free, and curl. Stop the service by the PID netstat -ano reports, never by name.
• The trigger set is the one OWASP's CSV Injection page names: =, +, -, @, tab 0x09, CR 0x0D, LF 0x0A.
• Keep every payload arithmetic. =1+1 demonstrates the class.
• Quoting has its own page: How to escape quotes in CSV.

make.mjs writes three fixtures. Every tab, CR and quote is built at runtime, because an escape typed into a tool argument can arrive decoded.

import { writeFileSync } from 'node:fs';

const TAB = String.fromCharCode(9);
const CR = String.fromCharCode(13);
const LF = String.fromCharCode(10);
const SP = String.fromCharCode(32);
const DQ = String.fromCharCode(34);
const AP = String.fromCharCode(39);
const q = (s) => DQ + s + DQ;

const injection = [
  'id,case,value',
  '1,equals,=1+1',
  '2,plus,+1+1',
  '3,minus,-1+1',
  '4,at,@1+1',
  '5,tab first,' + q(TAB + '=1+1'),
  '6,cr first,' + q(CR + '=1+1'),
  '7,quoted,' + q('=1+1'),
  '8,string result,' + q('=CONCATENATE(' + q(q('a')) + ';' + q(q('b')) + ')'),
  '9,comma args,' + q('=CONCATENATE(' + q(q('a')) + ',' + q(q('b')) + ')'),
];

const split = [
  'id,comment',
  '1,see note,=1+1',
  '2,' + q('see note,=1+1'),
];

const escapes = [
  'id,escape,value',
  '1,none,=1+1',
  '2,quoted,' + q('=1+1'),
  '3,apostrophe,' + AP + '=1+1',
  '4,space,' + SP + '=1+1',
  '5,tab,' + q(TAB + '=1+1'),
  '6,stripped,1+1',
];

for (const [name, lines] of [['injection.csv', injection], ['split.csv', split], ['escapes.csv', escapes]]) {
  const text = lines.join(LF) + LF;
  writeFileSync(name, text, 'utf8');
  console.log(name.padEnd(14) + 'rows ' + (lines.length - 1) + '  bytes ' + Buffer.byteLength(text, 'utf8'));
}

scan.mjs is the detection pass.

import { readFileSync } from 'node:fs';

const DQ = String.fromCharCode(34);
const CR = String.fromCharCode(13);
const LF = String.fromCharCode(10);
const TAB = String.fromCharCode(9);
const SP = String.fromCharCode(32);

const TRIGGER = ['=', '+', '-', '@'];   // leading characters a scan flags
const BLANK = [TAB, CR, LF, SP];        // skipped before the trigger is read

// RFC 4180 reader. Returns rows of fields with the enclosing quotes removed.
function parse(text) {
  const rows = [];
  let row = [];
  let field = '';
  let quoted = false;
  let i = 0;
  const endRow = () => { row.push(field); rows.push(row); row = []; field = ''; };
  while (i < text.length) {
    const c = text[i];
    if (quoted) {
      if (c === DQ && text[i + 1] === DQ) { field += DQ; i += 2; continue; }
      if (c === DQ) { quoted = false; i += 1; continue; }
      field += c; i += 1; continue;
    }
    if (c === DQ && field === '') { quoted = true; i += 1; continue; }
    if (c === ',') { row.push(field); field = ''; i += 1; continue; }
    if (c === CR && text[i + 1] === LF) { endRow(); i += 2; continue; }
    if (c === LF || c === CR) { endRow(); i += 1; continue; }
    field += c; i += 1;
  }
  if (field !== '' || row.length > 0) endRow();
  return rows;
}

// Position of the trigger, or null. Leading blanks do not make a field safe.
function risk(value) {
  let i = 0;
  while (i < value.length && BLANK.includes(value[i])) i += 1;
  if (i >= value.length || !TRIGGER.includes(value[i])) return null;
  return i;
}

const cp = (s, n) => [...s].slice(0, n)
  .map((c) => 'U+' + c.codePointAt(0).toString(16).toUpperCase().padStart(4, '0')).join(' ');
const seen = (s) => s.split(TAB).join('[TAB]').split(CR).join('[CR]').split(LF).join('[LF]');

const file = process.argv[2];
const mode = process.argv[3] ?? 'report';
const rows = parse(readFileSync(file, 'utf8'));
const header = rows[0] ?? [];
let flagged = 0;

for (const [r, row] of rows.entries()) {
  for (const [c, value] of row.entries()) {
    const at = risk(value);
    if (mode === 'fields') {
      console.log('row ' + (r + 1) + '  col ' + (c + 1) + '  len ' + String(value.length).padStart(2)
        + '  ' + seen(value).padEnd(14) + cp(value, 3).padEnd(22) + (at === null ? '-' : 'FLAG'));
      continue;
    }
    if (r === 0 || at === null) continue;
    flagged += 1;
    console.log('row ' + (r + 1) + '  col ' + (c + 1) + ' (' + (header[c] ?? '?') + ')  '
      + cp(value, at + 1) + '  blanks before the trigger ' + at);
  }
}
if (mode !== 'fields') console.log(file + '  rows ' + rows.length + '  fields flagged ' + flagged);

cells.mjs reads the displayed string and the stored formula out of the HTML.

import { readFileSync } from 'node:fs';

const html = readFileSync(process.argv[2], 'utf8');
const un = (s) => s.split('&quot;').join(String.fromCharCode(34)).split('&lt;').join('<').split('&gt;').join('>').split('&amp;').join('&');
const rows = html.split('<tr>').slice(1);

for (const [r, row] of rows.entries()) {
  console.log('row ' + (r + 1));
  for (const m of row.matchAll(/<td([^>]*)>([^]*?)<[/]td>/g)) {
    const attrs = m[1];
    const text = un(m[2].split('<br>').join('[BR]')).split(String.fromCharCode(9)).join('[TAB]');
    const f = /data-sheets-formula="([^"]*)"/.exec(attrs);
    console.log(('  cell: ' + text.padEnd(16) + (f ? 'formula ' + un(f[1]) : '')).trimEnd());
  }
}

server.mjs is the target for steps 8 to 10.

import { createServer } from 'node:http';

const DQ = String.fromCharCode(34);
const LF = String.fromCharCode(10);
const notes = [];

// A writer that quotes correctly and does nothing else. RFC 4180 and no more.
const field = (v) => (/[,"]/.test(v) ? DQ + v.split(DQ).join(DQ + DQ) + DQ : v);

createServer((req, res) => {
  if (req.method === 'POST' && req.url === '/notes') {
    let body = '';
    req.on('data', (d) => { body += d; });
    req.on('end', () => {
      const text = new URLSearchParams(body).get('text') ?? '';
      notes.push(text);
      res.writeHead(201, { 'content-type': 'text/plain' });
      res.end('stored id ' + notes.length + ' len ' + text.length + LF);
    });
    return;
  }
  if (req.method === 'GET' && req.url === '/export.csv') {
    const rows = ['id,text', ...notes.map((t, i) => (i + 1) + ',' + field(t))];
    res.writeHead(200, { 'content-type': 'text/csv' });
    res.end(rows.join(LF) + LF);
    return;
  }
  res.writeHead(404).end();
}).listen(8933, '127.0.0.1', () => console.log('notes service on 127.0.0.1:8933'));

Export SOFFICE.

export SOFFICE="/c/Program Files/LibreOffice/program/soffice.com"

Steps

1. 1

   Step 1.

   Write the three fixtures.

   node make.mjs
   

   injection.csv rows 9  bytes 203
   split.csv     rows 2  bytes 45
   escapes.csv   rows 6  bytes 106

   Everything below reads these 354 bytes twice, as text and as a sheet.

2. 2

   Step 2.

   Read what the first file holds.

   cat -A injection.csv
   

   id,case,value$
   1,equals,=1+1$
   2,plus,+1+1$
   3,minus,-1+1$
   4,at,@1+1$
   5,tab first,"^I=1+1"$
   6,cr first,"^M=1+1"$
   7,quoted,"=1+1"$
   8,string result,"=CONCATENATE(""a"";""b"")"$
   9,comma args,"=CONCATENATE(""a"",""b"")"$

   ^I is the tab in row 5 and ^M the carriage return in row 6, both inside the quoted field in front of the equals sign.

3. 3

   Step 3.

   Convert the same bytes to a sheet.

   "$SOFFICE" --headless --convert-to html --outdir calc injection.csv split.csv escapes.csv >/dev/null && node cells.mjs calc/injection.html
   

   row 1
   cell: id
   cell: case
   cell: value
   row 2
   cell: 1
   cell: equals
   cell: 2               formula =1+1
   row 3
   cell: 2
   cell: plus
   cell: +1+1
   row 4
   cell: 3
   cell: minus
   cell: -1+1
   row 5
   cell: 4
   cell: at
   cell: @1+1
   row 6
   cell: 5
   cell: tab first
   cell: [TAB]=1+1
   row 7
   cell: 6
   cell: cr first
   cell: [BR]=1+1
   row 8
   cell: 7
   cell: quoted
   cell: 2               formula =1+1
   row 9
   cell: 8
   cell: string result
   cell: ab
   row 10
   cell: 9
   cell: comma args
   cell: Помилка:501     formula =CONCATENATE("a","b")

   Row 2 is the subject: the file holds =1+1, the cell shows 2. Row 8 shows quoting changed nothing. Row 9 evaluated CONCATENATE to ab and recorded no formula; row 10 used a comma between arguments and answered Помилка:501. On this reader +, -, @, the tab and the CR stayed text.

4. 4

   Step 4.

   Read the delimiter fixture.

   node cells.mjs calc/split.html
   

   row 1
   cell: id
   cell: comment
   cell: [BR]
   row 2
   cell: 1
   cell: see note
   cell: 2               formula =1+1
   row 3
   cell: 2
   cell: see note,=1+1
   cell: [BR]

   Both rows store one value. Row 2, written unquoted, split at the comma and the tail became a formula cell. Row 3 was quoted and stays one cell.

5. 5

   Step 5.

   Display the five treatments of one payload.

   node cells.mjs calc/escapes.html
   

   row 1
   cell: id
   cell: escape
   cell: value
   row 2
   cell: 1
   cell: none
   cell: 2               formula =1+1
   row 3
   cell: 2
   cell: quoted
   cell: 2               formula =1+1
   row 4
   cell: 3
   cell: apostrophe
   cell: '=1+1
   row 5
   cell: 4
   cell: space
   cell:  =1+1
   row 6
   cell: 5
   cell: tab
   cell: [TAB]=1+1
   row 7
   cell: 6
   cell: stripped
   cell: 1+1

   Four stopped the evaluation, quoting did not. The apostrophe, space and tab stay in the displayed string, visible to whoever reads the sheet.

6. 6

   Step 6.

   Read the same five back with a parser.

   node scan.mjs escapes.csv fields
   

   row 1  col 1  len  2  id            U+0069 U+0064         -
   row 1  col 2  len  6  escape        U+0065 U+0073 U+0063  -
   row 1  col 3  len  5  value         U+0076 U+0061 U+006C  -
   row 2  col 1  len  1  1             U+0031                -
   row 2  col 2  len  4  none          U+006E U+006F U+006E  -
   row 2  col 3  len  4  =1+1          U+003D U+0031 U+002B  FLAG
   row 3  col 1  len  1  2             U+0032                -
   row 3  col 2  len  6  quoted        U+0071 U+0075 U+006F  -
   row 3  col 3  len  4  =1+1          U+003D U+0031 U+002B  FLAG
   row 4  col 1  len  1  3             U+0033                -
   row 4  col 2  len 10  apostrophe    U+0061 U+0070 U+006F  -
   row 4  col 3  len  5  '=1+1         U+0027 U+003D U+0031  -
   row 5  col 1  len  1  4             U+0034                -
   row 5  col 2  len  5  space         U+0073 U+0070 U+0061  -
   row 5  col 3  len  5   =1+1         U+0020 U+003D U+0031  FLAG
   row 6  col 1  len  1  5             U+0035                -
   row 6  col 2  len  3  tab           U+0074 U+0061 U+0062  -
   row 6  col 3  len  5  [TAB]=1+1     U+0009 U+003D U+0031  FLAG
   row 7  col 1  len  1  6             U+0036                -
   row 7  col 2  len  8  stripped      U+0073 U+0074 U+0072  -
   row 7  col 3  len  3  1+1           U+0031 U+002B U+0031  -

   This is the cost. The apostrophe field returns 5 characters beginning U+0027, so anything re-importing this export carries an apostrophe.

7. 7

   Step 7.

   Run the detection pass over the fixture.

   node scan.mjs injection.csv
   

   row 2  col 3 (value)  U+003D  blanks before the trigger 0
   row 3  col 3 (value)  U+002B  blanks before the trigger 0
   row 4  col 3 (value)  U+002D  blanks before the trigger 0
   row 5  col 3 (value)  U+0040  blanks before the trigger 0
   row 6  col 3 (value)  U+0009 U+003D  blanks before the trigger 1
   row 7  col 3 (value)  U+000D U+003D  blanks before the trigger 1
   row 8  col 3 (value)  U+003D  blanks before the trigger 0
   row 9  col 3 (value)  U+003D  blanks before the trigger 0
   row 10  col 3 (value)  U+003D  blanks before the trigger 0
   injection.csv  rows 10  fields flagged 9

   Nine of the twenty seven fields below the header are named with row, column and triggering code points. Rows 6 and 7 are what a first character check misses.

8. 8

   Step 8.

   Start the local service and store two notes through its form field.

   node server.mjs & sleep 2
   curl -s -X POST http://127.0.0.1:8933/notes --data-urlencode 'text=delivered on time'
   curl -s -X POST http://127.0.0.1:8933/notes --data-urlencode 'text==1+1'
   

   notes service on 127.0.0.1:8933
   stored id 1 len 17
   stored id 2 len 4

   Both were accepted. The reported length, 4 characters, is the only sign anything unusual happened.

9. 9

   Step 9.

   Download the export and scan it.

   curl -s http://127.0.0.1:8933/export.csv -o export.csv && cat -A export.csv && node scan.mjs export.csv
   

   id,text$
   1,delivered on time$
   2,=1+1$
   row 3  col 2 (text)  U+003D  blanks before the trigger 0
   export.csv  rows 3  fields flagged 1

   The writer quoted correctly; nothing in the value needed quoting. The scan still names row 3, column text, and one flagged field fails the export.

10. 10

    Step 10.

    Open the export the way its reader will.

    "$SOFFICE" --headless --convert-to html --outdir calc export.csv >/dev/null && node cells.mjs calc/export.html
    

    row 1
    cell: id
    cell: text
    row 2
    cell: 1
    cell: delivered on time
    row 3
    cell: 2
    cell: 2               formula =1+1

    A value that entered through an ordinary form field, through a correct writer, is a formula in the sheet. The export is where it surfaced, the input is where it entered.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | A cell showing a number where the file holds text | The reader evaluated the field. Step 3 row 2 is the reference case. | Treat the export as defective. Escape or reject the field at the writer. | | formula printed beside a cell by cells.mjs | Calc kept the source expression in data-sheets-formula | Read the attribute to recover what was stored. Step 3 row 10 shows it beside an error string. | | A cell displaying text with no formula beside it, where the file held an expression | The formula returned text, so the HTML export records only the result | Step 3 row 9 is that case. Scan the CSV, not the converted sheet. | | One row with more cells than the header names | A delimiter inside an unquoted value split it. Step 4 row 2. | Fix the quoting first. The formula is the second defect on that row. | | blanks before the trigger greater than 0 | A tab, CR, LF or space sits in front of the trigger character | Keep it flagged. The reader skips those before deciding. | | A parsed field one character longer than the value you stored | An escape was prefixed and is now part of the data | Decide whether the consumers of this export can accept it. Step 6 measures the difference. | | fields flagged 0 on your own export | Nothing in the file begins with a trigger | Assert on the number in CI so a later change has to move it. |

Common mistakes

Thresholds

What to check next

• How to escape quotes in CSV: the framing rule step 4 depends on.
• How to check the delimiter of a csv file: the separator decides where a value splits.
• How to test csv import validation: where the input half is rejected.
• How to check leading zeros in a csv export: another value a sheet rewrites on open.
• Csv scientific notation: the same contrast, for numbers.

FAQ

What is csv injection?

A stored text value that a spreadsheet treats as a formula when the export is opened. The service returns the characters it was given; the reader decides they are code.

How do you prevent csv injection?

Strip the leading character at the writer, or reject the value at input. Step 6 measures the prefixes: apostrophe, space and tab each stop the evaluation and each add a character to what a parser reads.

Does quoting a field prevent csv injection?

No. Step 3 row 8 and step 5 row 3 are quoted and both display 2. Quoting tells the CSV reader where a field ends, nothing more.

Which spreadsheet produced these results?

LibreOffice Calc 24.2.3.2 headless under a Ukrainian UI locale, LCID 1058, named in every block above. That locale is why row 10 reads Помилка:501. Run the fixtures through your users' reader.

Verified