Java数据库的连接方式大致有三种:JDBC,Mybatis,和Hibernate
JDBC 利用Tomcat编写的测试代码如下
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 33 34 35 36 37 38 39 40 package servlet;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.sql.*;@WebServlet(value = "/demo",name = "123") public class Sql extends HttpServlet { @Override protected void doGet (HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { System.out.println("get访问" ); String id = req.getParameter("id" ); Connection conn = null ; try { Class.forName("com.mysql.jdbc.Driver" ); conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/security" , "root" , "root" ); String sql = "select * from users where id = '" +id+"' " ; Statement statement = conn.createStatement(); ResultSet resultSet = statement.executeQuery(sql); System.out.println(resultSet); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } @Override protected void doPost (HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this .doGet(req,resp); } }
注意需要将相应的mysql-connector的jar包导入到lib目录下
该段代码的功能为获取id参数,并将其拼接到sql查询语句,由于没做任何过滤,便可能产生sql注入
注入方法和普通的sql注入一样
1 1%27%20union%20select%20123,1,1%23
但好像用sqlmap跑不出来,会有访问次数的限制
1 2 3 在实际运用当中如果不采用框架使用JDBC的方式,普遍会编写一个工具类来完成这些繁琐的配置,但是具体的实现还是调用这些方法来进行实现,只是进行了一个简单的封装。 在代码审计的时候,如果看到是JDBC的方式进行连接可以跟踪一下他的代码,看他有没有调用自己定义的过滤方法,如果没有的话,就会存在sql注入,当然这是在未使用预编译的情况下。
Like型注入:
1 2 String name = req.getParameter("name" );String sql = "select * from users where name like '%'+name+'%'" ;
Header注入:
1 2 String referer = req.getHeader("referer" );String sql = "update user set referer ='" +referer+"'" ;
易产生的漏洞点
未使用占用符
PreparedStatement只有在使用”?”作为占位符才能预防sql注入,直接拼接仍会存在sql注入漏洞
使用in语句
删除语句中可能会存在此类语句,由于无法确定delIds含有对象个数而直接拼接sql语句,造成sql注入。
1 String sql = "delete from users where id in(" +delIds+"); //存在sql注入
解决方法为遍历传入的对象个数,使用“?”占位符。
使用like语句
使用like语句直接拼接会造成sql注入
1 String sql = "select * from users where password like '%" + con + "%'" ;
%和_
没有手动过滤%。预编译是不能处理这个符号的, 所以需要手动过滤,否则会造成慢查询,造成 dos。
Order by 、from 等无法预编译
通过上面对使用in关键字和like关键字发现,只需要对要传参的位置使用占位符进行预编译时似乎就可以完全防止SQL注入,然而事实并非如此,当使用order by语句时是无法使用预编译的,原因是order by子句后面需要加字段名或者字段位置,而字段名是不能带引号的,否则就会被认为是一个字符串而不是字段名,然而使用PreapareStatement将会强制给参数加上’,所以,在使用order by语句时就必须得使用拼接的Statement,所以就会造成SQL注入,需要进行手动过滤,否则存在sql注入。
String sql = "Select * from news where title =?" + "order by '" + time + "' asc"
防范:JDBC预编译 预编译的定义其实就是使用问号先来占位,后面再传入具体的值。 后面传值的时候,程序会把传入的参数,自动转换为spring类型的字符,并不会拼接成sql语句生效。
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 33 34 35 36 37 38 39 40 41 42 43 package servlet;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.sql.*;@WebServlet(value = "/demo",name = "123") public class Sql extends HttpServlet { @Override protected void doGet (HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { System.out.println("get访问" ); String id = req.getParameter("id" ); Connection conn = null ; try { Class.forName("com.mysql.jdbc.Driver" ); conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/security" , "root" , "root" ); String sql = "select * from users where id = ?" ; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1 ,id); ResultSet rs = pstmt.executeQuery(); System.out.println(rs); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } @Override protected void doPost (HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this .doGet(req,resp); } }
传入id=1''
,发现不会报错,正常解析
Mybatis 注入 Mybatis获取值的方式有两种,分别是${}
和 #{}
。
1 2 #{}:解析的是占位符问号,可以防止SQL注入,使用了预编译。 ${}:直接获取值
在Mybatis里面一般会采用#{}
来进行取值,但是也会有特殊情况。
两者的区别如下:
#{}
为参数占位符 ?
,即 SQL 预编译。${}
为字符串替换,即 SQL 拼接。
#{}
是“动态解析->预编译->执行”的过程。${}
是“动态解析->编译->执行”的过程。
#{}
的变量替换是在 DBMS 中。${}
的变量替换是在 DBMS 外。
变量替换后,#{}
对应的变量自动加上引号。变量替换后,${}
对应的变量不会加上引号。
环境结构如下:
Mapper.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 <?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 ="main.java.Mybatis.security" > <select id ="GetUserByID" parameterType ="int" resultType ="security" > select * from users where id = #{id} </select > </mapper >
config.xml
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 33 34 35 36 37 38 39 40 41 42 43 44 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd" > <configuration > <environments default ="mysql" > <environment id ="mysql" > <transactionManager type ="JDBC" /> <dataSource type ="POOLED" > <property name ="driver" value ="com.mysql.jdbc.Driver" /> <property name ="url" value ="jdbc:mysql://127.0.0.1:3306/security" /> <property name ="username" value ="root" /> <property name ="password" value ="root" /> </dataSource > </environment > </environments > <mappers > <mapper resource ="Mapper.xml" /> </mappers > </configuration >
security.java
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 public class security { private int id; private String name; private String email; public security (int id, String name, String email) { this .id = id; this .name = name; this .email = email; } public int getId () { return id; } public String getName () { return name; } public String getEmail () { return email; } public void setId (int id) { this .id = id; } public void setName (String name) { this .name = name; } public void setEmail (String email) { this .email = email; } @Override public String toString () { return "Person{" + "id=" + id + ", name='" + name + '\'' + ", email='" + email + '\'' + '}' ; } }
Test.java
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 33 34 35 36 37 import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import java.io.IOException;import java.io.InputStream;public class Test { public static void main (String[] args) throws IOException { String resource = "config.xml" ; InputStream is = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder ().build(is); SqlSession session = sqlSessionFactory.openSession(); String Statement = "main.java.Mybatis.security.GetUserByID" ; security se = session.selectOne(Statement,1 ); System.out.println(se); } }
运行结果:
漏洞测试 Mybatis 框架下易产生 SQL 注入漏洞的情况主要有以下三种:
模糊查询
在模糊查询场景下,考虑安全编码规范,使用 #{}
传入参数:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 <?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 ="main.java.Mybatis.security" > <select id ="GetUserByID" parameterType ="int" resultType ="security" > select * from users where id like '%#{username}%' </select > </mapper >
这种情况下由于安全编码会出现报错(使用concat函数解决,concat('%',#{_parameter}, '%')
)
某些程序可能会因此将#改为$,如果此时还未对输入内容进行过滤,那么就会造成SQL注入漏洞
注意此时的Mapper.xml文件必须这样编写(整了一个小时,气死了真的)
1 2 3 4 5 6 7 8 9 10 11 12 13 <?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 ="security" > <select id ="GetUserByID" parameterType ="int" resultType ="security" > select * from users where id like '%${value}%' </select > </mapper >
再次运行Test.java文件,报错消失,正常显示
尝试引发SQL报错
带有IN谓词的查询
同样,在IN语句中使用#{}可能会出现编码安全问题,正确用法为使用foreach语句(id in<foreach collection="ids" item="item" open="("separatosr="," close=")">#{ids} </foreach>
)
Mapper.xml
1 2 3 <select id ="GetUserByID" parameterType ="String" resultType ="security" > select * from users where name in (${value}) </select >
Order by语句
和JDBC一样,使用#{}预编译会发生报错,需要进行充分的过滤
Mybatis-Plus MyBatis-Plus (opens new window) (简称 MP)是一个 MyBatis (opens new window) 的增强工具,在 MyBatis 的基础上只做增强不做改变,为简化开发、提高效率而生。
环境确实有点难搞,最终项目如下:
apply注入 apply
函数用于在SQL查询语句中添加原生SQL片段。这个函数可以用于在生成的SQL语句中插入一些自定义的SQL代码。
1 2 apply(String applySql, Object... params) apply(boolean condition, String applySql, Object... params)
拼接sql
注:该方法可用于数据库函数 动态入参的params
对应前面applySql
内部的{index}
部分.这样是不会有sql注入风险的,反之会有!
示例:
apply()直接拼接sql语句存在sql注入
测试类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 package com.baomidou.mybatisplus.samples.quickstart.controller;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.baomidou.mybatisplus.samples.quickstart.entity.SysUser;import com.baomidou.mybatisplus.samples.quickstart.mapper.SysUserMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;@Controller public class applyvuln { @Autowired private SysUserMapper sysUserMapper; @RequestMapping("/applyvuln") public SysUser applyvuln (String name, String email) { QueryWrapper<SysUser> wrapper = new QueryWrapper <>(); wrapper.eq("name" , name).apply("email=" + email); SysUser sysUser = sysUserMapper.selectOne(wrapper); return sysUser; } }
传入?name=Jack&email=1%27,出现报错:
相反,我们使用使用{index}params进行预编译处理,不存在sql注入
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 package com.baomidou.mybatisplus.samples.quickstart.controller;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.baomidou.mybatisplus.samples.quickstart.entity.SysUser;import com.baomidou.mybatisplus.samples.quickstart.mapper.SysUserMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;@Controller public class applyvuln { @Autowired private SysUserMapper sysUserMapper; @RequestMapping("/applyvuln") public SysUser applyvuln (String name, String email) { QueryWrapper<SysUser> wrapper = new QueryWrapper <>(); wrapper.eq("name" , name).apply("email={0}" , email); SysUser sysUser = sysUserMapper.selectOne(wrapper); return sysUser; } }
进行预编译,正常运行程序,无报错
last 1 2 last(String lastSql) last(boolean condition, String lastSql)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 package com.baomidou.mybatisplus.samples.quickstart.controller;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.baomidou.mybatisplus.samples.quickstart.entity.SysUser;import com.baomidou.mybatisplus.samples.quickstart.mapper.SysUserMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import java.util.List;@Controller public class lastvuln { @Autowired private SysUserMapper sysUserMapper; @RequestMapping("/lastvuln") public List<SysUser> applyvuln (String column) { QueryWrapper<SysUser> wrapper = new QueryWrapper <>(); wrapper.last("order by " + column); List<SysUser> list = sysUserMapper.selectList(wrapper); return list; } }
传入?column=1’,出现报错
exists/notExists 1 2 3 4 exists(String existsSql) exists(boolean condition, String existsSql) notExists(String notExistsSql) notExists(boolean condition, String notExistsSql)
拼接EXISTX / NOT EXISTS ( sql语句 )
注:若existsSql或notExistsSql中有关内容用户可控,则存在sql注入风险
1 2 3 4 5 6 7 @RequestMapping("/exsistvuln") public List<SysUser> applyvuln (String column) { QueryWrapper<SysUser> wrapper = new QueryWrapper <>(); wrapper.exists("select title from tutorials where title = " + title); List<SysUser> list = sysUserMapper.selectList(wrapper); return list; }
having 1 2 having(String sqlHaving, Object... params) having(boolean condition, String sqlHaving, Object... params)
HAVING ( sql语句 ),用于Having查询,一般用配合groupby在对分组统计函数进行过滤的场景中
注:与apply一样,动态入参的params对应前面applySql内部的{index}部分,可以进行预编译防止SQL注入问题。
示例:
1 2 3 4 5 6 7 @RequestMapping("/exsistvuln") public List<SysUser> applyvuln (String column) { QueryWrapper<SysUser> wrapper = new QueryWrapper <>(); wrapper.select().groupBy("author" ).having("id > " +id); List<SysUser> list = sysUserMapper.selectList(wrapper); return list; }
order by
1 orderBy(boolean condition, boolean isAsc, R... columns)
1 2 orderByAsc(R... columns) orderByAsc(boolean condition, R... columns)
1 2 orderByDesc(R... columns) orderByDesc(boolean condition, R... columns)
排序:ORDER BY 字段, (ASC/DESC)
注:Order by排序时不能进行预编译处理,故相关内容用户可控的话会存在sql注入风险。
示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 @RequestMapping("/orderbyvuln") public List<Tutorial> mybatishorderbyvuln (String column) { QueryWrapper<Tutorial> wrapper = new QueryWrapper <>(); wrapper.select().orderBy(true , true , column); List<Tutorial> list = tutorialMapper.selectList(wrapper); return list; } @RequestMapping("/orderbyAscvuln") public List<Tutorial> mybatishorderbyAscvuln (String column) { QueryWrapper<Tutorial> wrapper = new QueryWrapper <>(); wrapper.select().orderByAsc(column); List<Tutorial> list = tutorialMapper.selectList(wrapper); return list; } @RequestMapping("/orderbyDescvuln") public List<Tutorial> mybatishorderbyDescvuln (String column) { QueryWrapper<Tutorial> wrapper = new QueryWrapper <>(); wrapper.select().orderByDesc(column); List<Tutorial> list = tutorialMapper.selectList(wrapper); return list; }
group By 1 2 groupBy(R... columns) groupBy(boolean condition, R... columns)
分组:GROUP BY 字段, … 主要用于用于结合聚合函数,根据一个或多个列对结果集进行分组
示例:
1 2 3 4 5 6 7 @RequestMapping("/gropbycvuln") public List<Tutorial> mybatishsgropbycvuln (String column) { QueryWrapper<Tutorial> wrapper = new QueryWrapper <>(); wrapper.select().groupBy(column); List<Tutorial> list = tutorialMapper.selectList(wrapper); return list; }
insql/notinsql 子查询
1 2 3 4 inSql(R column, String inValue) inSql(boolean condition, R column, String inValue) notInSql(R column, String inValue) notInSql(boolean condition, R column, String inValue)
字段IN / NOT IN ( sql语句 )
column字段、inValue字段可控的情况下存在注入风险。
示例:
1 2 3 4 5 6 7 @RequestMapping("/insqlcvuln") public List<Tutorial> mybatisinsqlvuln (String column,String id) { QueryWrapper<Tutorial> wrapper = new QueryWrapper <>(); wrapper.select().inSql(column,"select id from tutorials where id >" + id); List<Tutorial> list = tutorialMapper.selectList(wrapper); return list; }
特殊的Wrapper模式 Wrapper提供了自定义SQL场景,与传统的mybatis一样使用$进行注解,但实际上ew已经做了预编译处理。同样的也支持注解&xml配置。
注:需要mybatis-plus版本 >= 3.0.7 param 参数名要么叫ew,要么加上注解@Param(Constants.WRAPPER) 使用${ew.customSqlSegment} 不支持 Wrapper 内的entity生成where语句
示例:
1.注解模式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 import com.baomidou.mybatisplus.core.conditions.Wrapper;import com.baomidou.mybatisplus.core.toolkit.Constants;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import org.joychou.dao.Person;import java.util.List;@Mapper public interface PersonMapper { @Select("select * from persons ${ew.customSqlSegment}") List<Person> selectPerson (@Param(Constants.WRAPPER) Wrapper wrapper) ; }
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 33 34 import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import org.joychou.dao.Person;import org.joychou.dao.Tutorial;import org.joychou.mapper.PersonMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import java.util.List;@RestController @RequestMapping("/sqlimybatis_plus") public class Mybatis_Plus_SQLI_2 { @Autowired PersonMapper personMapper; @RequestMapping("/selectperson") public List<Person> selectPerson (String column) { QueryWrapper<Object> wrapper = new QueryWrapper <>(); wrapper.orderByAsc(column); return personMapper.selectPerson(wrapper); } @RequestMapping("/selectpersonlike") public List<Person> selectPersonlike (String name) { QueryWrapper<Object> wrapper = new QueryWrapper <>(); wrapper.like("name" , name); return personMapper.selectPerson(wrapper); } }
Wrapper自定义模式下like、in等会自动进行预编译,但若存在last、orderby等未进行预编译方法,若相应数据用户可控仍会存在SQL注入风险,风险API仍为2.1中常见注入场景。
2.XML模式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 import com.baomidou.mybatisplus.core.conditions.Wrapper;import com.baomidou.mybatisplus.core.toolkit.Constants;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import org.joychou.dao.Person;import java.util.List;@Mapper public interface PersonMapper { @Select("select * from persons ${ew.customSqlSegment}") List<Person> selectPerson (@Param(Constants.WRAPPER) Wrapper wrapper) ; List<Person> selectPersonXML1 (Wrapper ew) ; List<Person> selectPersonXML2 (Wrapper ew) ; List<Person> selectPersonXMLOrdeyBy (Wrapper ew) ; }
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 <?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 ="org.joychou.mapper.UserMapper" > <resultMap type ="org.joychou.dao.Person" id ="Person" > <id column ="id" property ="id" javaType ="java.lang.Integer" jdbcType ="NUMERIC" /> <id column ="name" property ="name" javaType ="java.lang.String" jdbcType ="VARCHAR" /> <id column ="address" property ="address" javaType ="java.lang.String" jdbcType ="VARCHAR" /> <id column ="city" property ="city" javaType ="java.lang.String" jdbcType ="VARCHAR" /> </resultMap > <select id ="selectPersonXML1" resultMap ="Person" > select * from persons where ${ew.customSqlSegment} </select > <select id ="selectPersonXML2" resultMap ="Person" > select * from persons <where > ${ew.SqlSegment} </where > </select > <select id ="selectPersonXMLOrdeyBy" resultMap ="Person" > select * from persons ${ew.SqlSegment} </select > </mapper >
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import org.joychou.dao.Person;import org.joychou.dao.Tutorial;import org.joychou.mapper.PersonMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import java.util.List;@RestController @RequestMapping("/sqlimybatis_plus") public class Mybatis_Plus_SQLI_2 { @Autowired PersonMapper personMapper; @RequestMapping("/selectperson") public List<Person> selectPerson (String column) { QueryWrapper<Object> wrapper = new QueryWrapper <>(); wrapper.orderByAsc(column); return personMapper.selectPerson(wrapper); } @RequestMapping("/selectperson1") public List<Person> selectPersonXML1 (String name) { QueryWrapper<Object> wrapper = new QueryWrapper <>(); wrapper.eq("name" , name); return personMapper.selectPerson(wrapper); } @RequestMapping("/selectperson2") public List<Person> selectPersonXML2 (String name) { QueryWrapper<Object> wrapper = new QueryWrapper <>(); wrapper.eq("name" , name); return personMapper.selectPerson(wrapper); } @RequestMapping("/selectperson3") public List<Person> selectPersonXMLOrderBy (String column) { QueryWrapper<Object> wrapper = new QueryWrapper <>(); wrapper.orderByAsc(column); return personMapper.selectPerson(wrapper); } }
以上selectPersonXML1、selectPersonXML2均实现相同功能,SQL注入问题与注解模式相同。
特殊插件 1.PaginationInnerInterceptor
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 import com.baomidou.mybatisplus.annotation.DbType;import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;@Configuration public class MybatisPlusConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor () { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor (); PaginationInnerInterceptor pageInterceptor = new PaginationInnerInterceptor (); pageInterceptor.setOverflow(false ); pageInterceptor.setMaxLimit(500L ); pageInterceptor.setDbType(DbType.MYSQL); interceptor.addInnerInterceptor(pageInterceptor); return interceptor; } }
1 2 3 4 5 6 7 8 9 10 @RequestMapping("/selectpage") public List<Person> mybatispluspage (Long page,Long size,String order) { QueryWrapper<Person> qw = new QueryWrapper <>(); Page<Person> personPage = new Page <>(page,size); personPage.addOrder(OrderItem.asc(order)); IPage<Person> iPage = personMapper.selectPage(personPage, qw); List<Person> persons = iPage.getRecords(); return persons; }
若直接使用addOrder()未进行过滤,则存在SQL注入漏洞。
2.pagehelper
因为Order by排序时不能进行预编译处理,所以在使用插件时需要额外注意如下function,同样会存在SQL注入风险:
com.github.pagehelper.Page
主要是setOrderBy(java.lang.String)方法
com.github.pagehelper.page.PageMethod
主要是startPage(int,int,java.lang.String)方法
com.github.pagehelper.PageHelper
主要是startPage(int,int,java.lang.String)方法
Hibernate是一个开放源代码的对象关系映射框架,它对JDBC进行了非常轻量级的对象封装,使得Java程序员可以随心所欲的使用对象编程思维来操纵数据库。
Hibernate可以使用hql来执行SQL语句,也可以直接执行SQL语句,无论是哪种方式都有可能导致SQL注入
Hibernate框架下的SQL注入 Hibernate是一个开放源代码的对象关系映射框架,它对JDBC进行了非常轻量级的对象封装,使得Java程序员可以随心所欲的使用对象编程思维来操纵数据库。
Hibernate可以使用hql来执行SQL语句,也可以直接执行SQL语句,无论是哪种方式都有可能导致SQL注入
hql语句:
1 String hql = "from People where username = '" + username + "' and password = '" + password + "'";
这种拼接方式存在SQL注入
正确使用以下几种HQL参数绑定的方式可以有效避免注入的产生:
1.命名参数(named parameter) 1 2 3 4 Query<User> query = session.createQuery("from users name = ?1", User.class); String parameter = "g1ts"; Query<User> query = session.createQuery("from users name = :name", User.class); query.setParameter("name", parameter);
2.位置参数(Positional parameter) 1 2 3 String parameter = "g1ts"; Query<User> query = session.createQuery("from users name = ?1", User.class); query.setParameter(1, parameter);
3.命名参数列表(named parameter list) 1 2 3 List<String> names = Arrays.asList("g1ts", "g2ts"); Query<User> query = session.createQuery("from users where name in (:names)", User.class); query.setParameter("names", names);
4.类实例(JavaBean) 1 2 3 user1.setName("g1ts"); Query<User> query = session.createQuery("from users where name =:name", User.class); query.setProperties(user1);
5.HQL拼接方法 这种方式是最常用,而且容易忽视且容易被注入的,通常做法就是对参数的特殊字符进行过滤,推荐大家使用 Spring工具包的StringEscapeUtils.escapeSql()方法对参数进行过滤:
1 2 3 4 5 import org.apache.commons.lang.StringEscapeUtils; public static void main(String[] args) { String str = StringEscapeUtils.escapeSql("'"); System.out.println(str); }
SQL
Hibernate支持使用原生SQL语句执行,所以其风险和JDBC是一致的,直接使用拼接的方法时会导致SQL注入
语句如下:
1 Query<People> query = session.createNativeQuery("select * from user where username = '" + username + "' and password = '" + password + "'");
正确写法:
1 2 3 String parameter = "g1ts"; Query<User> query = session.createNativeQuery("select * from user where name = :name"); query.setParameter("name",parameter);