Exasol JDBC Driver Improvement: TLS Certificate Support for Local Import/Export

Details

Detail name Value
Changelog Number 19453
Type Improvement
Status Resolved
Fix Versions JDBC 24.2.0
Resolution Date 2024-11-11

Background

Previously, when performing LOCAL IMPORT and EXPORT operations, the TLS certificate was previously not validated, leaving users vulnerable to potential security risks.

Improvement Overview

The Exasol JDBC driver has been enhanced to support TLS certificate verification during LOCAL IMPORT and EXPORT operations. The driver now validates the self-signed certificate generated during the data transfer process, providing better security. Additionally, the driver offers more flexible options for specifying certificate parameters in SQL statements, ensuring compatibility with user-defined settings and database configurations.

New Features & Functionality

  • Automatic Certificate Generation and Verification
    The Exasol JDBC driver continues to generate a self-signed certificate during LOCAL IMPORT and EXPORT operations. However, now, it also calculates the certificate's public key and includes it in the SQL statement when the database supports the corresponding TLS certificate verification option, ensuring that the certificate is validated.
  • Flexible Placement of Certificate Parameters
    The new Exasol JDBC driver allows certificate parameters to be specified either before or after the FILE keyword in SQL statements. This flexibility provides greater control over the statement structure. However, it is recommended to place the certificate parameters before the FILE keyword for clarity.
  • Error Handling for Multiple Certificate Parameter Locations
    If certificate parameters are specified in both locations (before and after the FILE keyword) in the same SQL statement, the following error will be thrown:
    Certificate Parameters are provided in multiple places.
  • User-Specified TLS Settings
    The driver respects user-specified TLS certificate settings in the SQL statement, such as VERIFY CERTIFICATE, IGNORE CERTIFICATE, or a specific PUBLIC KEY. These settings will override the driver's default behavior, allowing users to customize certificate validation as needed. As certificate for LOCAL IMPORT/EXPORT is generated on-the-fly, there aren’t use cases for “PUBLIC KEY“ clause in this scenarios yet.
  • Example: 
-- Recommended placement (before FILE)
IMPORT INTO Test.DB FROM LOCAL CSV VERIFY CERTIFICATE FILE 'Test.csv';

-- Alternative placement (after FILE)
IMPORT INTO Test.DB FROM LOCAL CSV FILE 'Test.csv' VERIFY CERTIFICATE;

Example SQL Syntax

-- Recommended: Add certificate parameters before FILE keyword
IMPORT INTO Test.DB FROM LOCAL CSV VERIFY CERTIFICATE PUBLIC KEY 'sha256//sdfsef' FILE 'Test.csv';

-- Alternative: Add certificate parameters after FILE keyword
IMPORT INTO Test.DB FROM LOCAL CSV FILE 'Test.csv' VERIFY CERTIFICATE PUBLIC KEY 'sha256//sdfsef';

-- Using older Exasol JDBC driver: Certificate parameters can only be added at the end
IMPORT INTO Test.DB FROM LOCAL CSV FILE 'Test.csv' IGNORE CERTIFICATE;

-- Error: Certificate parameters added in both places
-- This will throw an error: Certificate Parameters are provided in multiple places.
IMPORT INTO Test.DB FROM LOCAL CSV VERIFY CERTIFICATE FILE 'Test.csv' IGNORE CERTIFICATE;

Default Behavior

  • With enforced certificate verification: The driver automatically generates a self-signed certificate which is validated by the database by including the public key in the SQL statement.
  • Without enforced certificate verification: The driver will not include certificate parameters in the SQL statement, maintaining compatibility with older database versions.

Compatibility

  • Compatibility with Older Drivers: If TLS certificate validation is activated in the database and an older driver is used, the operation will fail due to the missing public key. To fix this, add IGNORE CERTIFICATE to the end of the SQL statement.

DB Supported

This feature is supported from Exasol DB 8.32+.

Additional Information

For more details on the new behavior and TLS settings, please refer to the following documentation pages:

Changelog: TLS Certificate Verification for Loader Connections except EXA, JDBC, ORA, CLOUD

Changed behavior

SQL Syntax Adjustments
Users can specify certificate parameters before or after the FILE keyword in the SQL statement. If the parameters are included in both locations, an error will be thrown. This is a change from older drivers, where parameters could only be added at the end.

Impact on Older Exasol JDBC Drivers
If the database has TLS certificate verification enabled by default, older Exasol JDBC drivers that do not include the public key in the SQL statement will not be able to perform LOCAL IMPORT and EXPORT operations. This will result in operation failures due to failing certificate validation.

Solution:
To resolve this issue, users must either:

  • Update the Exasol JDBC driver to the latest version, which supports automatic public key inclusion.
  • Modify their SQL statements to include the IGNORE CERTIFICATE keyword in the end of the SQL statement, bypassing certificate verification.