首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >SQL错误:执行预准备语句时DB2代码=-302

SQL错误:执行预准备语句时DB2代码=-302
EN

Stack Overflow用户
提问于 2019-06-11 03:40:56
回答 2查看 1.8K关注 0票数 0

我有一个接受用户输入的SQL查询,因此存在安全缺陷。

现有查询为:

代码语言:javascript
复制
SELECT  BUS_NM, STR_ADDR_1, CITY_NM, STATE_CD, POSTAL_CD, COUNTRY_CD,
        BUS_PHONE_NB,PEG_ACCOUNT_ID, GDN_ALERT_ID, GBIN, GDN_MON_REF_NB,
        ALERT_DT, ALERT_TYPE, ALERT_DESC,ALERT_PRIORITY 
FROM ( SELECT A.BUS_NM, AE.STR_ADDR_1, A.CITY_NM, A.STATE_CD, A.POSTAL_CD,
              CC.COUNTRY_CD,  A.BUS_PHONE_NB, A.PEG_ACCOUNT_ID, 'I' || 
              LPAD(INTL_ALERT_DTL_ID, 9,'0') GDN_ALERT_ID, 
              LPAD(IA.GBIN, 9,'0') GBIN, IA.GDN_MON_REF_NB,
              DATE(IAD.ALERT_TS) ALERT_DT, 
              XMLCAST(XMLQUERY('$A/alertTypeConfig/biqCode/text()' passing  
                IAC.INTL_ALERT_TYPE_CONFIG as "A") AS CHAR(4)) ALERT_TYPE, 
              , ROW_NUMBER() OVER () AS "RN" 
       FROM ACCOUNT A, Other tables 
       WHERE IA.GDN_MON_REF_NB = '100'  
         AND A.PEG_ACCOUNT_ID = IAAR.PEG_ACCOUNT_ID  
         AND CC.COUNTRY_CD = A.COUNTRY_ISO3_CD 
       ORDER BY IA.INTL_ALERT_ID ASC )  
WHERE  ALERT_TYPE  IN (" +TriggerType+ ");

我将其更改为接受来自setString的TriggerType,如下所示:

代码语言:javascript
复制
SELECT BUS_NM, STR_ADDR_1, CITY_NM, STATE_CD, POSTAL_CD, COUNTRY_CD,
       BUS_PHONE_NB,PEG_ACCOUNT_ID, GDN_ALERT_ID, GBIN, GDN_MON_REF_NB,
       ALERT_DT, ALERT_TYPE, ALERT_DESC,ALERT_PRIORITY 
FROM ( SELECT A.BUS_NM, AE.STR_ADDR_1, A.CITY_NM, A.STATE_CD, A.POSTAL_CD, 
              CC.COUNTRY_CD,  A.BUS_PHONE_NB, A.PEG_ACCOUNT_ID, 
              'I' || LPAD(INTL_ALERT_DTL_ID, 9,'0') GDN_ALERT_ID,
              LPAD(IA.GBIN, 9,'0') GBIN, IA.GDN_MON_REF_NB, 
              DATE(IAD.ALERT_TS) ALERT_DT, 
              XMLCAST(XMLQUERY('$A/alertTypeConfig/biqCode/text()' passing  
                IAC.INTL_ALERT_TYPE_CONFIG as "A") AS CHAR(4)) ALERT_TYPE, 
              ROW_NUMBER() OVER () AS "RN" 
       FROM ACCOUNT A, other tables 
       WHERE IA.GDN_MON_REF_NB = '100'  
         AND A.PEG_ACCOUNT_ID = IAAR.PEG_ACCOUNT_ID   
         AND CC.COUNTRY_CD = A.COUNTRY_ISO3_CD 
       ORDER BY IA.INTL_ALERT_ID ASC )  
WHERE  ALERT_TYPE  IN (?);

触发器类型设置如下:

代码语言:javascript
复制
if (StringUtils.isNotBlank(request.getTriggerType())) {
   preparedStatement.setString(1, triggerType != null ? triggerType.toString() : "");
}

获取错误为

原因: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL错误: SQLCODE=-302,SQLSTATE=22001,SQLERRMC=null,DRIVER=4.19.26

EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/56532611

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档