mybatis注解动态sql注入map和list方式(防sql注入攻击)

219 阅读 0 评论 145 点赞

我是靠谱客的博主还单身毛豆，这篇文章主要介绍mybatis注解动态sql注入map和list方式(防sql注入攻击)，现在分享给大家，希望可以做个参考。

网上的教程

配置xml
注解中写xml脚本@Select()
使用Java类中的Java方法拼写sql语句(不防sql注入攻击的纯字符串拼接)

我的教程(防sql注入攻击)

注入Map

Mapper层代码

复制代码

1
2
3
4
5
@Repository
public interface ManageMapper { 
 @SelectProvider(type = ManageProvider.class, method = "queryDevices")
 List<Map<String, Object>> queryDevices(@Param("devicetypeno") String devicetypeno, @Param("map") Map<String, Object> map);
}

Service层代码

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
@Service("manageService")
public class ManageServiceImpl implements ManageService {
 
 @Resource
 private ManageMapper manageMapper;
 @Override
 public List<Map<String, Object>> queryDevices(String devicetypeno) {
  HashMap<String, Object> map = new HashMap<>();
  map.put("1-1", "1800");
  map.put("1-2", "1854");
  return manageMapper.queryDevices(devicetypeno, map);
 }
}

SqlProvider代码

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
public class ManageProvider {
 
 public String queryDevices() {
  String sql = new SQL()
    .SELECT("TERMINALNUM, ORGCODE, DEVICETYPENO, DEVICENAME")
    .FROM("S_DEVICE_INFO")
    .WHERE("DEVICETYPENO = #{devicetypeno}")
    .WHERE("ORGCODE IN (#{map.1-1}, #{map.1-2})")
    .toString();
  return sql;
 }
}

注入List

Mapper层代码

复制代码

1
2
3
4
5
@Repository
public interface ManageMapper {
 @SelectProvider(type = ManageProvider.class, method = "queryDevices")
 List<Map<String, Object>> queryDevices(@Param("devicetypeno") String devicetypeno, @Param("list") List<Object> list);
}

Service层代码

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
@Service("manageService")
public class ManageServiceImpl implements ManageService { 
 @Resource
 private ManageMapper manageMapper;
 @Override
 public List<Map<String, Object>> queryDevices(String devicetypeno) {
  ArrayList<Object> list = new ArrayList<>();
  list.add("1800");
  list.add("1854");
  return manageMapper.queryDevices(devicetypeno, list);
 }
}

SqlProvider代码

复制代码

public class ManageProvider {
 public String queryDevices(Map<String, Object> params) {
//  String sql = new SQL()
//    .SELECT("TERMINALNUM, ORGCODE, DEVICETYPENO, DEVICENAME")
//    .FROM("S_DEVICE_INFO")
//    .WHERE("DEVICETYPENO = #{devicetypeno}")
//    .WHERE("ORGCODE IN (#{list[0]}, #{list[1]})")
//    .toString();
//  return sql;
 
  @SuppressWarnings("unchecked")
  List<Object> list = (List<Object>) params.get("list");
  StringBuilder inBuilder = new StringBuilder();
  for (int i = 0, size = list.size(); i < size; i++) {
   if (i == 0) {
    inBuilder.append("(").append("#{list[").append(i).append("]}");
   } else if (i == size - 1) {
    inBuilder.append(", ").append("#{list[").append(i).append("]}").append(")");
   } else {
    inBuilder.append(", ").append("#{list[").append(i).append("]}");
   }
  }
  SQL sql = new SQL()
     .SELECT("TERMINALNUM, ORGCODE, DEVICETYPENO, DEVICENAME")
     .FROM("S_DEVICE_INFO")
     .WHERE("DEVICETYPENO = #{devicetypeno}");
  if (inBuilder.length() > 0) {
   sql.WHERE("ORGCODE IN " + inBuilder);
  }
  return sql.toString();
 }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
public class ManageProvider {
 public String queryDevices(Map<String, Object> params) {
//  String sql = new SQL()
//    .SELECT("TERMINALNUM, ORGCODE, DEVICETYPENO, DEVICENAME")
//    .FROM("S_DEVICE_INFO")
//    .WHERE("DEVICETYPENO = #{devicetypeno}")
//    .WHERE("ORGCODE IN (#{list[0]}, #{list[1]})")
//    .toString();
//  return sql;
 
  @SuppressWarnings("unchecked")
  List<Object> list = (List<Object>) params.get("list");
  StringBuilder inBuilder = new StringBuilder();
  for (int i = 0, size = list.size(); i < size; i++) {
   if (i == 0) {
    inBuilder.append("(").append("#{list[").append(i).append("]}");
   } else if (i == size - 1) {
    inBuilder.append(", ").append("#{list[").append(i).append("]}").append(")");
   } else {
    inBuilder.append(", ").append("#{list[").append(i).append("]}");
   }
  }
  SQL sql = new SQL()
     .SELECT("TERMINALNUM, ORGCODE, DEVICETYPENO, DEVICENAME")
     .FROM("S_DEVICE_INFO")
     .WHERE("DEVICETYPENO = #{devicetypeno}");
  if (inBuilder.length() > 0) {
   sql.WHERE("ORGCODE IN " + inBuilder);
  }
  return sql.toString();
 }
}

封装foreach

像xml foreach标签一样使用foreach方法

请看mybatis注解动态sql中foreach工具方法

mybatis防止sql注入的循环map写法

复制代码

1
2
3
4
5
<foreach collection="condition.keys" item="k" separator="and"> 
<if test="null != condition[k]"> 
${k} = #{condition[${k}]}
</if>
</foreach>

以上为个人经验，希望能给大家一个参考，也希望大家多多支持靠谱客。