Bug: Report export doesn't escape double quotes in values and truncates values with two adjacent double quotes

mattp · 12 October 2021 12:37

In some circumstances, Lead Source is free text, in other cases it includes some JSON.
In the UI the JSON renders okay.

However when exporting the report (CSV) it seems the special characters break the export…in particular two double quotes in a row in the json (empty string) seems to terminate the exported line. JSON that doesn’t have any empty string values ("") exports to the CSV fine.

Extract of a few sample lines from the exported CSV.

"Date Created";"Status";"Referrer";"UTM Campaign";"UTM Medium";"UTM Source";"Lead Source";"Lead Source Description"
"2021-09-13";"";"https://www.google.de";"";"";"";"Website Contact Form";"{"referrer":""
"2021-09-23";"Kein Interesse";"http://m.facebook.com";"agency_name";"facebook";"paid_social";"Website Contact Form";"{"utm_source":"paid_social","utm_medium":"facebook","utm_campaign":"agency_test","utm_content":"feed_story","referrer":""
"2021-10-06";"Kein Interesse";"";"";"";"";"Website Contact Form";"{"consent-necessary":"true","consent-preferences":"true","consent-statistics":"true","consent-marketing":"true"}"

I am guessing this bug is related to quote escaping logic.

RFC-4180
If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.

You can see the CSV is using double-quote char around each field…so any double quotes in the values need to be escaped…which is not happening if you look at the above sample output.

value {"referrer":""} when quoted as a CSV export value should be "{""referrer"":""""}".

I personally prefer using a \t separator for CSV as excel recognizes this pretty universally whereas , vs ; is regional and are more likely to be part of a value (and need escaping) whereas storing a tab character in a field is less likely and can be easily stripped or escaped.

I’m going to go look for the export code to see if I can fix this and get unstuck.

mattp · 12 October 2021 15:09

While poking around the code I noticed there was some changes to the CSV stuff since .11.20 so I updated to .11.22 and did a QR&R. Now export yields a 500 error.

PHP message: PHP Notice:  Uninitialized string offset: 0 in /opt/apps/suitecrm/include/CleanCSV.php on line 100
PHP message: PHP Notice:  Uninitialized string offset: 0 in /opt/apps/suitecrm/include/CleanCSV.php on line 100

Seems that there isn’t an empty string check here:

github.com

salesagility/SuiteCRM/blob/v7.11.22/include/CleanCSV.php#L100

    
      
              /**
               * @param string $cell
               * @return string
               */
              public function escapeField($cell)
              {
                  if (!is_string($cell)) {
                      return $cell;
                  }
          
          
        if (in_array($cell[0], $this->startingChars, true)) {
                      return $this->escapeChar . $cell;
                  }
          
          
        return $cell;
              }
          }

Results in the server-side error for empty fields passed to escapeField.

mattp · 13 October 2021 09:46

Okay, inserted some debug logging in a couple spots.

Inserted this…

$GLOBALS['log']->fatal($name . '=' . $row[$name]);

…here

And inserted this…

$GLOBALS['log']->fatal('raw: '. $t);
$GLOBALS['log']->fatal('strip_tags: '. strip_tags($t));
$GLOBALS['log']->fatal('trim: '. trim(strip_tags($t)));

…here

Got the following output…

...[FATAL] Lead_Source_Description3={"referrer":"https://www.google.com/"}
...[FATAL] raw:
<span class="sugar_field" id="lead_source_description">{"referrer":" <a href="https://www.google.com/"}" target="_new"  style="font-weight: normal;">https://www.google.com/"}</a></span>
...[FATAL] strip_tags:
{"referrer":"
...[FATAL] trim: {"referrer":"

The interesting bit is that $t is <span class="sugar_field" id="lead_source_description">{"referrer":" <a href="https://www.google.com/"}" target="_new" style="font-weight: normal;">https://www.google.com/"}</a></span>. So the export code is trying to render the field value as an html tag (incorrectly due to dumb “smart linking” it seems) and then converting it back to the raw/plain-text value for CSV which causes the value to be mangled by strip_tags (only {"referrer":" is left after strip_tags).

So I originally thought this issue was about double double-quotes or special characters…but it is really just about exporting values that have things that look like urls in them and the combination of trying to convert urls to html and then running strip_tags on the result instead of exporting the raw value to CSV. There is a separate issue that double quotes are not escaped in the export.

Yikes.

This is a large complex function with a lot of conditions (meaning it is doing a bunch of different things) so I will probably not bother trying to fix this and will just use mysql queries to export the CSV.

But just out of curiosity…if I was inclined to patch this is modules/AOR_Reports/AOR_Report.php a file that can be overridden/patched via custom/ to be upgrade safe?