Dependencies causing trouble to have a new custom rule

Question

Dependencies causing trouble to have a new custom rule

Closed this issue a year ago · 9 comments

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

Answer 1 · 2023-04-13T13:32:00.000Z

Can anyone please respond to this issue

Answer 2 · 2023-04-13T13:58:51.000Z

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

Answer 3 · 2023-04-13T16:08:16.000Z

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.

Answer 4 · 2023-04-13T16:11:18.000Z

Could you please point to where exactly it went wrong

And is there any issue with rule class I wrote?

Answer 5 · 2023-04-13T17:36:40.000Z

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"
}

Answer 6 · 2023-04-14T01:18:34.000Z

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.

Answer 7 · 2023-04-14T02:27:23.000Z

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)

Answer 8 · 2023-04-17T02:30:04.000Z

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

Answer 9 · 2023-04-18T11:16:01.000Z

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.