避免公式注入，保留单元格值(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. 按回车键
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?

推荐答案

感谢 这里是 Axel Richter 和 这里是 Nikos Paraskevopoulos....

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

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屋！