liuyu000 commented on a change in pull request #1825: [TRAFODION3293] Add the AES_ENCRYPT
Function in the Trafodion SQL Reference Manual
URL: https://github.com/apache/trafodion/pull/1825#discussion_r272905835
##########
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 blockbased encryption algorithm.
The default mode is the aes128ecb, which means the encryption using a key length of 128
bits and the ECB mode.
+
++
+[cols="50%,50%"]
+===
+^ *Value* ^ *Mode*
+^ 0 ^ aes128ecb
+^ 1 ^ aes192ecb
+^ 2 ^ aes256ecb
+===
+
++
+*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.
Review comment:
Thanks Roberta, I've incorporated your comments.
The developer for the AES_ENCRYPT function and the AES_DECRYPT function is a ghost user
in GitHub, so I've tested them by myself.
Q1. Are there limits to how long string can be?
I don't know how to test the accurate length for the `string`, I've tried 0, 1 ... 100,
and they all work.
MySQL Manual says the `string` and the `key` can be any length, I don't know whether it
is appropriate for Trafodion.
Q2. Any restrictions on what can be contained in str?
Q3. Any rules on what can be used as a key?
I've tested and found that the `string` and the `key` are casesensitive and can contain
a mix of uppercase and lowercase letters, and includes numbers and special characters. I've
added these descriptions.
Q4. Do we know the length of the encrypted value?
Q5. Is it less than or equal to the string?
MySQL Manual says:
> The str and crypt_str arguments can be any length, and padding is automatically added
to str
so it is a multiple of a block as required by blockbased algorithms such as AES. This
padding is
automatically removed by the AES_DECRYPT() function. The length of crypt_str can be calculated
using this formula: 16 * (trunc(string_length/16) + 1).
I've tested it on Trafodion as below, it seems that this formula is not appropriate for
Trafodion, I don't know how to get the correct formula, could you please help?
 The length of the `string` _aaaaaaaaaaaaaaaa_ is 16, and the length of the result (encrypted
value) is 24.
```
SELECT AES_ENCRYPT ('aaaaaaaaaaaaaaaa','b') FROM DUAL;
(EXPR)

P+¯{ÄPCöúúCüüñXWV(ÿÿçIÁ
 1 row(s) selected.
```
 The length of the `string` _aaaaaaaaaaaaaaaa_ is 16, and the length of the result (encrypted
value) is 21.
```
SELECT AES_ENCRYPT ('aaaaaaaaaaaaaaaa','8') FROM DUAL;
(EXPR)

0ÃbA>óWý°à3"?!ò!êyÞÅÜ
 1 row(s) selected.
```
 The length of the `string` _aaaaaaaaaaaaaaaa_ is 16, and the length of the result (encrypted
value) is 20.
```
SELECT AES_ENCRYPT ('aaaaaaaaaaaaaaaa','bB123&*') FROM DUAL;
(EXPR)

(;·ÚàÍ<+±PLO½r;è%{Í
 1 row(s) selected.
```

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:
users@infra.apache.org
With regards,
Apache Git Services
