fix: simple quotes escape in queries

[ranim-n]

I based my branch on the refactoring branch

[github-actions[bot]]

Test Results

   62 files  ±0     62 suites  ±0   1m 29s :stopwatch: +3s 1 010 tests +6  1 010 :white_check_mark: +6  0 :zzz: ±0  0 :x: ±0  1 049 runs  +6  1 049 :white_check_mark: +6  0 :zzz: ±0  0 :x: ±0 

Results for commit 549e591e. ± Comparison against base commit 7d9c62f3.

This pull request removes 217 and adds 67 tests. Note that renamed tests count towards both.

``` { "id":…, withTemporalValues=true, withAudit=false, expectation={ "@id": "https://uri… "@type": "@json", … "@value": "/A/B" "@value": "/C/D" "@value": 20 "… { "@type": "https://uri.etsi.org/ngsi-ld/DateTime", … ``` ``` com.egm.stellio.search.entity.service.EntityServiceQueriesTests ‑ [10] q=simpleQuoteString~="(?i).*It's a name.*", expectedCount=1, expectedListOfEntities=urn:ngsi-ld:BeeHive:01 com.egm.stellio.search.entity.service.EntityServiceQueriesTests ‑ [11] q=simpleQuoteString~="(?i)^it's.*", expectedCount=2, expectedListOfEntities=urn:ngsi-ld:BeeHive:01,urn:ngsi-ld:BeeHive:02 com.egm.stellio.search.entity.service.EntityServiceQueriesTests ‑ [12] q=simpleQuoteString=="It's a name", expectedCount=1, expectedListOfEntities=urn:ngsi-ld:BeeHive:01 com.egm.stellio.search.entity.service.EntityServiceQueriesTests ‑ [13] q=dateTime==2023-02-16T00:00:00Z, expectedCount=1, expectedListOfEntities=urn:ngsi-ld:BeeHive:01 com.egm.stellio.search.entity.service.EntityServiceQueriesTests ‑ [14] q=dateTime~=2023-02-16T00:00:00Z, expectedCount=1, expectedListOfEntities=urn:ngsi-ld:BeeHive:01 com.egm.stellio.search.entity.service.EntityServiceQueriesTests ‑ [15] q=dateTime>2023-02-16T00:00:00Z, expectedCount=1, expectedListOfEntities=urn:ngsi-ld:BeeHive:02 com.egm.stellio.search.entity.service.EntityServiceQueriesTests ‑ [16] q=boolean==true, expectedCount=1, expectedListOfEntities=urn:ngsi-ld:BeeHive:01 com.egm.stellio.search.entity.service.EntityServiceQueriesTests ‑ [17] q=observedProperty.observedAt>2023-02-25T00:00:00Z, expectedCount=1, expectedListOfEntities=urn:ngsi-ld:BeeHive:02 com.egm.stellio.search.entity.service.EntityServiceQueriesTests ‑ [18] q=observedProperty.observedAt>2023-02-01T00:00:00Z, expectedCount=2, expectedListOfEntities=urn:ngsi-ld:BeeHive:01,urn:ngsi-ld:BeeHive:02 com.egm.stellio.search.entity.service.EntityServiceQueriesTests ‑ [19] q=observedProperty.observedAt<2023-01-01T00:00:00Z, expectedCount=0, expectedListOfEntities=null … ```

:recycle: This comment has been updated with latest results.

[bobeal]

• out of curiosity, did a test with the == operator and the same error occurs
• cleaner and safer to use a PG function, quote_literal should do it (see https://www.postgresql.org/docs/16/functions-string.html)

[ranim-n]

• out of curiosity, did a test with the == operator and the same error occurs
• cleaner and safer to use a PG function, quote_literal should do it (see https://www.postgresql.org/docs/16/functions-string.html)

I tried using quote_literal but it's not working. It could be because it's used with json path

[bobeal]

• out of curiosity, did a test with the == operator and the same error occurs

• cleaner and safer to use a PG function, quote_literal should do it (see https://www.postgresql.org/docs/16/functions-string.html)

I tried using quote_literal but it's not working. It could be because it's used with json path

Please give some details

[ranim-n]

• out of curiosity, did a test with the == operator and the same error occurs

• cleaner and safer to use a PG function, quote_literal should do it (see https://www.postgresql.org/docs/16/functions-string.html)

I tried using quote_literal but it's not working. It could be because it's used with json path

Please give some details

I used the method like this :

""" jsonb_path_exists(#{TARGET}#, '$."${mainAttributePath[0]}"."$NGSILD_PROPERTY_VALUE"."$JSONLD_VALUE" ? (@ like_regex quote_literal($value))') """

and still got the same error : java.lang.IllegalArgumentException: Sql cannot be parsed: unclosed quoted identifier .. SELECT ... (@ like_regex quote_literal("(?i).*It's a name.*"))')

[bobeal]

• out of curiosity, did a test with the == operator and the same error occurs

• cleaner and safer to use a PG function, quote_literal should do it (see https://www.postgresql.org/docs/16/functions-string.html)

I tried using quote_literal but it's not working. It could be because it's used with json path

Please give some details

I used the method like this :

` """

    jsonb_path_exists(#{TARGET}#,

        '$."${mainAttributePath[0]}"."$NGSILD_PROPERTY_VALUE"."$JSONLD_VALUE" ? (@ like_regex quote_literal($value))')

    """`

and still got the same error :

    `java.lang.IllegalArgumentException: Sql cannot be parsed: unclosed quoted identifier .. SELECT ... (@ like_regex quote_literal("(?i).*It's a name.*"))')`

Did you try passing the value as a named parameter in the jsonb path function? (Like is done for some other queries)

[ranim-n]

• out of curiosity, did a test with the == operator and the same error occurs

• cleaner and safer to use a PG function, quote_literal should do it (see https://www.postgresql.org/docs/16/functions-string.html)

I tried using quote_literal but it's not working. It could be because it's used with json path

Please give some details

I used the method like this : ` """

    jsonb_path_exists(#{TARGET}#,

        '$."${mainAttributePath[0]}"."$NGSILD_PROPERTY_VALUE"."$JSONLD_VALUE" ? (@ like_regex quote_literal($value))')

    """`

and still got the same error :

    `java.lang.IllegalArgumentException: Sql cannot be parsed: unclosed quoted identifier .. SELECT ... (@ like_regex quote_literal("(?i).*It's a name.*"))')`

Did you try passing the value as a named parameter in the jsonb path function? (Like is done for some other queries)

I didn't understand your suggestion well

[bobeal]

• out of curiosity, did a test with the == operator and the same error occurs

• cleaner and safer to use a PG function, quote_literal should do it (see https://www.postgresql.org/docs/16/functions-string.html)

I tried using quote_literal but it's not working. It could be because it's used with json path

Please give some details

I used the method like this : ` """

    jsonb_path_exists(#{TARGET}#,

        '$."${mainAttributePath[0]}"."$NGSILD_PROPERTY_VALUE"."$JSONLD_VALUE" ? (@ like_regex quote_literal($value))')

    """`

and still got the same error :

    `java.lang.IllegalArgumentException: Sql cannot be parsed: unclosed quoted identifier .. SELECT ... (@ like_regex quote_literal("(?i).*It's a name.*"))')`

Did you try passing the value as a named parameter in the jsonb path function? (Like is done for some other queries)

I didn't understand your suggestion well

instead of doing:

jsonb_path_exists(#{TARGET}#,
            '$."${mainAttributePath[0]}"."$NGSILD_PROPERTY_VALUE"."$JSONLD_VALUE" ? (@ like_regex $value)')

doing something like this (wondering if in this case the PG query parser will process the quote_literal before calling the jsonb function):

        jsonb_path_exists(#{TARGET}#,
            '$."${mainAttributePath[0]}"."$NGSILD_PROPERTY_VALUE"."$JSONLD_VALUE" ? (@ like_regex ${'$'}value)',
            '{ "value": quote_literal($value) }')

[ranim-n]

${'$'}value)', '{ "value": quote_literal($value) }')

I got the same error :

java.lang.IllegalArgumentException: Sql cannot be parsed: unclosed quoted identifier (identifier opened at index 423) in statement: SELECT ... (@ like_regex $value)', '{ "value": quote_literal("(?i).*It's a name.*") }')

[bobeal]

${'$'}value)', '{ "value": quote_literal($value) }')

I got the same error :

java.lang.IllegalArgumentException: Sql cannot be parsed: unclosed quoted identifier (identifier opened at index 423) in statement: SELECT ... (@ like_regex $value)', '{ "value": quote_literal("(?i).*It's a name.*") }')

too bad.

[bobeal]

@thomasBousselin your review is missing

[sonarcloud[bot]]

Quality Gate passed

Issues
3 New issues
0 Accepted issues

Measures
0 Security Hotspots
0.0% Coverage on New Code
0.0% Duplication on New Code

See analysis details on SonarCloud