How to use the mysql encryption functions aes_encrypt () and aes_decrypt ()

If you need to encrypt and decrypt some mysql fields, using the mysql encryption and decryption function may be more convenient than processing in the program.
The mysql-encrypt-funcs.png takes aes_encrypt () and aes_decrypt () as an example.

Note that versions of mysql5.5 and below only support the aes-128-ecb mode. If you need other modes that require mysql5.6 or later, you can specify them using the following mysql global variables:

Mysql> SET block_encryption_mode = 'aes-256-cbc ';
Mysql> SET @ key_str = SHA2 ('My secret passphrase ', 512 );
Mysql> SET @ init_vector = RANDOM_BYTES (16 );
Mysql> SET @ crypt_str = AES_ENCRYPT ('text', @ key_str, @ init_vector );
Mysql> SELECT AES_DECRYPT (@ crypt_str, @ key_str, @ init_vector );
+ ----------------------------------------------- +
| AES_DECRYPT (@ crypt_str, @ key_str, @ init_vector) |
+ ----------------------------------------------- +
| Text |
+ ----------------------------------------------- +

The reference documents are as follows:

Https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_aes-encrypt
Http://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_aes-encrypt

What types of encrypted binary data are stored in mysql fields (blob or varbinay )?
Reference document:

Encryption and compression functions return strings for which the result might contain arbitrary byte values. if you want to store these results, use a column with a VARBINARY or BLOB binary string data type. this will avoid potential problems with trailing space removal or character set conversion that wocould change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT ).

Try to use the blob type for the following reasons:

There is no trailing-space removal for BLOB columns when values are stored or retrieved.
For indexes on BLOB columns, you must specify an index prefix length.
BLOB columns can not have DEFAULT values.

How to insert binary data using SQL statements?

You cannot directly splice SQL inserts, or it will be treated as a string. You cannot insert binary data into hexadecimal or base64 formats. If yes, you also need to convert the binary data. However, stream data can be inserted through mysql prepared statement. For example, php pdo can be implemented as follows:

$ Db = new PDO ('odbc: sample', 'db2inst1', 'ibmdb ');
$ Stmt = $ db-> prepare ("insert into images (id, contenttype, imagedata) values (?, ?, ?) ");
$ Id = get_new_id (); // some function to allocate a new ID
 
// Assume that we are running as part of a file upload form
// You can find more information in the PHP documentation
 
$ Fp = fopen ($ _ FILES ['file'] ['tmp _ name'], 'RB ');
 
$ Stmt-> bindParam (1, $ id );
$ Stmt-> bindParam (2, $ _ FILES ['file'] ['type']);
$ Stmt-> bindParam (3, $ fp, PDO: PARAM_LOB );
 
$ Db-> beginTransaction ();
$ Stmt-> execute ();
$ Db-> commit ();