{"id":843,"date":"2014-01-11T12:59:05","date_gmt":"2014-01-11T11:59:05","guid":{"rendered":"http:\/\/qualilogy.com\/en\/?p=843"},"modified":"2014-01-11T17:21:40","modified_gmt":"2014-01-11T16:21:40","slug":"plsql-analysis-with-sonarqube-criticals","status":"publish","type":"post","link":"http:\/\/qualilogy.com\/en\/plsql-analysis-with-sonarqube-criticals\/","title":{"rendered":"PL\/SQL analysis with SonarQube &#8211; Criticals"},"content":{"rendered":"<p><a href=\"http:\/\/500px.com\/Vicken\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-full wp-image-1546\" alt=\"PLSQL_Critical\" src=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2014\/01\/PLSQL_Critical.jpg\" width=\"252\" height=\"380\" \/><\/a>In the previous post in this series on the analysis of PL\/SQL code with SonarQube, we reviewed the existing <a href=\"http:\/\/qualilogy.com\/en\/plsql-analysis-with-sonarqube-blockers\/\" target=\"_blank\">Blockers rules<\/a> in our <a href=\"http:\/\/qualilogy.com\/fr\/analyse-plsql-sonarqube-quality-profile-plsql\/\" target=\"_blank\">Quality Profile<\/a>.<\/p>\n<p><span id=\"result_box\" lang=\"en\"><span class=\"hps\">We found<\/span> <span class=\"hps\">three<\/span> <span class=\"hps\">violations of<\/span> <span class=\"hps\">PL\/<\/span><span class=\"hps\">SQL<\/span> <span class=\"hps\">programming best practices<\/span> <span class=\"hps\">whose<\/span> <span class=\"hps\">consequences are<\/span> <span class=\"hps\">so significant that no<\/span> <span class=\"hps\">tolerance can be allowed<\/span><span class=\"hps\"> for<\/span> <span class=\"hps\">them.<\/span> <span class=\"hps\">Which<\/span> <span class=\"hps\">therefore justifies<\/span> <span class=\"hps\">their status of<\/span> <span class=\"hps atn\">&#8216;<\/span><span>blockers<\/span><span>&#8216;<\/span><span>.<\/span> <\/span><\/p>\n<p><span id=\"result_box\" lang=\"en\"><span class=\"hps\">We also found<\/span> <span class=\"hps\">a total of 18<\/span> <span class=\"hps\">defects<\/span> <span class=\"hps\">for<\/span> <span class=\"hps\">these 3 rules<\/span><span>, so with this<\/span> <span class=\"hps\">rather low number,\u00a0<\/span><span class=\"hps\">let&#8217;s\u00a0<\/span><span class=\"hps\">assume that<\/span> <span class=\"hps\">these rules are<\/span> <span class=\"hps\">known by<\/span> <span class=\"hps\">the project team<\/span>. <\/span><\/p>\n<p><span id=\"result_box\" lang=\"en\"><span class=\"hps\">Finally, these<\/span> <span class=\"hps\">defects have<\/span> <span class=\"hps\">resulted in a<\/span> <span class=\"hps\">logical<\/span> <span class=\"hps\">bug<\/span> <span class=\"hps\">in the application<\/span> <span class=\"hps\">&#8211;<\/span> <span class=\"hps\">an operation that<\/span> <span class=\"hps\">will never be<\/span> <span class=\"hps\">performed because<\/span> <span class=\"hps\">the corresponding condition<\/span> <span class=\"hps\">will never be<\/span> <span class=\"hps\">met<\/span> <span class=\"hps\">&#8211;<\/span> or <span class=\"hps\">even<\/span> <span class=\"hps\">a possible<\/span> <span class=\"hps\">crash<\/span><span>.<\/span><\/span><!--more--><\/p>\n<p>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.<\/p>\n<p>Let&#8217;s see if we encounter such defects among the Critical rules.<\/p>\n<h2>Criticals<\/h2>\n<p><a href=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2014\/01\/Criticals.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1555\" alt=\"Criticals\" src=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2014\/01\/Criticals.jpg\" width=\"676\" height=\"100\" \/><\/a><\/p>\n<p>Again, we have only 3 rules concerning this category of Critical violations. The first of these, &#8216;Sensitive SYS owned functions shoulds not be used&#8217; is about the use of Oracle packages such as:<\/p>\n<ul>\n<li><a href=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2014\/01\/UTLDefects.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignright size-full wp-image-1556\" alt=\"UTLDefects\" src=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2014\/01\/UTLDefects.jpg\" width=\"360\" height=\"199\" \/><\/a>UTL_FILE to manage files and directories.<\/li>\n<li>UTL_SMTP to manage emails.<\/li>\n<li>UTL_TCIP to manage TCP <span id=\"result_box\" lang=\"en\"><span class=\"hps\">connections<\/span> <span class=\"hps\">and<\/span> <span class=\"hps\">read or<\/span> <span class=\"hps\">write on a remote<\/span> <span class=\"hps\">server<\/span> <span class=\"hps\">for instance<\/span><\/span>.<\/li>\n<\/ul>\n<p><span id=\"result_box\" lang=\"en\"><span class=\"hps\">There are<\/span> <span class=\"hps\">many other<\/span> <span class=\"hps\">Oracle<\/span> packages with other functions<span>, but these are<\/span> <span class=\"hps\">at least those<\/span> <span class=\"hps\">that we can find<\/span> <span class=\"hps\">in the analyzed<\/span> <span class=\"hps\">code.<\/span><\/span><\/p>\n<p>I must say that I did not know this standard. I&#8217;m not a security expert, and in fact, there are quite a few experts in security of databases.<\/p>\n<p>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 &#8220;GAME OVER&#8221;. This will give him the rights of database admin and therefore allows to do everything possible, including deleting all access rights.<\/p>\n<p>Obviously, with 320 defects identified by SonarQube in the analyzed code, these standards are not known. Remediate them will also take a little time.<\/p>\n<p><a href=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2014\/01\/PLSQL_SysSunburst.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1560\" alt=\"PLSQL_SysSunburst\" src=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2014\/01\/PLSQL_SysSunburst.jpg\" width=\"582\" height=\"604\" \/><\/a><\/p>\n<p>As we can see in this Sqale diagram (<a href=\"http:\/\/www.sonarsource.com\/products\/plugins\/governance\/sqale\/\" target=\"_blank\">commercial plugin from SonarQube<\/a>), <span id=\"result_box\" lang=\"en\"><span class=\"hps\">40 days<\/span> <span class=\"hps\">are needed to<\/span> <span class=\"hps\">remedy these<\/span> 320 <span class=\"hps\">defects<\/span><span>.<\/span> <span class=\"hps\">I would recommend<\/span> <span class=\"hps\">a specific<\/span> <span class=\"hps\">action<\/span> <span class=\"hps\">to realize these<\/span> <span class=\"hps\">corrections<\/span> <span class=\"hps\">in the short term<\/span><span>. <\/span><\/span><\/p>\n<p><span id=\"result_box\" lang=\"en\"><span class=\"hps\">The second of these<\/span> <span class=\"hps\">three<\/span> <span class=\"hps\">rules<\/span> <span class=\"hps\">identifies the use<\/span> <span class=\"hps\">of<\/span> <span class=\"hps\">DELETE<\/span> <span class=\"hps\">or UPDATE<\/span> <span class=\"hps\">without a WHERE<\/span> <span class=\"hps\">clause<\/span><span>, which means<\/span> <span class=\"hps\">to remove or<\/span> <span class=\"hps\">update all<\/span> <span class=\"hps\">records in a<\/span> <span class=\"hps\">table.<\/span> <span class=\"hps\">It can<\/span> <span class=\"hps\">happen to see<\/span> <span class=\"hps\">a<\/span> <span class=\"hps\">whole<\/span> <span class=\"hps\">DELETE<\/span> <span class=\"hps\">on a temporary table<\/span> <span class=\"hps\">but<\/span> <span class=\"hps\">an UPDATE<\/span> <span class=\"hps\">is very<\/span> <span class=\"hps\">uncommon<\/span><span>.<\/span> <span class=\"hps\">In any case<\/span><span>, we want<\/span> <span class=\"hps\">to check whether the<\/span> <span class=\"hps\">use of this statement<\/span> <span class=\"hps\">is justified<\/span> <span class=\"hps\">or not.<\/span><\/span><\/p>\n<p><a href=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2014\/01\/Update.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignright size-full wp-image-1561\" alt=\"Update\" src=\"http:\/\/qualilogy.com\/fr\/wp-content\/uploads\/sites\/2\/2014\/01\/Update.jpg\" width=\"264\" height=\"61\" \/><\/a> 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.<\/p>\n<p>Especially since this same block of code is repeated again 8 times in the same program! We already encountered this case with <a href=\"http:\/\/qualilogy.com\/en\/plsql-analysis-with-sonarqube-blockers\/\" target=\"_blank\">the Blockers rules<\/a>, 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.<\/p>\n<p>Finally, we meet one single violation for the rule &#8216;Avoid CROSS JOIN queries&#8217;, 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.<\/p>\n<p>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.<\/p>\n<p>Ans this we will see in the next post on Major rules.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8216;blockers&#8216;. We [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[],"class_list":["post-843","post","type-post","status-publish","format-standard","hentry","category-sonarqube-plsql"],"_links":{"self":[{"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/posts\/843"}],"collection":[{"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/comments?post=843"}],"version-history":[{"count":12,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/posts\/843\/revisions"}],"predecessor-version":[{"id":855,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/posts\/843\/revisions\/855"}],"wp:attachment":[{"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/media?parent=843"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/categories?post=843"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/qualilogy.com\/en\/wp-json\/wp\/v2\/tags?post=843"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}