MySql WorkBench AES 256解密 [英] MySql WorkBench AES 256 Decryption

问题描述

我的桌子有：
1）Encrypted_ID varchar（256）
2）初始化向量（iv）varchar（256）。

我想使用密钥解密列值

我正在使用：

 从MyTable中选择Cast（AES_DECRYPT（Encrypted_ID，'Key'，InitializationVector_iv）as CHAR）作为DecryptedValue; 
  

结果为Null。

I还尝试过：

 选择Cast（AES_DECRYPT（AES_ENCRYPT（Encrypted_ID，'Key'，InitializationVector_iv），'Key'）as CHAR））作为MyTable中的DecryptedValue； 
  

结果是少数行的斑点。

<我不确定我在做什么错。当我有以下内容时，任何人都可以提供有关对列进行解密的语法的帮助吗？

1. Key


2. 初始化向量值


3. 加密列

解决方案

实际上

  mysql> ;，从语法上讲，第一个查询没有问题。 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).

这篇关于MySql WorkBench AES 256解密的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！