AesEncryptDecrypt UDX

The AES Encrypt/Decrypt UDX performs Advanced Encryption Standard (AES) encryption / decryption, which is compatible with the MySQL functions AES_ENCRYPT and AES_DECRYPT. You can call the UDX multiple times within a single pipeline. Typically, you will call it twice — once for encryption and once for decryption.

Note: Encryption libraries are not included with SQLstream. Rather, the AesEncryptDecrypt UDX dynamically links to encryption libraries in the underlying OS or JVM (Java Virtual Machine).

Input parameters for the UDX are defined in a table below. They include:

  • A boolean encrypt/decrypt indicator to specify either encryption or decryption.
  • The name of a file containing the AES encryption key. This file is stored in the $SQLSTREAM_HOME/plugin/jndi directory and secured for read access by the s-Server user. It is a standard java properties file with a line containing key=value (“value” being the 16-byte encryption key).
    • Note: $SQLSTREAM_HOME stands for the installation directory for s-Server, typically /opt/sqlstream/<VERSION>/s-Server.
  • A list of input columns (as defined in the input cursor) that also appear in the list of output columns (as defined in the SQL function). Input columns that do not appear in the output column list are discarded. Input columns are copied to output columns based on matching case-sensitive names.

This page includes information on the following subtopics:

Mapping

Input columns which appear in the mapping column list are encrypted/decrypted according to a mapping definition, a mapping character, and a separator character. Mapped columns must appear in the output column list. Mapped columns which appear in the output column list are encrypted/decrypted. Null SQL columns are not encrypted/decrypted and are set to Null on output.

The mapping definition, mapping character, and separator character may alternately be placed in the same properties file as the key with the following property names: KEYMAP, KEYPAIRSEPARATOR, and KEYMAPSEPARATOR. For example: mapping column list ‘c1=c1enc;c2=C2ENC’ and separator ‘;’ and mapping ‘=’ indicates that input column c1 is encrypted/decrypted to output column c1enc and input columns c2 is encrypted/decrypted to output column C2ENC.

Encrypted Columns

Encrypted columns must be of type VARBINARY and of at least the proper multiple of 16 bytes. (Longer VARBINARY is acceptable.) Encryption is performed on character strings. Columns which are defined with data types other than CHAR or VARCHAR are converted to a character string (for encryption) and from a character string (for decryption).

On encryption, the UDX pads values with values equal to the length of the padded data. This corresponds to RFC 2630 - see https://www.di-mgt.com.au/cryptopad.html. On decryption, the UDX strips padding as appropriate. (These practices are compatible with MySQL AES_ENCRYPT and AES_DECRYPT functions for encrypting CHAR and VARCHAR. CHAR and VARCHAR strings have trailing spaces trimmed before encryption.) A column value which has been encrypted and later decrypted is assumed to have the identical datatype in its original value as after being encrypted and then decrypted. For example, a column with a BIGINT value is a 64 bit value, and will be encrypted and then decrypted to a BIGINT. If the datatypes of decrypted data are not correct, the results are indeterminate.

Assumptions:

VARCHAR columns do not contain trailing \0 values.

Parameter Definitions

Parameter Type Description
inputRows CURSOR The input stream of rows
Encrypt BOOLEAN Encryption (true) or decryption (false)
Dsname VARCHAR(64) File name in the $SQLSTREAM_HOME/plugin/jndi directory containing the AES encryption key
colNamesMap VARCHAR(1024) The mapping definition for input to output columns. Each input column name must be in the input stream. Each output column name must be in the output stream. If the colNamesMap is an empty string ‘’, the map is found in the Dsname file.
colPairSeparator CHAR(1) Character used to separate individual input to output column mappings. Usually a semi-colon (;) symbol.
colMapSeparator CHAR(1) Character used to define the input column to encrypted/decrypted output column. Usually an equal (=) sign.

Validation Errors

Validation errors are logged to the Trace.log.N file. The following errors are logged.

Trace Log Message Meaning
Column Map Separator must be 1 character in length Column map separator character not defined as 1 UTF-8 character
Column Pair Separator must be 1 character in length Column pair separator character not defined as UTF-8 character
Column Pair Separator must differ from Column Map Separator Column name separator character and the column pair separator character cannot be the same UTF-8 character.
Column Pair definition not <input_col> colMapSeparator <output_col> Column pair definition is missing a column pair separator
Column Pair definition <input_col> colMapSeparator <output_col> does not have an input column Column pair definition references an input column name which is not in the input stream
Column Pair definition <input_col> colMapSeparator <output_col> does not have an output column Column pair definition references an output column name which is not in the output stream
Column Pair definition <input_col> colMapSeparator <output_col> cannot also be an input column Column pair definition references an output column column name which is also an input column name
Column Pair definition <input_col> colMapSeparator <output_col> has an output column which is an incompatible type for AES Encryption VARBINARY encrypted column is too narrow to hold the encrypted format for the input data.

Trace Definitions

You can set the tracing level for the AESEncryptDecrypt UDX in $SQLSTREAM_HOME/trace/Trace.properties, by changing the following:

com.sqlstream.plugin.EncryptDecrypt.AESEncryptDecrypt.level
Trace level Meaning
WARNING Error Conditions which disturb successful processing
INFO Logs configuration definitions
FINE Logs input and output column definitions at UDX initialization
FINER Logs mapping input and output column definitions at UDX initialization
FINEST Logs input and output column data (row by row)

Note that logging at the finest level is for debugging encryption. The contents of the Trace.log.N will contain both encrypted and decrypted data.

For installation:

  1. Add the JAR to s-Server by using code along the following lines:

    create jar ENCRYPTDECRYPT library 'file:plugin/AesEncryptDecrypt.jar' options(0);
    
  2. Create the key properties file in $SQLSTREAM_HOME/plugin/jndi/ directory.

Example UDX Definition of Encryption

The following SQL creates a function called “Encrypt” which makes use of the AesEncryptDecrypt UDX to encrypt a set of input columns defined in colNamesMap and listed in the table returned by the function.

drop schema test cascade;
create schema test;

set schema 'TEST';
set path 'TEST';

create jar EncryptDecrypt library 'file:plugin/AesEncryptDecrypt.jar' options(0);

CREATE OR REPLACE FUNCTION Encrypt(
inputRows   CURSOR,  -- input stream
encrypt   BOOLEAN,  -- perform encryption or decryption
dsName    VARCHAR(64), -- name of file in the JNDI directory which contains the AES key
colNamesMap  VARCHAR(128), -- set of column names to be encrypted
colPairSeparator VARCHAR(1), -- separator character for list of column names
colMapSeparator VARCHAR(1) -- mapping character
)
RETURNS TABLE(
   -- un-modified columns from input stream
     ROWTIME        TIMESTAMP,
       col1         BIGINT,
       col2         INT,
       col3         SMALLINT,
       col4         BOOLEAN,
       col5         CHAR(16),
       col6         VARCHAR(16),
       col7         CHAR(40),
       col8         VARCHAR(40),
       -- encrypted columns, must be VARBINARY and a multiple of 16 bytes
       -- these column names cannot appear in the input stream
       col1Enc       VARBINARY(16),
       col2Enc       VARBINARY(16),
       col3Enc       VARBINARY(16),
       col4Enc       VARBINARY(16),
       col5Enc       VARBINARY(32),
       col6Enc       VARBINARY(32),
       col7Enc       VARBINARY(48),
       col8Enc       VARBINARY(48)

)
 LANGUAGE JAVA
 PARAMETER STYLE SYSTEM DEFINED JAVA
 NO SQL
 EXTERNAL NAME 'EncryptDecrypt:com.sqlstream.plugin.aes.AesEncryptDecrypt.aesEncryptDecrypt';

Sample Properties file contents

key=abcdefghijlmnopq

colPairSeparator=;

Stream Using Encrypt Function

The stream below makes use of this function. Eight input columns are encrypted and the resulting stream has 8 unencrypted columns and 8 encrypted columns.

CREATE OR REPLACE STREAM InputStream (
 col1         BIGINT,
 col2         INT,
 col3         SMALLINT,
 col4         BOOLEAN,
 col5         CHAR(16),
 col6         VARCHAR(16),
 col7         CHAR(40),
 col8         VARCHAR(40)
) DESCRIPTION 'test';

CREATE OR REPLACE VIEW InputEncrypted
DESCRIPTION 'Encrypt the input data' AS
 SELECT STREAM *
 FROM STREAM(Encrypt(
--STREAM keyword is necessary because UDX
--returns an infinite (streaming) result
     CURSOR(SELECT STREAM *
         FROM InputStream), -- input stream
     true,
     'my_key',
     'col1=col1Enc;' ||
     'col2=col2Enc;' ||
     'col3=col3Enc;' ||
     'col4=col4Enc;' ||
     'col5=col5Enc;' ||
     'col6=col6Enc;' ||
     'col7=col7Enc;' ||
     'col8=col8Enc' ,
     '', -- ';',
     '='
     ));

Example UDX Definition of Decryption

The following SQL creates a function called “Decrypt” which makes use of the AesEncryptDecrypt UDX to decrypt a set of input columns defined in colNamesMap and listed in the table returned by the function.

drop schema test cascade;
create schema test;
set schema 'TEST';
set path 'TEST';

create jar EncryptDecrypt library 'file:plugin/AesEncryptDecrypt.jar' options(0);

CREATE OR REPLACE FUNCTION Decrypt(
  inputRows     CURSOR,    -- input stream
  encrypt      BOOLEAN,   -- perform encryption or decryption  
  dsName       VARCHAR(64), -- name of file in the JNDI directory which contains the AES key
  colNamesMap    VARCHAR(128), -- set of column names to be decrypted
  colPairSeparator  VARCHAR(1),  -- separator character for list of column names
  colMapSeparator  VARCHAR(1)  -- mapping character
  )
  RETURNS TABLE(
        -- un-modified columns from input stream
        ROWTIME        TIMESTAMP,
        col1         BIGINT,
        col2         INT,
        col3         SMALLINT,
        col4         BOOLEAN,
        col5         CHAR(16),
        col6         VARCHAR(16),
        col7         CHAR(40),
        col8         VARCHAR(40),
        -- encrypted columns, must be VARBINARY and a multiple of 16 bytes
        col1Enc       VARBINARY(16),
        col2Enc       VARBINARY(16),
        col3Enc       VARBINARY(16),
        col4Enc       VARBINARY(16),
        col5Enc       VARBINARY(32),
        col6Enc       VARBINARY(32),
        col7Enc       VARBINARY(48),
        col8Enc       VARBINARY(48),
        -- decrypted columns, should be the same as the original
        col1Dec       BIGINT,
        col2Dec       INT,
        col3Dec       SMALLINT,
        col4Dec       BOOLEAN,
        col5Dec       CHAR(16),
        col6Dec       VARCHAR(16),
        col7Dec       CHAR(40),
        col8Dec       VARCHAR(40)

 )
  LANGUAGE JAVA
  PARAMETER STYLE SYSTEM DEFINED JAVA
  NO SQL
  EXTERNAL NAME 'EncryptDecrypt:com.sqlstream.plugin.aes.AesEncryptDecrypt.aesEncryptDecrypt';

View Using Decrypt Function

The stream defined above is decrypted and the resulting stream has 8 unencrypted columns, 8 encrypted columns, and 8 decrypted columns (which match the original unencrypted columns).

Note: When a table function returns an infinite result, it needs to be wrapped with a STREAM(…) marker when invoked. s-Server will return an error otherwise.

SET SCHEMA 'Test';
CREATE OR REPLACE VIEW InputDecrypted
DESCRIPTION 'Decrypt the input data' AS
  SELECT STREAM *
  FROM STREAM(Decrypt(
--STREAM keyword is necessary because UDX
--returns an infinite (streaming) result
      CURSOR(SELECT STREAM *
      FROM InputEncrypted), -- input stream
      false,
      'my_key',
      'col1Enc=col1Dec;' ||
      'col2Enc=col2Dec;' ||
      'col3Enc=col3Dec;' ||
      'col4Enc=col4Dec;' ||
      'col5Enc=col5Dec;' ||
      'col6Enc=col6Dec;' ||
      'col7Enc=col7Dec;' ||
      'col8Enc=col8Dec' ,
      '', -- ';',
      '='
      ));