sonar-sql-plugin

SQL language (T-SQL, MySQL, PostgreSQL, Snowflake and Vertica SQL dialects) plugin for SonarQube

Donating

You can support this project and others via Paypal

Description

Currently plug-in supports:

• Several SQL dialects by using ANTLR4 grammars:
  • TSQL
  • MySQL
  • PostgreSQL
  • PostgreSQL
  • VSQL
  • SNOWFLAKE
• Reporting of issues found by:
  • SQLCodeGuard
  • MSBuild
  • SQLCheck
  • SQL code violations reported by the plugin
• Reporting of code coverage calculated by SQLCover
• Lines and comment lines measures reporting
• Cognitive and cyclomatic complexity metrics reporting
• Custom user rules. Users can define additional detection rules in the declarative format for the supported SQL dialects. These rules can report code violations specific to the code base and domain. For example, user wants to see code violdations where after each INSERT statement COMMIT statement is not found. Plugin does not report such code, however, if user defines custom rule in the declarative format, then SonarQube will report such violations. More details can be found at here

Tutorials

Tutorials:

• Integrating Redgate SQL Code Guard with SonarQube
• Scanning a TSQL Project With SonarQube

Requirements

Different plugin versions supports the following:

• 1.0.0 - Sonarqube 7.4+ versions
• 1.2.0 - Sonarqube 9+ versions

Installation

1. Download and install SonarQube
2. Download plugin from the releases and copy it to sonarqube's extensions\plugins directory
3. Start SonarQube and enable rules
4. [TSQL] (Optional) Install SQLCodeGuard into your build machine where you plan to run sonar scanner
5. [TSQL, MySQL, PotsgreSQL] (Optional) - Install SQLCheck into your build machine where you plan to run sonar scanner
6. [TSQL] (Optional) Setup SQLCover reorting. You can check tsql example at here for full setup.

Getting started

Please see examples on how to use different dialects.

T-SQL

Sonar settings for tsql. You can check example at here

sonar.projectKey=examples.sql.mysql.project
sonar.projectName=examples.sql.mysql.project
sonar.projectVersion=1.1
sonar.sources=src
# optional
sonar.language=sql
# optional as plugin defaults to tsql
sonar.sql.dialect=tsql

PostgreSQL

Sonar settings for pssql. You can check example at here

sonar.projectKey=examples.sql.psql.project
sonar.projectName=examples.sql.psql.project
sonar.projectVersion=1.1
sonar.sources=src
# optional
sonar.language=sql
sonar.sql.dialect=pssqlv2

MySQL

Sonar settings for mysql. You can check example at here

sonar.projectKey=examples.sql.mysql.project
sonar.projectName=examples.sql.mysql.project
sonar.projectVersion=1.1
sonar.sources=src
# optional
sonar.language=sql
sonar.sql.dialect=mysql

VSQL

Sonar settings for vsql (Vertica SQL). You can check example at here

sonar.projectKey=examples.sql.vsql.project
sonar.projectName=examples.sql.vsql.project
sonar.projectVersion=1.1
sonar.sources=src
# optional
sonar.language=sql
sonar.sql.dialect=vsql

SNOWFLAKE

Sonar settings for snowflake. You can check example at here

sonar.projectKey=examples.sql.snowflake.project
sonar.projectName=examples.sql.snowflake.project
sonar.projectVersion=1.1
sonar.sources=src
# optional
sonar.language=sql
sonar.sql.dialect=snowflake

Custom rules example

This is an example for sonar settings for project which uses custom plugin rules from local directory (located at ./rules directory). You can check full example at here

sonar.projectKey=pssql.custom.rules
sonar.projectName=Test PSSQL custom rules project
sonar.projectVersion=1.0
sonar.sources=src

# optional
sonar.language=sql
sonar.sql.dialect=pssqlv2

# change these
sonar.sql.rules.path=./rules

Plugin configuration

The following options are available for configuration:

• sonar.sql.dialect - SQL dialect for analysis. Defaults to tsql. Supported values are : tsql, mysql, pssql, pssql, pssqlv2.
• sonar.sql.rules.path - path to custome rules, can be directory or absolute file. Defaults to .. Multiple values are supported.
• sonar.sql.rules.suffix - custom rules suffix
• sonar.sql.tsql.ms.report - suffix to search path directories for MSBuild report. Defaults to staticcodeanalysis.results.xml. Can be absolute or relative
• sonar.sql.sqlcheck.path - Path to SQLCheck tool. Defaults to /usr/bin/sqlcheck
• sonar.sql.tsql.cg.path - Path to CodeGuard tool. Defaults to C:\Program Files\SQLCodeGuardCmdLine\SqlCodeGuard30.Cmd.exe
• sonar.sql.tsql.sqlcover.report - path to SQLCover report. Can be absolute or suffix to search in base dir. Defaults to Coverage.opencoverxml.
• sonar.sql.file.suffixes - file suffixes which will be reported belonging to SQL langauge. Defaults to .sql
• sonar.sql.sca.timeout - timeout value for static code analysis done by plugin in seconds. Defaults to 3600
• sonar.sql.sca.maxfilesize - limit in bytes for files to be analyzed by plugin. Defaults to 2097152
• sonar.sql.rules.skip - comma separated list of repoKey:ruleId pairs to select rules which will not be reported by the plugin, i.e. tsql-cg:ST008

Using together with PLSQL plugin

Please configure additional properties:

• When you want PLSQL plugin execution ( this will disable execution sql plugin): sonar.lang.patterns.sql=na

• When you want SQL plugin execution ( this will disable pssql plugin): sonar.lang.patterns.plsqlopen=na

Using command line tools

With the plugin - there is additional cli tool available (it does not require sonar execution):

• rulesHelper.jar - command line helper tool for working with plugin and custom sql rules

rulesHelper

Usage:

• java -jar rulesHelper.jar - will print help
• java -jar rulesHelper.jar print text "SELECT * FROM dbo.test;" tsql - will print parsed AST tree for TSQL dialect

Full help info:

Please pass the following: 
	action  (print, verify or analyze)
	type    (text or file)
	value   (sql string or path to rules file/folder) 
	dialect (tsql, pssql, mysql, pssql, pssqlv2, snowflake)
	folder  (folder to analyze, only applicable when using analyze action)

Example to print AST tree:
	print text "SELECT * FROM dbo.test;" tsql

Example to verify custom rules definitions:
	verify file "c:/tests/customRules.rules;" mysql

Example to execute custom rules and plugin rules against specified folder:
	analyze file "c:/tests/customRules.rules;" snowflake "c:\docs\src"

Example to execute sql analysis againt specified folder:
	analyze file "NA" snowflake "c:\docs\src"

Contributing

Building locally

Run: mvn versions:display-dependency-updates spotless:check spotless:apply install

Developing locally

Added container definitions for easy development with VSCode. Download the remote containers extension and let it figure out the maven targets.

1. Then you can lifecycle > package target to build the plugin. The .jar file will end up in the sonar-sql-plugin/src/sonar-sql-plugin/target/ folder.
2. Copy the jar to the plugins folder of your sonarqube instance

mkdir -p ~/workspace/sonarqube/extensions/plugins
cp ~/workspace/sonar-sql-plugin/src/sonar-sql-plugin/target/sonar-sql-plugin-1.1.0.jar ~/workspace/sonarqube/extensions/plugins

3. Start sonarqube

• first time create the container

docker run -i --name sonarqube \
  -p 9000:9000 \
  -v ~/workspace/sonarqube/conf:/opt/sonarqube/conf \
  -v ~/workspace/sonarqube/extensions:/opt/sonarqube/extensions \
  -v ~/workspace/sonarqube/logs:/opt/sonarqube/logs \
  -v ~/workspace/sonarqube/data:/opt/sonarqube/data \
  sonarqube

• next time only start the container

docker start sonarqube

4. Scan your code (I use a docker scanner)

docker run \
    --rm \
    -e SONAR_HOST_URL="http://127.0.0.1:9000" \
    -e SONAR_LOGIN="YOUR_ADMIN_TOKEN_HERE" \
    -e SONAR_PASSWORD="YOUR_ADMIN_PWD_HERE" \
    --network="host" \
    -v "FOLDER_WITH_THE_CODE:/usr/src" \
    sonarsource/sonar-scanner-cli -X

5. (optional) Stop sonarqube
6. Rinse - repeat

Adding a new grammar support

0. Check how external parsers and lexers are generated from external sources at ./src/external/README.md
1. Generate lexer and parser, example package: org.antlr.sql.dialects.vsql
2. Implement SQLDialect extending BaseDialect.class, i.e. VSQLDialect
3. Implement sql rules, example VSQLRules
4. Register rules at SQLDialectRules. This step is optional as plugin will support custom rules from user project provided in xml format.
5. Update ./src/external/README.md with references to your added grammar

Example commit for adding Snowflake grammar: https://github.com/gretard/sonar-sql-plugin/commit/e3296a5d1c69a031f24358aad87a4e46c46ea785