robertamarton commented on a change in pull request #1825: [TRAFODION-3293] Add
the AES_ENCRYPT Function in the Trafodion SQL Reference Manual
URL: https://github.com/apache/trafodion/pull/1825#discussion_r273145418
##########
File path:
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
##########
@@ -487,6 +487,141 @@ LARGEINT if the precision of the argument is greater
than or equal to
ABS (-20 + 12)
```
+<<<
+[[aes_encrypt_function]]
+== AES_ENCRYPT Function
+
+The AES_ENCRYPT function encrypts a `_string_` using a specified encryption
`_key_` with the AES (Advanced Encryption Standard) algorithm and returns a
binary string.
+
+This function prevents sensitive data from being visible to the public. For
example, use the AES_ENCRYPT function to encrypt person identity number to
preserve user confidentiality.
+
+`AES_ENCRYPT (_string_, _key_)`
+
+[[syntax_description_of_aes_encrypt]]
+=== Syntax Description of AES_ENCRYPT
+
+* `_string_`
++
+is the data to be encrypted.
+
+* `_key_`
++
+is the encryption key to encrypt the `_string_`.
+
++
+TIP: The CQD `block_encryption_mode` controls the mode for the block-based
encryption algorithm. The default mode is the aes-128-ecb, which means the
encryption using a key length of 128 bits and the ECB mode.
+
++
+[cols="50%,50%"]
+|===
+^| *Value* ^| *Mode*
+^| 0 ^| aes-128-ecb
+^| 1 ^| aes-192-ecb
+^| 2 ^| aes-256-ecb
+|===
+
++
+*Example*
+
++
+The following examples show that the AES_ENCRYPT function returns the
different results based on the different modes.
+
++
+** The mode aes_192_ecb is in effect.
+
++
+```
+CQD BLOCK_ENCRYPTION_MODE '1';
+
+--- SQL operation complete.
+```
+
++
+```
+SELECT AES_ENCRYPT ('Technical_Writer','Coder') FROM DUAL;
+
+(EXPR)
+--------------------------------
+4}\îî¢Øã^ûh<á¼Æ7õé³ê!cH^Ð
+```
+
++
+** The mode aes_256_ecb is in effect.
+
++
+```
+CQD BLOCK_ENCRYPTION_MODE '2';
+
+--- SQL operation complete.
+```
+
++
+```
+SELECT AES_ENCRYPT ('Technical_Writer','Coder') FROM DUAL;
+
+(EXPR)
+--------------------------------
+Ô_8ÆðÑ=Uú2g1¥@°Ô3yä
+
+--- 1 row(s) selected.
+```
+
+[[considerations_for_aes_encrypt]]
+=== Considerations for AES_ENCRYPT
+
+Either argument cannot be NULL.
+
+[[examples_of_aes_encrypt]]
+=== Examples of AES_ENCRYPT
+
+* This example encrypts the string _'2019-11-02 00:00:00'_ with the key
_'888'_.
+
++
+```
+SELECT AES_ENCRYPT ('2019-11-02 00:00:00', '888') FROM DUAL;
+
+(EXPR)
+--------------------------------
+ºMµ-b(úaP\ìqXqsnTç<`®
+
+--- 1 row(s) selected.
+```
+
+* This example encrypts the string _'Oct 9, 2013 was my first day in
Muenster'_ with the key _'Germany-10:06'_ and inserts the encrypted string into
the table _t1_.
+
Review comment:
I am not familiar with the AES_DECRYPT internals but it looks like the data
being returned causes issues with the terminal character set. It works fine
when using Hex:
SELECT converttohex (AES_ENCRYPT ('aaa','b')) FROM DUAL -> returns
0D15222D3D8E40ECCE7AEDC3B8C4280D
select aes_decrypt (X'0D15222D3D8E40ECCE7AEDC3B8C4280D', 'b') from dual ->
returns
aaa
However, if I:
create table t1 (a char (100));
insert into t1 select aes_encrypt('aaa', 'b') from dual;
select (aes_decrypt(a, 'b')) from t1
select converttohex(aes_decrypt(a, 'b')) from t1 -> both return:
*** ERROR[8957] OpenSSL returned error during call to function AES_DECRYPT
FUNCTION.
but select converttohex(a) from t1 -> returns that same data as above spaced
filled.
I looked in SQL regressions and there was no test case where data was
inserted encrypted.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
