Groovy脚本参数化查询SQL注入验证：queryParser能否防范注入？

数年前我司收到渗透测试报告，指出某模块存在SQL注入漏洞。我正尝试在有限知识储备下验证该问题：下方代码中SQL语句引入了where变量，该变量存在被篡改的可能，因此存在SQL注入风险，建议采用参数化查询。请问queryParser是否能充分净化输入，避免sql.eachRow执行恶意payload？

关键代码片段：

where = where + " AND " + queryParser(query)

完整代码如下：

switch (objectClass) {
    case ObjectClass.ACCOUNT:

        def where = "" ;
        def whereParams = []
        def fieldMap = [      
                "__ACCOUNT__" : [
                        "__UID__" : "USER_CONTEXT_KEY",
                        "__NAME__": "USER_CONTEXT_KEY"
                ] 
        ]

        if (filter != null) {

            def query = filter.accept(MapFilterVisitor.INSTANCE, null)
             log.info("Filter query:"+ query);
            // this closure function recurses through the (potentially complex) query object in order to build an equivalent
            // SQL 'where' expression
            def queryParser
            queryParser = { queryObj ->

                if (queryObj.operation == "OR" || queryObj.operation == "AND") {
                    return "(" + queryParser(queryObj.right) + " " + queryObj.operation + " " + queryParser(queryObj.left) + ")"
                } else {

                    if (fieldMap[objectClass.objectClassValue] && fieldMap[objectClass.objectClassValue][queryObj.get("left")]) {
                        queryObj.put("left", fieldMap[objectClass.objectClassValue][queryObj.get("left")])
                    }

                    def left = queryObj.get('left')
                    def not = queryObj.get('not')
                    def template
                    
                    switch (queryObj.get('operation')) {
                        case 'CONTAINS':
                            template = "$left ${not ? "NOT " : ""}LIKE ?"
                            whereParams.add("%" + queryObj.get("right") + "%")
                            break
                        case 'ENDSWITH':
                            template = "$left ${not ? "NOT " : ""}LIKE ?"
                            whereParams.add("%" + queryObj.get("right"))
                            break
                        case 'STARTSWITH':
                            template = "$left ${not ? "NOT " : ""}LIKE ?"
                            whereParams.add(queryObj.get("right") + "%")
                            break
                        case 'EQUALS':
                            template = "$left ${not ? "<>" : "="} ?"
                            whereParams.add(queryObj.get("right"))
                            break
                        case 'GREATERTHAN':
                            template = "$left ${not ? "<=" : ">"} ?"
                            whereParams.add(queryObj.get("right"))
                            break
                        case 'GREATERTHANOREQUAL':
                            template = "$left ${not ? "<" : ">="} ?"
                            whereParams.add(queryObj.get("right"))
                            break
                        case 'LESSTHAN':
                            template = "$left ${not ? ">=" : "<"} ?"
                            whereParams.add(queryObj.get("right"))
                            break
                        case 'LESSTHANOREQUAL':
                            template = "$left ${not ? ">" : "<="} ?"
                            whereParams.add(queryObj.get("right"))
                    }
                    
                    return template.toString()
                }
            }

            where = where + " AND " + queryParser(query)
            log.info("Search WHERE clause is: " + where)
        }

       def statement = """
        select 
             a.USERUID||'^'||b.OCRCNMBR as USER_CONTEXT_KEY 
            ,b.USERUID,b.OCRCNMBR,b.AUTHRCNTXTTYPE,b.AUTHRCNTXTSTATUS,b.CNTXTSTATUSREAS,b.REVIEWDATE,b.CNTXTSTRTDATE
            ,b.CNTXTEXPDATE,b.DOFPROFILEID,b.DESC, b.DATAAUTHRREF,b.APPROVERNAME,b.AC_TIMESTMP,b.APPROVER_EMAIL_ADDRESS,b.APPROVER_PHONE_NO
            ,c.AGENTLEVEL
            ,CASE 
                WHEN a.USERTYPE = 'AD' then '1' 
                WHEN a.USERTYPE in ('AA' , 'AB') and c.AGENCYACCNO is NULL  then '1'
                ELSE c.AGENCYACCNO
            END as AGENCYACCNO,
            c.RINO,c.COMPANYSIBNO,c.AAC_TIMESTMP,c.GI_AGENCY_NO,c.GI_AGENT_LEVEL,c.COMPANY_NAME,c.IRN_NO           
        from
            ESEC.TIAUSER a,
            ESEC.TIAAUTHRCNTXT b
            LEFT JOIN ESEC.TIAAGNTAUTHR c ON b.useruid = c.useruid AND b.OCRCNMBR = c.OCRCNMBR
        where a.USERTYPE in ('AA', 'AB' , 'AD')
            and a.useruid = b.useruid
            ${where}
            order by a.USERUID, b.OCRCNMBR WITH UR
        """
        log.info("Statement is: {0}", statement);
         log.info("Statement params : {0}", whereParams);

• queryParser的防护机制：

  1. 操作符白名单：仅允许AND/OR以及8种预定义比较操作（CONTAINS/EQUALS等），非法操作符无法进入最终SQL语句。
  2. 字段映射校验：查询字段（left）必须在fieldMap中有对应映射，用户无法随意指定任意数据库字段，杜绝了字段篡改风险。
  3. 参数化处理：所有用户输入的查询值（right）都被存入whereParams数组，SQL中用占位符?替代，不会直接拼接输入内容，从根源上避免了SQL注入的核心风险——用户输入被当作SQL指令执行。

• 最终结论：
  只要后续执行sql.eachRow时，是传入生成的statement和whereParams（而非直接执行拼接后的SQL字符串），那么queryParser可以充分防范SQL注入。

  需注意：如果最终执行时未使用whereParams，而是直接执行拼接好的statement，则仍存在风险，但从代码逻辑来看，whereParams的设计就是为了配合参数化查询使用的。

内容的提问来源于stack exchange，提问作者Anderson