批量修改本地成功测试环境失败报SQL注入的问题

记录一次诡异的Bug，下面是我原先的业务方法[精简过]，目的是批量新增或批量修改食谱内容

 public int insertOrUpdateBatch(List<RecipeContent> recipeContents) {
        List<RecipeContent> insertList = new ArrayList<>();
        List<RecipeContent> updateList = new ArrayList<>();
        for (RecipeContent recipeContent : recipeContents) {

            if (recipeContent.getId() == null) {
                insertList.add(recipeContent);
            } else {
                updateList.add(recipeContent);
            }
        }
        int count = 0;
        if (!insertList.isEmpty()) {
            count += recipeContentMapper.batchInsertRecipeContent(insertList);
        }
        if (!updateList.isEmpty()) {
            count += recipeContentMapper.batchUpdateRecipeContent(updateList);
        }
        return count;
 }

mapper的批量新增方法没问题，也贴出来

<insert id="batchInsertRecipeContent" parameterType="java.util.List">
        INSERT INTO recipe_content (recipe_template_id, eating_time, total_kcal, content, creator, create_time, updater, update_time, is_deleted)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (
            #{item.recipeTemplateId},
            #{item.eatingTime},
            #{item.totalKcal},
            #{item.content},
            #{item.creator},
            #{item.createTime},
            #{item.updater},
            #{item.updateTime},
            #{item.isDeleted}
            )
        </foreach>
</insert>

但是批量修改就有问题了

报错信息：java.sql.SQLException: sql injection violation, multi-statement not allow

 mapper映射文件中的sql语句如下

<update id="batchUpdateRecipeContent" parameterType="java.util.List">
        <foreach collection="list" item="item" >
            UPDATE recipe_content
            SET
            recipe_template_id = #{item.recipeTemplateId},
            eating_time = #{item.eatingTime},
            total_kcal = #{item.totalKcal},
            content = #{item.content},
            creator = #{item.creator},
            create_time = #{item.createTime},
            updater = #{item.updater},
            update_time = #{item.updateTime},
            is_deleted = #{item.isDeleted}
            WHERE id = #{item.id}
        </foreach>
</update>

我本地测试时postman传参如下

[{
  "id":"23",
  "recipeTemplateId": "1",
  "eatingTime": "breakfast",
  "totalKcal": 122,
   "content": "{\"meal_a\":\"1、肉3g+菜3g,2、蛋3个,3、牛奶3ml\",\"meal_b\":\"1、蔬菜饺子3个+杂粮粥(菜心3g、虾仁3g),2、鹌鹑蛋3个\",\"meal_c\":\"1、云吞3个+油麦菜3g,2、鸡蛋3个,3、纯牛奶3ml\"}",
  "creator": null,
  "createTime": null,
  "updater": null,
  "updateTime": null,
  "isDeleted": null
}
]

主要是修改content字段里面的内容，数据库里是修改成功的。

但是不知为何到测试环境就报sql注入的问题，我以为是没有加if标签判断参数是否为null的问题，但是加了也还是报sql注入的错。

解决办法：将批量修改换成单个修改for循环

将mapper映射文件中批量修改换成单个对象的修改，就很普通的一个修改方法：

<update id="updateRecipeContent" parameterType="com.onecode.gdm.service.infrastructure.persistence.model.RecipeContent">
        update recipe_content
        <trim prefix="SET" suffixOverrides=",">
            <if test="recipeTemplateId != null">recipe_template_id = #{recipeTemplateId},</if>
            <if test="eatingTime != null and eatingTime != ''">eating_time = #{eatingTime},</if>
            <if test="totalKcal != null">total_kcal = #{totalKcal},</if>
            <if test="content != null">content = #{content},</if>
            <if test="creator != null and creator != ''">creator = #{creator},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updater != null">updater = #{updater},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="isDeleted != null">is_deleted = #{isDeleted},</if>
        </trim>
        where id = #{id}
</update>

业务代码中的修改

public int insertOrUpdateBatch(List<RecipeContent> recipeContents) {
        List<RecipeContent> insertList = new ArrayList<>();
        List<RecipeContent> updateList = new ArrayList<>();
        for (RecipeContent recipeContent : recipeContents) {
            if (recipeContent.getId() == null) {
                insertList.add(recipeContent);
            } else {
                updateList.add(recipeContent);
            }
        }
        if (!insertList.isEmpty()) {
            recipeContentMapper.batchInsertRecipeContent(insertList);
        }
        if (!updateList.isEmpty()) {
            for (RecipeContent recipeContent : updateList) {
                recipeContentMapper.updateRecipeContent(recipeContent);
            }
          // recipeContentMapper.batchUpdateRecipeContent(updateList);
        }
        int count = insertList.size() + updateList.size();
        return count;
    }

改成这样后，测试环境竟然就好了，后来看到一篇文章，又想的是不是这个问题，sql injection violation, multi-statement not allow - 灰信网（软件开发博客聚合）

因为druid数据源默认不允许批量更新，从中WallConfig这个类来看，是由druid.wall.multiStatementAllow这个属性来控制的，但是我在启动类中加了下面这个并没有批量修改成功，不知道为什么

System.setProperty("druid.wall.multiStatementAllow","true");

后来还是老老实实用for循环单个修改这个笨办法了