Groovy脚本参数化查询SQL注入

Question

几年前，我收到了一份pentest报告，报告我们拥有的一个模块易受SQL注入的攻击。我试图用有限的知识来验证这一点。下面的代码显示了SQLstatement接受where，where变量可能会受到SQL注入，因为它可以被操作。建议使用参数化查询。我的问题是，queryParser是否对其进行了充分的消毒，以防止sql.eachRow执行潜在的恶意负载？

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

https://github.com/OpenRock/OpenIDM/blob/master/openidm-zip/src/main/resources/samples/sample3/tools/SearchScript.groovy

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);

Answer 1

我的groovy很弱，但它至少有几个潜在的问题：

• 正如daggett提到的，您的$left未经验证-这意味着攻击者可以将任意SQL放入该部分
• 即使你修复了这个问题，攻击者也可以做语法分析器在语义上允许的任何事情，因为语法分析器无法判断源是什么；可以查询其他表的DB函数

如果您确实解决了上述问题，您还应该确保；不可信"；其中片段在它自己的括号中(以避免用"OR"子句绕过现有查询的部分(。

唯一安全的方法是通过正/白名单验证来验证SQL的所有元素。每个解析的行都应该是

AND/OR <column name> <operation> :<param> 

所有这些都与列表进行了正面验证。

您需要验证能够通过此验证的所有内容在语义上对用户有效。