Converting MySQL AES_DECRYPT() to Postgres decrypt()

Question

I have this MySQL query using AES_DECRYPT() that is the following:

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

I've figured out as much that the equivalent in Postgres 11 is decrypt() (F.25.4. Raw Encryption Functions) and the query would look something like this:

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

At least, the documentation for both leads me to believe that these are equivalent.

The data was dumped from MySQL and imported into PostgreSQL.

• The data-point field in MySQL is type varbinary(255) and the values all show BLOB in MySQL Workbench.
• The data_point field in Postgres is type bytea and the values all show [binary data] in pgAdmin.

Whether those data types are equivalent, I admit, I'm not sure.

The MySQL query works and finds a record based on he criteria. The PostgreSQL does not, although the record is in there. So it seems like one of three things:

1. The data types (varbinary(255) vs bytea) are not equivalent
2. The data does not match (BLOB vs [binary data]) are not equivalent
3. The functions (AES_DECRYPT vs decrypt()) are not equivalent

I did try to decrypt the value in PostgreSQL just to see what it is in a readable format by first trying:

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

But that just returned [binary data] for all the rows.

Then I came across this answer to use convert_from as in:

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

However, I just get this error:

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

So at this point I'm stumped as to how to resolve the primary issue. The primary issue being doing the equivalent of AES_DECRYPT but in PostgreSQL 11. The last error message isn't so much an issue, but it is likely related to decrypt() not returning the same results.

Any suggestions?

Answer 1

Yes, it seems you could use pgcrypto's decrypt with values that were encrypted with mysql's aes_encrypt.

Example:

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)

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

As for the trouble you mention with seeing the data:

• pgAdmin does just display [binary data] for contents of type bytea.

• If using the command-line tool psql, it will display an hexadecimal representation of the contents (actually it depends on the bytea_output parameter, but it's hex by default).

• Otherwise, the SQL function encode can do it:

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

• If the initial (pre-encryption) contents represent a piece of text validly encoded, use convert_from(binary_string, encoding) with the correct encoding. Stay away from SQL_ASCII: it's a pseudo-encoding that does not represent any particular encoding, and a hack with very few legitimate use cases.

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

Personally what I'd do first in your case is to compare the contents of the data rows that exhibit the problem in PostgreSQL vs MySQL before decryption, to validate the export/import procedure. If they don't match, of course they won't decrypt to the same values.

Answer 2

Actually, I suspect all three of your assumptions are correct and all play a part in why you cannot decrypt the data. I will attempt to explain a little.

One of the most important factors in encryption & decryption is binary compatibility - to be more precise, how does each database physically store the data? Is the data stored big-endian or little-endian, how are the internal data structures implemented (what is the binary difference between VARBINARY and BYTEA), what is the character set & encoding in use on each system? Encryption is bit-sensitive and so any variations in the bit/byte order will affect the outcome of any encryption or decryption.

Additionally, even though both systems can use AES their default settings vary slightly. On MySQL the default for AES is a 128bit key using ECB mode ; whereas on PostgreSQL the default is a 128bit key using CBC mode to encrypt the data. You will need to ensure that all of the options used to encrypt/decrypt are the same (even if you were trying to decrypt it within the same system).

Logically, if everything was identical between the two systems you should be able to decrypt the data. In practice, it's going to be a lot harder than that and if you have access to the source system, decrypt in on there, export the data, then import & encrypt it in PostgreSQL.