I'm trying to encrypt some fields on MySQL. I'm using the v2.8 version of TPC-DS and I'm trying to use AES on some columns of Customer Address table. Any idea how you can encrypt all the rows of the fields? I tried to use
UPDATE customer SET c_first_name = AES_ENCRYPT(c_first_name, key)
but I'm getting an error saying the Data too long for column c_first_name at row 1
The documentation recommends storing encrypted values in VARBINARY and BLOB type columns and also gives the following for calculating the required column size when using AES_ENCRYPT().
16 * (trunc(string_length / 16) + 1)
'string_length' is the bytes required to store the string, not the number of characters, which is dependent on the CHARACTER SET you are using. You'll get this information as character_octet_length in information_schema.columns
If you are using utf8mb4 as your character set, then your VARCHAR(12) c_first_name column would have character_octet_length of 48 and you would require VARBINARY(64) to accommodate any possible value.
mysql> SELECT 16 * (TRUNCATE(48 / 16, 0) + 1);
+---------------------------------+
| 16 * (TRUNCATE(48 / 16, 0) + 1) |
+---------------------------------+
| 64 |
+---------------------------------+
e.g.
mysql> SELECT LENGTH(AES_ENCRYPT(_utf8mb4'💩💩💩💩💩💩💩💩💩💩💩💩','salt')) AS v arbinary_length;
+------------------+
| varbinary_length |
+------------------+
| 64 |
+------------------+
You can use this to get the required VARBINARY column sizes for the customer table
SELECT `column_name`, `character_set_name`, `column_type`, `character_octet_length`,
16 * (TRUNCATE(`character_octet_length` / 16, 0) + 1) AS varbinary_size
FROM `information_schema`.`columns`
WHERE `table_name` = 'customer';
you can try below way
alter table customer modify c_first_name varbinary(200);
update customer
set c_first_name = aes_encrypt(c_first_name , 'secretyKey');
c_first_namehave?