Springboot+JdbcTemplate模拟SQL注入攻击案例及解决方法

说明

SQL注入是软件开发项目测试过程中必测项，重要等级极高。本文以springboot项目为例，模拟含有SQL注入攻击，并提供解决方法。部分内容整理自网络。

搭建项目

1.创建表tbuser

DROPTABLEIFEXISTS`tbuser`;CREATETABLE`tbuser`(`username`varchar(255)CHARACTERSET utf8 COLLATE utf8_unicode_ci NULLDEFAULTNULL)ENGINE=InnoDBCHARACTERSET= utf8 COLLATE= utf8_unicode_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of tbuser-- ----------------------------INSERTINTO`tbuser`VALUES('admin');INSERTINTO`tbuser`VALUES('zhangsan');INSERTINTO`tbuser`VALUES('lisi');

2.创建工程

• pom.xml

<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!-- fastjson --><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.47</version></dependency><dependency><groupId>commons-fileupload</groupId><artifactId>commons-fileupload</artifactId><version>1.2.1</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency></dependencies>

• application.yml

spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT&useSSL=falsepassword: root123
server:port:8081logging:level:org.springframework.jdbc.core.JdbcTemplate: DEBUG

• 实体类

publicclassUser{privateString name;publicStringgetName(){return name;}publicvoidsetName(String name){this.name = name;}}

• DAO接口实现类

publicinterfaceUserDao{publicList<User>findUser(String name);publicList<User>findUserSec(String name);}

@RepositorypublicclassUserDaoImplimplementsUserDao{@AutowiredprivateNamedParameterJdbcTemplate jdbcTemplate;/**
     * 字符串拼接方式，有注入漏洞
     * @param name
     * @return
     */@OverridepublicList<User>findUser(String name){List<User> myUserList=newArrayList<>();String sql="select * from tbuser where username ='"+name+"'";Map<String,Object> param =newHashMap<>();List<Map<String,Object>> mapList=newArrayList<>();
        mapList=jdbcTemplate.queryForList(sql,param);for(int i=0;i<mapList.size();i++){Map<String,Object> testmap= mapList.get(i);User myuser=newUser();
            myuser.setName((String) testmap.get("username"));
            myUserList.add(myuser);}return myUserList;}/**
     *  预编译方式，执行会报错
     * @param name
     * @return
     */@OverridepublicList<User>findUserSec(String name){List<User> myUserList=newArrayList<>();String sql="select * from tbuser where username =:name";Map<String,Object> param =newHashMap<>();
        param.put("name",name);List<Map<String,Object>> mapList=newArrayList<>();
        mapList=jdbcTemplate.queryForList(sql,param);for(int i=0;i<mapList.size();i++){Map<String,Object> testmap= mapList.get(i);User myuser=newUser();
            myuser.setName((String) testmap.get("username"));
            myUserList.add(myuser);}return myUserList;}}

• service接口实现

publicinterfaceUserService{publicList<User>findUser(String name);publicList<User>findUserSec(String name);}

@ServicepublicclassUserServiceImplimplementsUserService{@AutowiredprivateUserDao userDao;@OverridepublicList<User>findUser(String name){return userDao.findUser(name);}@OverridepublicList<User>findUserSec(String name){return userDao.findUserSec(name);}}

• controller

@RestControllerpublicclassUserController{@AutowiredprivateUserService userService;@PostMapping("/user")publicList<User>findUser(@RequestBodyUser user){return userService.findUser(user.getName());}@PostMapping("/usersec")publicList<User>findUserSec(@RequestBodyUser user){return userService.findUserSec(user.getName());}}

SQL注入测试

可以看到明明只查admin，拼接后返回了所有用户信息，造成用户信息泄露！！！

解决方法

方式1：绑定变量

采用预编译绑定变量方式，避免SQL拼接。

String sql="select * from tbuser where username =:name";Map<String,Object> param =newHashMap<>();
param.put("name",name);

方式2：全局过滤器

packagecom.demo.jdbcinject.config;importcom.alibaba.fastjson.JSONObject;importlombok.extern.slf4j.Slf4j;importorg.apache.tomcat.util.http.fileupload.servlet.ServletFileUpload;importorg.springframework.web.multipart.commons.CommonsMultipartResolver;importjavax.servlet.ReadListener;importjavax.servlet.ServletInputStream;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletRequestWrapper;importjava.io.*;importjava.nio.charset.StandardCharsets;importjava.util.Arrays;importjava.util.regex.Pattern;/**
 * @Author laoxu
 * @Date 2023/3/15 23:09
 * @Desc xxx
 */@Slf4jpublicclassXssHttpServletRequestWrapperextendsHttpServletRequestWrapper{/**
     * post请求体
     */privatebyte[] body;/**
     * 是否是文件上传
     */privateboolean fileUpload =false;/**
     * sql注入正则
     */privatestaticString badStrReg ="\\b(and|or)\\b.{1,6}?(=|>|<|\\bin\\b|\\blike\\b)|\\/\\*.+?\\*\\/|<\\s*script\\b|\\bEXEC\\b|UNION.+?SELECT|UPDATE.+?SET|INSERT\\s+INTO.+?VALUES|(SELECT|DELETE).+?FROM|(CREATE|ALTER|DROP|TRUNCATE)\\s+(TABLE|DATABASE)";/**
     * xss脚本正则
     */privatefinalstaticPattern[] scriptPatterns ={Pattern.compile("<script>(.*?)</script>",Pattern.CASE_INSENSITIVE),Pattern.compile("src[\r\n]*=[\r\n]*\\\'(.*?)\\\'",Pattern.CASE_INSENSITIVE |Pattern.MULTILINE |Pattern.DOTALL),Pattern.compile("</script>",Pattern.CASE_INSENSITIVE),Pattern.compile("<script(.*?)>",Pattern.CASE_INSENSITIVE |Pattern.MULTILINE |Pattern.DOTALL),Pattern.compile("eval\\((.*?)\\)",Pattern.CASE_INSENSITIVE |Pattern.MULTILINE |Pattern.DOTALL),Pattern.compile("expression\\((.*?)\\)",Pattern.CASE_INSENSITIVE |Pattern.MULTILINE |Pattern.DOTALL),Pattern.compile("javascript:",Pattern.CASE_INSENSITIVE),Pattern.compile("vbscript:",Pattern.CASE_INSENSITIVE),Pattern.compile("onload(.*?)=",Pattern.CASE_INSENSITIVE |Pattern.MULTILINE |Pattern.DOTALL)};publicXssHttpServletRequestWrapper(){super(null);}/**
     * 构造函数 - 获取post请求体
     * @param httpservletrequest
     * @throws IOException
     */publicXssHttpServletRequestWrapper(HttpServletRequest httpservletrequest)throwsIOException{super(httpservletrequest);String sessionStream =getBodyString(httpservletrequest);
        body = sessionStream.getBytes(StandardCharsets.UTF_8);}/**
     * 读取post请求体
     * @param httpservletrequest
     * @return
     * @throws IOException
     */privateStringgetBodyString(HttpServletRequest httpservletrequest)throwsIOException{StringBuilder sb =newStringBuilder();InputStream ins = httpservletrequest.getInputStream();boolean isMultipartContent =ServletFileUpload.isMultipartContent(httpservletrequest);CommonsMultipartResolver commonsMultipartResolver =newCommonsMultipartResolver(httpservletrequest.getSession().getServletContext());boolean isMultipart = commonsMultipartResolver.isMultipart(httpservletrequest);if(isMultipartContent || isMultipart){
            fileUpload =true;}try(BufferedReader isr =newBufferedReader(newInputStreamReader(ins,StandardCharsets.UTF_8));){String line ="";while((line = isr.readLine())!=null){
                sb.append(line);}}catch(IOException e){throw e;}return sb.toString();}/**
     * 过滤springmvc中的 @RequestParam 注解中的参数
     * @param s
     * @return
     */@OverridepublicString[]getParameterValues(String s){String[] str =super.getParameterValues(s);if(str ==null){returnnull;}int i = str.length;String[] as1 =newString[i];for(int j =0; j < i; j++){
            as1[j]=cleanXSS(cleanSQLInject(str[j]));}
        log.info("XssHttpServletRequestWrapper净化后的请求为：========== {}",Arrays.toString(as1));return as1;}/**
     * 过滤request.getParameter的参数
     * @param s
     * @return
     */@OverridepublicStringgetParameter(String s){String s1 =super.getParameter(s);if(s1 ==null){returnnull;}else{String s2 =cleanXSS(cleanSQLInject(s1));
            log.info("XssHttpServletRequestWrapper净化后的请求为：========== {}",  s2);return s2;}}/**
     * 过滤请求体 json 格式的
     * @return
     * @throws IOException
     */@OverridepublicServletInputStreamgetInputStream()throwsIOException{// 非文件上传进行过滤if(!fileUpload){// 获取body中的请求参数JSONObject json =JSONObject.parseObject(newString(body));// 校验并过滤xss攻击和sql注入for(String k : json.keySet()){cleanSQLInject(cleanXSS(json.getString(k)));}}// 将请求体参数流转 -- 流读取一次就会消失,所以我们事先读取之后就存在byte数组里边方便流转finalByteArrayInputStream bais =newByteArrayInputStream(body);returnnewServletInputStream(){@Overridepublicintread()throwsIOException{return bais.read();}@OverridepublicbooleanisFinished(){returnfalse;}@OverridepublicbooleanisReady(){returnfalse;}@OverridepublicvoidsetReadListener(ReadListener readListener){}};}/**
     * 清除xss
     * @param src 单个参数
     * @return
     */publicStringcleanXSS(String src){String temp = src;// 校验xss脚本for(Pattern pattern : scriptPatterns){
            temp = pattern.matcher(temp).replaceAll("");}// 校验xss特殊字符
        temp = temp.replaceAll("\0|\n|\r","");
        temp = temp.replaceAll("<","&lt;").replaceAll(">","&gt;");if(!temp.equals(src)){

            log.error("xss攻击检查：参数含有非法攻击字符，已禁止继续访问！！");
            log.error("原始输入信息-->"+ temp);thrownewRuntimeException("xss攻击检查：参数含有非法攻击字符，已禁止继续访问！！");}return src;}/**
     * 过滤sql注入 -- 需要增加通配，过滤大小写组合
     * @param src 单个参数值
     * @return
     */publicStringcleanSQLInject(String src){// 非法sql注入正则Pattern sqlPattern =Pattern.compile(badStrReg,Pattern.CASE_INSENSITIVE);if(sqlPattern.matcher(src.toLowerCase()).find()){
            log.error("sql注入检查：输入信息存在SQL攻击！");thrownewRuntimeException("sql注入检查：参数含有非法攻击字符，已禁止继续访问！！");}return src;}}

packagecom.demo.jdbcinject.config;importjavax.servlet.*;importjavax.servlet.annotation.WebFilter;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;/**
 * @Author laoxu
 * @Date 2023/3/15 23:22
 * @Desc xxx
 */@WebFilter(filterName ="xssFilter", urlPatterns ="/*", asyncSupported =true)publicclassXSSFilterimplementsFilter{/**
     * 忽略权限检查的url地址
     */privatefinalString[] excludeUrls =newString[]{"/login.html"};@OverridepublicvoiddoFilter(ServletRequest arg0,ServletResponse arg1,FilterChain arg2)throwsIOException,ServletException{HttpServletRequest req =(HttpServletRequest) arg0;HttpServletResponse response =(HttpServletResponse) arg1;//获取请求你ip后的全部路径String uri = req.getRequestURI();//跳过不需要的Xss校验的地址for(String str : excludeUrls){if(uri.contains(str)){
                arg2.doFilter(arg0, response);return;}}//注入xss过滤器实例XssHttpServletRequestWrapper reqW =newXssHttpServletRequestWrapper(req);//过滤
        arg2.doFilter(reqW, response);}@Overridepublicvoiddestroy(){}@Overridepublicvoidinit(FilterConfig filterConfig1)throwsServletException{}}

• 启动类注解

@SpringBootApplication@ServletComponentScan(basePackages ={"com.demo.jdbcinject.config"})publicclassWebApplication{publicstaticvoidmain(String[] args){SpringApplication.run(WebApplication.class, args);}}

采用预编译接口测试

可以看到无论前端传入什么参数，后端采用绑定变量方式不会返回任何内容。

采用全局过滤器方式后测试

添加过滤器后再次访问，直接报错！！！