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.

Answer 1

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

Answer 2

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.