fromArray method write cell failure because of xEFxBFxBE character(s) existence in string

[donnyreza]

This is:

- [X ] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

$sheet->fromArray success writing cells

What is the current behavior?

The existence of xEFxBFxBE character(s) in a string affected fromArray method failed to write not only single cell, but all cells that have string in it. It does not affects cell with number.

String example:
LLC “CPC “Business Perspectives�

What features do you think are causing the issue

• Reader
• [X ] Writer
• Styles
• Data Validations
• Formula Calulations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

xlsx

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.19
PHP 7.4

[MarkBaker]

That looks like an invalid UTF-8 string; what wold you expect to be stored in the cell if you set it's value to something that isn't valid utf-8?

[MarkBaker]

Prior to PHP 5.4, the iconv() function simply removed any invalid character sequences from a string; but a change in PHP 5,4 meant that it now drops the complete string, if the input encoding is invalid UTF-8. PhpSpreadsheet expects any string value that you set in a cell to be valid UTF-8.

So you should ensure that your string values are valid before setting them in a cell

Filtering your strings through the following function:

function valid_utf8_bytes($str)
{
    $return = '';
    $length = strlen($str);
    $invalid = array_flip(array("xEFxBFxBF" /* U-FFFF */, "xEFxBFxBE" /* U-FFFE */));

    for ($i=0; $i < $length; $i++)
    {
        $c = ord($str[$o=$i]);

        if ($c < 0x80) $n=0; # 0bbbbbbb
        elseif (($c & 0xE0) === 0xC0) $n=1; # 110bbbbb
        elseif (($c & 0xF0) === 0xE0) $n=2; # 1110bbbb
        elseif (($c & 0xF8) === 0xF0) $n=3; # 11110bbb
        elseif (($c & 0xFC) === 0xF8) $n=4; # 111110bb
        else continue; # Does not match

        for ($j=++$n; --$j;) # n bytes matching 10bbbbbb follow ?
            if ((++$i === $length) || ((ord($str[$i]) & 0xC0) != 0x80))
                continue 2
        ;

        $match = substr($str, $o, $n);

        if ($n === 3 && isset($invalid[$match])) # test invalid sequences
            continue;

        $return .= $match;
    }
    return $return;
}

before setting them in a cell should filter out any invalid UTF-8 character sequences, leaving only valid UTF-8 characters, so that when you set that value in a cell it will be valid and won't be dropped

[donnyreza]

A brief history about the case. I'm working in a project that use MySQL. The string I shared as an example coming from the database. Later I know that the data converted from latin_* collation to utf8.

So, when I try adding a feature to export the data to Excel, this happened: the excel file contains only data with number, but no single string at all. Not only in a sheet, but in entire spreadsheet, since I generated an excel file with multiple sheet.

After debugging process, i learned that the trouble coming from strings with those characters. The fact that all strings disappears in the result file, and I was not aware that the characters exists, make debugging process a bit tricky since I need to chunking the data and recreate the process many times, until I find the problematic chunk or single data.

So, while we don't want to store those characters in the cell, and surely we could validating the data before processing, it would be nice and better if we have a feature to make sure that only cells with problematic characters that not shown in the result, and all others good data still appears in the result file.

[MarkBaker]

We specifically request that string data should be valid UTF-8, and then PhpSpreadsheet uses a very simply validation function to verify that the string is valid utf-8:

$textValue = @iconv('UTF-8', 'UTF-8', $textValue);

If the string contains all valid characters, then it returns the string; if there are any invalid characters, then it returns an empty string. That is validation.
That means the responsibility is on you as an end-user developer to ensure that you provide valid UTF-8

Yes, it might be a better approach if we discarded invalid characters and returned a string containing only valid characters instead of returning an empty string... using the valid_utf8_bytes() code I suggested previously would do that... but that comes with a performance overhead that I'm not prepared to add to the library.

[oleibman]

I'm not sure exactly what's going wrong here, but Php/iconv is giving unexpected results:

$invalid_utf8_string = "\xef\xbf\xbe\xef\xbf\xbf"; // illegal FFFE and FFFF character
var_dump(iconv('UTF-8', 'UTF-8', $invalid_utf8_string));
var_dump(mb_convert_encoding($invalid_utf8_string, 'UTF-8', 'UTF-8'));
var_dump(preg_match('//u', $invalid_utf8_string)); 
var_dump(UConverter::transcode($invalid_utf8_string, 'UTF-8', 'UTF-8')); 

Those are 4 methods I would expect to detect this as an invalid UTF8 string, but all 4 of them treat it as valid in all of Php 7.4, 8.0, 8.1, and 8.2alpha. I do see code in Mark's routine which will fail those values. Based on this dreadful result, it might make sense to do a str_replace for those 2 illegal characters (deleting them or replacing them with the Unicode substitution character). Uconverter::transcode will (I hope) automatically use the substitution character in place of any illegal characters except for the 2 special cases, which I think is a better treatment than dropping the characters. The replacement of the 2 undetected characters can happen before or after iconv (or transcode or mb_convert_encoding) ... or just use Mark's routine in place of all of it. Possibly you can add a setting to enable this treatment if you think the overhead will be too high.

[MarkBaker]

Likewise with the variants:

var_dump(iconv('UTF-8', 'UTF-8//IGNORE', $invalid_utf8_string));
var_dump(iconv('UTF-8', 'UTF-8//TRANSLIT', $invalid_utf8_string));

There's also an mb_* ini setting (that I can't remember at the moment) that determines how to handle invalid characters for mb_convert_encoding(), but changing ini settings isn't a good idea, and I don't know if it would even work or not

EDIT

mb_substitute_character() - Specifies a substitution character when input character encoding is invalid or character code does not exist in output character encoding. Invalid characters may be substituted "none" (no output), string or int value (Unicode character code value).