Bug: XLSX writer does not XML-escape font names and hyperlink targets

[tatamata]

Hello Werner,

I found two XML escaping bugs in the XLSX writer (xlsxooxml.pas) that produce invalid OOXML files, causing openpyxl/lxml to fail with XMLSyntaxError: attributes construct error.

Bug #1: Unescaped quotes in font names

In TsSpreadOOXMLWriter.WriteFont (line ~5783), AFont.FontName is inserted into an XML attribute without escaping:

Code: [Select]

if AFont.FontName <> '' then
  s := s + Format('<%s val="%s" />', [NAME_TAG[UseInStyleNode], AFont.FontName]);

If the font name contains a double quote, the XML is malformed:

Code: [Select]

<!-- Produced (invalid): -->
<name val=""Google Sans"" />

<!-- Expected: -->
<name val="&quot;Google Sans&quot;" />

Bug #2: Unescaped ampersands in hyperlink targets

In TsSpreadOOXMLWriter.WriteWorksheetRels (line ~7372), the hyperlink target URL is inserted without escaping:

Code: [Select]

s := Format('Id="rId%d" Target="%s" TargetMode="External" Type="%s"',
  [rId_Hyperlink, target, SCHEMAS_HYPERLINK]);

URLs with query parameters (e.g. ?a=1&b=2) produce invalid XML:

Code: [Select]

<!-- Produced (invalid): -->
<Relationship Target="https://example.com/page?a=1&b=2" ... />

<!-- Expected: -->
<Relationship Target="https://example.com/page?a=1&amp;b=2" ... />

Suggested fix

Both can be fixed using the existing UTF8TextToXMLText() function from fpsxmlcommon (already in the uses clause):

Bug #1:

Code: [Select]

s := s + Format('<%s val="%s" />', [NAME_TAG[UseInStyleNode], UTF8TextToXMLText(AFont.FontName)]);

Bug #2:

Code: [Select]

s := Format('Id="rId%d" Target="%s" TargetMode="External" Type="%s"',
  [rId_Hyperlink, UTF8TextToXMLText(target), SCHEMAS_HYPERLINK]);

Reproduction

1. Create a workbook with a font name containing " (e.g. "Google Sans")
2. Add a hyperlink with a URL containing & (e.g. https://example.com/page?a=1&b=2)
3. Save as .xlsx
4. Load with openpyxl.load_workbook("file.xlsx") → lxml.etree.XMLSyntaxError

I've also filed this on GitHub: https://github.com/veb86/fpspreadsheet/issues/1

Thank you!

[paweld]

BBCode is used on the forum instead of Markdown—please correct the formatting

Errors related to components/projects in the CCR can be reported here: https://gitlab.com/freepascal.org/lazarus/ccr/-/work_items?sort=created_date&state=opened&first_page_size=100

[wp]

Fixed along with ods which has the same issue. In case of the font names I also added an UnquoteStr to remove the quotes altogether, otherwise the quotes would become part of the font name, and Excel/Calc would not find them.

paweld's note is important: please don't post bug reports on any private github pages, there is zero chance that I will find them there. Report bugs on the CCR project of the Lazarus bug tracker, as linked by paweld. (Also, don't report bugs on the sourceforge bugtracker for CCR, it may take a long time until I'll take a look there).