spring boot:配置druid数据库连接池(开启sql防火墙/使用log4j2做异步日志/spring boot 2.3.2)

2021-08-21 12:27:56

一，druid数据库连接池的功能？

1，Druid是阿里巴巴开发的号称为监控而生的数据库连接池

它的优点包括：

可以监控数据库访问性能

SQL执行日志

SQL防火墙

2,druid的官方站:

https://github.com/alibaba/druid/

说明：刘宏缔的架构森林是一个专注架构的博客，地址：https://www.cnblogs.com/architectforest

对应的源码可以访问这里获取： https://github.com/liuhongdi/

说明：作者:刘宏缔邮箱: 371125307@qq.com

二，演示项目的相关信息:

1，项目地址:

https://github.com/liuhongdi/druid

2, 项目功能说明：

为druid配置log4j2作为日志记录工具,

演示mybatis代码中#和$变量的区别

3, 项目结构:如图:

三，配置文件说明

1,pom.xml

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-web</artifactId>

            <exclusions>

                <exclusion>

                    <groupId>org.springframework.boot</groupId>

                    <artifactId>spring-boot-starter-logging</artifactId>

                </exclusion>

            </exclusions>

        </dependency>

        <!--druid begin-->

        <dependency>

            <groupId>com.alibaba</groupId>

            <artifactId>druid-spring-boot-starter</artifactId>

            <version>1.1.23</version>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-log4j2</artifactId>

        </dependency>

        <dependency>

            <groupId>com.lmax</groupId>

            <artifactId>disruptor</artifactId>

            <version>3.4.2</version>

        </dependency>

        <!--druid   end-->

        <!--mybatis begin-->

        <dependency>

            <groupId>org.mybatis.spring.boot</groupId>

            <artifactId>mybatis-spring-boot-starter</artifactId>

            <version>2.1.3</version>

        </dependency>

        <!--mybatis end-->

        <!--mysql begin-->

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <scope>runtime</scope>

        </dependency>

        <!--mysql end-->

        <!--pagehelper begin-->

        <dependency>

            <groupId>com.github.pagehelper</groupId>

            <artifactId>pagehelper-spring-boot-starter</artifactId>

            <version>1.2.13</version>

        </dependency>

        <!--pagehelper end-->

说明：关闭了spring-boot-starter-web自带的log功能,

用druid-spring-boot-starter引入druid,

disruptor这个依赖也需要引入，是log4j2使用异步日志中必需的

2,application.properties

#error

server.error.include-stacktrace=always

#error

logging.level.org.springframework.web=trace

#   数据源基本配置

spring.datasource.username = root

spring.datasource.password = lhddemo

spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver

spring.datasource.url = jdbc:mysql://127.0.0.1:3306/store?serverTimezone=UTC

spring.datasource.type = com.alibaba.druid.pool.DruidDataSource

#   Druid数据源配置

spring.datasource.druid.initialSize = 5

spring.datasource.druid.minIdle = 5

spring.datasource.druid.maxActive = 20

spring.datasource.druid.maxWait = 60000

spring.datasource.druid.timeBetweenEvictionRunsMillis = 60000

spring.datasource.druid.minEvictableIdleTimeMillis = 300000

spring.datasource.druid.validationQuery = SELECT 1 FROM DUAL

spring.datasource.druid.testWhileIdle = true

spring.datasource.druid.testOnBorrow = false

spring.datasource.druid.testOnReturn = false

spring.datasource.druid.poolPreparedStatements = true

#   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙

spring.datasource.druid.filters = stat,wall,log4j2

spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize = 20

spring.datasource.druid.useGlobalDataSourceStat = true

spring.datasource.druid.connectionProperties = druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

#druid sql firewall monitor

spring.datasource.druid.filter.wall.enabled=true

#druid sql monitor

spring.datasource.druid.filter.stat.enabled=true

spring.datasource.druid.filter.stat.log-slow-sql=true

spring.datasource.druid.filter.stat.slow-sql-millis=10000

spring.datasource.druid.filter.stat.merge-sql=true

#druid uri monitor

spring.datasource.druid.web-stat-filter.enabled=true

spring.datasource.druid.web-stat-filter.url-pattern=/*

spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*

#druid session monitor

spring.datasource.druid.web-stat-filter.session-stat-enable=true

spring.datasource.druid.web-stat-filter.profile-enable=true

#druid spring monitor

spring.datasource.druid.aop-patterns=com.druid.*

#druid login user config

spring.datasource.druid.stat-view-servlet.login-username=root

spring.datasource.druid.stat-view-servlet.login-password=root

#monintor

spring.datasource.druid.stat-view-servlet.enabled=true

#spring.datasource.druid.stat-view-servlet.url-pattern="/druid/*"

#mybatis

mybatis.mapper-locations=classpath:/mapper/*Mapper.xml

mybatis.type-aliases-package=com.example.demo.mapper

mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

logging.config = classpath:log4j2.xml

说明：除了druid的配置，指定了log的配置文件为: log4j2.xml

如果需要查看监控界面，需要设置以下一项：

spring.datasource.druid.stat-view-servlet.enabled=true

大家如果在生产环境中，可以设置它为false，只查看日志文件

使用log4j2日志时，注意spring.datasource.druid.filters 设置为 stat,wall,log4j2

3,log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>

<configuration status="OFF">

<appenders>

    <Console name="Console" target="SYSTEM_OUT">

        <ThresholdFilter level="DEBUG" onMatch="ACCEPT" onMismatch="DENY"/>

        <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%file:%line] %-5level %logger{35} - %msg %n"/>

    </Console>

    <!--处理INFO级别的日志，写入到logs/info.log文件-->

    <RollingFile name="RollingFileInfo" fileName="./logs/info.log"

                 filePattern="logs/$${date:yyyy-MM}/info-%d{yyyy-MM-dd}-%i.log.gz">

        <Filters>

            <ThresholdFilter level="INFO"/>

            <ThresholdFilter level="WARN" onMatch="DENY" onMismatch="NEUTRAL"/>

        </Filters>

        <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%file:%line] %-5level %logger{35} - %msg %n"/>

        <Policies>

            <SizeBasedTriggeringPolicy size="500 MB"/>

            <TimeBasedTriggeringPolicy/>

        </Policies>

    </RollingFile>

    <!--处理WARN级别的日志，写入到logs/warn.log文件-->

    <RollingFile name="RollingFileWarn" fileName="./logs/warn.log"

                 filePattern="logs/$${date:yyyy-MM}/warn-%d{yyyy-MM-dd}-%i.log.gz">

        <Filters>

            <ThresholdFilter level="WARN"/>

            <ThresholdFilter level="ERROR" onMatch="DENY" onMismatch="NEUTRAL"/>

        </Filters>

        <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%file:%line] %-5level %logger{35} - %msg %n"/>

        <Policies>

            <SizeBasedTriggeringPolicy size="500 MB"/>

            <TimeBasedTriggeringPolicy/>

        </Policies>

    </RollingFile>

    <!--处理error级别的日志，写入到logs/error.log文件-->

    <RollingFile name="RollingFileError" fileName="./logs/error.log"

                 filePattern="logs/$${date:yyyy-MM}/error-%d{yyyy-MM-dd}-%i.log.gz">

        <ThresholdFilter level="ERROR"/>

        <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%file:%line] %-5level %logger{35} - %msg %n"/>

        <Policies>

            <SizeBasedTriggeringPolicy size="500 MB"/>

            <TimeBasedTriggeringPolicy/>

        </Policies>

    </RollingFile>

    <!--druid的日志记录追加器-->

    <RollingFile name="druidSqlRollingFile" fileName="./logs/druid-sql.log"

                 filePattern="logs/$${date:yyyy-MM}/api-%d{yyyy-MM-dd}-%i.log.gz">

        <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%file:%line] %-5level %logger{35} - %msg %n"/>

        <Policies>

            <SizeBasedTriggeringPolicy size="500 MB"/>

            <TimeBasedTriggeringPolicy/>

        </Policies>

    </RollingFile>

</appenders>

<loggers>

    <AsyncRoot level="info">

        <appender-ref ref="Console"/>

        <appender-ref ref="RollingFileInfo"/>

        <appender-ref ref="RollingFileWarn"/>

        <appender-ref ref="RollingFileError"/>

    </AsyncRoot>

    <!--记录druid-sql的记录-->

    <AsyncLogger name="druid.sql.Statement" level="debug" additivity="false">

        <appender-ref ref="druidSqlRollingFile"/>

    </AsyncLogger>

</loggers>

</configuration>

说明：这里只是举例，直接把日志放到了当前目录，生产环境中建议为日志配置专门的目录

4,数据表的结构：

CREATE TABLE `user` (

 `userId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',

 `username` varchar(200) NOT NULL DEFAULT '' COMMENT 'name',

 `password` varchar(100) NOT NULL DEFAULT '' COMMENT 'pass',

 PRIMARY KEY (`userId`),

 UNIQUE KEY `username` (`username`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='user'

四，java代码说明:

1,UserController.java

@RestController

@RequestMapping("/user")

public class UserController {

    @Resource

    private UserService userService;

    //mybatis使用#变量

    @GetMapping("/login")

    public Object login(@RequestParam("username") String username,

                               @RequestParam("password") String password

                               ) {

        User userOne = userService.getOneUserByUsernamePassword(username,password);

        if (userOne == null) {

            System.out.println("user is null");

        }

        return userOne;

    }

    //mybatis使用$变量

    @GetMapping("/login2")

    public Object login2(@RequestParam("username") String username,

                        @RequestParam("password") String password

    ) {

        User userOne = userService.getOneUserByUsernamePassword2(username,password);

        if (userOne == null) {

            System.out.println("user is null");

        }

        return userOne;

    }

}

说明：mybatis在mapper文件中，如果使用$时，属于拼接sql语句，有sql注入的危险,

我们用来检测druid的sql注入检测是否生效

2,UserServiceImpl.java

@Service

public class UserServiceImpl implements UserService {

    @Resource

    private UserMapper userMapper;

    //mybatis使用#变量

    @Override

    public User getOneUserByUsernamePassword(String username,String password) {

        User userOne = userMapper.selectOneUserByUsernamePassword(username,password);

        System.out.println(userOne);

        return userOne;

    }

    //mybatis使用$变量

    @Override

    public User getOneUserByUsernamePassword2(String username,String password) {

        User userOne = userMapper.selectOneUserByUsernamePassword2(username,password);

        System.out.println(userOne);

        return userOne;

    }

}

3,UserMapper.xml

<?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="com.druid.demo.mapper.UserMapper">

    <select id="selectOneUserByUsernamePassword" parameterType="String" resultType="com.druid.demo.pojo.User">

        select * from user where username=#{username} and password=#{password}

    </select>

    <select id="selectOneUserByUsernamePassword2" parameterType="String" resultType="com.druid.demo.pojo.User">

        select * from user where username=${username} and password=${password}

    </select>

</mapper>

4,User.java

public class User {

    //用户id

    private String userId;

    public String getUserId() {

        return userId;

    }

    //用户名

    private String username;

    public String getUsername() {

        return this.username;

    }

    public void setUsername(String username) {

        this.username = username;

    }

}

五，测试效果

1,打开druid监控界面:

http://127.0.0.1:8080/druid/login.html

输入我们在配置文件中的定义的用户和密码 root/root

登录后可以看到druid的界面:

2,测试sql的注入,检查druid的防火墙效果

http://127.0.0.1:8080/user/login?username=1&password=2 or 1=1 limit 1

返回为空，

查看控制台:

==>  Preparing: select * from user where username=? and password=?

==> Parameters: 1(String), 2 or 1=1 limit 1(String)

<==      Total: 0

可见在mybatis使用#我们输入的注入语句也被作为参数的一部分，

因为mybatis把输入的内容解析为一个 JDBC 预编译语句（prepared statement）的参数标记符，

一个 #{ } 被解析为一个参数占位符,

所以注入是失败的

访问:

http://127.0.0.1:8080/user/login2?username=1&password=2 or 1=1 limit 1

mybatis在使用$时，是通过拼接字符串来构造sql,

可见我们的sql注入已生效，但因为druid的防火墙机制，导致抛出 sql injection violation

说明druid的防sql注入防火墙是有效的

3,测试过sql注入后，再查看druid中的防火墙页面:

我们使用的注入sql已被添加到了黑名单

六，查看spring boot的版本:

  .   ____          _            __ _ _

 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \

( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \

 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )

  '  |____| .__|_| |_|_| |_\__, | / / / /

 =========|_|==============|___/=/_/_/_/

 :: Spring Boot ::        (v2.3.2.RELEASE)