Rename error codes from VS-BIGQ to VSBIGQ

Migrate BigQuery dialect implementation from the virtual-schemas repository

Summary

ThreeTen Backport v1.6.8 was discovered to contain an integer overflow via the component org.threeten.bp.format.DateTimeFormatter::parse(CharSequence, ParsePosition).

CVE: CVE-2024-23082
CWE: CWE-190

References

• https://ossindex.sonatype.org/vulnerability/CVE-2024-23082?component-type=maven&component-name=org.threeten%2Fthreetenbp&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
• http://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2024-23082
• https://gist.github.com/LLM4IG/d2618f5f4e5ac37eb75cff5617e58b90

See log messages from build job Dependency Check:

• io.netty:netty-handler:jar:4.1.86.Final in test
  • CVE-2023-34462, severity CWE-770: Allocation of Resources Without Limits or Throttling (6.5)
• com.google.guava:guava:jar:31.1-jre in test
  • CVE-2023-2976, severity CWE-552: Files or Directories Accessible to External Parties (7.1)

Excluded vulnerabilities:

• [CVE-2020-36641] CWE-611: Improper Restriction of XML External Entity Reference ('XXE') (9.8); https://ossindex.sonatype.org/vulnerability/CVE-2020-36641?component-type=maven&component-name=fr.turri%2FaXMLRPC&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
• [CVE-2020-8908] CWE-379: Creation of Temporary File in Directory with Incorrect Permissions (3.3); https://ossindex.sonatype.org/vulnerability/CVE-2020-8908?component-type=maven&component-name=com.google.guava%2Fguava&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1

Situation

When loading a table from BigQuery that contains a DATE, on the Exasol side it results in a VARCHAR instead of a DATE.

Example:
DATE value "09.01.2020" on BigQuery turned to a VARCHAR(10) UTF8 in Exasol.

Situation

We need to support nanosecond timestamp resolution.

Acceptance Criteria

• An integration test against Exasol 8 proves that TS(9) is supported

Situation

We have added a few new functions to the common part recently.
We need to check if some dialects could support them. The list of the new function capabilities to check:

• FN_BIT_LROTATE -> not supported
• FN_BIT_RROTATE -> not supported
• FN_BIT_LSHIFT -> added
• FN_BIT_RSHIFT -> added
• FN_FROM_POSIX_TIME -> not supported
• FN_HOUR -> not supported
• FN_INITCAP -> added
• FN_AGG_EVERY -> not supported
• FN_AGG_SOME -> not supported
• FN_AGG_MUL_DISTINCT -> not supported
• FN_PRED_IS_JSON -> not supported
• FN_PRED_IS_NOT_JSON -> not supported
• FN_HASHTYPE_MD5 -> not supported
• FN_HASHTYPE_SHA1 -> not supported
• FN_HASHTYPE_SHA256 -> not supported
• FN_HASHTYPE_SHA512 -> not supported
• FN_HASHTYPE_TIGER -> not supported
• FN_AGG_MUL -> not supported
• FN_JSON_VALUE -> function is supported, but doesn't cover all the functionality that Exasol has, so we will skip it
• FN_MIN_SCALE -> not supported
• FN_AGG_LISTAGG -> not supported
• FN_AGG_LISTAGG_DISTINCT -> not supported
• FN_AGG_LISTAGG_SEPARATOR -> not supported
• FN_AGG_LISTAGG_ON_OVERFLOW_ERROR -> not supported
• FN_AGG_LISTAGG_ON_OVERFLOW_TRUNCATE -> not supported
• FN_AGG_LISTAGG_ORDER_BY -> not supported
• FN_AGG_COUNT_TUPLE -> not supported

In order to verify the bugfix for #8 we need to add integration tests using the BigQuery JDBC driver and bigquery-emulator.

This requires changes to bigquery-emulator: goccy/bigquery-emulator#7

This issue would be useful: exasol/exasol-testcontainers#74

Add to class BigQueryDatasetFixture (and potentially others as well):

@SuppressWarnings("try") 
// auto-closeable resource virtualSchema is never referenced in body of corresponding try statement

Remove from file pom.xml, maven-compiler-plugin:

-try 

Compare exasol/virtual-schema-common-document#183

Summary

Netty is an asynchronous event-driven network application framework for rapid development of maintainable high performance protocol servers & clients. The HttpPostRequestDecoder can be tricked to accumulate data. While the decoder can store items on the disk if configured so, there are no limits to the number of fields the form can have, an attacher can send a chunked post consisting of many small fields that will be accumulated in the bodyListHttpData list. The decoder cumulates bytes in the undecodedChunk buffer until it can decode a field, this field can cumulate data without limits. This vulnerability is fixed in 4.1.108.Final.

CVE: CVE-2024-29025
CWE: CWE-770

References

• https://ossindex.sonatype.org/vulnerability/CVE-2024-29025?component-type=maven&component-name=io.netty%2Fnetty-codec-http&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
• http://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2024-29025
• GHSA-5jpm-x58v-624v

• org.json:json:jar:20230618 in test
  • CVE-2023-5072, severity CWE-770: Allocation of Resources Without Limits or Throttling (7.5)

https://github.com/goccy/bigquery-emulator
Check if BigQuery Emulator now supports the required features, see #9.

• org.json:json:jar:20220924 in test
  • CVE-2022-45688, severity CWE-787: Out-of-bounds Write (7.5)

Currently already version 2.23.2 com.google.cloud/google-cloud-bigquery is available on maven central.

Summary

ThreeTen Backport v1.6.8 was discovered to contain a NullPointerException via the component org.threeten.bp.LocalDate::compareTo(ChronoLocalDate).

CVE: CVE-2024-23081
CWE: CWE-476

References

• https://ossindex.sonatype.org/vulnerability/CVE-2024-23081?component-type=maven&component-name=org.threeten%2Fthreetenbp&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
• http://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2024-23081
• https://gist.github.com/LLM4IG/3cc9183dcd887020368a0bafeafec5e3

• com.google.protobuf:protobuf-java:jar:3.21.1 in test
  • CVE-2022-3171, severity CWE-400: Uncontrolled Resource Consumption ('Resource Exhaustion') (7.5)
• com.fasterxml.jackson.core:jackson-databind:jar:2.13.3 in test
  • CVE-2022-42003, severity CWE-502: Deserialization of Untrusted Data (7.5)
  • CVE-2022-42004, severity CWE-502: Deserialization of Untrusted Data (7.5)

Additionally check if the following currently excluded vulnerabilities need to remain so

• 1 vulnerability found (6.2); https://ossindex.sonatype.org/vulnerability/sonatype-2020-0926
• 1 vulnerability found (6.5); https://ossindex.sonatype.org/vulnerability/sonatype-2020-0026
• 1 vulnerability found (6.5); https://ossindex.sonatype.org/vulnerability/sonatype-2021-0818

Currently, joins are not pushed down to BigQuery.

However, this would be very useful when working with Virtual Schemas on BigQuery.

Update dependencies to use enhanced Datatype Detection For Result Sets from virtual-schemas-common-jdbc

Since 2023-06-02 for version 8.18.1 of Exasol database a Docker image is available on Dockerhub.

The current ticket therefore requests to update the integration tests of VSBIGQ to use version 8.18.1 as latest default version.

Please note sibling-tickets for all JDBC-based virtual schemas.

See https://github.com/exasol/bigquery-virtual-schema/actions/runs/4867663879/jobs/8680444060

Error:  Failed to execute goal org.sonatype.ossindex.maven:ossindex-maven-plugin:3.2.0:audit (default-cli) on project bigquery-virtual-schema: Detected 1 vulnerable components:
Error:    com.google.guava:guava:jar:31.1-jre:test; https://ossindex.sonatype.org/component/pkg:maven/com.google.guava/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
Error:      * [CVE-2020-8908] CWE-379: Creation of Temporary File in Directory with Incorrect Permissions (6.2); https://ossindex.sonatype.org/vulnerability/CVE-2020-8908?component-type=maven&component-name=com.google.guava%2Fguava&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
Error:  

Virtual-schema-shared-integration-tests provide generic integration tests.
Refactor this repository to use that library.

IntegrationTestSetup.java sets constant values for DEBUG_ADDRESS and LOG_LEVEL.

if (System.getProperty("test.vs-logs", "false").equals("true")) {
properties.put("DEBUG_ADDRESS", "127.0.0.1:3001");

Please consider to remove these lines and propose using the system properties supported by exasol/test-db-builder-java#103 in the documentation (either user_guide or developer_guide).