主页 / dba / 问题 / 263850
Accepted
cjones
Asked: 2020-03-29 13:24:49 +0800 CST

将 MySQL AES_DECRYPT() 转换为 Postgres 解密()

  • 772

我使用以下 MySQL 查询AES_DECRYPT()：

SELECT * 
FROM data_tbl
WHERE AES_DECRYPT(data_point,'aBcDeF')='data_1';

我已经发现 Postgres 11 中的等价物是decrypt()(F.25.4. Raw Encryption Functions)，查询看起来像这样：

SELECT * 
FROM data_tbl
AND decrypt(data_point,'aBcDeF','aes')='data_1';

至少，两者的文档让我相信它们是等价的。

数据从 MySQL 转储并导入 PostgreSQL。

  • MySQL 中的data-point字段是类型varbinary(255)，值都显示BLOB在 MySQL Workbench 中。
  • Postgres 中的data_point字段是类型bytea，值都显示[binary data]在 pgAdmin 中。

我承认，我不确定这些数据类型是否等效。

MySQL 查询工作并根据他的条件查找记录。PostgreSQL 没有，尽管记录在那里。所以它似乎是三件事之一：

  1. 数据类型 ( varbinary(255)vs bytea) 不等价
  2. 数据不匹配 ( BLOBvs [binary data]) 不等价
  3. 函数 ( AES_DECRYPTvs decrypt()) 不等价

我确实尝试通过首先尝试解密 PostgreSQL 中的值，以查看它的可读格式：

SELECT decrypt(data_point, 'aBcDeF', 'aes') 
FROM data_tbl;

但这只是[binary data]为所有行返回。

然后我遇到了这个答案，使用convert_from如下：

SELECT convert_from(decrypt(data_point, 'aBcDeF', 'aes'), 'SQL_ASCII')
FROM data_tbl;

但是，我只是收到此错误：

ERROR:  invalid byte sequence for encoding "UTF8": 0xcf 0xf5
SQL state: 22021

所以在这一点上，我对如何解决主要问题感到困惑。主要问题是在 PostgreSQL 11 中进行等效AES_DECRYPT操作。最后一条错误消息不是什么大问题，但它可能与decrypt()不返回相同的结果有关。

有什么建议么？

mysql postgresql

2 个回答

  1. 实际上，我怀疑您的所有三个假设都是正确的，并且都在您无法解密数据的原因中发挥了作用。我将尝试解释一下。

    加密和解密中最重要的因素之一是二进制兼容性——更准确地说，每个数据库如何物理存储数据？数据是大端还是小端存储，内部数据结构是如何实现的（VARBINARY和之间的二进制区别BYTEA是什么），每个系统上使用的字符集和编码是什么？加密是位敏感的，因此位/字节顺序的任何变化都会影响任何加密或解密的结果。

    此外，即使两个系统都可以使用 AES，它们的默认设置也略有不同。在 MySQL 上，AES 的默认值是使用 ECB 模式的 128 位密钥；而在 PostgreSQL 上，默认是使用 CBC 模式加密数据的 128 位密钥。您将需要确保用于加密/解密的所有选项都是相同的（即使您尝试在同一系统中对其进行解密）。

    从逻辑上讲，如果两个系统之间的一切都相同，您应该能够解密数据。在实践中，它会比这更难，如果你可以访问源系统，在那里解密，导出数据，然后在 PostgreSQL 中导入和加密它。

    • 2
  2. Best Answer

    是的，您似乎可以将 pgcryptodecrypt与用 mysql 加密的值一起使用aes_encrypt。

    例子：

    mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

mysql> select aes_encrypt(unhex('AA'), unhex('BB'));
+------------------------------------------------------------------------------+
| aes_encrypt(unhex('AA'), unhex('BB'))                                        |
+------------------------------------------------------------------------------+
| 0xB10FF3B381FFA2025B603CEFCB04590A                                           |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    在 PostgreSQL 中：

    test=> select version();
                                                              version                                                              
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.7 (Ubuntu 11.7-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)


test=> \dx pgcrypto 
             List of installed extensions
   Name   | Version | Schema |       Description       
----------+---------+--------+-------------------------
 pgcrypto | 1.3     | public | cryptographic functions
(1 row)

test=> select decrypt('\xB10FF3B381FFA2025B603CEFCB04590A'::bytea, '\xbb'::bytea, 'aes');
 decrypt 
---------
 \xaa
(1 row)

    至于您提到的查看数据的麻烦：

    • pgAdmin 只显示类型内容的 [二进制数据] bytea。

    • 如果使用命令行工具psql，它将显示内容的十六进制表示（实际上取决于bytea_output参数，但默认为十六进制）。

    • 否则，SQL 函数encode可以做到这一点：

      test=> select encode('foobar'::bytea, 'hex');
    encode    
--------------
 666f6f626172

    • 如果初始（预加密）内容表示一段经过有效编码的文本，请使用convert_from(binary_string, encoding)正确的编码。远离SQL_ASCII：它是一种不代表任何特定编码的伪编码，并且是一种很少有合法用例的hack。

      test=> select convert_from('\x666f6f626172'::bytea, 'UTF-8');
 convert_from 
--------------
 foobar
(1 row)

    就您个人而言，我首先要做的是在解密之前比较 PostgreSQL 和 MySQL 中出现问题的数据行的内容，以验证导出/导入过程。如果它们不匹配，当然它们不会解密为相同的值。

    • 1

相关问题