Gitlab整合Sonarqube自動檢測程式碼併傳送報告給提交者

Sunzz發表於2020-09-27

Gitlab

使用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>