iBATIS update 特殊字符处理

在近期项目中发现有部分用户投诉文件名中带有  单引号、反斜杠及双引号（‘ 、 “）的文件无法上传或者文件名中少了反斜杠或双引号，经查实后发现后台执行SQL异常：

org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred while applying a parameter map.  
--- Check the com.cn21.edrive.mysql.persistence.file.dao.model.FMUploadFilePart.updateByPrimaryKeySelective-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: com.foundationdb.sql.parser.SQLParserException: Encountered " <EXACT_NUMERIC> "1. "" at line 1, column 140.
Was expecting one of:
    <EOF> 
    "and" ...
    "collate" ...
    "is" ...
    "where" ...
    "returning" ...
    "," ...
    
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.update(SqlMapClientTemplate.java:378)

 

从日志上看，sql语法出错，可能出现关键字冲突。debug导出相关的sql查看：

update fm_upload_file_part   set   OPER_ID = ?  ,  FILE_OWNER_ID =   ? ,     FILE_NAME =   ?         where UPLOAD_FILE_ID = ?  

参数值 2'1.jpg 带有上引号，导致sql语法错误
 Parameters: [8546, 8546, 2'1.jpg, 31468211402]

该sql是由iBATIS生成，查看sql_map.xml, 字段fileName使用的是#变量传参的形式，所以将2'1.jpg赋值给sql语句造成语法错误。后来改成 "$fileName$" 字符串直接替换的形式解决了sql语法问题。

 <isNotNull prepend="," property="fileName">
       FILE_NAME = "$fileName$"
       <!-- #fileName:VARCHAR# --> 
 </isNotNull>

在解决了sql语法问题之后，需要在上层业务层对fileName做特殊字符转义，通常采用添加反斜杠的形式进行转义：

public static String encodingFileName4MysqlQuota(String fileName) {
        String result = fileName;
        if (fileName != null && fileName.length() > 0) {
            try {

                result = result .replaceAll("\\\\", "\\\\\\\\");//反斜杠 \  转义
                result = fileName.replaceAll("'", "\\\\'");//单引号 ' 转义
                result = result .replaceAll("\"", "\\\\\"");// 双引号 “ 转义
                //过滤不允许的特殊字符
                if (result != null && result.length() > 0  ) {
                    result = result.replaceAll("[\\x00-\\x08\\x0b-\\x0c\\x0e-\\x1f]", "");
                }
                log.debug("encodingFileName4MysqlQuota() - result=" + result );
                
            } catch (Exception e) {
                log.warn("encodingFileName4MysqlQuota() - fileName=" + fileName, e);
                result = fileName;
            }
         
        }
        return result;
    }

至于反斜杠的数目，主要是考虑了java 转义->正则表达式转义->mysql转义等几层，如剥洋葱般层层转义。有时平台侧和数据库连接时经过了MyCat等中间件，肯能还需要进一步转义，视具体情况而定，做好相应的单元测试，检查写入是否正确。

单元测试后 名为 1'2ww"ee"<> & % #$*.jpg 的图片可成功入库。

对于需要将该数据读取后以xml格式返回的服务，需要对< > & 等字符做进一步处理。