今天在出来多表删除的时候在mapper.xml用了下面的多个delete语句
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.nbcio.modules.flowable.mapper.FlowTaskMapper"> <select id="queryActivityInstance" resultType="org.flowable.engine.impl.persistence.entity.ActivityInstanceEntityImpl"> select t.* from act_ru_actinst t <where> <if test="processInstanceId !=null and processInstanceId != ''" > t.PROC_INST_ID_=#{processInstanceId} and ACT_TYPE_ = 'userTask' and END_TIME_ is not null </if> </where> order by t.END_TIME_ ASC </select> <delete id="deleteRunActinstsByIds" parameterType="java.util.List"> delete from act_ru_actinst where ID_ in <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} </foreach> </delete> <delete id="deleteHisActinstsByIds" parameterType="java.util.List"> delete from act_hi_actinst where ID_ in <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} </foreach> </delete> <delete id="deleteAllHisAndRun" parameterType="String"> delete from act_ru_actinst where proc_inst_id_ = #{processInstanceId}; delete from act_ru_identitylink where proc_inst_id_ = #{processInstanceId}; delete from act_ru_task where proc_inst_id_ = #{processInstanceId}; delete from act_ru_variable where proc_inst_id_ = #{processInstanceId}; delete from act_ru_execution where proc_inst_id_ = #{processInstanceId}; delete from act_hi_actinst where proc_inst_id_ = #{processInstanceId}; delete from act_hi_comment where proc_inst_id_ = #{processInstanceId}; delete from act_hi_identitylink where proc_inst_id_ = #{processInstanceId}; delete from act_hi_procinst where proc_inst_id_ = #{processInstanceId}; delete from act_hi_taskinst where proc_inst_id_ = #{processInstanceId}; delete from act_hi_varinst where proc_inst_id_ = #{processInstanceId}; </delete> </mapper>
结果执行的时候出现下面错误
2023-08-19 10:32:22.408 [http-nio-8080-exec-6] [34mINFO [0;39m [36mo.j.c.util.security.AbstractQueryBlackListHandler:56[0;39m - --获取sql信息-- 2023-08-19 10:32:32.175 [http-nio-8080-exec-4] [1;31mERROR[0;39m [36mo.jeecg.common.exception.JeecgBootExceptionHandler:69[0;39m - ### Error updating database. Cause: java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ? ### The error may exist in file [F:\codestudy\nbcio-oa\develop\nbcio-boot\nbcio-boot-module-flowable\target\classes\com\nbcio\modules\flowable\mapper\xml\FlowTaskMapper.xml] ### The error may involve com.nbcio.modules.flowable.mapper.FlowTaskMapper.deleteAllHisAndRun ### The error occurred while executing an update ### SQL: DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ? ### Cause: java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ? ; uncategorized SQLException; SQL state [null]; error code [0]; sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?; nested exception is java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ? org.springframework.jdbc.UncategorizedSQLException: ### Error updating database. Cause: java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ? ### The error may exist in file [F:\codestudy\nbcio-oa\develop\nbcio-boot\nbcio-boot-module-flowable\target\classes\com\nbcio\modules\flowable\mapper\xml\FlowTaskMapper.xml] ### The error may involve com.nbcio.modules.flowable.mapper.FlowTaskMapper.deleteAllHisAndRun ### The error occurred while executing an update ### SQL: DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ? ### Cause: java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ? ; uncategorized SQLException; SQL state [null]; error code [0]; sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?; nested exception is java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ? at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
主要是说druid不支持多表删除的操作，先相关资料实际上是可以配置druid进行多表操作
配置如下，sql连接增加&allowMultiQueries=true
url: jdbc:mysql://192.168.199.151:3306/nbcio-boot?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai&allowMultiQueries=true
同时druid里filters: stat,slf4j 这里去掉防火墙wall
但这里也会引起一次多表操作的风险，不安全的时候风险也比较大，所以还是想办法考虑其它方式来处理为好。
