Syntax:
AES_ENCRYPT(str,key_str[,init_vector][,kdf_name][,salt][,info |
iterations])

AES_ENCRYPT() and AES_DECRYPT() implement encryption and decryption of
data using the official AES (Advanced Encryption Standard) algorithm,
previously known as "Rijndael." The AES standard permits various key
lengths. By default these functions implement AES with a 128-bit key
length. Key lengths of 196 or 256 bits can be used, as described later.
The key length is a trade off between performance and security.

AES_ENCRYPT() encrypts the string str using the key string key_str, and
returns a binary string containing the encrypted output. AES_DECRYPT()
decrypts the encrypted string crypt_str using the key string key_str,
and returns the original (binary) string in hexadecimal format. (To
obtain the string as plaintext, cast the result to CHAR. Alternatively,
start the mysql client with --skip-binary-as-hex to cause all binary
values to be displayed as text.) If either function argument is NULL,
the function returns NULL. If AES_DECRYPT() detects invalid data or
incorrect padding, it returns NULL. However, it is possible for
AES_DECRYPT() to return a non-NULL value (possibly garbage) if the
input data or the key is invalid.

These functions support the use of a key derivation function (KDF) to
create a cryptographically strong secret key from the information
passed in key_str. The derived key is used to encrypt and decrypt the
data, and it remains in the MySQL Server instance and is not accessible
to users. Using a KDF is highly recommended, as it provides better
security than specifying your own premade key or deriving it by a
simpler method as you use the function. The functions support HKDF
(available from OpenSSL 1.1.0), for which you can specify an optional
salt and context-specific information to include in the keying
material, and PBKDF2 (available from OpenSSL 1.0.2), for which you can
specify an optional salt and set the number of iterations used to
produce the key.

AES_ENCRYPT() and AES_DECRYPT() permit control of the block encryption
mode. The block_encryption_mode system variable controls the mode for
block-based encryption algorithms. Its default value is aes-128-ecb,
which signifies encryption using a key length of 128 bits and ECB mode.
For a description of the permitted values of this variable, see
https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html.
The optional init_vector argument is used to provide an initialization
vector for block encryption modes that require it.

Statements that use AES_ENCRYPT() or AES_DECRYPT() are unsafe for
statement-based replication.

If AES_ENCRYPT() is invoked from within the mysql client, binary
strings display using hexadecimal notation, depending on the value of
the --binary-as-hex. For more information about that option, see
https://dev.mysql.com/doc/refman/8.4/en/mysql.html.

The arguments for the AES_ENCRYPT() and AES_DECRYPT() functions are as
follows:

o str

  The string for AES_ENCRYPT() to encrypt using the key string key_str,
  or the key derived from it by the specified KDF. The string can be
  any length. Padding is automatically added to str so it is a multiple
  of a block as required by block-based algorithms such as AES. This
  padding is automatically removed by the AES_DECRYPT() function.

o crypt_str

  The encrypted string for AES_DECRYPT() to decrypt using the key
  string key_str, or the key derived from it by the specified KDF. The
  string can be any length. The length of crypt_str can be calculated
  from the length of the original string using this formula:

16 * (trunc(string_length / 16) + 1)

o key_str

  The encryption key, or the input keying material that is used as the
  basis for deriving a key using a key derivation function (KDF). For
  the same instance of data, use the same value of key_str for
  encryption with AES_ENCRYPT() and decryption with AES_DECRYPT().

  If you are using a KDF, key_str can be any arbitrary information such
  as a password or passphrase. In the further arguments for the
  function, you specify the KDF name, then add further options to
  increase the security as appropriate for the KDF.

  When you use a KDF, the function creates a cryptographically strong
  secret key from the information passed in key_str and any salt or
  additional information that you provide in the other arguments. The
  derived key is used to encrypt and decrypt the data, and it remains
  in the MySQL Server instance and is not accessible to users. Using a
  KDF is highly recommended, as it provides better security than
  specifying your own premade key or deriving it by a simpler method as
  you use the function.

  If you are not using a KDF, for a key length of 128 bits, the most
  secure way to pass a key to the key_str argument is to create a truly
  random 128-bit value and pass it as a binary value. For example:

INSERT INTO t
VALUES (1,AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3')));

  A passphrase can be used to generate an AES key by hashing the
  passphrase. For example:

INSERT INTO t
VALUES (1,AES_ENCRYPT('text', UNHEX(SHA2('My secret passphrase',512))));

  If you exceed the maximum key length of 128 bits, a warning is
  returned. If you are not using a KDF, do not pass a password or
  passphrase directly to key_str, hash it first. Previous versions of
  this documentation suggested the former approach, but it is no longer
  recommended as the examples shown here are more secure.

o init_vector

  An initialization vector, for block encryption modes that require it.
  The block_encryption_mode system variable controls the mode. For the
  same instance of data, use the same value of init_vector for
  encryption with AES_ENCRYPT() and decryption with AES_DECRYPT().

  *Note*:

  If you are using a KDF, you must specify an initialization vector or
  a null string for this argument, in order to access the later
  arguments to define the KDF.

  For modes that require an initialization vector, it must be 16 bytes
  or longer (bytes in excess of 16 are ignored). An error occurs if
  init_vector is missing. For modes that do not require an
  initialization vector, it is ignored and a warning is generated if
  init_vector is specified, unless you are using a KDF.

  The default value for the block_encryption_mode system variable is
  aes-128-ecb, or ECB mode, which does not require an initialization
  vector. The alternative permitted block encryption modes CBC, CFB1,
  CFB8, CFB128, and OFB all require an initialization vector.

  A random string of bytes to use for the initialization vector can be
  produced by calling RANDOM_BYTES(16).

o kdf_name

  The name of the key derivation function (KDF) to create a key from
  the input keying material passed in key_str, and other arguments as
  appropriate for the KDF. Optional.

  For the same instance of data, use the same value of kdf_name for
  encryption with AES_ENCRYPT() and decryption with AES_DECRYPT(). When
  you specify kdf_name, you must specify init_vector, using either a
  valid initialization vector, or a null string if the encryption mode
  does not require an initialization vector.

  The following values are supported:

  o hkdf

    HKDF, which is available from OpenSSL 1.1.0. HKDF extracts a
    pseudorandom key from the keying material then expands it into
    additional keys. With HKDF, you can specify an optional salt (salt)
    and context-specific information such as application details (info)
    to include in the keying material.

  o pbkdf2_hmac

    PBKDF2, which is available from OpenSSL 1.0.2. PBKDF2 applies a
    pseudorandom function to the keying material, and repeats this
    process a large number of times to produce the key. With PBKDF2,
    you can specify an optional salt (salt) to include in the keying
    material, and set the number of iterations used to produce the key
    (iterations).

  In this example, HKDF is specified as the key derivation function,
  and a salt and context information are provided. The argument for the
  initialization vector is included but is the empty string:

SELECT AES_ENCRYPT('mytext','mykeystring', '', 'hkdf', 'salt', 'info');

  In this example, PBKDF2 is specified as the key derivation function,
  a salt is provided, and the number of iterations is doubled from the
  recommended minimum:

SELECT AES_ENCRYPT('mytext','mykeystring', '', 'pbkdf2_hmac','salt', '2000');

o salt

  A salt to be passed to the key derivation function (KDF). Optional.
  Both HKDF and PBKDF2 can use salts, and their use is recommended to
  help prevent attacks based on dictionaries of common passwords or
  rainbow tables.

  A salt consists of random data, which for security must be different
  for each encryption operation. A random string of bytes to use for
  the salt can be produced by calling RANDOM_BYTES(). This example
  produces a 64-bit salt:

SET @salt = RANDOM_BYTES(8);

  For the same instance of data, use the same value of salt for
  encryption with AES_ENCRYPT() and decryption with AES_DECRYPT(). The
  salt can safely be stored along with the encrypted data.

o info

  Context-specific information for HKDF to include in the keying
  material, such as information about the application. Optional;
  available when you specify hkdf as the KDF name. HKDF adds this
  information to the keying material specified in key_str and the salt
  specified in salt to produce the key.

  For the same instance of data, use the same value of info for
  encryption with AES_ENCRYPT() and decryption with AES_DECRYPT().

o iterations

  The iteration count for PBKDF2 to use when producing the key.
  Optional; available when you specify pbkdf2_hmac as the KDF name. A
  higher count gives greater resistance to brute-force attacks because
  it has a greater computational cost for the attacker, but the same is
  necessarily true for the key derivation process. The default if you
  do not specify this argument is 1000, which is the minimum
  recommended by the OpenSSL standard.

  For the same instance of data, use the same value of iterations for
  encryption with AES_ENCRYPT() and decryption with AES_DECRYPT().

URL: https://dev.mysql.com/doc/refman/8.4/en/encryption-functions.html

