How to perform quality control and vulnerability scans in Data Virtuality

Introduction

SQL code static code analysis is an essential practice that can assist in identifying coding issues, errors, and vulnerabilities in SQL code before it is executed. Static code analysis tools for SQL code can detect syntax mistakes, performance issues, and security flaws. In this guide, we will discuss the need for static code analysis of SQL code, demonstrate how to check the code using ZPA CLI, and build an example CI/CD workflow using ZPA CLI to test a folder containing SQL code and potentially submit the results to SonarQube.

The need for static code analysis of SQL

SQL code static code analysis is critical for finding errors and vulnerabilities in SQL code before it is executed. SQL code is vulnerable to syntax errors and performance problems, which can result in major performance degradation or security risks. A static code analysis tool can analyze SQL code for syntax errors, performance issues, and security flaws.

A static code analysis tool, for example, can scan SQL code for security vulnerabilities such as SQL injection, a frequent attack that can result in unauthorized database access and modification. Static code analysis tools can also identify performance problems such as long-running queries or inefficient queries that can have an impact on database performance.

Checking SQL code using ZPA CLI

ZPA CLI is a free and open-source tool that can be used for static code analysis of SQL code. You can use ZPA CLI to scan SQL code for syntax errors, performance issues, and security vulnerabilities.

Requirements

• Java 11 or newer
• Download the ZPA CLI from the official "Releases" page.

Usage

The zpa-cli supports these options:

• --sources: �required] Path to the folder containing the files to be analyzed.
• --forms-metadata: Path to the Oracle Forms metadata file.
• --extensions: File extensions to analyze, separated by a comma. The default value is sql,pkg,pks,pkb,fun,pcd,tgg,prc,tpb,trg,typ,tab,tps.
• --output-format: Format of the output. The default value is console.
• --output-file: Path to the output file.

Output formats:

• console: writes the analysis result on the standard output
• sq-generic-issue-import: generates a XML file using the "Generic Issue Data" format that can be used in SonarCloud or in a SonarQube server (as an alternative to the dedicated Z PL/SQL Analyzer Plugin).

Example

./zpa-cli/bin/zpa-cli --sources . --output-file zpa-issues.json --output-format sq-generic-issue-import

SonarQube integration

The code analysis report can be optionally submitted to a SonarCloud or SonarQube server to make use of the advanced code quality tracking features. To install a free Open Source version of SonarQube, please follow the official documentation.

To upload the analysis report to SonarQube, you need to download the official SonarScanner tool and set
the sonar.externalIssuesReportPaths property:

sonar-scanner 
  -Dsonar.organization=$SONARCLOUD_ORGANIZATION \
  -Dsonar.projectKey=myproject \
  -Dsonar.sources=. \
  -Dsonar.host.url=https://sonarcloud.io \
  -Dsonar.externalIssuesReportPaths=zpa-issues.json

Integrating SQL code analysis with CI/CD

The SQL code analysis can easily be included in the CI/CD workflow to ensure the code is regularly checked and vulnerabilities are fixed before reaching the production systems. Here is a simple GitHub Actions workflow that downloads the ZPA CLI and runs the test on SQL scripts stored in the 'sql-scripts' folder.

name: Test SQL Scripts

on:
  push:
    branches:
      - main

jobs:
  test-sql-scripts:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v2
      - name: Install test-cli
        run: |
          curl -LO https://github.com/felipebz/zpa-cli/releases/download/1.2.0/zpa-cli-1.2.0.zip
          unzip zpa-cli-1.2.0.zip
          chmod +x zpa-cli-1.2.0/bin/zpa-cli
      - name: Run ZPA CLI
        run: |
          zpa-cli-1.2.0/bin/zpa-cli --sources ./sql-scripts \
             --output-file results.json \
             --output-format sq-generic-issue-import