gretard / sonar-sql-plugin Goto Github PK

Hi,

We are using the plugin (SqlCoverCore.dll) to generate a SQLCover report (Coverage.opencover.xml)
Coverage.opencover.zip (Zipped for upload purpose)

From my untrained eyes, this report that is generated looks good. I can see from the Summary that the sequenceCoverage is at 33%.

<Summary numSequencePoints="762" visitedSequencePoints="259" numBranchPoints="0" visitedBranchPoints="0" sequenceCoverage="33.989500999450684" branchCoverage="0.0" maxCyclomaticComplexity="0" minCyclomaticComplexity="0" />

Now when calling SonarQube I pass this file with the following CLI call

sonar-scanner -D sonar.host.url=https://sonarqube.eu.xxxxx.xx-xxxxxxx.net -D sonar.login=sonarscanner -D sonar.Password=***** -D sonar.projectKey=database.immobilization -D sonar.projectName=database.immobilization -D sonar.projectVersion=0.0.120-develop-120 -D sonar.sources='/vzc/bamboo/build-agent-home/xml-data/build-dir/AWSRMGDT-IMD-JOB1/_src' -D sonar.dynamicAnalysis=reuseReports -D sonar.projectBaseDir='/vzc/bamboo/build-agent-home/xml-data/build-dir/AWSRMGDT-IMD-JOB1/_src' -D sonar.exclusions='**/*.tsql' -D sonar.typescript.coverage.ignoreNotFound=true -D sonar.working.directory='/vzc/bamboo/build-agent-home/xml-data/build-dir/AWSRMGDT-IMD-JOB1/_tmp/scannerwork' -D sonar.javascript.node.maxspace=16384 -D sonar.verbose=true -D sonar.inclusions='**/*.sql' -D sonar.sql.tsql.sqlcover.report="/vzc/bamboo/build-agent-home/xml-data/build-dir/AWSRMGDT-IMD-JOB1/_tst/Coverage.opencover.xml" -D sonar.lang.patterns.tsql="**/*.tsql" -D sonar.lang.patterns.sql='**/*.sql' -D sonar.branch.name='develop' -D sonar.analysis.packageVersion=0.0.120-develop-120

When looking at the code coverage for the project i see

and overall code

Attached are the SQ Logfiles
SonarqubeLogs.txt

Additional information:
SQ Version: Version 9.7 (build 61563)

Plugin Version:
1.3.0

Settings:

All of our SQL Scripts have the suffix SQL and not TSQL, but as all we want the plugin for is to show the code coverage, so I assume the following settings are ok:

sonar.sql.file.suffixes=.sqldisable
sonar.sql.dialect=tsql

Any help appreciated, as we seem unable to fix this ourselves.

Thanks
Jon

Server Version: 8.9.3.48735

SonarQube plugins:
...

• SQL language plugin 1.2.1 (sql)

WARN: Unexpected error adding issue on file sample.sql
java.lang.IllegalStateException: Rule id is mandatory on ad hoc rule
at org.sonar.api.utils.Preconditions.checkState(Preconditions.java:49)
at org.sonar.api.batch.sensor.rule.internal.DefaultAdHocRule.doSave(DefaultAdHocRule.java:85)
at org.sonar.api.batch.sensor.internal.DefaultStorable.save(DefaultStorable.java:45)
at org.sonar.plugins.sql.sensors.BaseSensor.addIssues(BaseSensor.java:120)
at org.sonar.plugins.sql.sensors.SqlCheckSensor.execute(SqlCheckSensor.java:100)
at org.sonar.plugins.sql.sensors.SqlCheckSensor.access$000(SqlCheckSensor.java:23)
at org.sonar.plugins.sql.sensors.SqlCheckSensor$1.run(SqlCheckSensor.java:62)
at java.base/java.util.concurrent.ForkJoinTask$AdaptedRunnableAction.exec(Unknown Source)
at java.base/java.util.concurrent.ForkJoinTask.doExec(Unknown Source)
at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(Unknown Source)
at java.base/java.util.concurrent.ForkJoinPool.scan(Unknown Source)
at java.base/java.util.concurrent.ForkJoinPool.runWorker(Unknown Source)
at java.base/java.util.concurrent.ForkJoinWorkerThread.run(Unknown Source)

In the IndexMatcher line 30 for the LESS validation
--> if (val > rule.getIndex()) {

is the same (but different written) as line 40 for the MORE validation
--> if (rule.getIndex() < val) {
I suspect that this should be "if (val < rule.getIndex()) {"

The same error is repeated for the next section in the same class ... line 52 and 62.

sonar.sql.rules.suffix=
not working, only customRules accepted

Hi Greta,

See

Am I wrong to see that traversiong the siblings here a mix is made with the getParents?

Looking forward to your reply，thx。

I submitted a patch to grammars-v4 (antlr/grammars-v4#2471) to get better support for Azure Synapse Analytics T-SQL.
How do these changes get into a release of this plugin?

Hi,

I´m trying to create custom rules to pssql dialect but when I launch the analysys with SonnarScanner the rules don´t work. Don´t appear in the analisys.

If I create a custom rule for tsql diales, it works fine.

I´ve tried a lot of configurations for pssql dialect but I hadn´t get worked.

I think the configuration for pssql dialect would must be:

SonnarQube Scanner

sonar.projectKey=examples.sql.pssql.project
sonar.projectName=examples.sql.pssql.project
sonar.projectVersion=1.1
sonar.sources=src
sonar.verbose=true
sonar.language=sql
sonar.sql.dialect=pssql

In the project folder, it must be the customrule with the name createrol_pssql.customRules and contains:

(view attached file)

This rule would check if the script contains a "Create Database" statament.

Is it a ok configuration rule(pssql) for the plugin? Must custom rule file include some aditional option for the pssql dialect?

Thanks for you support and regards
rule.customRule.zip
.

hello 👋 sir
I appreciate your plugin. but i have some questions that i want to ask.
first i used plugin sonar-sql-plugin with normal sonar-scanner code like below in jenkins pipeline.
/usr/bin/sonar-scanner
-Dsonar.projectKey="${SONAR_PROJECT}"
-Dsonar.sources=.
-Dsonar.sql.rules.path=./
-Dsonar.sql.rules.suffix=.xml
-Dsonar.host.url="${URL}"
-Dsonar.login="${TOKEN}"
and it can run for scanning sql code in project successfully.
When i used sonar-scanner with MSbuild official. how can i specific path sql custom rules. i try code like below but it didn't work and build failed in jenkins.
dotnet /usr/bin/SonarScanner.MSBuild.dll begin /k:"${SONAR_PROJECT}" /d:sonar.host.url=${SONAR_URL}
/d:sonar.verbose=true
/d:sonar.login=${SONAR_TOKEN}
dotnet build ./
dotnet /usr/bin/SonarScanner.MSBuild.dll end /d:sonar.login=${SONAR_TOKEN} /d:sonar.sql.rules.path=./ \
/d:sonar.sql.rules.suffix=.xml
Do you have any suggestion about this situation problem. THANK YOU FOR YOU SUPPORT.

with sonar 10, they think : https://docs.sonarsource.com/sonarqube/10.2/user-guide/clean-code/ when a put some rules, now appear some tags in the rules , we need to change something?.

rImpl.setRuleViolationMessage("Consider using UNION ALL instead of OR in a WHERE clause.");
r.setRuleImplementation(rImpl);
rImpl.getCompliantRulesCodeExamples().getRuleCodeExample()
.add("SELECT name, surname, count from dbo.test where name = 'or' and surname = 'TestOR';");
rImpl.getViolatingRulesCodeExamples().getRuleCodeExample()
.add("SELECT name, surname, count from dbo.test where name = 'Test' OR surname = 'Testor';");

Hey,

The plugin works great. I was wondering if there is a possibility of this plugin working alongside sonar-plsql

I integrated both and during scan I get this error:

Sonarqube version: 8.9.5 LTS
Sonar scanner CLI: 4.6.2.2472

Thanks

Error stacktrace:

WARN: Unexpected error adding issue on file sample.sql
java.lang.IllegalStateException: Rule id is mandatory on ad hoc rule
	at org.sonar.api.utils.Preconditions.checkState(Preconditions.java:49)
	at org.sonar.api.batch.sensor.rule.internal.DefaultAdHocRule.doSave(DefaultAdHocRule.java:85)
	at org.sonar.api.batch.sensor.internal.DefaultStorable.save(DefaultStorable.java:45)
	at org.sonar.plugins.sql.sensors.BaseSensor.addIssues(BaseSensor.java:120)
	at org.sonar.plugins.sql.sensors.SqlCheckSensor.execute(SqlCheckSensor.java:100)
	at org.sonar.plugins.sql.sensors.SqlCheckSensor.access$000(SqlCheckSensor.java:23)
	at org.sonar.plugins.sql.sensors.SqlCheckSensor$1.run(SqlCheckSensor.java:62)
	at java.base/java.util.concurrent.ForkJoinTask$AdaptedRunnableAction.exec(Unknown Source)
	at java.base/java.util.concurrent.ForkJoinTask.doExec(Unknown Source)
	at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(Unknown Source)
	at java.base/java.util.concurrent.ForkJoinPool.scan(Unknown Source)
	at java.base/java.util.concurrent.ForkJoinPool.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ForkJoinWorkerThread.run(Unknown Source)

Context:

Server Version:  8.5.1.38104

SonarQube plugins:
...
  - SQL language plugin 1.1.1 (sql)
...

Project scanner properties:
  - sonar.branch.name=master
  - sonar.host.url=***
  - sonar.projectBaseDir=/builds/example
  - sonar.projectKey=sql-dialects.pssql
  - sonar.qualitygate.timeout=300
  - sonar.qualitygate.wait=true
  - sonar.scanner.app=ScannerCLI
  - sonar.scanner.appVersion=4.6.2.2472
  - sonar.sourceEncoding=UTF-8
  - sonar.sources=sql
  - sonar.sql.dialect=pssql
  - sonar.sql.sqlcheck.path=/usr/bin/sqlcheck
  - sonar.working.directory=/builds/example/.scannerwork

Note: Issue copied from https://github.com/gretard/sonar-tsql-plugin/issues/26

Is it possible to have a custom rule with variable search parameter... (textToFind). What I'm looking to do is tie back a table's name to the parent name of the table being updated (Ddl_objectContext). If the parent is being updated, skip the rule. Basically, the rule is to look for tables not using "WITH (NOLOCK)".

Update T1
Set Col1 = T2.Col1
From dbo.Table1 T1 Inner Join dbo.Table2 T2 WITH (NOLOCK)
On T1.Col1 = T2.Col1

Basically, Table1 doesn't need the NOLOCK because it is being updated, but Table2 should use the NOLOCK table hint.

Additionally, I noticed that debtRemediationFunctionCoefficient is not working for me. As I understand it, this is supposed to show the amount of work required to remediate the issue. This is showing as "0min effort" even though that node is present.

<remediationFunction>LINEAR</remediationFunction>
<debtRemediationFunctionCoefficient>2min</debtRemediationFunctionCoefficient>

Thanks in advance for any help you could provide.

Implementation file included in attachment.

TSQL.txt

Hi,

We have recently installed this plug-in on our SonarQube Sever Community EditionVersion 7.9.2 (build 30863). When I tried to Code analysis of our Visual Studio DacPac project that has many .sql files (TSQL), its ending up with the errors shown in the attached file.

Sonar-SQL-Code-Analysis.txt

I tried setting the environment variable "SONAR_RUNNER_OPTS" in the build server as "-Xmx3062m -XX:MaxPermSize=512m -XX:ReservedCodeCacheSize=128m" and restarted it, but no use. The error still remains.

Prepare Analysis Task in TFS CI/CD Build Pipeline is set as "Use Standalone Scanner". However, it works fine when I use "Integrate with MSBuild" option. But Sonar documentation says, for doing this kind (SQL Code Analysis, Angular Code Analysis) of code analysis, it should be set as "Use Standalone Scanner".

Could you have a look and suggest any possible solution please?

Thanks,
Mohan

Currently we are adding DB2 on z/OS sql syntax and rules support to this outstanding plugin. We use the antlr4 g4 grammar from Craig Schneiderwent (https://github.com/cschneid-the-elder/mapa) and that works perfectly. We even communicate with him to improve on the g4 grammar.

So the first tests with a few rules were a success.
But we are missing the syntax error handling when parsing a faulty sql file. As I can see in the code between the sqlsensor and the dialect specific parsing, no errorlistener is used and hence no syntax errors are passed higher up. I even wonder how a parse error could be displayed in the sonarqube project dashboard.

Any ideas?

It would be nice to have an option to ignore some rules produced by third party analyzers like SQL Code Guard and SQL Check.
So, for instance, I'd like to disable only Hint usage rule and keep other rules.

I've installed the plugin in sonarqube 8.2 (build 32929) and all issue report 0min effort.
Using MSBuild 14.0 on x86 with /p:RimSqlCodeAnalysis=true. CodeAnalysis and plugin issues are reported, but with effort 0min.

Hi Greta,
In the readme section "Using together with PLSQL plugin" you refer to the sonar.lang.pattern.sqlopen. We have teams who use the standard pl/sql plugin of sonarqube. So we tested this and I think that there is a small change in naming.

I think the property must now be:

sonar.lang.patterns.plsql

Supporting scanner log:
[DEBUG] 14:54:26.592 Declared extensions of language PL/SQL were converted to sonar.lang.patterns.plsql : /*.sql,/.pks,**/.pkb
[DEBUG] 14:49:48.567 Declared extensions of language SQL were converted to sonar.lang.patterns.sql : **/.db2z
[DEBUG] 14:54:26.595 Declared extensions of language T-SQL were converted to sonar.lang.patterns.tsql : **/.tsql

Is it possible to decrease the amount of logs generated by the plugin?
I've tried to set the Sonar scanner log level:

/d:sonar.log.level=WARN

But it didn't help.

The are many log events like this:

[14:09:25] :	 [Step 8/8] CREATE CREATE
[14:09:25] :	 [Step 8/8] TABLE TABLE
[14:09:25] :	 [Step 8/8] SQUARE_BRACKET_ID [dbo]
[14:09:25] :	 [Step 8/8] SQUARE_BRACKET_ID [store_order_payment]
[14:09:25] :	 [Step 8/8] SQUARE_BRACKET_ID [paymentID]
[14:09:25] :	 [Step 8/8] INT INT
[14:09:25] :	 [Step 8/8] IDENTITY IDENTITY
[14:09:25] :	 [Step 8/8] NOT NOT
[14:09:25] :	 [Step 8/8] NULL NULL
[14:09:25] :	 [Step 8/8] SQUARE_BRACKET_ID [orderID]
[14:09:25] :	 [Step 8/8] ID VARCHAR
[14:09:25] :	 [Step 8/8] DECIMAL 80
[14:09:25] :	 [Step 8/8] NOT NOT
[14:09:25] :	 [Step 8/8] NULL NULL

And the log generated by the build is pretty large.

When I enable the plugin, the DB project analysis fails with the following log:

[19:13:13][Step 5/5] INFO: ------------------------------------------------------------------------
[19:13:13][Step 5/5] INFO: EXECUTION FAILURE
[19:13:13][Step 5/5] INFO: ------------------------------------------------------------------------
[19:13:13][Step 5/5] INFO: Total time: 45:17.320s
[19:15:06][Step 5/5] INFO: Final Memory: 1194M/3448M
[19:15:06][Step 5/5] INFO: ------------------------------------------------------------------------
[19:15:07][Step 5/5] ERROR: Error during SonarQube Scanner execution
[19:15:07][Step 5/5] ERROR: null
[19:15:07][Step 5/5] ERROR: 
[19:15:08][Step 5/5] The SonarQube Scanner did not complete successfully
[19:15:08][Step 5/5] 12:15:08.888  Post-processing failed. Exit code: 1
[19:15:09][Step 5/5] Process exited with code 1
[19:15:09][Step 5/5] Process exited with code 1 (Step: SonarScanner for MSBuild: finish analysis)
[19:15:10][Step 5/5] Step SonarScanner for MSBuild: finish analysis failed

I've tried to disable SQLCodeGuard analysis, this doesn't help.

We have a pretty large DB T-SQL project, so the analysis takes ~1 hour.
The previous plugin https://github.com/gretard/sonar-tsql-plugin works without errors.

sqlcheck v1.3 report changed, the sqlcheckreader.java in plugin need update.

v1.3 Latest
Features:

• Line numbers, jarulraj/sqlcheck#24

• Reporting status code 1 when antipatterns found, jarulraj/sqlcheck#22 and jarulraj/sqlcheck#34

Fixes:

• Long queries causing hang, jarulraj/sqlcheck#32

• False positives, jarulraj/sqlcheck#37 and jarulraj/sqlcheck#25

• Crash on create table statement, jarulraj/sqlcheck#33

Windows support available by building from source on your machine, see README
contributor welcome to add Windows support to build pipeline - jarulraj/sqlcheck#53

I am looking for help on implementing logic to check if comment headers are missing in the file. For example , I want to ensure all developers add "Author name: " as comment in top of the code

Hi Greta,

See

I can't figure out why the distance checktype is checked, while checking the index order of the parsed nodes.
Could it be that the indexchecktype of the rule implementation should be used?

Regards,
Maarten.

we can have support for antlr 4.10.1 or 4.11.1 ?.

Hi,
Are you planning to integrate project with Snowflake.
Thank you
Regards

Hello, I'm trying to create a custom rule that require nolock. I have tried many ways and can't seem to get it to work. It should be fairly simple but it's not working. Below is the simplest ones that I made that I thought should work. Any helps would be appreciated! Thank you in advance!
<rule> <key>CC101</key> <name>nolock hint is not being used</name> <internalKey>CC101</internalKey> <descriptionFormat>HTML</descriptionFormat> <description>nolock hint is not being used</description> <severity>BLOCKER</severity> <cardinality>SINGLE</cardinality> <ruleImplementation> <names> <textItem>Table_hintContext</textItem> </names> <textToFind> <textItem>NOLOCK</textItem> </textToFind> <parentRules/> <childrenRules/> <siblingsRules/> <usesRules/> <ruleViolationMessage>Nolock is not being used</ruleViolationMessage> <times>0</times> <ruleMode>Default</ruleMode> <ruleMatchType>TextAndClass</ruleMatchType> <ruleResultType>FailIfNotFound</ruleResultType> <textCheckType>Contains</textCheckType> <usesRules/> </ruleImplementation> </rule>

WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by net.sf.cglib.core.ReflectUtils$1 (file:/C:/Windows/system32/config/systemprofile/.sonar/cache/a89f1943fc75b65becd9fb4ecab8d913/sonar-tsql-plugin.jar) to method java.lang.ClassLoader.defineClass(java.lang.String,byte[],int,int,java.security.ProtectionDomain)
WARNING: Please consider reporting this to the maintainers of net.sf.cglib.core.ReflectUtils$1
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release

I keep seeing this with the sonar-tsql-plugin.jar and thought to post the issue here.

Hi Greta,

We use the jvnet jaxb2 maven plugin (org.jvnet.jaxb2.maven2:maven-jaxb2-plugin:0.15.1) to generate the SqlRules java objects from the customRules.xsd (the jvnet has an extension to initialise fields with the default values). So we removed the hardcoded SqlRules classes. Hence the addhocprovider uses unmarshalling with a jaxbcontext.
Problem with the maven-jaxb2-plugin plugin is that even the latest version is still using the javax.xml.bind classes, through transitive dependencies. There is a clash with the jakarta xml dependencies (versions 4.0.#) versus this plugin ... javax versus jakarta. If I choose versions 2.3.3 of both jakarta xml dependencies, then all is well. The result is that the old style javax classes are used.
Even the other jaxb plugin (jaxb2-maven-plugin) deals with the same issue, but also lacks the support of default values in the xsd.

Question 2: Is there a specific reason that the latest versions of the jakarta xml implementation dependencies are used?
Can we downgrade to 2.3.3?

Best regards,
Maarten.

The three links in "Several SQL dialects by using ANTLR4 grammars" is broken

I think https://github.com/antlr/grammars-v4/ reogranize the repo.
https://github.com/antlr/grammars-v4/tree/master/sql

Hi Greta,

The DB2 z/OS sql dialect we have added to our fork is based on the grammar files of Craig his implementation (see db2 at https://github.com/cschneid-the-elder/mapa).
We use the antlr4 maven plugin to generate the corresponding java objects. This is really very convenient!

question:
Why do you not use it for all the current available dialects? You did document the url's to the grammar sources nicely, so why not use it?

Best regards,
Maarten.

Hi gertard,

I´m trying to create custom rules with the sql plugin but I find me with the next issue:

• "I need to create a custom rule where the use of "create user" statement isn´t allowed".

• When I use the rulesHelper.jar tool with pssql dialect, the next error is getting (ErrorNodeImpl).

java -jar rulesHelper.jar print text "CREATE USER user1;" pssql

Printing tree:

RootContext: @(1:0,1:6) with text: CREATE :CREATEUSERuser1;
        StmtContext: @(1:0,1:6) with text: CREATE :CREATEUSERuser1
                Create_stmtContext: @(1:0,1:6) with text: CREATE :CREATEUSERuser1
                        ErrorNodeImpl: @(1:0,1:6) with text: CREATE :CREATE
                        ErrorNodeImpl: @(1:7,1:11) with text: USER :USER
                        ErrorNodeImpl: @(1:12,1:17) with text: user1 :user1
        TerminalNodeImpl: @(1:17,1:18) with text: ; :;
        StmtContext: @(1:18,1:23) with text: <EOF> :

I think I should get a tree output contains a line with Create_user_stmtContext statement and not the message "ErrorNodeImpl".

The same issue is getting with "create tablespace" statement.

• If I execute rulesHelper.jar with the create schema statement, the parse works fine.

java -jar rulesHelper.jar print text "CREATE SCHEMA myschema;" pssql

Printing tree:

RootContext: @(1:0,1:6) with text: CREATE :CREATESCHEMAmyschema;<EOF>
        StmtContext: @(1:0,1:6) with text: CREATE :CREATESCHEMAmyschema;
                Create_stmtContext: @(1:0,1:6) with text: CREATE :CREATESCHEMAmyschema;
                        Create_schema_stmtContext: @(1:0,1:6) with text: CREATE :CREATESCHEMAmyschema;
                                TerminalNodeImpl: @(1:0,1:6) with text: CREATE :CREATE
                                TerminalNodeImpl: @(1:7,1:13) with text: SCHEMA :SCHEMA
                                IdentifierContext: @(1:14,1:22) with text: myschema :myschema
                                        TerminalNodeImpl: @(1:14,1:22) with text: myschema :myschema
                                Todo_fill_inContext: @(1:22,1:23) with text: ; :;
                                        TerminalNodeImpl: @(1:22,1:23) with text: ; :;
        TerminalNodeImpl: @(1:23,1:28) with text: <EOF> :<EOF>

Could you help with this issue?

Thanks and regards.

Hi Greta,

While testing the db2 z/os rules I noticed that the issues detected always get the type code_smell, although we defined the ruletype of a rule as being a bug.

I think that the first bug is located in the SQLRulesDefinition class ... an ".setType(RuleType.valueOf(rule.getRuleType()))" at line 32 is missing.

And I did not discover why the rules from the customrules xml file with ruletype bug are registered as being of type code_smell. Somehow the bug is not saved in sonarqube accordingly and falls back to the default setting, being a code_smell.