Integrating SQLE into a DevOps CI/CD Pipeline for Automated SQL Auditing

1 Introduction

With the growing popularity of DevOps, many processes are moving toward automation. Common DevOps tools include Maven, Sonar, and Docker. This article introduces SQLE, a new tool that can be integrated into a DevOps toolchain to provide SQL compliance auditing.

1.1 Basic Environment

Tool Type

Tool Name

Operating System

CentOS

Code Repository

GitLab

DevOps Tool

GitLab-Runner

SQL Audit Tool

SQLE

1.2 Expected Effect

Automatically trigger an SQL audit task during code submission and upload the scan results.

2 Installation & Configuration

2.1 Installation Methods for Each Tool

Installation is not covered in detail here; refer to the official documentation:

GitLab: https://gitlab.cn/install/

GitLab-Runner: https://docs.gitlab.com/runner/install/

SQLE: https://actiontech.github.io/sqle-docs/docs/quick-usage

2.2 Creating a Scan Task in SQLE

After creating the task, copy the access token displayed; it will be needed to configure CI/CD so that scan results can be viewed in the task.

2.3 Automation Configuration

2.3.1 Configuration File

First, create a .gitlab-ci file in the project. This file is the entry point for GitLab‑Runner and defines all pipeline jobs.

stages:  
- sqle

# SQLE execution scan
master-deploy:
  image: ubuntu:20.04-ssh
  stage: sqle
  before_script:
    - eval $(ssh-agent -s)
    - echo "$SSH_PRIVATE_KEY"
    - echo "$SSH_PRIVATE_KEY" | tr -d '\r' | ssh-add - > /dev/null
    - mkdir -p ~/.ssh
    - chmod 700 ~/.ssh
    - echo "$SSH_KNOWN_HOSTS" > ~/.ssh/known_hosts
    - chmod 644 ~/.ssh/known_hosts
  script:
    - scp -r ../rbac centos@IP:/opt/code/
    - ssh centos@IP "cd /opt/code/ && scannerd mybatis -D /opt/code/rbac/src/main/resources/ -N auto --token eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9... -P 10001"
  only:
    - master
  tags:
    - gitlab-runner

Note: replace IP with your server address.

2.3.2 Brief Explanation of Configuration

Using an ubuntu:20.04-ssh container, the code is uploaded to the SQLE server and the scan is executed.

scannerd is the SQLE-provided scan client; the token mentioned earlier is the access credential.

[root@sqle-server bin]# ./scannerd mybatis --help
Parse MyBatis XML file

Usage:
  SQLE mybatis [flags]

Flags:
  -D, --dir string          xml directory
  -h, --help                help for mybatis
  -S, --skip-error-query    skip statements that cannot be parsed

Global Flags:
  -H, --host string    sqle host (default "127.0.0.1")
  -N, --name string    audit plan name
  -P, --port string    sqle port (default "10000")
  -A, --token string   sqle token

only: indicates the GitLab branch.

tag: indicates the GitLab‑Runner tag.

2.3.3 CI/CD Environment Variable Configuration

The CI/CD process requires scp and ssh to transfer files and invoke the scan command. Configure certificate‑based login on the server and add the necessary variables in GitLab CI settings.

2.3.4 Completion

After completing the steps, each code commit will generate an SQL scan record. Review the results to decide whether the changes pass the audit and can be released.

3 Summary

SQLE adds a valuable tool to the DevOps toolchain, helping to identify SQL performance issues early, mitigate risks, and reduce the likelihood of production incidents.

We hope SQLE continues to improve.

References

[1] Maven: https://maven.apache.org/

[2] Sonar: https://www.sonarqube.org/