In the previous post in this series on the analysis of PL/SQL code with SonarQube, we reviewed the existing Blockers rules in our Quality Profile.
We found three violations of PL/SQL programming best practices whose consequences are so significant that no tolerance can be allowed for them. Which therefore justifies their status of ‘blockers‘.
We also found a total of 18 defects for these 3 rules, so with this rather low number, let’s assume that these rules are known by the project team.
Finally, these defects have resulted in a logical bug in the application – an operation that will never be performed because the corresponding condition will never be met – or even a possible crash.
These three Blockers are directed toward robustness and reliability of the application, which suits us very well. Indeed, we wish to highlight defects that directly affect the user, so the reliability of the application but also everything related to security and performance, all that can prevent possible hacking, data corruption or problems of response time which again will impact the end user.
Let’s see if we encounter such defects among the Critical rules.
Criticals
Again, we have only 3 rules concerning this category of Critical violations. The first of these, ‘Sensitive SYS owned functions shoulds not be used’ is about the use of Oracle packages such as:
UTL_FILE to manage files and directories.- UTL_SMTP to manage emails.
- UTL_TCIP to manage TCP connections and read or write on a remote server for instance.
There are many other Oracle packages with other functions, but these are at least those that we can find in the analyzed code.
I must say that I did not know this standard. I’m not a security expert, and in fact, there are quite a few experts in security of databases.
Of course, I know the principle of SQL injection, which allows a hacker to penetrate a database, but in this case, he would have the same rights as a regular user. However, if he encounters one of these SYS procedures performed by a SYS user, then, according to these experts, it is “GAME OVER”. This will give him the rights of database admin and therefore allows to do everything possible, including deleting all access rights.
Obviously, with 320 defects identified by SonarQube in the analyzed code, these standards are not known. Remediate them will also take a little time.
As we can see in this Sqale diagram (commercial plugin from SonarQube), 40 days are needed to remedy these 320 defects. I would recommend a specific action to realize these corrections in the short term.
The second of these three rules identifies the use of DELETE or UPDATE without a WHERE clause, which means to remove or update all records in a table. It can happen to see a whole DELETE on a temporary table but an UPDATE is very uncommon. In any case, we want to check whether the use of this statement is justified or not.
With SonarQube, we can identify and qualify immediately the defect with a drill-down to the line of code, and check that this is really an error.
Especially since this same block of code is repeated again 8 times in the same program! We already encountered this case with the Blockers rules, and a bug copy-pasted several times. One of my recommendations will be to check whether at least one person in the project team requires some training about PL/SQL programming best practices.
Finally, we meet one single violation for the rule ‘Avoid CROSS JOIN queries’, which identifies queries on two tables (or more) without specifying a join between them, which has the effect of returning the cartesian product of all data from these two tables. Such a defect may result in a logical bug for the user, certainly a performance problem, or even a possible error (ie corruption) of data.
One could also justify to upgrade these rules to Blockers. You will tell me that in this case, we do no longer have Criticals rules? In fact no, as we will move some rules of Major type to the Criticals category, the rules which also have an impact on the user in terms of robustness, security and performance.
Ans this we will see in the next post on Major rules.
This post is also available in Leer este articulo en castellano and Lire cet article en français.