Gitlab集成Sonarqube自动检测代码并发送报告给提交者

使用gitlab-ci、sonarqube、sonar-scanner 实现如下功能

1.一旦提交代码就进行代码质量检测

2. 发送检测报告邮件给提交者

先来看下最终结果，邮件中有检测报告，具体bug等详细情况可点击邮件中的 url 跳转到检测结果进行查看

Sonarqube中代码bug等具体信息

Gitlab-ci 结果

如果这也是你想实现的功能的话，那么请往下看，否则就不需要浪费时间了

Jenkins结合sonarqube可参考 https://www.cnblogs.com/Sunzz/p/10075791.html

环境说明

Gitlab 服务器：

centso: 7.4 gitlab: 12.2.3

jdk: 11.0.3 Scanner: 4.0.0.1744

python: 3.6.8

Sonarqube 服务器：

centso: 7.4 docker: 19.03.13

jdk: 11.0.3 sonarqube: 7.9.4

postgres：13

gitlab、gitlab-runner、jdk 安装与配置请自行解决

sonarqube 安装与配置

首先安装PostgreSQL

因为不支持mysql了，oracle和SqlServer又不想用。

docker pull postgres

启动并设置用户名和密码均为 sonarqube

docker run --name=postgresql -p 5432:5432 -e POSTGRES_DB=sonarqube \
 -e POSTGRES_USER=sonarqube -e POSTGRES_PASSWORD=sonarqube -d postgres

下载并配置sonarqube

新建sonarqube用户

useradd sonarqube

切换至sonarqube

su - sonarqube

sonarqube官网下载：

wget https://binaries.sonarsource.com/Distribution/sonarqube/sonarqube-7.9.4.zip -d /opt/

sonarqube配置：

sonar.jdbc.url=jdbc:postgresql://localhost/sonarqube?currentSchema=my_schema
sonar.jdbc.username=sonarqube
sonar.jdbc.password=sonarqube
sonar.jdbc.url=jdbc:postgresql://127.0.0.1/sonarqube
sonar.web.port=9000

注意：如果不是yum安装jdk的话，还需要改 wrapper.conf 中的 wrapper.java.command配置

启动sonarqube，不能以root用户启动，我这里使用的是sonarqube用户

/opt/sonarqube-7.9.4/bin/linux-x86-64/sonar.sh start

sonar-scanner 安装与配置

官网下载：

wget https://binaries.sonarsource.com/Distribution/sonar-scanner-cli/sonar-scanner-cli-4.0.0.1744-linux.zip

解压并配置

unzip sonar-scanner-cli-4.0.0.1744-linux.zip -d  /opt/

编辑 /opt/sonar-scanner-4.0.0.1744-linux/conf/sonar-scanner.properties

sonar.host.url=https://your-sonarqube.com  # sonarqube 的url
sonar.login=admin　　   # sonarqube 的用户名和密码
sonar.password=admin
sonar.sourceEncoding=UTF-8
sonar.language=java
sonar.sources=.
sonar.java.binaries=.

配置环境变量

新增文件 /etc/profile.d/sonar-scanner.sh，内容如下

export PATH=$PATH:/opt/sonar-scanner-4.0.0.1744-linux/bin/sonar-scanner

source  /etc/profile.d/sonar-scanner.sh

检查是否安装成功

sonar-scanner -v

INFO: Scanner configuration file: /opt/sonar-scanner/conf/sonar-scanner.properties
INFO: Project root configuration file: NONE
INFO: SonarQube Scanner 4.0.0.1744
INFO: Java 11.0.3 AdoptOpenJDK (64-bit)
INFO: Linux 3.10.0-693.el7.x86_64 amd64

gitlab-ci 配置

目的就是一旦用户提交代码，触发代码扫描并发送邮件给代码提交者。

在项目中新增 .gitlab-ci.yml 文件

stages:
  - sonarqube_scan
  - sendmail

sonarqube_scan_job:
  stage: sonarqube_scan
  script:
    -  sonar-scanner -Dsonar.projectName=$CI_PROJECT_NAME -Dsonar.projectKey=$CI_PROJECT_NAME -Dsonar.host.url=https://your-sonarqube.com -Dsonar.login=admin -Dsonar.password=admin
  tags:
    - sonar-scanner
  when: always

sendmail_job:
  stage: sendmail
  script:
    - echo $GITLAB_USER_EMAIL
    - echo $CI_PROJECT_NAME

- cd /opt/scripts && python3 /opt/scripts/sonarqube.py $CI_PROJECT_NAME $GITLAB_USER_EMAIL tags: - sonar-scanner

参数说明：

tag: gitlab-runner中的tag, 我配置的tag是sonar-scanner

$CI_PROJECT_NAME： gitlab内置参数，为项目名称

$GITLAB_USER_EMAIL： gitlab内置参数，提交者的邮箱，传递给Python，为了后边发邮件用

-Dsonar.projectName=$CI_PROJECT_NAME 为在sonarqube中项目的名称

-Dsonar.projectKey=$CI_PROJECT_NAME 为在sonarqube中项目的唯一标识

-Dsonar.host.url=https://your-sonarqube.com sonarqube的url

-Dsonar.login=admin -Dsonar.password=admin sonarqube的用户名和密码配置

发送邮件配置

由于不知道 sonarqube代码检测完之后怎么回调给gitlab，我这里用比较笨的方法实现

用sonar-scanner扫描代码之后，去查sonarqube的数据库然后在把数据拼凑成邮件进行发送，

由于7.9已经不支持mysql，我这里用的postgresql，其他数据库改下Python所用模块和连接就行，sql语句应该不需要改动就可使用

编辑 sonarqube.py

import os
import sys
import smtplib
import psycopg2
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from jinja2 import FileSystemLoader, Environment


def select_project_uuid(project_name):
    db = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
    cursor = db.cursor()
    select_p_uuid = "SELECT project_uuid,kee FROM projects WHERE name= '%s'" % (project_name)
    cursor.execute(select_p_uuid)
    result = cursor.fetchone()
    p_uuid = result[0]
    projectKey = result[1]
    db.close()
    return (p_uuid, projectKey)


def select_total_info(p_uuid):
    total_info = []
    db = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
    cursor = db.cursor()
    select_p_links = "SELECT text_value FROM project_measures WHERE text_value LIKE 'java=%' and component_uuid='{}'".format(
        str(p_uuid))
    cursor.execute(select_p_links)
    p_links = str(cursor.fetchone()[0].split("=")[1]).split(";")[0]

    sql_info = "SELECT count(*) FROM issues WHERE project_uuid='%s' and issue_type =%s"
    for leak in [2, 3, 1]:
        search_data = sql_info % (p_uuid, leak)
        cursor.execute(search_data)
        total_info.append(cursor.fetchone()[0])
    db.close()
    return p_links, total_info


def select_bugs(p_uuid):
    bugs = []
    db = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
    cursor = db.cursor()

    sql_info = "SELECT count(*) FROM issues WHERE project_uuid='%s' and issue_type =2 AND severity ='%s'"
    for leak in ['BLOCKER', 'CRITICAL', "MAJOR", 'MINOR', 'INFO']:
        search_data = sql_info % (p_uuid, leak)
        cursor.execute(search_data)
        bugs.append(cursor.fetchone()[0])
    db.close()
    return bugs


def select_leaks(p_uuid):
    leaks = []
    db = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
    cursor = db.cursor()

    sql_info = "SELECT count(*) FROM issues WHERE project_uuid='%s' and issue_type =3 AND severity ='%s'"
    for leak in ['BLOCKER', 'CRITICAL', "MAJOR", 'MINOR', 'INFO']:
        search_data = sql_info % (p_uuid, leak)
        cursor.execute(search_data)
        leaks.append(cursor.fetchone()[0])
    db.close()
    return leaks


def select_bad_tastes(p_uuid):
    tastes = []
    db = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
    cursor = db.cursor()

    sql_info = "SELECT count(*) FROM issues WHERE project_uuid='%s' and issue_type =1 AND severity ='%s'"
    for leak in ['BLOCKER', 'CRITICAL', "MAJOR", 'MINOR', 'INFO']:
        search_data = sql_info % (p_uuid, leak)
        cursor.execute(search_data)
        tastes.append(cursor.fetchone()[0])
    db.close()
    return tastes


def generate_errmsg_table(s_lines="", total_data=[], bugs=[], leaks=[], tastes=[], report_url="",
                          project_name=sys.argv[1], user_email=sys.argv[2]):
    env = Environment(loader=FileSystemLoader(curpath, 'utf-8'))  # 创建一个包加载器对象
    template = env.get_template(table_tem_name)
    html_content = (
        template.render(
            lins=s_lines, total_data=total_data, bugs=bugs, leaks=leaks, tastes=tastes, report_url=report_url,
            project_name=project_name, user_email=user_email,
        ))
    fh = open(report_html_path, 'w')
    fh.write(html_content)
    fh.close()


def sendmail(subject, msg, toaddrs, fromaddr, smtpserver, password):
    """
    :param subject: 邮件主题
    :param msg:  邮件内容
    :param toaddrs: 收信人的邮箱地址
    :param fromaddr: 发信人的邮箱地址
    :param smtpserver: smtp服务地址
    :param password: 发信人的邮箱密码
    :return:
    """
    mail_msg = MIMEMultipart()
    mail_msg['Subject'] = subject
    mail_msg['From'] = fromaddr
    mail_msg['To'] = ','.join(toaddrs)
    mail_msg.attach(MIMEText(msg, 'html', 'utf-8'))
    try:
        s = smtplib.SMTP_SSL(smtpserver)
        s.connect(smtpserver, 465)  # 连接smtp服务器
        s.login(fromaddr, password)  # 登录邮箱
        s.sendmail(fromaddr, toaddrs, mail_msg.as_string())  # 发送邮件
        s.quit()
        print("send successful！")
    except Exception as e:
        print(e)
        print("Failed to send ")


def main():
    p_uuid, projectKey = select_project_uuid(project_name)
    s_lines, total_data = select_total_info(p_uuid)
    bugs = select_bugs(p_uuid)
    leaks = select_leaks(p_uuid)
    tastes = select_bad_tastes(p_uuid)
    report_url = "https://your-sonarqube.com/dashboard?id=%s" % (projectKey)
    generate_errmsg_table(s_lines, total_data, bugs, leaks, tastes, report_url, project_name, user_email)

    with open("report_%s.html" % (project_name)) as f:
        message = str(f.read())
        f.close()

    #配置邮件服务器，我这里用的是阿里的邮件服务器

    fromaddr = "xxxx@xxx.com"
    smtpserver = "xxx.com"
    toaddrs = [user_email, ]
    subject = "Gitlab代码质量检测"
    password = "yourpassword"
    msg = message
    sendmail(subject, msg, toaddrs, fromaddr, smtpserver, password)


curpath = os.getcwd()
table_tem_name = "table.html"
project_name = sys.argv[1]
user_email = sys.argv[2]
report_html_path = "report_" + project_name + ".html"

# sonarqube数据库账号密码等
database = "sonarqube"
user = "sonarqube"
password = "sonarqube"
host = "xx.xx.xx.xx"
port = "5432"

if __name__ == '__main__':
    main()

table.html , table.html需要和sonarqube.py放在同一个目录下

<!DOCTYPE html>
<html lang="en">
<head>
    <title></title>
    <meta charset="utf-8">
    <style type="text/css">
        table {
            text-align: center;
            border-style: solid solid solid solid;
            border-collapse: collapse;
            width:550px ;
            height:120px;
        }
        tr:hover {
            background-color: #F7F9FF;
            cursor: pointer;
        }
        .page {
            margin-left: 30px;
        }
    </style>
</head>
<body>
<div class="page">
    <h3>{{ user_email }}, 你好</h3>
    <h3> 本次提交代码检查结果如下</h3>
    <h3> 项目名称：{{ project_name }} </h3>
    <h4>一、总体情况</h4>
    <ul>
        <li style="font-weight:bold;">
            整体运行情况：扫描代码行数:&nbsp; <span style="color:blue">{{lins}} </span>,
            bugs: &nbsp;<span style="color:red">{{total_data[0]}}</span>,
            Vulnerabilities: &nbsp;<span style="color:red">{{total_data[1]}}</span>,
            Code Smells: &nbsp; <span style="color:red">{{total_data[2]}}</span>
        </li>
        <li style="font-weight:bold;margin-top: 10px;">
            URL地址：&nbsp;<a style="font-weight:bold;"
                           href={{report_url}}>{{report_url}}</a>
        </li>
    </ul>
    <h4>二、信息详情</h4>
    <table cellspacing="0"  border="1px" >
        <thead>
        <tr style="background-color:#F7F9FF">
            <th>{{ project_name }}</th>
            <th>阻断</th>
            <th>严重</th>
            <th>主要</th>
            <th>次要</th>
            <th>提示</th>
            <th>总数</th>
        </tr>
        </thead>
        <tbody>
        <tr>
            <td>bugs</td>
            <td>{{bugs[0]}}</td>
            <td>{{bugs[1]}}</td>
            <td>{{bugs[2]}}</td>
            <td>{{bugs[3]}}</td>
            <td>{{bugs[4]}}</td>
            <td style="color:red">{{total_data[0]}}</td>
        </tr>
        <tr>
            <td>Vulnerabilities</td>
            <td>{{leaks[0]}}</td>
            <td>{{leaks[1]}}</td>
            <td>{{leaks[2]}}</td>
            <td>{{leaks[3]}}</td>
            <td>{{leaks[4]}}</td>
            <td style="color:red">{{total_data[1]}}</td>
        </tr>
        <tr>
            <td>Code Smells</td>
            <td>{{tastes[0]}}</td>
            <td>{{tastes[1]}}</td>
            <td>{{tastes[2]}}</td>
            <td>{{tastes[3]}}</td>
            <td>{{tastes[4]}}</td>
            <td style="color:red">{{total_data[2]}}</td>
        </tr>
        </tbody>
    </table>
</div>
</body>
</html>