使用mybatis遇到的一些问题

最新推荐文章于 2021-08-21 10:59:17 发布

原创最新推荐文章于 2021-08-21 10:59:17 发布 · 357 阅读

0 ·

CC 4.0 BY-SA版权

mybatis 专栏收录该内容

1 篇文章

订阅专栏

本文探讨了在使用SpringBoot与Mybatis时遇到的SQL注入及OrderBy排序问题。通过调整mapper.xml文件中的SQL语句，引入参数化处理，有效避免了SQL注入风险。同时，针对OrderBy动态排序需求，提供了灵活的解决方案。

1.使用druid,具有防止sql注入的功能.

https://blog.youkuaiyun.com/vichou_fa/article/details/79285749 SpringBoot + Mybatis 在控制台输出sql语句(主要是配置)

问题一: 发现直接在mapper.xml中接直接写

select t1.constant_name as departmentName,
  CASE t2.pool_type
      WHEN '1' THEN '后勤线'
      WHEN '2' THEN '业务线'
      WHEN '3' THEN '物流线'
  end as line,
  t2.grant_amount as distributeAmount,t2.forfeit_amount as forfeitAmount,t2.result_amount as grantAmount,
  t2.surplus_amount as surplusAmount
   from
            t_system_constant t1,
            t_bonus_monthly_surplus t2
  where t1.constant_key=t2.department_id and t1.constant_type='pool_type' and 1=1

它会把它作为sql注入处理,就会报错.还有就是order by 排序问题

<select id="selectDeptBonusListByMap" parameterType="java.util.Map" resultType="com.deli.dom.bonus.form.BonusStatisticsForm">
select t1.constant_name as departmentName,
    CASE t2.pool_type
        WHEN '1' THEN '后勤线'
        WHEN '2' THEN '业务线'
        WHEN '3' THEN '物流线'
    end as line,
    t2.grant_amount as distributeAmount,t2.forfeit_amount as forfeitAmount,t2.result_amount as grantAmount,
    t2.surplus_amount as surplusAmount
     from
              t_system_constant t1,
              t_bonus_monthly_surplus t2
    where t1.constant_key=t2.department_id and t1.constant_type=#{constantType,jdbcType=VARCHAR} and 1=1
    <if test="year !=null and year!=''">
      and t2.year = #{year,jdbcType=VARCHAR}
    </if>
    <if test="month !=null and month!=''">
      and t2.month = #{month,jdbcType=VARCHAR}
    </if>
    <if test="str !=null and str!=''">
      and FIND_IN_SET(t2.pool_type,#{str,jdbcType=VARCHAR})
    </if>
    <if test="deptName !=null and deptName!=''">
      and t1.constant_name like #{deptName,jdbcType=VARCHAR}
    </if>
    <if test="orderBy !=null and orderBy!=''">
      order by ${orderBy}
    </if>
</select>

参数如下:

Map map = MapUtil
    .convert2HashMap("deptName", deptName == null ? "" : "%" + deptName + "%", "str",
        s, "orderBy", "department_id", "year", year,
        "month", month, "constantType", "bonus_department_info");

https://www.jb51.net/article/125744.htm find_in_set的用法参考