安全防御

使用Kingshard实现数据库防火墙

2019-08-29

传统的防御大多把核心工作放在WEB应用层面,随着各类开源项目0day爆出,WEB安全也面临严峻挑战,一旦WEB应用层面被打穿,就可能造成数据全部泄露。

由于公司业务需要,设计纵深防御架构,在数据库层面再做一层防御,即使web被打穿也依然还有一层防火墙保护数据安全,实现原理基本和CDN-WAF类似,在proxy层面进行阻断、审计,多方比较后选择了Kingshard实施。

kingshard是一个由Go开发高性能MySQL Proxy开源项目,kingshard在满足基本的读写分离的功能上,致力于简化MySQL分库分表操作;

Kingshard项目地址:

https://github.com/flike/kingshard

计划在Kingshard实现SQL白名单,即只允许应用执行范围内的语句执行,再加入黑名单过滤,并加入监控模块,可以对爬虫攻击进行检测,这样一来即便web被打穿,数据库连接信息被泄露,黑客也难以绕过黑白名单防御完整拖库。

实施环境:CentOS 7 x64

以php+mysql为例,docker环境实现

安装docker并配置加速(国内阿里云)

yum update
yum install -y yum-utils device-mapper-persistent-data lvm2
yum install docker
mkdir -p /etc/docker
tee /etc/docker/daemon.json <<-'EOF'
{
  "registry-mirrors": ["https://2any63of.mirror.aliyuncs.com"]
}
EOF
sudo systemctl daemon-reload
sudo systemctl restart docker

安装并配置后端Mysql,将端口映射为3307

docker pull mysql:5.7
docker run --name mysql -v /home/mysqldb:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -p 3307:3306 -d mysql:5.7

安装php环境和相应模块

docker pull php:5.6-fpm
docker run -p 9000:9000 --name  phpfpm -v /home/www:/var/www -d php:5.6-fpm
docker exec -it phpfpm /bin/bash
docker-php-ext-install php_mysqli

查看各容器IP

docker inspect --format='{{.NetworkSettings.IPAddress}}' phpfpm

安装配置nginx

docker pull nginx
docker run --name mynginx -p 80:80 -v /home/www:/var/www -v /home/nginx/conf.d:/etc/nginx/conf.d -d nginx

配置web,php

# php
server {
    charset utf-8;
    client_max_body_size 128M;

    listen 80; ## listen for ipv4
    #listen [::]:80 default_server ipv6only=on; ## listen for ipv6

    server_name www.baidu.com;
    root        /var/www;
    index       index.php;

    location / {
        if (!-e $request_filename){
            rewrite  ^(.*)$  /index.php?s=$1  last;
            break;
        }
        # Redirect everything that isn't a real file to index.php
        try_files $uri $uri/ /index.php$is_args$args;
    }

    # uncomment to avoid processing of calls to non-existing static files by Yii
    #location ~ \.(js|css|png|jpg|gif|swf|ico|pdf|mov|fla|zip|rar)$ {
    #    try_files $uri =404;
    #}
    #error_page 404 /404.html;

    # deny accessing php files for the /assets directory
    location ~ ^/assets/.*\.php$ {
        deny all;
    }

    location ~ \.php$ {
        include fastcgi_params;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        fastcgi_pass {PHPFPM_IPADDR}:9000;
        #fastcgi_pass unix:/var/run/php5-fpm.sock;
        try_files $uri =404;
    }

    location ~* /\. {
        deny all;
    }
}

安装Golang

wget https://studygolang.com/dl/golang/go1.12.9.linux-amd64.tar.gz
tar -C /usr/local -xzf go1.12.9.linux-amd64.tar.gz
mkdir -p /home/gocode
vim /etc/profile 

export GOROOT=/usr/local/go
export GOPATH=/home/gocode 
export PATH=$PATH:$GOROOT/bin:$GOPATH/bin

source /etc/profile 
go version

 

安装kingshard

git clone https://github.com/flike/kingshard.git $GOPATH/src/github.com/flike/kingshard
cd $GOPATH/src/github.com/flike/kingshard
source ./dev.sh
make

修改配置,测试使用不分片

addr : 0.0.0.0:9696
prometheus_addr : 0.0.0.0:7080
user_list:
-
    user :  root
    password : root
-
    user :  kingshard
    password : kingshard
web_addr : 0.0.0.0:9797
web_user : admin
web_password : admin
log_level : debug
log_sql: on
nodes :
- 
    name : node1 
    max_conns_limit : 32
    user :  root 
    password : root
    master : 127.0.0.1:3307
    down_after_noalive : 32
schema_list :
-
    user: root
    nodes: [node1]
    default: node1
    shard:
    -

-
    user: kingshard
    nodes: [node1]
    default: node1
    shard:
    -

 

启动kingshard

./bin/kingshard -config=etc/unshard.yaml

至此,一个最简单的DEMO环境就完成了,但还不具备安全防御能力,我们在对kingshard项目进行了轻微改动,实现了黑白名单安全防御功能,kingshard默认具有黑名单功能,但经过实测用来做防御的话还差太多。

相关代码位于

proxy/server/conn_preshard.go

原代码:

func (c *ClientConn) isBlacklistSql(sql string) bool {
  fingerprint := mysql.GetFingerprint(sql)
  md5 := mysql.GetMd5(fingerprint)
  if _, ok := c.proxy.blacklistSqls[c.proxy.blacklistSqlsIndex].sqls[md5]; ok {
    return true
  }
  return false
}

 

改动:

 

var RequestTotal = prometheus.NewCounterVec(
  prometheus.CounterOpts{
    Name: "sqlQuery",
    Help: "SQL Qeury Group Count",
  },
  []string{"SQL","PASS"},
)
func init() {
  prometheus.MustRegister(RequestTotal)
}
func (c *ClientConn) isBlacklistSql(sql string) bool { 
  defense_rules := `(?i)\/\*.*\*/|length\s*\(.*\)|char_length\s*\(.*\)|concat_ws\s*\(.*\)|schema\s*\(.*\)|SESSION_USER\s*\(.*\)|SYSTEM_USER\s*\(.*\)|CURRENT_USER\s*\(.*\)|CURRENT_USER\s*\(.*\)|ascii\s*\(.*\)|values\(.*select.*\)|ord\s*\(.*\)|substr\s*\(.*\)|mid\s*\(.*\)|left\s*\(.*\)|right\s*\(.*\)|char\s*\(.*\)|sleep\s*\(.*\)|benchmark\s*\(.*\)|get_lock\s*\(.*\)|if\s*\(.*\)|database\s*\(.*\)|version\s*\(.*\)|user\s*\(.*\)|concat\s*\(.*\)|min\s*\(.*\)|exp\s*\(.*\)|information_schema|performance_schema|TABLE_SCHEMA|TABLE_NAME|COLUMN_NAME|min\s*\(.*\)|exp\s*\(.*\)|extractvalue|updatexml|floor\s*\(.*\)|name_const\s*\(.*\)|-- `
  matched, err := regexp.Match(defense_rules, []byte(sql))
  if err == nil {
    if matched == true{
      RequestTotal.With(prometheus.Labels{"SQL": sql,"PASS":"False"}).Inc()
      return true 
    }
  }else{
    RequestTotal.With(prometheus.Labels{"SQL": sql,"PASS":"False"}).Inc()
    return true 
  }
  fingerprint := mysql.GetFingerprint(sql)
  md5 := mysql.GetMd5(fingerprint)
  if _, ok := c.proxy.blacklistSqls[c.proxy.blacklistSqlsIndex].sqls[md5]; ok {
    RequestTotal.With(prometheus.Labels{"SQL": fingerprint,"PASS":"True"}).Inc()
    return false 
  }
  RequestTotal.With(prometheus.Labels{"SQL": fingerprint,"PASS":"False"}).Inc()
  return true 
}

上述修改,将kingshard黑名单功能魔改成了白名单功能,并加入了正则黑名单,实现对SQL注入的防御,规则根据业务需求调整,并加入了prometheus对SQL统计。

重新编译kingshard,根据你的应用情况加入sql白名单语句,修改配置后运行。

我们的demo白名单

insert into yan(id,title,user,content,ctime) values(?+)
select * from yan order by id desc limit ?
select * from yan where user = ?
select * from yan where user = ?
set names utf8
select * from yan where id = ?

配置加入:blacklist_sql_file: /Users/blacklist.txt

其实这是白名单,,,因为我们将黑白颠倒了过来

 

No Comments

Leave a Reply