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

错误记录 专栏收录该内容
11 篇文章 0 订阅

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

见异常描述

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>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

sigua0306

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值