实际上

  mysql&gt ;，从语法上讲，第一个查询没有问题。 SET @@ SESSION.block_encryption_mode ='aes-256-cbc'; 

 mysql>创建表MyTable（
-> Encrypted_ID varbinary（256），
-> InitializationVector_iv varbinary（16）
->）; 
查询正常，受影响的0行（0.93秒）

 mysql> SET @iv = RANDOM_BYTES（16）; 
查询正常，受影响的0行（0.00秒）

 mysql>插入MyTable SET SET Encrypted_ID = AES_ENCRYPT（'hello'，'key'，@iv），InitializationVector_iv = @iv; 
查询正常，受影响1行（0.17秒）

 mysql>从MyTable中选择SELECT CAST（AES_DECRYPT（Encrypted_ID，'key'，InitializationVector_iv）AS CHAR）； 
 + --------------------------------------------- --------------------------- + 
 | CAST（AES_DECRYPT（Encrypted_ID，'key'，InitializationVector_iv）AS CHAR）| 
 + --------------------------------------------- --------------------------- + 
 |你好
 + --------------------------------------------- --------------------------- + 
集合中的1行（0.00秒）
 

关于为什么它不起作用，我设法在两种情况下使查询返回NULL。第一，如果您使用不同的iv进行加密和解密，则将返回NULL，因此您可能需要查看如何将其存储为iv。二，如果存储和尝试检索该值时，block_encryption_mode变量的设置不同，则会得到NULL，请确保您在会话之间没有意外地恢复为默认的'aes-128-ebc。可能还有其他...

第二个查询将失败，因为您需要同时向加密和解密函数提供iv，而仅使用它进行加密。另外，由于您正在从MyTable中获取值，因此Encrypted_ID已经被加密，并且此查询的作用是再次对其进行加密，然后再将其反转以使您返回到存储（加密）的值。

最后，AES仅使用 iv的16个字节，因此您***将其设为VARBINARY（16）。

I have table with: 1) Encrypted_ID varchar (256) 2) Initialization Vector(iv)varchar(256).

I would like to decrypt the column value using the key

I am using:

select Cast(AES_DECRYPT(Encrypted_ID,'Key',InitializationVector_iv)as CHAR ) as DecryptedValue from MyTable;

The result is Null.

I Also tried:

select Cast(AES_DECRYPT(AES_ENCRYPT(Encrypted_ID,'Key',InitializationVector_iv),'Key') as CHAR ) as DecryptedValue from MyTable;

The result is blob for few rows.

I'm not sure what I'm doing wrong here. Can any one help with the syntax to decrypt the column when I have:

1. Key
2. Initialization Vector value
3. Encrypted Column

There's actually nothing wrong with your first query, syntactically it's spot on as this worked example demonstrates.

mysql> SET @@SESSION.block_encryption_mode = 'aes-256-cbc';

mysql> create table MyTable(
    ->  Encrypted_ID varbinary(256),
    ->  InitializationVector_iv varbinary(16)
    -> );
Query OK, 0 rows affected (0.93 sec)

mysql> SET @iv = RANDOM_BYTES(16);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO MyTable SET Encrypted_ID = AES_ENCRYPT('hello','key', @iv), InitializationVector_iv = @iv;
Query OK, 1 row affected (0.17 sec)

mysql> SELECT CAST(AES_DECRYPT(Encrypted_ID,'key', InitializationVector_iv) AS CHAR) from MyTable;
+------------------------------------------------------------------------+
| CAST(AES_DECRYPT(Encrypted_ID,'key', InitializationVector_iv) AS CHAR) |
+------------------------------------------------------------------------+
| hello                                                                  |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

As for why it's not working, I managed to get the query to return NULL in 2 scenarios. One, you get NULL returned if you use a different iv for encryption and decryption, so you might want to look at how you are storing as the iv. Two, you get NULL where you have the block_encryption_mode variable set differently when storing and trying to retrieve the value, check that you're not accidentally reverting to the default 'aes-128-ebc between sessions. There may be others...

The second query will fail because you need to supply the iv to both of he encryption and decryption functions, you only use it to encrypt. Also, since you are taking the values from the MyTable, Encrypted_ID will already be encrypted and the effect of this query would be to encrypt it again, before reversing that to get you back to the stored (encrypted) value.

Finally, AES is only going to use 16 bytes of the iv so you might as well make that VARBINARY(16).