Converting MySQL AES_DECRYPT() to PostgreSQL DECRYPT()

encryptionMySQLpostgresql

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?

Best Answer

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.

Related Question