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:
This page includes information on the following subtopics:
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 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 | 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 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. |
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:
Add the JAR to s-Server by using code along the following lines:
create jar ENCRYPTDECRYPT library 'file:plugin/AesEncryptDecrypt.jar' options(0);
Create the key properties file in $SQLSTREAM_HOME/plugin/jndi/ directory.
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';
key=abcdefghijlmnopq
colPairSeparator=;
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' ,
'', -- ';',
'='
));
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';
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' ,
'', -- ';',
'='
));