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

You can support this project and others via Paypal

Currently plug-in supports:

• Several SQL dialects by using ANTLR4 grammars:
  • TSQL
  • MySQL
  • PostgreSQL
  • PostgreSQL
  • VSQL
• 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:

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

Different plugin versions supports the following:

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

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.

Please see examples on how to use different dialects.

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

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

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

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

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

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

With the plugin - there are additional 2 cli tools available (they are not required for sonar execution):

• sql-sca-cli.jar - allows to execute sql code analysis from the command line
• rulesHelper.jar - command line helper tool for writing custom sql rules

Usage:

• java -jar sql-sca-cli.jar --help - will print help
• java -jar sql-sca-cli.jar - will scan current directory
• java -jar sql-sca-cli.jar -i /home/test - will scan test directory

Full help info:

-c,--custom-rules-path <arg>           path to custom rules directory,
                                       defaults to current directory
-csuffix,--custom-rules-suffix <arg>   custom rules suffix, defaults to:
                                       .customRules
-d,--dialect <arg>                     SQL dialect, defaults to: tsql,
                                       possible values: [TSQL, PSSQL,
                                       MYSQL, VSQL, PSSQLV2]
-h,--help                              show help
-i,--input <arg>                       input directory for analysis,
                                       defaults to current directory
-p,--prefixes <arg>                    file prefixes for analysis,
                                       defaults to: .sql
-warnOnly,--warnOnly                   flag whether tool should report
                                       warnings only

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 or  verify)
	type (text or  file)
	value (sql string or path to folder) 
	dialect (tsql, pssql, mysql, pssql, pssqlv2) 
Example:
print text "SELECT * FROM dbo.test;" tsql

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

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

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
5. Update ./src/external/README.md with references to your added grammar