Difference between sonarcube web interface and DB

[avl (Andreas L.)]

• which versions are you using (SonarQube, Scanner, Plugin, and any relevant extension)

Using SonarQube 10.0.0.68432

• how is SonarQube deployed: zip, Docker, Helm

local install from zip file.

• what are you trying to achieve

I need to obtain a csv/xlsx from issues list with rule-names and other info, for discussing scope of orders (i.e. what my customers want me to fix, and what they don’t care about).

• what have you tried so far to achieve this

I connect to SonarQube’s database (postreSql) to query for issues and related data, like their rule’s name, and the locations of findings.

The problem is, that many findings in the database do not seem to show up in the web interfaces.

My select is the following:

select r.name,c.path||' '||i.line as "location",i.status,i.resolution,i.severity,i.effort,r.language
  from issues i,rules r,components c
 where i.rule_uuid=r.uuid and i.component_uuid=c.uuid
 order by r.priority desc,r.name asc,r.language asc,c.path asc,
          i.line asc,i.effort asc;

This select currently gets 171666 of results for the code base, but if I pick a more or less random line from that result, and look into the source file (and line) using the sonarqube web interface, then it often just doesn’t show anything on that file/line, except that I see the culprit that correctly triggered the rule.

In other cases, It finds the same rule applying multiple times on the same file and line, but with varying values for the estimated effort. The source code only has one such spot in that line, though.

Given that the issue actually matches the source code, I wonder why those issues are not displayed in the browser - selecting that file and scrolling to the respective line, I see the culprit triggering the issue, but no markup about that particular issue.

Is there some extra column in the db scheme that would determine whether it is displayed?

To me it looks like the db-query is correct, but the web interfaces filters the results shown by no-idea-what. (all these issues I’m looking for are still “OPEN”, btw.)

[avl (Andreas L.)]

There may be a specific “bias” on the “more or less randomly picked line”:
The customers tend to only order for fixes that are security relevant, so the
rules I looked for are e.g.

“Using pseudorandom number generators (PRNGs) is security-sensitive”
“Hard-coded passwords are security-sensitive”

My impression is, that mostly rules ending in “… is security-sensitive” are those that
do not show up in the sonarqube web interface.

[avl (Andreas L.)]

One more correction update…

The lines not showing up in the web interface appear to have “TO_REVIEW”, not “OPEN”.
I don’t know why they have the other status (I’m sure, nobody set it consciously), and I also don’t
know why “TO_REVIEW” are NOT presented, but at least it seems like I’ve discovered the criteria by which it might be filtered somewhere.

[avl (Andreas L.)]

And a final note: I finally found those “TO_REVIEW” entries under “Security Hotspots”.

So, as far as I have gathered it, certain rules cause their issues to be set to status “TO_REVIEW”, and these issues appear only among the security hotspots, but can not be selected for under “Issues”.

Under Issues I can select for Status, but only among values “Open”, “Reopened”, “Closed”, “Confirmed” and “Resolved” - nothing here allows selection of “to_review”, which I think is a pity.

In the Top-navigation bar I can select “Rules” and then find rules by their names. For most rules there is a very convenient link to the issues at the bottom of their description, but not so for those “security-sensitive” rules… They lack this link, most likely for the same reason: all its issues are “to_review” and not “open”, so aren’t found

[ganncamp (G Ann Campbell)]

Hi,

We advise people to treat the DB like a black box that you really want to keep your hands out of. (Obligatory Dune reference.)

And yes, we make low-level distinctions between issues and Security Hotspots. They are different things, with different lifecycles.

You don’t mention your SonarQube version or edition, but you may be interested the “Regulatory Report”, which is available starting in Enterprise Edition($$). It includes a list of all issues and Security Hotspots.

 
HTH,
Ann