java.sql.SQLException: sql injection violation, syntax error: TODO QUES

异常记录一下，之前遇到过类似问题，但是由于未记录，又排查了近两个小时，切记切记！！！

见异常描述

org.springframework.jdbc.UncategorizedSQLException: 
### Error querying database.  Cause: java.sql.SQLException: sql injection violation, syntax error: TODO QUES : SELECT nvl(QBXSSR,0) from GS_SB_SBXX t
		WHERE t.result_id=? AND t.ZSXM_DM='10101'
		AND trunc(t.SSSQZ,'mm') BETWEEN add_months(TO_DATE(?,'yyyy-mm-dd'),-?)  
					AND add_months(TO_DATE(?,'yyyy-mm-dd'),-?)
### The error may exist in file [D:\Java\Eclipse\eclipseworkspace\other\dg-platform-B-0058\target\classes\config\mybatis\mapper\dgsqlmap\sqlmap-mapping-warnquantifymodelindex.xml]
### The error may involve com.vplatform.webside.earlywarning.mapper.WarnQuantifyModelIndexMapper.getTaxZzsXSSR
### The error occurred while executing a query
......

mybatis XML中异常代码段

	<select id="getTaxZzsXSSR" parameterType="map" resultType="BigDecimal">
		SELECT nvl(QBXSSR,0) from GS_SB_SBXX t
		WHERE t.result_id=#{resultId} AND t.ZSXM_DM='10101'
		AND trunc(t.SSSQZ,'mm') BETWEEN add_months(TO_DATE(#{zzsMaxDate},'yyyy-mm-dd'),-#{somendxj})  
					AND add_months(TO_DATE(#{zzsMaxDate},'yyyy-mm-dd'),-#{somendsj})
	</select>

 

该异常的描述很概括，只是说存在问题，然后把整个sql语句抛出，并未明确说明是什么问题及具体位置，通过debug将入参带入sql，在plsql中执行该脚本并未报错，所以排除语句问题，并且经对比确定了parameterType与resultType属性无误，最终确定为接参方式问题，入参map为Map<String, Object>，其中somendxj与somendsj为int类型，接参不能为#{xxx}格式，改成${xxx}后异常解决。

解决后的代码段为

	<select id="getTaxZzsXSSR" parameterType="map" resultType="BigDecimal">
		SELECT nvl(QBXSSR,0) from GS_SB_SBXX t
		WHERE t.result_id=#{resultId} AND t.ZSXM_DM='10101'
		AND trunc(t.SSSQZ,'mm') BETWEEN add_months(TO_DATE(#{zzsMaxDate},'yyyy-mm-dd'),-${somendxj})  
					AND add_months(TO_DATE(#{zzsMaxDate},'yyyy-mm-dd'),-${somendsj})
	</select>

另补充一个同一问题排查点

<insert id="insertWarnSMSModel" parameterType="java.util.List">
		insert into ALERT_TO_GLYH
	    (
	        ID,
	        RESULT_ID,
<!-- 	        CJSJ, -->
	        MSG_CJSJ,
	        MSG_TYPE,
	        SEND_TIMES,
	        SEND_CYCLE,
	        WORKDAY,
	        OVER_TIME,
	        PHONENUMBER,
	        INPUT_TIME,
	        INDNAME,
	        STATUS,
	        CREATE_TIME
	        )
       select ALERT_TO_GLYH_SEQ.NEXTVAL ID, A.*, sysdate from  
      <foreach collection="list" separator="UNION ALL" item="item" open="(" close=")">
<!--         #{ID,jdbcType=DECIMAL}, -->
        #{RESULT_ID,jdbcType=VARCHAR},
<!--         #{CJSJ,jdbcType=TIMESTAMP}, -->
        #{MSG_CJSJ,jdbcType=TIMESTAMP},
        #{MSG_TYPE,jdbcType=VARCHAR},
        #{SEND_TIMES,jdbcType=VARCHAR},
        #{SEND_CYCLE,jdbcType=VARCHAR},
        #{WORKDAY,jdbcType=VARCHAR},
        #{OVER_TIME,jdbcType=VARCHAR},
        #{PHONENUMBER,jdbcType=VARCHAR},
        #{INPUT_TIME,jdbcType=TIMESTAMP},
        #{INDNAME,jdbcType=VARCHAR},
        #{STATUS,jdbcType=VARCHAR}
        from dual 
      </foreach> 
        A 
	</insert>

代码中设置了集合中的实体为item，则入参前都需加上item.

	<insert id="insertWarnSMSModel" parameterType="java.util.List">
		insert into ALERT_TO_GLYH
	    (
	        ID,
	        RESULT_ID,
<!-- 	        CJSJ, -->
	        MSG_CJSJ,
	        MSG_TYPE,
	        SEND_TIMES,
	        SEND_CYCLE,
	        WORKDAY,
	        OVER_TIME,
	        PHONENUMBER,
	        INPUT_TIME,
	        INDNAME,
	        STATUS,
	        CREATE_TIME
	        )
       select ALERT_TO_GLYH_SEQ.NEXTVAL ID, A.*, sysdate from  
      <foreach collection="list" separator="UNION ALL" item="item" open="(" close=")">
<!--         #{item.ID,jdbcType=DECIMAL}, -->
        #{item.RESULT_ID,jdbcType=VARCHAR},
<!--         #{item.CJSJ,jdbcType=TIMESTAMP}, -->
        #{item.MSG_CJSJ,jdbcType=TIMESTAMP},
        #{item.MSG_TYPE,jdbcType=VARCHAR},
        #{item.SEND_TIMES,jdbcType=VARCHAR},
        #{item.SEND_CYCLE,jdbcType=VARCHAR},
        #{item.WORKDAY,jdbcType=VARCHAR},
        #{item.OVER_TIME,jdbcType=VARCHAR},
        #{item.PHONENUMBER,jdbcType=VARCHAR},
        #{item.INPUT_TIME,jdbcType=TIMESTAMP},
        #{item.INDNAME,jdbcType=VARCHAR},
        #{item.STATUS,jdbcType=VARCHAR}
        from dual 
      </foreach> 
        A 
	</insert>