TSQL custom rule implementation

SonarQube Writing rules
, ,
1

Hi - We are using sonarqube for sql script analysis(create table script, stored proc…etc…).
We have license for TSQL.
Trying to write a custom rule to check if the table has atleast one foriegn key. Can any one please help to write the rule and import into tsql rules?

Thanks
Nithiya

2

Hi Nithiya,

Welcome to the community!

Unfortunately, custom rules aren’t supported for T-SQL. Would you mind sharing what rules you think are missing?

 
Ann

1 Like
4

Thanks for your reply. As mentioned, would like to write a custom rule to check if the sql table has atleast one foreign key constraint defined.We will give the input as .tsql file for sonarqube to anlayze. This .tsql file is a script with create table and constraint auto generated from sql server.

Thanks
Nithiya

1 Like
5

Hi Ann - Can you please let me know if you have any details on it…

Thanks
Nithiya

7

Hi @Nithiya,

Thank you for suggesting this rule. This would be an “audit” rule, i.e. a rule warning the developer and asking him/her to check if this is what he/she really wants to do. We strive to have a different user experience where developers are only notified when we are sure that there is a problem. Developers tend to ignore every issue if too many of them are wrong.
Thus we sadly can’t add this rule to SonarQube.

8

One thing I forgot to mention. SonarQube has a generic import format. You could generate a report with a script and import the issues in SonarQube. The main drawback is that it makes the analysis more complex.

9

Thanks for the reply. We need to implement this rule for our business need. Can you please guide us how to proceed on this ourself.

10

Hi @Nithiya,

If you want to generate a report and import it in SonarQube using the “generic import format” you will need to implement a basic analyzer. If you have only one SQL query per file a regular expression searching for foreign keys should be enough.
You will have to run your script before running SonarQube analyzer. Then you set in your sonar-project.properties the property sonar.externalIssuesReportPaths to the path of your report. Finally you run SonarQube scanner.

11

Thanks Nicolas.I am a newbie to this. Can you please provide a sample with full steps need to be run to check a rule on .sql file which has create table script. Would be a great help in getting to know the steps to be followed in detail. Thanks a lot.

12

Hi @Nithiya,

I’m sorry but providing a script analyzing TSQL code would be equivalent to implementing a third party application. It goes beyond what I can provide on this forum. If you have any question regarding SonarQube documentation I’ll gladly answer them.

I suggest that as a first step you create manually a file, let’s name it report.txt, and write into it a generic report. You have an example in the doc I referenced above. Change the filePath and textRange to match an existing code file.
Then add sonar.externalIssuesReportPaths=report.txt in your sonar-project.properties and run the analyzer. You should see these issues appear in SonarQube.

Once you have all this working you can implement the script generating the report.txt file.

13

Thanks Nicolas.
I tried the same way, I am getting
01:52:21.413 DEBUG: Importing issues from ‘C:\Sonar\Reports.txt’
01:52:21.429 INFO: Imported 0 issues in 0 files
01:52:21.429 INFO: External issues ignored for 1 unknown files, including: /About.aspx.cs.
In Reports.txt, I have mentioned “filePath”: “/About.aspx.cs”,. Here About.aspx.cs resides in same folder where .sln exists.

Can you please guide.

Thanks
Nithiya

14

Hi @Nithiya
The file About.aspx.cs does not seem to be analyzed by SonarQube, or its path might be different in SonarQube. Could you look in SonarQune to see if you have the file listed there, and if it is the case, use the same path.

15

Hi Nicolas - I got as below in command prompt. Could not see any issues in Sonarqube
21:58:59.863 DEBUG: Sensors : Import external issues report -> SonarCSS Rules -> JaCoCo XML Report Importer -> JavaXmlSensor -> C#
21:58:59.863 INFO: Sensor Import external issues report
21:58:59.863 DEBUG: Importing issues from ‘C:\Sonar\Reports.txt’
21:58:59.865 INFO: Imported 0 issues in 0 files
21:58:59.865 INFO: External issues ignored for 1 unknown files, including: /About.aspx.cs
21:58:59.865 INFO: Sensor Import external issues report (done) | time=2ms

My Reports.txt:
{ “issues”: [
{
“engineId”: “test”,
“ruleId”: “rule1”,
“severity”:“BLOCKER”,
“type”:“CODE_SMELL”,
“primaryLocation”: {
“message”: “fully-fleshed issue”,
“filePath”: “/About.aspx.cs”,
“textRange”: {
“startLine”:1,
“endLine”: 2,
“startColumn”: 1,
“endColumn”: 1
}
},
“effortMinutes”: 90,
“secondaryLocations”: [
{
“message”: “cross-file 2ndary location”,
“filePath”: “/Site.Master.cs”,
“textRange”: {
“startLine”: 1,
“endLine”: 2,
“startColumn”: 1,
“endColumn”: 1
}
}
]
}
]}

16

Could you please guide whats going wrong.

Thanks
Nithiya