2685051 - List of Requirements for Feature request for RAW encryption in SAP IQ 16

Feature request for ENCRYPT function, extended features.

List of requirements:

1. Document in the manuals whether a IQ-LOB license is required to use RAW encryption, as the generated encrypted string is a long varchar.

2. Document in the manuals whether IQ_SECURITY license is required

3. Fast performance optimized for IQ tables

4. Must have FORMAT=RAW feature

5. ENCRYPT can be used in an UPDATE statement with IQ tables.

6. When using the FORMAT=RAW option, for the same string to encrypt, the encrypted value must always be the same, especially when the same Initialization Vector is specified.

This will allow doing joins on encryted columns without the need of decrypting each value and save query performance execution times

7. Data encrypted with ENCRYPT function must be decrypted by DECRYPT function that returns the original human readable string

If SAP IQ uses the same encryption method, we would expect that the decrypt feature would return the same results as IQ,
therefore we can Encrypt and Decrypt using any database that supports RAW encryption.

8. This implies that DECRYPT function must also be optimized for IQ tables

9. Encrypted data export/import

There should be a command or tool that allows to export/import encrypted data.

Currently, we have to use the function BFILE to export long binary.
The exported data file cannot be reloaded

Example below:

--Insert 3 encrypted values
CREATE TABLE tencrypt (string varchar(16) null , vencrypt long binary null);
INSERT tencrypt (vencrypt) SELECT ENCRYPT( 'SybaseIQ', 'TheEncryptionKey','AES(FORMAT=RAW)','ThisIsTheIV' );
INSERT tencrypt (vencrypt) SELECT ENCRYPT( 'SybaseIQ', 'TheEncryptionKey','AES(FORMAT=RAW)','ThisIsTheIV' )
INSERT tencrypt (vencrypt) SELECT ENCRYPT( 'SybaseIQ', 'TheEncryptionKey','AES(FORMAT=RAW)','ThisIsTheIV' )
update tencrypt set string= convert (varchar (16), rowid(tencrypt ))

--Export data
select BFILE ('TestFile.txt', vencrypt) from tencrypt;

TestFile.txt's content looks wrong.

more TestFile.txt
ûÕüyâjÑÅX?¡Îl¿

od -c TestFile.txt
0000000 373 325 374 y 342 j 321 305 X ? 005 241 316 l 034 277

--Create a new table for reload
create table newtb (vencrypt long binary null);

--Load the encrypted data file in the new table

LOAD TABLE newtb
(vencrypt BINARY WITH NULL BYTE NULL('NULL'))
FROM '/TestFile.txt'
DELIMITED BY ','
ROW DELIMITED BY '\x0A'
ESCAPES OFF
QUOTES OFF;

I. 04/13 13:56:25. 0000001901 [20618]: Warning: Partial input record (16 bytes) skipped at EOF (O/S 2). 0 rows read.
I. 04/13 13:56:25. 0000001901 [20896]: Insert for 'newtb' completed in 0 seconds. 0 rows inserted.

SAP IQ 16

CR809842 , CR#809842, 809842, encrypt, decrypt, IV, Initialization, Vector, BFILE, , KBA , BC-SYB-IQ , Sybase IQ , Problem