Springboot+JdbcTemplate模拟SQL注入攻击案例及解决方法
说明
SQL注入是软件开发项目测试过程中必测项，重要等级极高。本文以springboot项目为例，模拟含有SQL注入攻击，并提供解决方法。部分内容整理自网络。
搭建项目
1.创建表tbuser
DROP TABLE IF EXISTS `tbuser`;
CREATE TABLE `tbuser` (
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tbuser
-- ----------------------------
INSERT INTO `tbuser` VALUES ('admin');
INSERT INTO `tbuser` VALUES ('zhangsan');
INSERT INTO `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=false
password: root123
server:
port: 8081
logging:
level:
org.springframework.jdbc.core.JdbcTemplate: DEBUG
- 实体类
public class User {
private String name;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
- DAO接口实现类
public interface UserDao {
public List<User> findUser(String name);
public List<User> findUserSec(String name);
}
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private NamedParameterJdbcTemplate jdbcTemplate;
/**
* 字符串拼接方式，有注入漏洞
* @param name
* @return
*/
@Override
public List<User> findUser(String name) {
List<User> myUserList= new ArrayList<>();
String sql="select * from tbuser where username ='"+name+"'";
Map<String, Object> param = new HashMap<>();
List<Map<String, Object>> mapList=new ArrayList<>();
mapList=jdbcTemplate.queryForList(sql,param);
for(int i=0;i<mapList.size();i++){
Map<String,Object> testmap= mapList.get(i);
User myuser=new User();
myuser.setName((String) testmap.get("username"));
myUserList.add(myuser);
}
return myUserList;
}
/**
* 预编译方式，执行会报错
* @param name
* @return
*/
@Override
public List<User> findUserSec(String name) {
List<User> myUserList= new ArrayList<>();
String sql="select * from tbuser where username =:name";
Map<String, Object> param = new HashMap<>();
param.put("name",name);
List<Map<String, Object>> mapList=new ArrayList<>();
mapList=jdbcTemplate.queryForList(sql,param);
for(int i=0;i<mapList.size();i++){
Map<String,Object> testmap= mapList.get(i);
User myuser=new User();
myuser.setName((String) testmap.get("username"));
myUserList.add(myuser);
}
return myUserList;
}
}
- service接口实现
public interface UserService {
public List<User> findUser(String name);
public List<User> findUserSec(String name);
}
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
@Override
public List<User> findUser(String name) {
return userDao.findUser(name);
}
@Override
public List<User> findUserSec(String name) {
return userDao.findUserSec(name);
}
}
- controller
@RestController
public class UserController {
@Autowired
private UserService userService;
@PostMapping("/user")
public List<User> findUser(@RequestBody User user){
return userService.findUser(user.getName());
}
@PostMapping("/usersec")
public List<User> findUserSec(@RequestBody User user){
return userService.findUserSec(user.getName());
}
}
SQL注入测试
可以看到明明只查admin，拼接后返回了所有用户信息，造成用户信息泄露！！！
解决方法
方式1：绑定变量
采用预编译绑定变量方式，避免SQL拼接。
String sql="select * from tbuser where username =:name";
Map<String, Object> param = new HashMap<>();
param.put("name",name);
方式2：全局过滤器
package com.demo.jdbcinject.config;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.tomcat.util.http.fileupload.servlet.ServletFileUpload;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;
import javax.servlet.ReadListener;
import javax.servlet.ServletInputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletRequestWrapper;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.Arrays;
import java.util.regex.Pattern;
/**
* @Author laoxu
* @Date 2023/3/15 23:09
* @Desc xxx
*/
@Slf4j
public class XssHttpServletRequestWrapper extends HttpServletRequestWrapper {
/**
* post请求体
*/
private byte[] body;
/**
* 是否是文件上传
*/
private boolean fileUpload = false;
/**
* sql注入正则
*/
private static String 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脚本正则
*/
private final static Pattern[] 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)
};
public XssHttpServletRequestWrapper() {
super(null);
}
/**
* 构造函数 - 获取post请求体
* @param httpservletrequest
* @throws IOException
*/
public XssHttpServletRequestWrapper(HttpServletRequest httpservletrequest) throws IOException {
super(httpservletrequest);
String sessionStream = getBodyString(httpservletrequest);
body = sessionStream.getBytes(StandardCharsets.UTF_8);
}
/**
* 读取post请求体
* @param httpservletrequest
* @return
* @throws IOException
*/
private String getBodyString(HttpServletRequest httpservletrequest) throws IOException {
StringBuilder sb = new StringBuilder();
InputStream ins = httpservletrequest.getInputStream();
boolean isMultipartContent = ServletFileUpload.isMultipartContent(httpservletrequest);
CommonsMultipartResolver commonsMultipartResolver = new CommonsMultipartResolver(httpservletrequest.getSession().getServletContext());
boolean isMultipart = commonsMultipartResolver.isMultipart(httpservletrequest);
if (isMultipartContent || isMultipart) {
fileUpload = true;
}
try (BufferedReader isr = new BufferedReader(new InputStreamReader(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
*/
@Override
public String[] getParameterValues(String s) {
String[] str = super.getParameterValues(s);
if (str == null) {
return null;
}
int i = str.length;
String[] as1 = new String[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
*/
@Override
public String getParameter(String s) {
String s1 = super.getParameter(s);
if (s1 == null) {
return null;
} else {
String s2 = cleanXSS(cleanSQLInject(s1));
log.info("XssHttpServletRequestWrapper净化后的请求为：========== {}", s2);
return s2;
}
}
/**
* 过滤请求体 json 格式的
* @return
* @throws IOException
*/
@Override
public ServletInputStream getInputStream() throws IOException {
// 非文件上传进行过滤
if (!fileUpload) {
// 获取body中的请求参数
JSONObject json = JSONObject.parseObject(new String(body));
// 校验并过滤xss攻击和sql注入
for (String k : json.keySet()) {
cleanSQLInject(cleanXSS(json.getString(k)));
}
}
// 将请求体参数流转 -- 流读取一次就会消失,所以我们事先读取之后就存在byte数组里边方便流转
final ByteArrayInputStream bais = new ByteArrayInputStream(body);
return new ServletInputStream() {
@Override
public int read() throws IOException {
return bais.read();
}
@Override
public boolean isFinished() {
return false;
}
@Override
public boolean isReady() {
return false;
}
@Override
public void setReadListener(ReadListener readListener) {
}
};
}
/**
* 清除xss
* @param src 单个参数
* @return
*/
public String cleanXSS(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("<", "<").replaceAll(">", ">");
if (!temp.equals(src)) {
log.error("xss攻击检查：参数含有非法攻击字符，已禁止继续访问！！");
log.error("原始输入信息-->" + temp);
throw new RuntimeException("xss攻击检查：参数含有非法攻击字符，已禁止继续访问！！");
}
return src;
}
/**
* 过滤sql注入 -- 需要增加通配，过滤大小写组合
* @param src 单个参数值
* @return
*/
public String cleanSQLInject(String src) {
// 非法sql注入正则
Pattern sqlPattern = Pattern.compile(badStrReg, Pattern.CASE_INSENSITIVE);
if (sqlPattern.matcher(src.toLowerCase()).find()) {
log.error("sql注入检查：输入信息存在SQL攻击！");
throw new RuntimeException("sql注入检查：参数含有非法攻击字符，已禁止继续访问！！");
}
return src;
}
}
package com.demo.jdbcinject.config;
import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/**
* @Author laoxu
* @Date 2023/3/15 23:22
* @Desc xxx
*/
@WebFilter(filterName = "xssFilter", urlPatterns = "/*", asyncSupported = true)
public class XSSFilter implements Filter {
/**
* 忽略权限检查的url地址
*/
private final String[] excludeUrls = new String[]{
"/login.html"
};
@Override
public void doFilter(ServletRequest arg0, ServletResponse arg1, FilterChain arg2)
throws IOException, 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 = new XssHttpServletRequestWrapper(req);
//过滤
arg2.doFilter(reqW, response);
}
@Override
public void destroy() {
}
@Override
public void init(FilterConfig filterConfig1) throws ServletException {
}
}
- 启动类注解
@SpringBootApplication
@ServletComponentScan(basePackages = {"com.demo.jdbcinject.config"})
public class WebApplication {
public static void main(String[] args) {
SpringApplication.run(WebApplication.class, args);
}
}
采用预编译接口测试
可以看到无论前端传入什么参数，后端采用绑定变量方式不会返回任何内容。
采用全局过滤器方式后测试
添加过滤器后再次访问，直接报错！！！
