using database encryption and decryption to fill in Oracle pagination SQL fields resulted in invalid 'ROWNUM' identifier

aillamsun commented 2 weeks ago

use version

<dependency>
  <groupId>org.apache.shardingsphere</groupId>
  <artifactId>shardingsphere-jdbc-core</artifactId>
  <version>5.2.1</version>
</dependency>

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1</version>
</dependency>

configuration

TB_VEHICLE_AUTH_REQUEST_LOGS: 
  columns:
    VIN:
      plainColumn: VIN
      cipherColumn: VIN_CIPHER
      assistedQueryColumn: VIN_QUERY_CIPHER 
      assistedQueryEncryptorName: assisted_query_encryptor 
      encryptorName: kms_encryptor
    PHONE: 
      plainColumn: PHONE
      cipherColumn: PHONE_CIPHER
      assistedQueryColumn: PHONE_QUERY_CIPHER 
      assistedQueryEncryptorName: assisted_query_encryptor 
      encryptorName: kms_encryptor
    VEHICLE_PLATE_NO: 
      plainColumn: VEHICLE_PLATE_NO
      cipherColumn: VEHICLE_PLATE_NO_CIPHER
      assistedQueryColumn: VEHICLE_PLATE_NO_QUERY_CIPHER 
      assistedQueryEncryptorName: assisted_query_encryptor 
      encryptorName: kms_encryptor

Problem occurred

1.Logic SQL

SELECT *
FROM 
    (
    SELECT TMP.*,
         ROWNUM ROW_ID
    FROM 
        (SELECT ID,
        TYPE,
        USER_ID,
        PHONE,
        VEHICLE_PLATE_NO,
        VIN,
        CHANGE_LOG,
        CREATE_TIME
        FROM TB_VEHICLE_AUTH_REQUEST_LOGS
        ORDER BY  CREATE_TIME DESC ) TMP
        WHERE ROWNUM <=?)
    WHERE ROW_ID > ?

2.Actual SQL


SELECT "ID",
         "TYPE",
         "USER_ID",
         "PHONE_CIPHER" AS "PHONE",
         "VEHICLE_PLATE_NO_CIPHER" AS "VEHICLE_PLATE_NO",
         "VIN_CIPHER" AS "VIN",
         "CHANGE_LOG",
         "CREATE_TIME",
         "ROWNUM" AS "ROW_ID"
FROM 
    (SELECT "ID",
         "TYPE",
         "USER_ID",
         "PHONE_CIPHER",
         "PHONE_QUERY_CIPHER",
         "PHONE",
         "VEHICLE_PLATE_NO_CIPHER",
         "VEHICLE_PLATE_NO_QUERY_CIPHER",
         "VEHICLE_PLATE_NO",
         "VIN_CIPHER",
         "VIN_QUERY_CIPHER",
         "VIN",
         "CHANGE_LOG",
         "CREATE_TIME",
         ROWNUM ROW_ID
    FROM 
        (SELECT ID,
        TYPE,
        USER_ID,
        PHONE_CIPHER,
         PHONE_QUERY_CIPHER,
         PHONE,
        VEHICLE_PLATE_NO_CIPHER,
         VEHICLE_PLATE_NO_QUERY_CIPHER,
         VEHICLE_PLATE_NO,
        VIN_CIPHER,
         VIN_QUERY_CIPHER,
         VIN,
        CHANGE_LOG,
        CREATE_TIME
        FROM TB_VEHICLE_AUTH_REQUEST_LOGS
        ORDER BY  CREATE_TIME DESC ) TMP
        WHERE ROWNUM <=?)
    WHERE ROW_ID > ?

result

as the boss encountered this problem?

terrymanu commented 2 weeks ago

Can you try to the lasted version?

comecny commented 2 weeks ago

It seems that the Oracle 5.21 version does not support ROWNUM parsing, but the latest version does

aillamsun commented 2 weeks ago

Can you try to the lasted version?

use 5.4.1 or 5.5.0 error occurred


columns:
  VIN: 
    cipher:
      name: VIN_CIPHER
      encryptorName: kms_encryptor
    assistedQuery:
      name: VIN_QUERY_CIPHER
      encryptorName: assisted_query_encryptor
  PHONE:
    cipher:
      name: PHONE_CIPHER
      encryptorName: kms_encryptor
    assistedQuery:
      name: PHONE_QUERY_CIPHER
      encryptorName: assisted_query_encryptor
  VEHICLE_PLATE_NO: 
    cipher:
      name: VEHICLE_PLATE_NO_CIPHER
      encryptorName: kms_encryptor
    assistedQuery:
      name: VEHICLE_PLATE_NO_QUERY_CIPHER
      encryptorName: assisted_query_encryptor


SELECT
  *
FROM
  (
    SELECT
      TMP.*,
      ROWNUM ROW_ID
    FROM
      (
        SELECT
          ID,
          TYPE,
          USER_ID,
          PHONE,
          VEHICLE_PLATE_NO,
          VIN,
          CHANGE_LOG,
          CREATE_TIME
        FROM
          TB_VEHICLE_AUTH_REQUEST_LOGS
        ORDER BY
          CREATE_TIME DESC
      ) TMP
    WHERE
      ROWNUM <= ?
  )
WHERE
  ROW_ID > ?

error msg

Cause: java.sql.SQLException: Unsupported SQL operation: Can not support encrypt shorthand expand with subquery statement.

aillamsun commented 2 weeks ago

It seems that the Oracle 5.21 version does not support ROWNUM parsing, but the latest version does

I use 5.4.1 can't do it.

aillamsun commented 2 weeks ago

5.4.1 does it not support storing plaintext fields ? I need a data transition phase for my transformation.

5.2.1 support plaintext

terrymanu commented 6 hours ago

Could you follow the exception instruction: Do not use encrypt shorthand expand with subquery statement?

thicv commented 2 hours ago

5.4.1 does it not support storing plaintext fields ? I need a data transition phase for my transformation.

5.2.1 support plaintext

In the new version, data encryption function became pretty robust. plaintext is not necessary any more.

apache / shardingsphere