避免公式注入，保留单元格值(HSSF/*.xls中的引号前缀) [英] Avoid formula injection, keeping cell value (quote prefix in HSSF/*.xls)

问题描述

我正在使用的应用程序使用Apache POI创建Excel导出.通过安全审核，我们注意到，如果用户不够谨慎，则包含恶意值的单元格可以生成任意进程.

The application I am working on creates Excel exports using Apache POI. It was brought to our attention, through a security audit, that cells containing malicious values can spawn arbitrary processes if the user is not careful enough.

要复制，请运行以下命令:

import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class BadWorkbookCreator {
    public static void main(String[] args) throws Exception {
        try(
            Workbook wb = new HSSFWorkbook();
            FileOutputStream fos = new FileOutputStream("C:/workbook-bad.xls")
        ) {
            Sheet sheet = wb.createSheet("Sheet");
            Row row = sheet.createRow(0);
            row.createCell(0).setCellValue("Aaaaaaaaaa");
            row.createCell(1).setCellValue("-2+3 +cmd|'/C calc'!G20");
            wb.write(fos);
        }
    }
}

然后打开生成的文件:

并按照以下步骤操作:

1. 单击(A)选择包含恶意内容的单元格
2. 单击(B)，使光标位于公式编辑器中
3. 按Enter
4. 将询问您是否允许Excel运行外部应用程序；如果您回答是，则启动了Calc(或任何恶意代码)

可能会说用户负责让Excel运行任意内容，并警告了用户.但是，仍然可以从受信任的来源下载Excel，但有人可能会陷入陷阱.

One may say that the user is responsible for letting Excel run arbitrary things and the user was warned. But still, the Excel is downloaded from a trusted source and someone may fall into the trap.

使用Excel，您可以在公式编辑器中的文本前面放置一个单引号，以对其进行转义.以编程方式将单引号放在单元格内容中(例如下面的代码)，使单引号可见！

Using Excel, you can place a single quote in front of the text in the formula editor to escape it. Placing the single quote in the cell content programmatically (e.g. code as below) makes the single quote visible!

String cellValue = cell.getStringCellValue();
if( cellValue != null && "=-+@".indexOf(cellValue.charAt(0)) >= 0 ) {
    cell.setCellValue("'" + cellValue);
}

问题:是否有一种方法可以使值在公式编辑器中转义，但在单元格中显示正确的值而没有前导单引号?

The question: Is there a way to keep the value escaped in the formula editor, but show the correct value, without the leading single quote, in the cell?

推荐答案

感谢此处为Nikos Paraskevopoulos ....

从Apache POI 3.16 beta 1开始(或者对于那些生活危险的人，在20161105之后的任何夜间构建)，

From Apache POI 3.16 beta 1 onwards (or for those who live dangerously, any nightly build after 20161105), there are handy methods on CellStyle for getQuotePrefixed and setQuotePrefixed(boolean)

您的代码将变为:

// Do this once for the workbook
CellStyle safeFormulaStyle = workbook.createCellStyle();
safeFormulaStyle.setQuotePrefixed(true);


// Per cell
String cellValue = cell.getStringCellValue();
if( cellValue != null && "=-+@".indexOf(cellValue.charAt(0)) >= 0 ) {
    cell.setCellStyle(safeFormulaStyle);
}

这篇关于避免公式注入，保留单元格值(HSSF/*.xls中的引号前缀)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！