Dependencies causing trouble to have a new custom rule

[csrvsk]

Hi Filepe,

I am trying to add a new custom rule to detect symbols & and && and report it as a code smell in sql/db files. But I am having issues with dependencies.

Could you please have a look at it and guide what am I missing?

here is my rule class. package com.company.plsql;

import com.sonar.sslr.api.Token; import org.sonar.check.Rule; import org.sonar.plugins.plsqlopen.api.squid.PlSqlCheck; import org.sonar.plugins.plsqlopen.api.tree.PlSqlNode; import org.sonarsource.sslr.channel.CodeReader; import org.sonarsource.sslr.parser.LexerlessGrammar;

@Rule(key = "AvoidANDSymbolCheck") public class AvoidANDSymbolCheck extends PlSqlCheck {

private static final String MESSAGE = "Avoid using '&' or '&&' in SQL code";

@Override
public void init() {
    subscribeTo(Token.class);
}

@Override
public void visitNode(PlSqlNode node) {
    // Implementation of the check logic
}

@Override
public void visitToken(Token token) {
    if (token.getValue().contains("&") || token.getValue().contains("&&")) {
        addIssue(token.getLine(), MESSAGE);
    }
}

}

Following is the errors I found for compilation of plsql custom rules plugin:

"C:\Program Files\Java\jdk-11.0.16.1\bin\java.exe" -javaagent:C:\Users\shiva\Downloads\ideaIC-2022.3.2.win\lib\idea_rt.jar=64244:C:\Users\shiva\Downloads\ideaIC-2022.3.2.win\bin -Dfile.encoding=windows-1252 -classpath C:\Users\shiva\Downloads\ideaIC-2022.3.2.win\plugins\Kotlin\kotlinc\lib\kotlin-compiler.jar org.jetbrains.kotlin.cli.jvm.K2JVMCompiler -kotlin-home C:\Users\shiva\Downloads\ideaIC-2022.3.2.win\plugins\Kotlin\kotlinc -script C:\Users\shiva\Downloads\zpa-main\zpa-main\plsql-custom-rules\build.gradle.kts build.gradle.kts:5:1: error: unresolved reference: plugins plugins { ^ build.gradle.kts:6:5: error: unresolved reference. None of the following candidates is applicable because of receiver type mismatch: public val KClass<TypeVariable(T)>.java: Class<TypeVariable(T)> defined in kotlin.jvm java ^ build.gradle.kts:9:1: error: unresolved reference: repositories repositories { ^ build.gradle.kts:10:5: error: unresolved reference: mavenCentral mavenCentral() ^ build.gradle.kts:11:5: error: unresolved reference: mavenLocal mavenLocal() ^ build.gradle.kts:12:5: error: unresolved reference: maven maven { ^ build.gradle.kts:13:9: error: unresolved reference: url url = uri("https://s01.oss.sonatype.org/content/repositories/snapshots/") ^ build.gradle.kts:13:15: error: unresolved reference: uri url = uri("https://s01.oss.sonatype.org/content/repositories/snapshots/") ^ build.gradle.kts:17:1: error: expression 'java' cannot be invoked as a function. The function 'invoke()' is not found java { ^ build.gradle.kts:17:1: error: unresolved reference. None of the following candidates is applicable because of receiver type mismatch: public val KClass<TypeVariable(T)>.java: Class<TypeVariable(T)> defined in kotlin.jvm java { ^ build.gradle.kts:18:5: error: unresolved reference: toolchain toolchain { ^ build.gradle.kts:19:9: error: unresolved reference: languageVersion languageVersion.set(JavaLanguageVersion.of(11)) ^ build.gradle.kts:19:29: error: unresolved reference: JavaLanguageVersion languageVersion.set(JavaLanguageVersion.of(11)) ^ build.gradle.kts:26:1: error: unresolved reference: dependencies dependencies { ^ build.gradle.kts:27:5: error: unresolved reference: compileOnly compileOnly("org.sonarsource.api.plugin:sonar-plugin-api:$minSonarQubeApiVersion") ^ build.gradle.kts:28:5: error: unresolved reference: compileOnly compileOnly("com.felipebz.zpa:sonar-zpa-plugin:3.3.0-SNAPSHOT") ^ build.gradle.kts:29:5: error: unresolved reference: testImplementation testImplementation("org.sonarsource.sonarqube:sonar-plugin-api-impl:$minSonarQubeVersion") ^ build.gradle.kts:30:5: error: unresolved reference: testImplementation testImplementation("com.felipebz.zpa:zpa-checks-testkit:3.3.0-SNAPSHOT") ^ build.gradle.kts:31:5: error: unresolved reference: testImplementation testImplementation("org.junit.jupiter:junit-jupiter:5.8.0") ^ build.gradle.kts:32:5: error: unresolved reference: implementation implementation group("'org.sonarsource.sslr', name: 'sslr-core', version: '1.20.0'") ^ build.gradle.kts:33:5: error: unresolved reference: implementation implementation group("'org.sonarsource.sonarqube', name: 'sonar-plsql-plugin', version: '3.7.0.2773'") ^ build.gradle.kts:37:1: error: unresolved reference: configurations configurations { ^ build.gradle.kts:39:5: error: unresolved reference: testImplementation testImplementation { ^ build.gradle.kts:40:9: error: unresolved reference: extendsFrom extendsFrom(configurations.compileOnly.get()) ^ build.gradle.kts:40:21: error: unresolved reference: configurations extendsFrom(configurations.compileOnly.get()) ^ build.gradle.kts:44:1: error: unresolved reference: tasks tasks.test { ^ build.gradle.kts:45:5: error: unresolved reference: useJUnitPlatform useJUnitPlatform() ^ build.gradle.kts:46:5: error: unresolved reference: testLogging testLogging { ^ build.gradle.kts:47:9: error: unresolved reference: events events("passed", "skipped", "failed") ^ build.gradle.kts:51:1: error: unresolved reference: tasks tasks.jar { ^ build.gradle.kts:52:5: error: unresolved reference: manifest manifest { ^ build.gradle.kts:55:9: error: unresolved reference: attributes attributes(mapOf( ^ build.gradle.kts:61:41: error: unresolved reference: project "Plugin-Display-Version" to project.version, ^ build.gradle.kts:65:33: error: unresolved reference: project "Plugin-Version" to project.version, ^ build.gradle.kts:72:1: error: unresolved reference: group group = "com.company" ^ build.gradle.kts:73:1: error: unresolved reference: version version = "1.0-SNAPSHOT" ^ build.gradle.kts:74:1: error: unresolved reference: description description = "Company PL/SQL Rules" ^ build.gradle.kts:76:1: error: unresolved reference: tasks tasks.withType() { ^ build.gradle.kts:76:16: error: unresolved reference: JavaCompile tasks.withType() { ^ build.gradle.kts:77:5: error: unresolved reference: options options.encoding = "UTF-8" ^ build.gradle.kts:77:13: error: variable expected options.encoding = "UTF-8" ^

Process finished with exit code 1

Please let me know how to fix it

[csrvsk]

Can anyone please respond to this issue

[csrvsk]

Now, i see a different error

Task :compileJava FAILED

FAILURE: Build failed with an exception.

• What went wrong: Execution failed for task ':compileJava'.

  Could not resolve all files for configuration ':compileClasspath'. Could not find 'org.sonarsource.sslr', name: 'sslr-core', version: '1.20.0'. Searched in the following locations:

  • https://repo.maven.apache.org/maven2/'org/sonarsource/sslr', name/ 'sslr-core', version/ '1.20.0'/ 'sslr-core', version- '1.20.0'.pom
  • file:/C:/Users/shiva/.m2/repository/'org/sonarsource/sslr', name/ 'sslr-core', version/ '1.20.0'/ 'sslr-core', version- '1.20.0'.pom Required by: project : Could not find 'org.sonarsource.sonarqube', name: 'sonar-plsql-plugin', version: '3.7.0.2773'. Searched in the following locations:
  • https://repo.maven.apache.org/maven2/'org/sonarsource/sonarqube', name/ 'sonar-plsql-plugin', version/ '3.7.0.2773'/ 'sonar-plsql-plugin', version- '3.7.0.2773'.pom
  • file:/C:/Users/shiva/.m2/repository/'org/sonarsource/sonarqube', name/ 'sonar-plsql-plugin', version/ '3.7.0.2773'/ 'sonar-plsql-plugin', version- '3.7.0.2773'.pom Required by: project :

• Try:

  Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output. Run with --scan to get full insights.

• Get more help at https://help.gradle.org

BUILD FAILED in 2s 1 actionable task: 1 executed

I am not sure where can i get the exact repo & plugin details to be added Help me here

[felipebz]

Hi,

Here are some helpful resources for creating a plugin with custom rules:

• Documentation: https://github.com/felipebz/zpa/wiki/Create-a-plugin-with-custom-rules
• Example: https://github.com/felipebz/zpa/tree/3.2.1/plsql-custom-rules

Let me know if need additional guidance.

[csrvsk]

Could you please point to where exactly it went wrong

And is there any issue with rule class I wrote?

[csrvsk]

Her is my build.gradle.kts

import java.time.ZoneId import java.time.format.DateTimeFormatter import java.util.*

plugins { java }

repositories { mavenCentral() mavenLocal() maven { url = uri("https://s01.oss.sonatype.org/content/repositories/snapshots/") url = uri("https://plugins.gradle.org/m2/") url = uri("https://repo.maven.apache.org/maven2/") } }

java { toolchain { languageVersion.set(JavaLanguageVersion.of(11)) } }

val minSonarQubeVersion = "9.9.0.65466" val minSonarQubeApiVersion = "9.14.0.375"

dependencies { compileOnly("org.sonarsource.api.plugin:sonar-plugin-api:$minSonarQubeApiVersion") compileOnly("com.felipebz.zpa:sonar-zpa-plugin:3.3.0-SNAPSHOT") testImplementation("org.sonarsource.sonarqube:sonar-plugin-api-impl:$minSonarQubeVersion") testImplementation("com.felipebz.zpa:zpa-checks-testkit:3.3.0-SNAPSHOT") testImplementation("org.junit.jupiter:junit-jupiter:5.8.0") compileOnly("'org.sonarsource.sslr', name: 'sslr-core', version: '1.20.0'") compileOnly("'org.sonarsource.sonarqube', name: 'sonar-plsql-plugin', version: '3.7.0.2773'")

}

configurations { // include compileOnly dependencies during test testImplementation { extendsFrom(configurations.compileOnly.get()) } }

tasks.test { useJUnitPlatform() testLogging { events("passed", "skipped", "failed") } }

tasks.jar { manifest { val buildDate = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ssZ").withZone(ZoneId.systemDefault()).format( Date().toInstant()) attributes(mapOf( "Plugin-BuildDate" to buildDate, "Plugin-ChildFirstClassLoader" to "false", "Plugin-Class" to "com.company.plsql.PlSqlCustomRulesPlugin", "Plugin-Description" to "PL/SQL Custom Rules", "Plugin-Developers" to "", "Plugin-Display-Version" to project.version, "Plugin-Key" to "myrules", "Plugin-License" to "", "Plugin-Name" to "Company PL/SQL Rules", "Plugin-Version" to project.version, "Sonar-Version" to minSonarQubeVersion, "SonarLint-Supported" to "false" )) } }

group = "com.company" version = "1.0-SNAPSHOT" description = "Company PL/SQL Rules"

tasks.withType() { options.encoding = "UTF-8" }

[felipebz]

Let me take a step back and clarify what syntax you are attempting to validate. As far as I understand, it seems like you are trying to create a check for substitution variables.

For instance, you might have SQL code like this:

SELECT tab.col
FROM &TABLE tab; -- register issue "Avoid using '&' or '&&' in SQL code"

However, due to some internal limitations, the &TABLE in this specific example is discarded, causing the plugin to see the code as follows:

SELECT tab.col
FROM        tab; -- register issue "Avoid using '&' or '&&' in SQL code"

This limitation makes it impossible to write a check for this code. However, please note that the & is preserved in comments and strings, which means you can validate these values in your rule. For example:

begin
  -- a comment with a &word
  x := '&value';
end;

I'm not sure if this meets your expectations, keeping in mind that it won't report all usages of substitution variables.

Regarding the code you sent, the following dependencies are not necessary:

compileOnly("'org.sonarsource.sslr', name: 'sslr-core', version: '1.20.0'") compileOnly("'org.sonarsource.sonarqube', name: 'sonar-plsql-plugin', version: '3.7.0.2773'")

Finally, here is the complete code for the check class, which reports issues in comments and strings containing &:

import org.sonar.plugins.plsqlopen.api.checks.PlSqlCheck;
import org.sonar.plugins.plsqlopen.api.sslr.Token;
import org.sonar.plugins.plsqlopen.api.sslr.Trivia;

@Rule(key = "AvoidANDSymbolCheck")
public class AvoidANDSymbolCheck extends PlSqlCheck {

    private static final String MESSAGE = "Avoid using '&' or '&&' in SQL code";

    @Override
    public void visitToken(Token token) {
        if (token.getValue().contains("&")) {
            addIssue(token, MESSAGE);
        }
    }

    @Override
    public void visitComment(Trivia trivia, String content) {
        if (content.contains("&")) {
            addIssue(trivia.getToken(), MESSAGE);
        }
    }
}

Please note that the all imported classes come from the org.sonar.plugins.plsqlopen.api package.

[csrvsk]

Thanks Filepe, those errors are gone. But my tests are failing

Task :test FAILED

AvoidANDSymbolCheckTest > test() FAILED org.sonar.plugins.plsqlopen.api.sslr.RecognitionException at AvoidANDSymbolCheckTest.java:10

I checked the failed test report at "zpa-main/plsql-custom-rules/build/reports/tests/test/classes/com.company.plsql.AvoidANDSymbolCheckTest.html" 

It says

org.sonar.plugins.plsqlopen.api.sslr.RecognitionException: Parse error at line 5 column 83:

1: DECLARE
2:    v_name VARCHAR2(20);
3:    v_id NUMBER(10);
4: BEGIN

--> SELECT first_name, last_name INTO v_name FROM employees WHERE employee_id = &id AND department_id = &&dept_id; 6: DBMS_OUTPUT.PUT_LINE('Employee name is ' || v_name); 7: END; 8: 9: SELECT * 10: FROM employees 11: WHERE department_id = && salary >

============================== I am sure there aren't any issues with syntax of this test code. Not sure if it is unable to recognize this "&" symbol. Please suggest how to get this fixed.

====================================

I also tried to escape the symbols by replacing & symbol with adding backslash before the symbol '\&' or '"&"id' etc.. by using SET DEFINE OFF, SET ESCAPE ON, SET SCAN OFF etc..

but when i tried that i see a different error than RecognitionException

it is as shown below

java.lang.AssertionError: Unexpected issue at line 6: "Avoid using '&' or '&&' in SQL code" at org.sonar.plsqlopen.checks.verifier.PlSqlCheckVerifier$Companion.verify(PlSqlCheckVerifier.kt:121) at org.sonar.plsqlopen.checks.verifier.PlSqlCheckVerifier$Companion.verify$default(PlSqlCheckVerifier.kt:101) at org.sonar.plsqlopen.checks.verifier.PlSqlCheckVerifier$Companion.verify(PlSqlCheckVerifier.kt)

[csrvsk]

Hi Filepe, Could you please suggest a fix for this issue

[felipebz]

Hi,

Unfortunately, the plugin is unable to analyze your code accurately due to limitations in its programming, and it discards all substitution variables. To clarify, here is the code you sent:

DECLARE
   v_name VARCHAR2(20);
   v_id NUMBER(10);
BEGIN
  SELECT first_name, last_name INTO v_name FROM employees WHERE employee_id = &id AND department_id = &&dept_id;
  DBMS_OUTPUT.PUT_LINE('Employee name is ' || v_name);
END;

And this is how the plugin sees it without the variables:

DECLARE
   v_name VARCHAR2(20);
   v_id NUMBER(10);
BEGIN
  SELECT first_name, last_name INTO v_name FROM employees WHERE employee_id =     AND department_id =          ;
  DBMS_OUTPUT.PUT_LINE('Employee name is ' || v_name);
END;

The WHERE clause is missing the variables, rendering the code invalid. As a result of this limitation, your rule cannot be implemented effectively. I can't suggest any other way to implement this rule.