Início / dba / Perguntas / 263850
Accepted
cjones
Asked: 2020-03-29 13:24:49 +0800 CST

Convertendo MySQL AES_DECRYPT() para Postgres decrypt()

  • 772

Eu tenho esta consulta MySQL usando AES_DECRYPT()que é o seguinte:

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

Eu descobri que o equivalente no Postgres 11 é decrypt()(F.25.4. Raw Encryption Functions) e a consulta seria algo assim:

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

Pelo menos, a documentação de ambos me leva a acreditar que são equivalentes.

Os dados foram despejados do MySQL e importados para o PostgreSQL.

  • O data-pointcampo no MySQL é tipo varbinary(255)e todos os valores são mostrados BLOBno MySQL Workbench.
  • O data_pointcampo no Postgres é tipo byteae todos os valores são mostrados [binary data]no pgAdmin.

Se esses tipos de dados são equivalentes, admito, não tenho certeza.

A consulta MySQL funciona e encontra um registro com base nos critérios. O PostgreSQL não, embora o registro esteja lá. Então, parece uma de três coisas:

  1. Os tipos de dados ( varbinary(255)vs bytea) não são equivalentes
  2. Os dados não coincidem ( BLOBvs [binary data]) não são equivalentes
  3. As funções ( AES_DECRYPTvs decrypt()) não são equivalentes

Eu tentei descriptografar o valor no PostgreSQL apenas para ver o que está em um formato legível, tentando primeiro:

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

Mas isso acabou de retornar [binary data]para todas as linhas.

Então me deparei com esta resposta para usar convert_fromcomo em:

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

No entanto, apenas recebo este erro:

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

Então, neste ponto, estou perplexo sobre como resolver o problema principal. O principal problema é fazer o equivalente a AES_DECRYPTmas no PostgreSQL 11. A última mensagem de erro não é tanto um problema, mas provavelmente está relacionada a decrypt()não retornar os mesmos resultados.

Alguma sugestão?

mysql postgresql

2 respostas

  1. Na verdade, suspeito que todas as três suposições estão corretas e todas desempenham um papel no motivo pelo qual você não pode descriptografar os dados. Vou tentar explicar um pouco.

    Um dos fatores mais importantes na criptografia e descriptografia é a compatibilidade binária - para ser mais preciso, como cada banco de dados armazena fisicamente os dados? Os dados armazenados são big-endian ou little-endian, como as estruturas de dados internas são implementadas (qual é a diferença binária entre VARBINARYe BYTEA), qual é o conjunto de caracteres e a codificação em uso em cada sistema? A criptografia é sensível aos bits e, portanto, quaisquer variações na ordem de bits/bytes afetarão o resultado de qualquer criptografia ou descriptografia.

    Além disso, embora ambos os sistemas possam usar AES, suas configurações padrão variam um pouco. No MySQL, o padrão para AES é uma chave de 128 bits usando o modo ECB; enquanto no PostgreSQL o padrão é uma chave de 128 bits usando o modo CBC para criptografar os dados. Você precisará garantir que todas as opções usadas para criptografar/descriptografar sejam as mesmas (mesmo se você estivesse tentando descriptografá-lo no mesmo sistema).

    Logicamente, se tudo for idêntico entre os dois sistemas, você poderá descriptografar os dados. Na prática, será muito mais difícil do que isso e se você tiver acesso ao sistema de origem, descriptografe lá, exporte os dados, importe e criptografe no PostgreSQL.

    • 2
  2. Best Answer

    Sim, parece que você poderia usar pgcrypto decryptcom valores que foram criptografados com mysql's aes_encrypt.

    Exemplo:

    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)

    No 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)

    Quanto ao problema que você mencionou ao ver os dados:

    • O pgAdmin apenas exibe [dados binários] para conteúdos do tipo bytea.

    • Se estiver usando a ferramenta de linha de comando psql, ela exibirá uma representação hexadecimal do conteúdo (na verdade, depende do bytea_outputparâmetro, mas é hexadecimal por padrão).

    • Caso contrário, a função SQL encodepode fazer isso:

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

    • Se o conteúdo inicial (pré-criptografia) representar um pedaço de texto codificado de forma válida, use convert_from(binary_string, encoding)com a codificação correta. Fique longe de SQL_ASCII: é uma pseudo-codificação que não representa nenhuma codificação específica e um hack com muito poucos casos de uso legítimos.

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

    Pessoalmente, o que eu faria primeiro no seu caso é comparar o conteúdo das linhas de dados que exibem o problema no PostgreSQL vs MySQL antes da descriptografia, para validar o procedimento de exportação/importação. Se eles não corresponderem, é claro que eles não serão descriptografados com os mesmos valores.

    • 1

relate perguntas