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 typevarbinary(255)
and the values all showBLOB
in MySQL Workbench. - The
data_point
field in Postgres is typebytea
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:
- The data types (
varbinary(255)
vsbytea
) are not equivalent - The data does not match (
BLOB
vs[binary data]
) are not equivalent - The functions (
AES_DECRYPT
vsdecrypt()
) 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?