Custom rules for SQL

Rules and Languages New rules / language support
1

I would like to add custom rule to my sonarqube database code scan. Many a times developers do not add “nolock” to select statements in stored procedures and they also use wild cards in select statements. I would like to add a custom rule to block that behavior. Has anybody implemented a similar rule ? What options do I have to add custom rules ?

2

Hi,

Welcome to the community!

You don’t mention which language you’re trying to support, but custom rules are only supported for T-SQL and PL/SQL via Generic Issue Reports. I.E. you would develop your rule entirely outside of SonarQube as a whole new tool and produce a report that could be imported into SonarQube.

Alternately, we could move this into the ‘New rules’ category if you’d like to provide specifics on language…?

 
Ann

3

Thank you for the response @ganncamp. In hindsight I should have provided more detail I thought after making that query. My apologies.

Our database source code which has lot of business logic is written using MS-SQL and we have enabled all T-SQL rules in our sonarqube quality profile and that works well.

So when developers write stored procedures they always have following issues that DBAs push back on

Developer query - SELECT * from table name where filter

DBA review -
Do not use * in SELECT. Use specific parameter names.
Do not write SELECT without (nolock)

DBAs expect query to be like below

SELECT col1, col2, col3 from table (nolock)
where filter

That said,
What is this “New rules” category mean ? Would you be implementing it ?
fyi, we use Enterprise edition (paid) for database code scanning.

4

Hi,

Thanks for the additional detail.

If we move this topic, then we turn it into a request for SonarSource to implement it. No promises and no timeline.

 
Ann

5

Thanks again @ganncamp. Yes please, if you could move this into a request for SonarSource to implement that will be great. Also will this be automatically available in SonarCloud once implemented ?

Question on the Generic Issue Reports

  • Do you have suggestions for generating SQL reports that can flag issues like the one I mentioned or should that be done via custom tool ?

  • Importing third-party issues talks about reports from specific languages like Apex, CSS, Go, Java, JS, Kotlin and Python. Can SQL reports be not imported ? Not sure if there is any tool that can generate SQL reports. If you know any please advise. Thanks

6

Hi,

I’ve moved the topic.

Yes!

Yes. Via a custom tool.

We have specific importers for some popular tools. But for everything else, there’s the Generic Issue Format. Just get your reports into that format & they’ll be imported.

 
HTH,
Ann

7

Thank you @ganncamp. Is there a link for the topic (JIRA link or whatever) that I can check to see if its picked up or check on the progress ?

Also I understand there is no fixed timeline but can I assume this will be done before end of this year ?

8

Hi,

There’s nothing in Jira for this and you cannot assume it will be done this year. I moved this topic to the ‘New rules’ category to be noticed by the folks who would create a Jira ticket for it.

 
:woman_shrugging:
Ann

9

Hi,

We already have a rule for that: Columns to be read with a “SELECT” statement should be clearly defined

I’m not a T-SQL server expert but that one seems questionable. There seems to be quite a few (1, 2…) discussions about such practice.

The WITH (NOLOCK) table hint is a good idea when the system uses explicit transactions heavily, which blocks the data reading very frequently. The WITH (NOLOCK) table hint is used when working with systems that accept out of sync data, such as the reporting systems.

We can’t check automatically whether it makes sense to use WITH(NOLOCK) and raising an issue on every single SELECT statement which does not have NOLOCK does not look like a very good idea…