TurnOffSecurityForExportToExcel DSS is not found in Pega 8.6?

User Experience
, , ,
1

We are trying to export the excel file which contains one of the column as some excel function. But on export excel is adding single quote (') in front of the value which interns excel considering that value as a normal text. But it is actually a excel forumal.

Found that there is a DSS introudced with name TurnOffSecurityForExportToExcel to off the security so that pega will not add that single quote before the value while exporting. This DSS is in 8.4.1, but not able to find it in 8.6.1.

please provide some inputs on this dss TurnOffSecurityForExportToExcel, is it removed in later versions or modified to some other name or any other approach to avoid that single quote infront of the value on export?

2

@satishJ Many thanks for highlighting this issue. This is the 8.4.1 Resolved Issue segment you referenced:

nj,g.png
nj,g.png1099×367 36 KB

Details about BUG-542361

Improved handling against formula injection attacks in Export to Excel Every time a possibly vulnerable cell value was found during Export to Excel, the value on that cell was modified to prevent a formula injection attack. If the value was non numeric, it would still render an apostrophe, although it should be hidden.

Previous work on this issue involved the addition of a DSS which allowed this security to be disabled if the Excel was going to be consumed by an external tool, but the security implementation used to protect against calculation injection has reworked the ExcelSecurity utility function to allow the ability to change the cell style of a cell that is potentially vulnerable to formula injection attacks. This change no longer changes the cell value but instead applies a new cell style that has quotePrefix enabled.

I can see that you have correctly logged a support ticket INC-B6337

—> Please continue to work with the GCS team and provide a reply here on your forum question at such time the issue is resolved with a solution or explanation.

3

@satishJ I can see that our GCS team resolved and closed your support ticket,.

The solution listed on this ticket is:-

To prevent the apostrophe at the beginning of the text, we made a local code adjustment in the Java function.

For suppose if we had a url in any column untill we double on the cell it won’t convert into a active column.

Please check the below article

https://stackoverflow.com/questions/2595692/how-do-i-convert-a-column-of-text-urls-into-active-hyperlinks-in-excel

Following this modification, you informed GCS you were no longer able to observe the apostrophe at the beginning of the text.

Subsequently, upon generating the Excel file, the entire text is displayed instead of the shortcut, appearing like “=HYPERLINK(“https://pd4ml.tech/release-notes/","Cell Link”)”.

Nevertheless, upon focusing on the column, the link becomes activated. This behavior is not specific to Pega but rather is the expected behavior of Excel; links are activated only upon focus.

To ensure that the link remains activated, please proceed with the previously implemented script.

4

@MarijeSchillern could you please get some info on the local code adjustment in the Java function?

5

@SaiSrinivasS

To address the security issue, Pega prevents commands or expressions from being executed in Excel by adding a single quote ('), to any cell value that starts with {+, -, =, @} in the generated Excel file.

In the given scenario, some cells contain expressions that start with “@”. To avoid the addition of a single quote, we recommended a local change in the file (DCOXLGenerateZipEntryMap at line 575) by removing the condition stringValue.startsWith("@").

The modified code should be:

if(null != stringValue &&
(stringValue.startsWith("=") || stringValue.startsWith("+") || stringValue.startsWith("-"))) {
stringValue = "'" + stringValue;
}
xlStringValues[index] = stringValue;