mybatis动态SQL防止SQL注入


IvrNodeTreeMapper.java如下:

package com.example.springbootannotationmybatis.mapper;


import com.example.springbootannotationmybatis.domain.IvrNodeTree;
import com.example.springbootannotationmybatis.sqlprovider.IvrNodeTreeSqlProvider;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.Date;
import java.util.List;

/**
 * Title: IvrNodeTreeMapper
 * Description: IvrNodeTreeMapper
 * Date:  2018/5/17
 *
 * @author <a href=mailto:zhouzhichao1024@gmail.com>chaochao</a>
 */
@Mapper
public interface IvrNodeTreeMapper {

    @InsertProvider(type = IvrNodeTreeSqlProvider.class, method = "batchInsert")
    boolean addBatch(List<IvrNodeTree> ivrNodeTrees);

    //不能防止SQL注入
    @SelectProvider(type = IvrNodeTreeSqlProvider.class,method = "queryTopDanger")
    @Results(value = {
            @Result(id = true, property = "id", column = "id", javaType = Long.class, jdbcType = JdbcType.BIGINT),
            @Result(id = false, property = "ivrFlag", column = "ivr_flag", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(id = false, property = "customerType", column = "customer_type", javaType = Integer.class, jdbcType = JdbcType.INTEGER),
            @Result(id = false, property = "businessType", column = "business_type", javaType = Integer.class, jdbcType = JdbcType.INTEGER),
            @Result(id = false, property = "treeContent", column = "tree_content", javaType = String.class, jdbcType = JdbcType.LONGVARCHAR),
            @Result(id = false, property = "signature", column = "signature", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(id = false, property = "versionTimestamp", column = "version_timestamp", javaType = Date.class, jdbcType = JdbcType.DATE),})
    List<IvrNodeTree> queryTopDanger(String ivrFlag, Integer customerType, Integer businessType, String endTimestamp, int count);


    //可以解决SQL注入
    @SelectProvider(type = IvrNodeTreeSqlProvider.class,method = "queryRecentTop")
    @Results(value = {
            @Result(id = true, property = "id", column = "id", javaType = Long.class, jdbcType = JdbcType.BIGINT),
            @Result(id = false, property = "ivrFlag", column = "ivr_flag", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(id = false, property = "customerType", column = "customer_type", javaType = Integer.class, jdbcType = JdbcType.INTEGER),
            @Result(id = false, property = "businessType", column = "business_type", javaType = Integer.class, jdbcType = JdbcType.INTEGER),
            @Result(id = false, property = "treeContent", column = "tree_content", javaType = String.class, jdbcType = JdbcType.LONGVARCHAR),
            @Result(id = false, property = "signature", column = "signature", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(id = false, property = "versionTimestamp", column = "version_timestamp", javaType = Date.class, jdbcType = JdbcType.DATE),})
    List<IvrNodeTree> queryTop(@Param("ivrFlag") String ivrFlag, @Param("customerType") Integer customerType, @Param("businessType") Integer businessType, @Param("endTimestamp") String endTimestamp, @Param("count") int count);


    @Select("select COUNT(*) from ivr_node_tree where signature=#{signature}")
    int findSignature(String signature);
}


IvrNodeTreeSqlProvider.java

package com.example.springbootannotationmybatis.sqlprovider;

import com.example.springbootannotationmybatis.domain.IvrNodeTree;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

import java.text.MessageFormat;
import java.util.List;
import java.util.Map;

/**
 * Title: IvrNodeTreeSqlProvider
 * Description: IvrNodeTreeSqlProvider
 * Date:  2018/5/17
 *
 * @author <a href=mailto:zhouzhichao1024@gmail.com>chaochao</a>
 */

public class IvrNodeTreeSqlProvider {

    private String TABLE_NAME = "ivr_node_tree";

    public String batchInsert(Map<String, List<IvrNodeTree>> map) {
        List<IvrNodeTree> list = map.get("list");
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ");
        sb.append(TABLE_NAME);
        sb.append(" (ivr_flag,customer_type,business_type,tree_content,signature,version_timestamp)");
        sb.append(" values ");
        MessageFormat mf = new MessageFormat(
                "(#'{'list[{0}].ivrFlag},#'{'list[{0}].customerType},#'{'list[{0}].businessType},"
                        + "#'{'list[{0}].treeContent},#'{'list[{0}].signature},#'{'list[{0}].versionTimestamp})");
        for (int i = 0; i < list.size(); i++) {
            sb.append(mf.format(new Object[] { i }));
            if (i < list.size() - 1) {
                sb.append(",");
            }
        }

        //insert into ivr_node_tree (ivr_flag,customer_type,business_type,tree_content,signature,version_timestamp) values (#{list[0].ivrFlag},#{list[0].customerType},#{list[0].businessType},#{list[0].treeContent},#{list[0].signature},#{list[0].versionTimestamp}),(#{list[1].ivrFlag},#{list[1].customerType},#{list[1].businessType},#{list[1].treeContent},#{list[1].signature},#{list[1].versionTimestamp}),(#{list[2].ivrFlag},#{list[2].customerType},#{list[2].businessType},#{list[2].treeContent},#{list[2].signature},#{list[2].versionTimestamp}),(#{list[3].ivrFlag},#{list[3].customerType},#{list[3].businessType},#{list[3].treeContent},#{list[3].signature},#{list[3].versionTimestamp}),(#{list[4].ivrFlag},#{list[4].customerType},#{list[4].businessType},#{list[4].treeContent},#{list[4].signature},#{list[4].versionTimestamp}),(#{list[5].ivrFlag},#{list[5].customerType},#{list[5].businessType},#{list[5].treeContent},#{list[5].signature},#{list[5].versionTimestamp})
        System.out.println(sb.toString());
        return sb.toString();
    }


    /**
     * 拼接字符串,不能防止SQL注入,有风险
     * @param ivrFlag
     * @param customerType
     * @param businessType
     * @param endTimestamp
     * @param count
     * @return
     */
    public String queryTopDanger(String ivrFlag, Integer customerType, Integer businessType, String endTimestamp, int count) {

        SQL sql = new SQL().SELECT("*").FROM(TABLE_NAME);
        if (StringUtils.hasText(ivrFlag)) {
            sql.WHERE("ivr_flag = '" + ivrFlag + "'");
        }

        if(customerType != null){
            sql.WHERE("customer_type = " + customerType);
        }

        if(businessType != null){
            sql.WHERE("business_type = " + businessType);
        }

        if(endTimestamp != null){
            sql.WHERE("version_timestamp <= '" + endTimestamp + "'");
        }

        System.out.println("生成SQL:" + sql.toString() + "  ORDER BY version_timestamp DESC limit " + count);
        return sql.toString() + "  ORDER BY version_timestamp DESC limit " + count;
    }


    /**
     * 可以避免SQL注入
     * 动态SQL的构建函数(method)只是构建SQL,值绑定并不是发生在这个阶段。但是在这个阶段显式绑定也没有太大问题。例如 sql.SET("name = #{name}") 写成 sql.SET("name = " + employee.getName()) 也没有什么不可以,除了可能引发SQL注入。
     * @param param
     * @return
     */
    public String queryRecentTop(Map<String, Object> param) {

        SQL sql = new SQL().SELECT("*").FROM(TABLE_NAME);
        if (StringUtils.hasText((String) param.get("ivrFlag"))) {
            sql.WHERE("ivr_flag = #{ivrFlag}");
        }

        if(param.get("customerType") != null){
            sql.WHERE("customer_type = #{customerType}");
        }

        if(param.get("businessType") != null){
            sql.WHERE("business_type = #{businessType}");
        }

        if(param.get("endTimestamp") != null){
            sql.WHERE("version_timestamp <= #{endTimestamp}");
        }

        /*
        SELECT *
                FROM ivr_node_tree
        WHERE (ivr_flag = #{ivrFlag} AND customer_type = #{customerType} AND business_type = #{businessType} AND version_timestamp <= #{endTimestamp})  ORDER BY version_timestamp DESC limit 3
         */
        System.out.println("生成SQL:" + sql.toString() + "  ORDER BY version_timestamp DESC limit " + (Integer)param.get("count"));
        return sql.toString() + "  ORDER BY version_timestamp DESC limit " + (Integer)param.get("count");
    }
}

参考:

[MyBatis spring howto] https://zzyongx.github.io/blogs/mybatis-spring-howto.html




已标记关键词 清除标记
课程简介: 历经半个多月的时间,Debug亲自撸的 “企业员工角色权限管理平台” 终于完成了。正如字面意思,本课程讲解的是一个真正意义上的、企业级的项目实战,主要介绍了企业级应用系统中后端应用权限的管理,其中主要涵盖了六大核心业务模块、十几张数据库表。 其中的核心业务模块主要包括用户模块、部门模块、岗位模块、角色模块、菜单模块和系统日志模块;与此同时,Debug还亲自撸了额外的附属模块,包括字典管理模块、商品分类模块以及考勤管理模块等等,主要是为了更好地巩固相应的技术栈以及企业应用系统业务模块的开发流程! 核心技术栈列表: 值得介绍的是,本课程在技术栈层面涵盖了前端和后端的大部分常用技术,包括Spring Boot、Spring MVC、MybatisMybatis-Plus、Shiro(身份认证与资源授权跟会话等等)、Spring AOP、防止XSS攻击、防止SQL注入攻击、过滤器Filter、验证码Kaptcha、热部署插件Devtools、POI、Vue、LayUI、ElementUI、JQuery、HTML、Bootstrap、Freemarker、一键打包部署运行工具Wagon等等,如下图所示: 课程内容与收益: 总的来说,本课程是一门具有很强实践性质的“项目实战”课程,即“企业应用员工角色权限管理平台”,主要介绍了当前企业级应用系统中员工、部门、岗位、角色、权限、菜单以及其他实体模块的管理;其中,还重点讲解了如何基于Shiro的资源授权实现员工-角色-操作权限、员工-角色-数据权限的管理;在课程的最后,还介绍了如何实现一键打包上传部署运行项目等等。如下图所示为本权限管理平台的数据库设计图: 以下为项目整体的运行效果截图: 值得一提的是,在本课程中,Debug也向各位小伙伴介绍了如何在企业级应用系统业务模块的开发中,前端到后端再到数据库,最后再到服务器的上线部署运行等流程,如下图所示:
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页