Escaping for CSV

Question

I need to store a string in a MySQL database. The values will later be used in a CSV. How do I escape the string so that it is CSV-safe? I assume I need to escape the following: comma, single quote, double quote.

PHP's addslashes function does:

single quote ('), double quote ("), backslash () and NUL (the NULL byte).

So that won't work. Suggestions? I'd rather not try to create some sort of regex solution.

Also, I need to be able to unescape.

score 41 · Accepted Answer · answered Jun 13 '11 at 00:21

41

Use fputcsv() to write, and fgetcsv() to read.

answered Jun 13 '11 at 00:21

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

1

do those support encoding of newlines and double quotes? – hakre Jun 13 '11 at 01:01
12

I dont want to write it to a file :( – NDM Jan 13 '16 at 12:08
2

@NDM - you don't have to. see: http://stackoverflow.com/a/13474770/702420 – dave Dec 01 '16 at 20:00
IF you use `;` as a delimiter with `fputcsv()` and you have a semicolon and quotation marks in your string, it breaks. Probably related to https://github.com/ajgarlag/AjglCsvRfc – Avatar Mar 08 '22 at 06:32

Francesco Casula · Answer 2 · 2017-05-02T15:11:29.097

24

fputcsv() is not always necessary especially if you don't need to write any file but you want to return the CSV as an HTTP response. All you need to do is to double quote each value and to escape double quote characters repeating a double quote each time you find one.

Here a few examples:

hello -> "hello"
this is my "quote" -> "this is my ""quote"""
catch 'em all -> "catch 'em all"

As you can see the single quote character doesn't need any escaping.

Follows a full working example:

<?php

$arrayToCsvLine = function(array $values) {
    $line = '';

    $values = array_map(function ($v) {
        return '"' . str_replace('"', '""', $v) . '"';
    }, $values);

    $line .= implode(',', $values);

    return $line;
};

$csv = [];
$csv[] = $arrayToCsvLine(["hello", 'this is my "quote"', "catch 'em all"]);
$csv[] = $arrayToCsvLine(["hello", 'this is my "quote"', "catch 'em all"]);
$csv[] = $arrayToCsvLine(["hello", 'this is my "quote"', "catch 'em all"]);

$csv = implode("\r\n", $csv);

If you get an error is just because you're using an old version of PHP. Fix it by declaring the arrays with their old syntax and replacing the lambda function with a classic one.

edited May 02 '17 at 15:11

answered May 22 '14 at 13:09

Francesco Casula

26,184
15
132
131

4

You can still use `fputcsv` if you make your file handle like this: `$fh = @fopen( 'php://output', 'w' );` and add the necessary headers. [source](http://ran.ge/2009/10/27/howto-create-stream-csv-php/) – Agi Hammerthief Dec 18 '14 at 13:30
Yep, writing to a stream is for sure another option. – Francesco Casula Dec 19 '14 at 09:55
1

This was the first answer of many that actually provided a raw solution without pointing out the function "fgetscsv" and "fputcsv". Thank you! – karns Jun 12 '15 at 15:38
1

This breaks when there are newlines in the string. – Jimbali Mar 08 '16 at 14:28
3

RFC 4180 says you should use CRLF as line break. Translated to PHP, that is `\r\n`. https://tools.ietf.org/html/rfc4180 – Slava May 02 '17 at 11:13
Was looking into escaping csv output during iteration. For this example, to reduce the function calls, [`str_replace()`](http://php.net/manual/en/function.str-replace.php) also accepts an array of strings, allowing you to move the double quoted value wrapping into `implode` Like so https://3v4l.org/YWTI4 `$line .= '"' . implode('","', str_replace('"', '""', $values)) . '"' . "\r\n";` – Will B. Jan 11 '19 at 18:43
It should be noted that PHP did not support anonymous functions until version 5.3. So, if you are using an earlier version you will need to create a function in the traditional way. – RationalRabbit May 23 '19 at 05:51

score 23 · Answer 3 · answered Jun 29 '15 at 14:28

For those of you trying to sanitise data using PHP and output as a CSV this can be done using PHP's fputcsv() function without having to write to a file as such:

<?php
// An example PHP array holding data to be put into CSV format
$data = [];
$data[] = ['row1_val1', 'row1_val2', 'row1_val3'];
$data[] = ['row2_val1', 'row2_val2', 'row2_val3'];

// Write to memory (unless buffer exceeds 2mb when it will write to /tmp)
$fp = fopen('php://temp', 'w+');
foreach ($data as $fields) {
    // Add row to CSV buffer
    fputcsv($fp, $fields);
}
rewind($fp); // Set the pointer back to the start
$csv_contents = stream_get_contents($fp); // Fetch the contents of our CSV
fclose($fp); // Close our pointer and free up memory and /tmp space

// Handle/Output your final sanitised CSV contents
echo $csv_contents;

This worked great! I sent csv headers and output using this to create dynamic csv files for Drupal Feeds importer. — LoveFineArt, Jan 13 '18 at 01:09

score 18 · Answer 4 · answered Jun 13 '11 at 00:21

18

Don't store the data CSV escaped in the database. Escape it when you export to CSV using fputcsv. If you're storing it CSV escaped you're essentially storing garbage for all purposes other than CSV exporting.

answered Jun 13 '11 at 00:21

deceze

510,633
85
743
889

1

I agree. I would rather take a small performance hit (of escaping the data several times going out) for the sake of having correctly stored data. – Magmatic Jun 03 '15 at 20:22

Escaping for CSV

4 Answers4

Linked

Related